CBSA crosswalk backfill: 1915 counties get cbsa_code from Census delineation file (fixes null metro on property Location panel)

12bd5ee3d94155dcbdad18774dfa47fa4ef83d47 · 2026-07-21 14:34:02 -0700 · Steve Abrams

Files touched

A src/ingest/cbsa_crosswalk.ts

Diff

commit 12bd5ee3d94155dcbdad18774dfa47fa4ef83d47
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Jul 21 14:34:02 2026 -0700

    CBSA crosswalk backfill: 1915 counties get cbsa_code from Census delineation file (fixes null metro on property Location panel)
---
 src/ingest/cbsa_crosswalk.ts | 62 ++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 62 insertions(+)

diff --git a/src/ingest/cbsa_crosswalk.ts b/src/ingest/cbsa_crosswalk.ts
new file mode 100644
index 0000000..2d69e03
--- /dev/null
+++ b/src/ingest/cbsa_crosswalk.ts
@@ -0,0 +1,62 @@
+/**
+ * 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); });

← d86921e M-P1 property-level detail layer: 13-section property.html + · back to Nationalrealestate · auto-save: 2026-07-21T14:38:01 (1 files) — src/enrich/firm_w bf3451d →