TGAZ Historical Gazetteer Query Skill

Setup: Get the Database

The database is 228 MB, so it is not stored in this repo. Keep one copy on the machine at ~/.local/share/sino-authorities/tgaz.db and point everything there.

query_tgaz.py finds it on its own. It looks in this order and uses the first one that exists:

1. the path given with --db
2. $TGAZ_DB, if that variable is set
3. tgaz.db in the current directory
4. tgaz.db next to query_tgaz.py
5. ~/.local/share/sino-authorities/tgaz.db (the shared copy)

Check whether the shared copy is already there:

ls -lh ~/.local/share/sino-authorities/tgaz.db 2>/dev/null

If it is missing, download it once from Hugging Face (https://huggingface.co/datasets/kltng/tgaz-sqlite). No git-lfs needed:

python3 scripts/query_tgaz.py setup            # 62 MB download, unzips to 228 MB
python3 scripts/query_tgaz.py check            # is a newer release out?
python3 scripts/query_tgaz.py setup --force    # download again (e.g. to update)
python3 scripts/query_tgaz.py setup --db PATH  # put it somewhere else

setup checks both SHA-256 checksums (the zip and the database) and writes tgaz.db.release.json next to the database, so check knows which release you have. It uses only the Python standard library.

By hand: download latest.json from the dataset, then the zip it names, and unzip it.

Older copies came from the GitHub repo kltng/tgaz-sqlite through Git LFS. A clone made without git-lfs holds a small text "pointer" file instead of the database. query_tgaz.py checks for this and tells you to run setup.

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, scripts/query_tgaz.py is fastest. Run it via Bash from the skill directory:

# Search by name (simplified, traditional, or romanized)
python3 scripts/query_tgaz.py "北京"
python3 scripts/query_tgaz.py "蘇州"          # traditional forms come from the spelling table
python3 scripts/query_tgaz.py "Chang'an"      # spaces and apostrophes are ignored
python3 scripts/query_tgaz.py "suzhou" --year 1100   # also finds "Su Zhou"

# Leave out rows with broken coordinates (see "Data Cautions")
python3 scripts/query_tgaz.py "蘇州" --good-coords

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

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

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

# Feature type + year
# (--feature-type takes xian, 县, or an English type such as county)
python3 scripts/query_tgaz.py --feature-type xian --year 1820 --limit 20
python3 scripts/query_tgaz.py --feature-type county --year 1820 --limit 20

# Children of a jurisdiction (uses part_of, so it follows the year)
python3 scripts/query_tgaz.py --parent hvd_9659 --year 1820
python3 scripts/query_tgaz.py --parent hvd_30037 --year 1600   # Ming Zhejiang: 11 prefectures

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

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

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

# Database statistics
python3 scripts/query_tgaz.py --stats

# Raw SQL (read-only; must start with SELECT or WITH)
python3 scripts/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"

How name search works

• It matches the start of a name. 苏州 finds 苏州 and 苏州府.
• It looks in the simplified name, the romanized name, and every form in the spelling table. So 蘇州 (traditional) works as well as 苏州.
• Romanized input ignores case, spaces and apostrophes. TGAZ writes some names with a space and others without: 苏州 (589-1112) is "Su Zhou", 苏州府 (from 1367) is "Suzhou Fu". It also mixes "Changan" and "Chang'an". suzhou finds all of them.
• If nothing matches, the CLI tries full-text search. It prints a note to stderr when it does this. --year and --feature-type still apply.
• With --year, the parent_* columns show the parent in that year (from part_of). Without --year, they show the earliest parent only.

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:

For the parent in a given year, use part_of (it has begin_year and end_year). Example: 杭州府 (hvd_32337) has 江南行省 in mv_pn_srch, but in 1600 its parent was 浙江布政使司 (hvd_30037).

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)
  • The CLI also accepts English types. It looks the word up in ftype.name_en (exact match) and searches the TGAZ terms it maps to: county → xian, gun, uezd; prefecture → zhou, fu, di qu, ken. --fts does the same when the plain query finds nothing, since only the TGAZ terms are indexed. In raw SQL, use the TGAZ terms.

Data Cautions

Measured in the 2026-03-25 release of tgaz.db (on 2026-10-26):

• Broken coordinates. 6,004 rows are obj_type = 'POLYGON'. Almost all of them (6,003) have longitude 0 or near 0, and 1,099 also have latitude 0. They repeat a place that also has a POINT row with good coordinates (for example hvd_90239 repeats 苏州 hvd_32071). 122 other rows also have longitude 0 (most are 0,0). In all, 6,125 rows are affected. On a map these land near Africa. The CLI adds a bad_coords column (1 = broken) and --good-coords drops those rows. In SQL, add AND ABS(CAST(x_coord AS REAL)) >= 10 AND CAST(y_coord AS REAL) <> 0.
• End year before begin year. 251 rows have end_yr < beg_yr, for example hvd_112536 吴郡 runs from 129 to 0. Year filters skip them.
• Earliest parent only in mv_pn_srch; see above.

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.

Feedback and Citation

Report a problem. This skill is part of the public repository kltng/humanities-skills. If it fails (an API changed, a script errors, or these instructions are wrong), report it as a GitHub issue:

1. Ask the user before filing. Issues are public.
2. Check for an existing report: gh issue list --repo kltng/humanities-skills --state all --search "tgaz-sqlite"
3. File it: gh issue create --repo kltng/humanities-skills --label bug --title "[tgaz-sqlite] <short summary>" --body "<details>". Without gh, give the user this link: https://github.com/kltng/humanities-skills/issues/new?template=skill-problem.yml
4. Include the skill name and version, what you ran, the error text, what you expected, and the environment (OS, Python version). Leave out API keys, personal data, private file paths, and unpublished research.

Cite. If this skill helped work that is published, please cite it, together with the data source it uses:

Tang, Kwok-leong. humanities-skills: tgaz-sqlite (version <version at the top of this file>). GitHub, <year>. https://github.com/kltng/humanities-skills

When you help a user write up results that relied on this skill, remind them once about this citation.