`src/ingest/cbsa_crosswalk.ts`

63 lines
/**
 * Backfills region.cbsa_code for COUNTY rows from the Census CBSA delineation
 * file (list1_2023.xlsx) — county FIPS -> CBSA code. Fixes the null metro name
 * on property/location panels (M-P1 report nit).
 */
import { query, pool } from '../../db/pool.ts';
import { openRun, closeRun, download } from './run.ts';
import { createRequire } from 'node:module';

const require_ = createRequire(import.meta.url);
const XLSX = require_('xlsx');

const URL_ = 'https://www2.census.gov/programs-surveys/metro-micro/geographies/reference-files/2023/delineation-files/list1_2023.xlsx';

async function main() {
  const runId = await openRun('cbsa_crosswalk', URL_);
  try {
    const { path, sha256 } = await download(URL_, 'list1_2023.xlsx', { reuseCached: true });
    const wb = XLSX.readFile(path);
    const sheet = wb.Sheets[wb.SheetNames[0]];
    const rows: any[][] = XLSX.utils.sheet_to_json(sheet, { header: 1 });
    // header row is a few rows down in this file — find it by the 'CBSA Code' cell
    const hIdx = rows.findIndex(r => Array.isArray(r) && r.some(c => String(c).trim() === 'CBSA Code'));
    if (hIdx < 0) throw new Error('header row not found — file format drift?');
    const header = rows[hIdx].map((h: any) => String(h).trim());
    const col = (name: string) => header.indexOf(name);
    const cCbsa = col('CBSA Code'), cStF = col('FIPS State Code'), cCoF = col('FIPS County Code');
    if (cCbsa < 0 || cStF < 0 || cCoF < 0) throw new Error('expected columns missing: ' + header.join('|'));

    const pairs: [string, string][] = [];
    for (const r of rows.slice(hIdx + 1)) {
      if (!Array.isArray(r) || r[cCbsa] == null || r[cStF] == null || r[cCoF] == null) continue;
      const cbsa = String(r[cCbsa]).trim();
      const fips = String(r[cStF]).padStart(2, '0') + String(r[cCoF]).padStart(3, '0');
      if (/^\d{5}$/.test(cbsa) && /^\d{5}$/.test(fips)) pairs.push([fips, cbsa]);
    }
    if (pairs.length < 1500) throw new Error(`only ${pairs.length} county-CBSA pairs parsed — drift?`);

    let updated = 0;
    for (let i = 0; i < pairs.length; i += 500) {
      const batch = pairs.slice(i, i + 500);
      const params: unknown[] = [];
      const values = batch.map(([f, c], j) => { params.push(f, c); return `($${j * 2 + 1},$${j * 2 + 2})`; });
      const r = await query(
        `UPDATE region rg SET cbsa_code = v.cbsa
           FROM (VALUES ${values.join(',')}) AS v(fips, cbsa)
          WHERE rg.region_type = 'county' AND rg.fips = v.fips`,
        params,
      );
      updated += r.rowCount ?? 0;
    }
    await closeRun(runId, 'ok', { upserted: updated, fileHash: sha256, notes: `${pairs.length} county-CBSA pairs` });
    console.log(`[cbsa_crosswalk] ok: ${updated} county regions updated from ${pairs.length} pairs`);
  } 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); });