Block-Group ↔ H3 Crosswalk + hex-level CNT H+T (hex_hta)

Built: 2026-06-12 on geocoder → commute DB (gtfs3, 172.26.1.152:5433) Status: SHIPPED — closes the long-flagged "blkgrp↔H3 needs TIGER geometries" item from the CNT H+T load. Scope: CT (09), NJ (34), NY (36), PA (42) — the four states of the CNT NYC CBSA (NY-NJ-PA delineation incl. Pike County PA) plus their full state extents, res 9 + res 10. → NATIONAL since 2026-06-12 (overnight rollout): all 50 states + DC at res 9; res 10 in every CBSA county. See "NATIONAL ROLLOUT" section at the bottom — totals there supersede the 4-state numbers below.

What this gives you

Any H3 cell (res 9 or 10) in NY/NJ/CT/PA now joins to its 2020-census block group and, through it, to every CNT H+T 2022 metric. One view does it:

select * from hex_hta where h3 = '892a100d67bffff' and res = 9;
-- h3, res, blkgrp_geoid, ht_ami, h_ami, t_ami, t_cost_ami, transit_cost_ami,
-- autos_per_hh_ami, vmt_per_hh_ami, pct_transit_commuters_ami, cbsa_name

This is the missing piece that lets the GPU RAPTOR hex products (res-10 NYC commute maps, TLCcosts H+U+C) pull CNT affordability context per hex with zero geometry work at query time.

Objects created (commute DB)

Everything else (hta_index, hex_demographics, …) was touched read-only.

Sources

• Geometries: Census TIGER/Line 2022 block groups — https://www2.census.gov/geo/tiger/TIGER2022/BG/tl_2022_{09,34,36,42}_bg.zip (no API key; TIGER2022+ carries the 2020 delineation, which is exactly what CNT H+T 2022 GEOIDs use). Zips archived in crosswalk/raw/ (151 MB). Native CRS NAD83 (EPSG:4269) → ST_Transform → 4326 at load.
• Attributes: hta_index (CNT H+T 2022, loaded 2026-06-12; 239,178 blkgrp rows national; 35,300 in these four states — 259 TIGER BGs have no CNT row, the usual zero-household BGs: parks, water, airports, prisons).

Shapefile feature counts (match TIGER row-for-row): CT 2,717 · NJ 6,599 · NY 16,070 · PA 10,173 = 35,559, all valid geometries, 0 ST_MakeValid needed.

Method

1. load_tiger_blkgrp.py (geopandas/pyogrio in the gpu-pilot venv) → CSV with NAD83 WKB hex (5.8 s for all four states).
2. sql/01_tiger_blkgrp.sql — temp-stage \copy, then ST_Multi(ST_Transform(ST_SetSRID(geom,4269),4326)) into tiger_blkgrp (~7 s).
3. sql/02_crosswalk.sql — h3_polygon_to_cells(geom, res) from h3-pg 4.2.3's h3_postgis extension. H3 v4 polygon-to-cells is centroid containment: a cell belongs to the BG that contains its center, so each cell maps to exactly one block group (PK (h3,res) enforces it; ON CONFLICT DO NOTHING absorbs degenerate boundary ties). res 9 ≈ 0.105 km²/cell, res 10 ≈ 0.015 km²/cell.
4. sql/03_hex_hta.sql — plain view; hta_index join keys (geoid, year=2022, level='blkgrp').
5. sql/04_validate.sql — coverage both directions + spot checks (results below).

The pg↔python H3 agreement was verified: h3_latlng_to_cell(point(lng,lat),res) in h3-pg returns bit-identical cells to h3-py 4.5 (892a100d67bffff for Times Square res 9). h3-pg native point is (x=lng, y=lat) — PostGIS axis order, not lat-first.

Validation results (sql/04_validate.sql, full output in validate_run.log)

Forward (cell → block group):

• hex_demographics nyc (all res 10, the product grid): 1,310,600 / 1,310,600 = 100.00 % matched. 1,310,563 (99.997 %) also carry a CNT row through hex_hta.
• Raw nyc_tristate_h3_res9.parquet grid (535,471 cells, unfiltered bbox grid): 385,678 = 72.03 % matched. The 149,793 unmatched are the grid's ocean rectangle, not a defect: every one sits at lat ≤ 40.78 (the grid's southern Atlantic band); 86.6 % are open Atlantic south of LI/NJ, 512 in the Delaware Bay corner. In a 2,000-cell sample, 92.4 % lie > 5 km from any block group, 7.0 % are the 0.5–5 km offshore fringe, 0.65 % within 500 m of shore (centers just offshore). Land-cell coverage is effectively complete.

