`src/ingest/regions_seed.ts`

145 lines
/**
 * Seeds the region anchor table:
 *   - all ~3,144 counties from the Census Gazetteer counties file (GEOID = 5-digit FIPS)
 *   - all ~935 CBSAs (metro + micro) from the Census Gazetteer CBSA file
 *   - 50 states + DC statically
 * Gazetteer zips hold one tab-delimited txt; parsed via `unzip -p` (no zip dep).
 * Idempotent: ON CONFLICT (canonical_key) DO UPDATE.
 */
import { execFileSync } from 'node:child_process';
import { query, pool } from '../../db/pool.ts';
import { openRun, closeRun, download } from './run.ts';

const GAZ_BASE = 'https://www2.census.gov/geo/docs/maps-data/data/gazetteer/2024_Gazetteer';
const COUNTIES_ZIP = `${GAZ_BASE}/2024_Gaz_counties_national.zip`;
const CBSA_ZIP = `${GAZ_BASE}/2024_Gaz_cbsa_national.zip`;

const STATES: Array<[string, string, string]> = [
  ['AL','01','Alabama'],['AK','02','Alaska'],['AZ','04','Arizona'],['AR','05','Arkansas'],
  ['CA','06','California'],['CO','08','Colorado'],['CT','09','Connecticut'],['DE','10','Delaware'],
  ['DC','11','District of Columbia'],['FL','12','Florida'],['GA','13','Georgia'],['HI','15','Hawaii'],
  ['ID','16','Idaho'],['IL','17','Illinois'],['IN','18','Indiana'],['IA','19','Iowa'],
  ['KS','20','Kansas'],['KY','21','Kentucky'],['LA','22','Louisiana'],['ME','23','Maine'],
  ['MD','24','Maryland'],['MA','25','Massachusetts'],['MI','26','Michigan'],['MN','27','Minnesota'],
  ['MS','28','Mississippi'],['MO','29','Missouri'],['MT','30','Montana'],['NE','31','Nebraska'],
  ['NV','32','Nevada'],['NH','33','New Hampshire'],['NJ','34','New Jersey'],['NM','35','New Mexico'],
  ['NY','36','New York'],['NC','37','North Carolina'],['ND','38','North Dakota'],['OH','39','Ohio'],
  ['OK','40','Oklahoma'],['OR','41','Oregon'],['PA','42','Pennsylvania'],['RI','44','Rhode Island'],
  ['SC','45','South Carolina'],['SD','46','South Dakota'],['TN','47','Tennessee'],['TX','48','Texas'],
  ['UT','49','Utah'],['VT','50','Vermont'],['VA','51','Virginia'],['WA','53','Washington'],
  ['WV','54','West Virginia'],['WI','55','Wisconsin'],['WY','56','Wyoming'],
];

// Connecticut switched to planning-region FIPS (09110+) in the 2024 Gazetteer, but
// Zillow/Redfin still publish by legacy county (09001-09015) and the choropleth
// GeoJSON carries legacy county shapes — seed both so CT data lands and renders.
const LEGACY_CT_COUNTIES: Array<[string, string, number, number]> = [
  ['09001', 'Fairfield County', 41.27, -73.39],
  ['09003', 'Hartford County', 41.81, -72.73],
  ['09005', 'Litchfield County', 41.79, -73.24],
  ['09007', 'Middlesex County', 41.43, -72.52],
  ['09009', 'New Haven County', 41.35, -72.90],
  ['09011', 'New London County', 41.47, -72.10],
  ['09013', 'Tolland County', 41.85, -72.34],
  ['09015', 'Windham County', 41.83, -71.99],
];

function parseGazetteer(zipPath: string): Array<Record<string, string>> {
  const text = execFileSync('unzip', ['-p', zipPath], { maxBuffer: 64 * 1024 * 1024 }).toString('utf8');
  const lines = text.split('\n').filter((l) => l.trim().length > 0);
  const headers = lines[0].split('\t').map((h) => h.trim());
  return lines.slice(1).map((line) => {
    const cells = line.split('\t');
    const row: Record<string, string> = {};
    headers.forEach((h, i) => { row[h] = (cells[i] ?? '').trim(); });
    return row;
  });
}

