`src/ingest/zip_county.ts`

51 lines
/**
 * zip_county — national ZIP (ZCTA) -> primary county FIPS crosswalk that powers ZIP
 * lookup in the top location search (/api/geo-search). Source: Census 2020 ZCTA-to-county
 * relationship file (pipe-delimited, keyless, free). A ZIP can span counties; we keep the
 * county with the largest land overlap (AREALAND_PART). Idempotent — rebuilds each run.
 * Run: `npm run ingest:zipcounty` (local) or on prod after deploy.
 */
import { query, pool } from '../../db/pool.ts';

const SRC = 'https://www2.census.gov/geo/docs/maps-data/data/rel2020/zcta520/tab20_zcta520_county20_natl.txt';

async function main() {
  console.log('[zip_county] downloading Census ZCTA→county relationship file…');
  const res = await fetch(SRC);
  if (!res.ok) throw new Error('download failed: HTTP ' + res.status);
  const text = await res.text();
  const lines = text.split('\n');
  // fields (0-based): 1=GEOID_ZCTA5 (zip), 9=GEOID_COUNTY (fips), 16=AREALAND_PART
  const best = new Map<string, { fips: string; area: number }>();
  for (let i = 1; i < lines.length; i++) {
    const f = lines[i].split('|');
    if (f.length < 17) continue;
    const zip = (f[1] || '').trim();
    const fips = (f[9] || '').trim();
    const area = Number(f[16] || 0);
    if (!/^\d{5}$/.test(zip) || !/^\d{5}$/.test(fips)) continue;
    const cur = best.get(zip);
    if (!cur || area > cur.area) best.set(zip, { fips, area });
  }
  console.log(`[zip_county] parsed ${best.size} unique ZIPs`);
  await query(`CREATE TABLE IF NOT EXISTS zip_county (zip text PRIMARY KEY, county_fips text NOT NULL)`);
  await query(`TRUNCATE zip_county`);
  const entries = [...best.entries()];
  const CHUNK = 1000;
  for (let i = 0; i < entries.length; i += CHUNK) {
    const slice = entries.slice(i, i + CHUNK);
    const vals: string[] = [];
    const params: string[] = [];
    slice.forEach(([zip, v], j) => { vals.push(`($${j * 2 + 1},$${j * 2 + 2})`); params.push(zip, v.fips); });
    await query(
      `INSERT INTO zip_county (zip, county_fips) VALUES ${vals.join(',')}
         ON CONFLICT (zip) DO UPDATE SET county_fips = EXCLUDED.county_fips`,
      params,
    );
  }
  const c = await query<{ n: string }>(`SELECT count(*)::text AS n FROM zip_county`);
  console.log(`[zip_county] loaded ${c.rows[0].n} rows`);
  await pool.end();
}
main().catch(e => { console.error(e); process.exit(1); });