Reverse (block group → ≥1 cell):

3,254 BGs have no res-9 cell (NYC's tiny 1–2-block BGs are smaller than a 26-acre res-9 hex); 3,156 of them (97 %) recover at res 10. 98 BGs — all NY, all micro-BGs smaller than a 3.7-acre res-10 hex — have no cell at either res; their territory is absorbed by neighboring BGs' cells, which is the correct centroid-containment behavior for hex products (use res 11 or point-on-surface assignment if a complete BG→cell map is ever needed).

Spot checks (h3-pg h3_latlng_to_cell verified bit-identical to h3-py):

• Times Square (40.758, −73.9855) → res 9 892a100d67bffff → BG 360610119001 (Manhattan 36061 ✓): ht_ami 12 %, t_ami 5 %, transit_cost_ami $2,736/yr, 30 % transit commuters — the classic hyper-transit Manhattan profile. res 10 lands in 360610119002 with the same shape (ht 10/t 5/$2,765).
• Piscataway NJ (40.5478, −74.4734) → 892a1069a97ffff → BG 340230006031 (Middlesex 34023 ✓): ht_ami 57 %, t_ami 18 %, transit_cost_ami $3/yr, 1 % transit commuters — car suburbia, exactly as expected.

CBSA sanity: plain per-cell avg ht_ami over the 1.31M nyc cells = 55.2 (area-weighted — exurban acreage dominates cell counts). Household-weighted over the same cells' BGs = 43.0, matching the CBSA row's ht_ami = 43 exactly. t_ami: cells 18.0 vs CBSA 13 (same area-vs-household effect).

Integrity: 0 crosswalk geoids missing from tiger_blkgrp; 93.58 % of cells (both res) join a CNT blkgrp row.

Runtime

How to extend nationally (the remaining 47 states)

The loop is state-FIPS-parameterized end to end; nothing in the SQL is region-specific:

1. curl https://www2.census.gov/geo/tiger/TIGER2022/BG/tl_2022_{fips}_bg.zip for the other 47 states + DC (+ 72 PR if wanted — CNT covers PR partially). Total national TIGER BG set ≈ 242K BGs, ~1.1 GB of zips.
2. load_tiger_blkgrp.py <fips> … → CSV → same 01 SQL (append new \copy lines or loop).
3. Re-run the two crosswalk INSERTs — they're idempotent (ON CONFLICT DO NOTHING), only new geoids generate new cells. Parallelize by running one psql per state list if wanted; the inserts don't contend (different h3 ranges).
4. hex_hta needs no change — hta_index is already national (239,178 blkgrp rows).

Measured basis: 4 states = 297,526 km² of BG territory → 22.0M cells in 6.1 min of polyfill+insert (≈ 60K cells/s single psql stream), 2,551 MB on disk.

National (50 states + DC ≈ 9.83 M km², ×33 area scale):

Plus: 51 TIGER zips ≈ 1.1 GB (~10 min download), national tiger_blkgrp 242,335 BGs ≈ 700 MB (~2 min load), final PK/geoid indexes ≈ 15 min at res-10 volume. Practical recommendation: res 9 everywhere + res 10 only in CBSA counties (where the hex products live) keeps it ≈ 25 GB.

Alaska gotcha for the rollout: the Aleutians cross the antimeridian; H3 polyfill mishandles transmeridian polygons unless they're split at ±180° first (ST_Split or shift-lon trick) — handle statefp 02 specially or polyfill it with h3-py's transmeridian-aware path.

Bulk alternative: IPUMS NHGIS API (key at ~/.ipums_api_key) serves single national block-group shapefiles per vintage (https://api.ipums.org/extracts/ with {"shapefiles":["us_blck_grp_2022_tl2022"]} — one ~900 MB national file instead of 51 zips). Useful if Census FTP throttles; same schema, same loader.

Files

crosswalk/
├── CROSSWALK.md              ← this doc
├── load_tiger_blkgrp.py      ← shapefile → staging CSV (WKB hex, NAD83)
├── raw/tl_2022_{09,34,36,42}_bg[.zip|/]   ← TIGER source of record
├── staging/                  ← CSVs (regenerable; safe to delete)
├── crosswalk_build.log       ← timed psql output of the 02 build
└── sql/
    ├── 01_tiger_blkgrp.sql   ← DDL + load
    ├── 02_crosswalk.sql      ← h3_postgis polyfill, res 9 + 10
    ├── 03_hex_hta.sql        ← the view
    └── 04_validate.sql       ← validation suite (rerunnable)

NATIONAL ROLLOUT — 2026-06-12 (overnight, geocoder → gtfs3)

Extended the crosswalk from 4 states to all 50 states + DC per the rollout plan above. Same method, same two tables, zero schema changes; hex_hta view needed no change.

What changed

Disk pressure: none — PG data dir is /data/postgres/main on a 1.8 TB NVMe with 1.6 TB still free (the earlier "df -h /" check was looking at the wrong mount; root barely moved).

Note: the doc's earlier "≈242,335 BGs national" was an estimate; the actual TIGER2022 national BG count is 239,781 — per-state DB counts match the shapefile feature counts exactly, all 51.

res-10 scope decision (documented choice)

res 10 was built ONLY for counties inside a CBSA, derived read-only from hta_index: level='county' AND year=2022 AND cbsa_name non-empty AND cbsa_name matches a real level='cbsa' row → 1,836 counties (metro + micropolitan). The 6 CT planning regions whose cbsa_name is a comma-separated multi-CBSA list (the known cbsa_name-list gotcha: 09110–09190) were excluded by that filter — harmless, CT is already full-state res 10 from the 4-state build. Verified post-build: 1,836 / 1,836 CBSA counties have res-10 cells (100%). Heads-up: metro+micro CBSA counties cover 4.5M km² (~half the lower 48), which is why res 10 came in at 311M rows — well above the doc's old "≈25 GB total" ballpark; actual total is 45 GB. Fine on /data.

Alaska disposition

AK has exactly one transmeridian BG: 020160001001 (Aleutians West, 28 parts, bbox −179.23…179.86). sql/05_ak_res9.sql polyfills AK in two passes: (1) the 503 normal BGs straight through; (2) the transmeridian BG clipped into west/east lobes with two envelopes (−180,45,−128,76) and (165,45,180,76), ST_Dump per lobe, polyfill per part — 294,172 cells in 6 s. Nothing was skipped: 504/504 AK BGs have res-9 cells (17,532,608 total). AK's 5 CBSA counties (Anchorage, Mat-Su, Fairbanks, Juneau, Ketchikan) are all far east of the antimeridian, so res 10 took the plain path. AK was the res-9 long pole: 23.1 min (giant North Slope BGs).

Runtime (wall clock, 2026-06-12 UTC)

Aggregate insert throughput ≈ 60–120K cells/s over 381M new rows with both indexes live. Failures: zero — all 47 downloads, 47 state loads, 47 res-9 txns, 47 res-10 txns, AK both passes OK. (Logging nit: drivers used psql -qAt, which suppresses INSERT tags, so per-state row counts in res9_build.log/res10_build.log are blank — counts below were pulled from the DB afterwards.)

Validation (full output: validate_national.log)

• Area sanity: per-state res-9 cells vs (aland+awater)/0.105 ratio ∈ [0.87, 1.12] for all 51 — no outliers.
• Reverse coverage (BG → ≥1 res-9 cell): ≥99% in 41 states; the only sub-96% are the known micro-BG cases — DC 88.4%, HI 92.7%, NY 82.6% (NYC's 1–2-block BGs, unchanged from 4-state build).
• hex_hta spot checks (new states): Chicago Loop → 170318391001 (ht 40/t 8), LA downtown → 060372073061 (ht 30/t 11), Seattle → 530330082001 (ht 35/t 9), Denver + Miami resolve with correct CBSAs (their BGs have NULL ht_ami in CNT itself).
• CNT-join coverage: 97.45% of res-9 and 96.21% of res-10 cells carry a CNT blkgrp row (better than the 4-state build's 93.6%); hex_hta now serves 89,974,921 res-9 + 299,122,249 res-10 CNT-joined cells.
• Integrity: 0 crosswalk geoids missing from tiger_blkgrp.

Per-state row counts

New files from the rollout

crosswalk/
├── download_national.sh            ← 47-state TIGER fetch + extract (idempotent)
├── build_res9_national.sh          ← res-9 driver: 1 txn/state, 3 parallel psql streams
├── build_res10_cbsa.sh             ← res-10 driver: CBSA-county filter read from hta_index
├── resume_res9.sh                  ← idempotent resume helper (unused — zero failures)
├── res9_build.log / res9_ak.log / res10_build.log / validate_national.log
├── staging/cbsa_counties.csv       ← the 1,836-county list (statefp,countyfp,geoid,cbsa_name)
├── staging/per_state_counts.txt    ← post-build per-state cell counts by res
└── sql/
    ├── 01b_tiger_blkgrp_national.sql   ← 47-state \copy + insert
    ├── 05_ak_res9.sql                  ← AK antimeridian two-envelope split
    └── 06_validate_national.sql        ← national validation suite (rerunnable)