async function upsertRegion(r: {
  region_type: string; canonical_key: string; name: string;
  state_code?: string | null; fips?: string | null; cbsa_code?: string | null;
  lat?: number | null; lng?: number | null;
}): Promise<void> {
  await query(
    `INSERT INTO region (region_type, canonical_key, name, state_code, fips, cbsa_code, lat, lng)
     VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
     ON CONFLICT (canonical_key) DO UPDATE
       SET name = EXCLUDED.name, state_code = EXCLUDED.state_code,
           fips = EXCLUDED.fips, cbsa_code = EXCLUDED.cbsa_code,
           lat = EXCLUDED.lat, lng = EXCLUDED.lng`,
    [r.region_type, r.canonical_key, r.name, r.state_code ?? null, r.fips ?? null,
     r.cbsa_code ?? null, r.lat ?? null, r.lng ?? null],
  );
}

async function main() {
  const runId = await openRun('census_gazetteer', COUNTIES_ZIP);
  let upserted = 0;
  try {
    // states (static)
    for (const [code, fips, name] of STATES) {
      await upsertRegion({ region_type: 'state', canonical_key: `state:${code}`, name, state_code: code, fips });
      upserted++;
    }

    // counties
    const counties = await download(COUNTIES_ZIP, 'gaz_counties_2024.zip', { reuseCached: true });
    const countyRows = parseGazetteer(counties.path);
    if (countyRows.length < 3000) throw new Error(`county gazetteer parsed only ${countyRows.length} rows — format drift?`);
    for (const row of countyRows) {
      const fips = row['GEOID'];
      if (!fips || fips.length !== 5) continue;
      // last header often carries trailing whitespace in the raw file; keys are trimmed at parse
      const lat = parseFloat(row['INTPTLAT'] ?? '');
      const lng = parseFloat(row['INTPTLONG'] ?? '');
      await upsertRegion({
        region_type: 'county',
        canonical_key: `county:${fips}`,
        name: row['NAME'],
        state_code: row['USPS'],
        fips,
        lat: Number.isFinite(lat) ? lat : null,
        lng: Number.isFinite(lng) ? lng : null,
      });
      upserted++;
    }

    // legacy CT counties (coexist with the planning regions)
    for (const [fips, name, lat, lng] of LEGACY_CT_COUNTIES) {
      await upsertRegion({ region_type: 'county', canonical_key: `county:${fips}`, name, state_code: 'CT', fips, lat, lng });
      upserted++;
    }

    // metros (CBSA — metro + micro areas)
    const cbsa = await download(CBSA_ZIP, 'gaz_cbsa_2024.zip', { reuseCached: true });
    const cbsaRows = parseGazetteer(cbsa.path);
    if (cbsaRows.length < 800) throw new Error(`cbsa gazetteer parsed only ${cbsaRows.length} rows — format drift?`);
    for (const row of cbsaRows) {
      const code = row['GEOID'];
      if (!code) continue;
      const lat = parseFloat(row['INTPTLAT'] ?? '');
      const lng = parseFloat(row['INTPTLONG'] ?? '');
      await upsertRegion({
        region_type: 'metro',
        canonical_key: `metro:${code}`,
        name: row['NAME'],
        cbsa_code: code,
        lat: Number.isFinite(lat) ? lat : null,
        lng: Number.isFinite(lng) ? lng : null,
      });
      upserted++;
    }

    await closeRun(runId, 'ok', { upserted, fileHash: counties.sha256, notes: `counties=${countyRows.length} cbsa=${cbsaRows.length} states=${STATES.length}` });
    console.log(`regions seeded: ${upserted} upserts (${countyRows.length} counties, ${cbsaRows.length} CBSAs, ${STATES.length} states)`);
  } catch (e: any) {
    await closeRun(runId, 'failed', { notes: String(e.message || e) });
    throw e;
  } finally {
    await pool.end();
  }
}

main().catch((e) => { console.error(e); process.exit(1); });