tgaz-query

name: tgaz-query description: > Query and explore the TGAZ (Temporal Gazetteer) SQLite database of 82,000+ historical Chinese placenames spanning 763 BCE to 1911 CE. Use this skill whenever the user asks about historical Chinese places, administrative geography, dynastic jurisdictions, place name evolution, or wants to query tgaz.db. Also trigger when the user mentions CHGIS, TGAZ, historical gazetteer, Chinese historical GIS, or asks questions like "what was X called in dynasty Y", "what counties existed in year Z", "where was X located", or any spatial/temporal query about Chinese historical geography. This skill is relevant even for casual questions like "tell me about ancient Chang'an" or "Tang dynasty cities near the Yellow River".

TGAZ Historical Gazetteer Query Skill

Setup: Get the Database

Before running any queries, check that tgaz.db and query_tgaz.py exist in the current directory. If they don't, clone the repo:

# Check if files exist
ls tgaz.db query_tgaz.py 2>/dev/null

# If not found, clone the repo (requires git-lfs for the 228 MB database)
git lfs install
git clone https://github.com/kltng/tgaz-sqlite.git /tmp/tgaz-sqlite
cp /tmp/tgaz-sqlite/tgaz.db /tmp/tgaz-sqlite/query_tgaz.py .

If the user is already inside the tgaz-sqlite repo directory, everything is ready — skip the clone.

You have access to tgaz.db, a SQLite database containing the full China Historical GIS (CHGIS) dataset from Harvard & Fudan University. It has 82,117 placenames, 245,042 name spellings, 83,400 jurisdictional relationships, and 25,655 historical notes covering 763 BCE to 1911 CE.

Quick Start: Use the CLI

For common lookups, query_tgaz.py is fastest. Run it via Bash:

# Search by name (Chinese or romanized)
python3 query_tgaz.py "北京"
python3 query_tgaz.py "Chang'an"

# Name + temporal filter
python3 query_tgaz.py "长安" --year -200

# Full-text search (searches names, feature types, parent names)
python3 query_tgaz.py --fts "county Beijing"

# Spatial query: bounding box (lon_min,lat_min,lon_max,lat_max)
python3 query_tgaz.py --bbox 108,34,110,36 --year -200

# Feature type + year
python3 query_tgaz.py --feature-type county --year 1820 --limit 20

# Children of a jurisdiction
python3 query_tgaz.py --parent hvd_9659 --year 1820

# All name forms for a place (simplified, traditional, pinyin)
python3 query_tgaz.py --spellings hvd_70621

# Full history: parents, predecessors, present location, notes
python3 query_tgaz.py --history hvd_70621

# Output as JSON (for further processing or LLM context)
python3 query_tgaz.py "长安" --format json

# Database statistics
python3 query_tgaz.py --stats

# Raw SQL
python3 query_tgaz.py --sql "SELECT name, transcription, beg_yr, end_yr FROM mv_pn_srch WHERE ftype_tr = 'fu' AND beg_yr <= 1400 AND end_yr >= 1400 ORDER BY name"

When to Use SQL Directly

Use --sql or open tgaz.db directly when:

• Aggregating (COUNT, GROUP BY, temporal distributions)
• Joining across multiple tables (e.g., spellings + notes + present location)
• Queries the CLI doesn't support (predecessor chains, multi-hop parent traversal)

Schema Essentials

The go-to table: mv_pn_srch

This pre-joined table has everything for 90% of queries:

Other key tables

Useful views

FTS5 indexes

Data Conventions

• Years: Negative integers = BCE. -202 means 202 BCE. 9999 means "still existing."
• sys_id format: hvd_NNNNN for CHGIS, TBRC_GNNNN for Tibetan data, HGR_NNNNN for Russian data.
• Coordinates: Stored as TEXT. Use CAST(x_coord AS REAL) for math. x = longitude, y = latitude.
• Feature types: The ftype_tr field has romanized types. Common ones:
  • xian (县, county), fu (府, prefecture), jun (郡, commandery)
  • sheng (省, province), lu (路, circuit), dao (道, circuit/route)
  • zhou (州, prefecture), guo (国, kingdom/state), dgon pa (monastery)

Common Query Patterns

"What was this area called in year X?"

SELECT name, transcription, ftype_tr, beg_yr, end_yr
FROM mv_pn_srch
WHERE CAST(x_coord AS REAL) BETWEEN 116.0 AND 117.0
  AND CAST(y_coord AS REAL) BETWEEN 39.5 AND 40.5
  AND beg_yr <= 1400 AND end_yr >= 1400
ORDER BY ftype_tr;

"Show the administrative hierarchy of place X"

-- Get the place and all its parents over time
SELECT m.name AS child_name, m.transcription AS child_trans,
       po.begin_year, po.end_year,
       pm.name AS parent_name, pm.transcription AS parent_trans,
       pm.ftype_tr AS parent_type
FROM part_of po
JOIN placename c ON po.child_id = c.id
JOIN placename p ON po.parent_id = p.id
JOIN mv_pn_srch m ON m.sys_id = c.sys_id
JOIN mv_pn_srch pm ON pm.sys_id = p.sys_id
WHERE c.sys_id = 'hvd_70621'
ORDER BY po.begin_year;

"What did this place used to be called?"

-- Predecessor chain
SELECT prev.sys_id, m.name, m.transcription, m.beg_yr, m.end_yr
FROM prec_by pb
JOIN placename curr ON pb.placename_id = curr.id
JOIN placename prev ON pb.prec_id = prev.id
JOIN mv_pn_srch m ON m.sys_id = prev.sys_id
WHERE curr.sys_id = 'hvd_70626';

"All spellings of a place across scripts"

SELECT s.written_form, sc.name AS script, sc.lang, tr.name AS trsys
FROM spelling s
JOIN placename p ON s.placename_id = p.id
LEFT JOIN script sc ON s.script_id = sc.id
LEFT JOIN trsys tr ON s.trsys_id = tr.id
WHERE p.sys_id = 'hvd_70621'
ORDER BY sc.lang;

"Counties per dynasty/period"

SELECT
  CASE
    WHEN beg_yr BETWEEN -221 AND -207 THEN 'Qin'
    WHEN beg_yr BETWEEN -206 AND 8 THEN 'Western Han'
    WHEN beg_yr BETWEEN 25 AND 220 THEN 'Eastern Han'
    WHEN beg_yr BETWEEN 618 AND 907 THEN 'Tang'
    WHEN beg_yr BETWEEN 960 AND 1279 THEN 'Song'
    WHEN beg_yr BETWEEN 1271 AND 1368 THEN 'Yuan'
    WHEN beg_yr BETWEEN 1368 AND 1644 THEN 'Ming'
    WHEN beg_yr BETWEEN 1644 AND 1911 THEN 'Qing'
    ELSE 'Other'
  END AS dynasty,
  COUNT(*) AS county_count
FROM mv_pn_srch
WHERE ftype_tr = 'xian'
GROUP BY dynasty
ORDER BY MIN(beg_yr);

Presenting Results

When answering user questions about historical places:

1. Lead with the answer in natural language, then show supporting data.
2. Include temporal context — say "during the Tang dynasty (618-907)" not just "in 700 CE."
3. Note name changes — if a place had different names in different periods, mention the evolution.
4. Use both Chinese and romanized names — e.g., "长安县 (Chang'an Xian)."
5. Mention modern location when relevant — use --history or present_loc table.
6. For JSON output to feed into other tools, use --format json or --format jsonl.