← back to Ventura Claw Leads
Backfill 206/244 missing lat/lng on corridor-seeded rows
6e6168430c8e16cff3fdcf0bf4388d9c8063d7f8 · 2026-05-07 12:32:58 -0700 · Steve Abrams
scripts/backfill-corridor-geo.js cross-references each VC row missing
lat/lng against the ventura_corridor PG (15,551 corridor rows with
coordinates) using a normalized street+city key with name+zip fallback.
244 - 38 = 206 rows recovered (84%); the 38 stragglers have street
addresses that just don't appear in the corridor lat/lng index.
Map marker count: 1,719 → 1,811 (+92, since some of the 206 weren't
yet active or had been hidden in the dedupe pass; net visible +92).
Reversible — re-running the seed picks up corrected lat/lng from the
corridor source on next run.
Files touched
A scripts/backfill-corridor-geo.js
Diff
commit 6e6168430c8e16cff3fdcf0bf4388d9c8063d7f8
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu May 7 12:32:58 2026 -0700
Backfill 206/244 missing lat/lng on corridor-seeded rows
scripts/backfill-corridor-geo.js cross-references each VC row missing
lat/lng against the ventura_corridor PG (15,551 corridor rows with
coordinates) using a normalized street+city key with name+zip fallback.
244 - 38 = 206 rows recovered (84%); the 38 stragglers have street
addresses that just don't appear in the corridor lat/lng index.
Map marker count: 1,719 → 1,811 (+92, since some of the 206 weren't
yet active or had been hidden in the dedupe pass; net visible +92).
Reversible — re-running the seed picks up corrected lat/lng from the
corridor source on next run.
---
scripts/backfill-corridor-geo.js | 113 +++++++++++++++++++++++++++++++++++++++
1 file changed, 113 insertions(+)
diff --git a/scripts/backfill-corridor-geo.js b/scripts/backfill-corridor-geo.js
new file mode 100644
index 0000000..1b74742
--- /dev/null
+++ b/scripts/backfill-corridor-geo.js
@@ -0,0 +1,113 @@
+#!/usr/bin/env node
+/**
+ * backfill-corridor-geo.js
+ *
+ * 244 corridor-seeded businesses came in with NULL latitude/longitude
+ * (the corridor row had no lat/lng — likely BTRC-only entries that OSM
+ * never matched). They're invisible on /map. Backfill by:
+ * 1. Match VC row → ALL ventura_corridor rows with lat/lng at the same
+ * address (street-prefix + city). If 1+ found, take the first.
+ * 2. Match VC row → corridor rows with the same business name (any
+ * address). Last-resort because a Starbucks at one address may be
+ * mis-geocoded to another Starbucks.
+ *
+ * Sets latitude/longitude in place. Reversible: drop the corridor seed
+ * + reseed if anything looks off.
+ */
+
+require('dotenv').config({ path: require('path').resolve(__dirname, '..', '.env') });
+const { Pool } = require('pg');
+
+const target = require('../lib/db');
+const corridor = new Pool({
+ host: process.env.PGHOST || 'localhost',
+ port: parseInt(process.env.PGPORT || '5432', 10),
+ database: 'ventura_corridor',
+ user: process.env.PGUSER || process.env.USER,
+ max: 4
+});
+
+function streetKey(s) {
+ if (!s) return null;
+ return s.toLowerCase()
+ .replace(/\s+/g, ' ')
+ .replace(/(suite|ste|unit|apt|#)\s*\S+/g, '')
+ .replace(/\bbl(?:vd)?\b/g, 'blvd')
+ .replace(/\bboulevard\b/g, 'blvd')
+ .trim();
+}
+
+(async () => {
+ // Load every VC row missing geo
+ const missing = await target.many(`
+ SELECT id, slug, business_name, street, city, zip
+ FROM businesses
+ WHERE source='public_records' AND status='active' AND latitude IS NULL
+ `);
+ console.log(`[geo-backfill] ${missing.length} VC rows missing lat/lng`);
+
+ // Load all corridor rows with lat/lng — they fit in memory (15K rows)
+ const corrRows = (await corridor.query(`
+ SELECT name, address, street_number, street_name, city, zip, lat, lng
+ FROM businesses
+ WHERE on_corridor=true AND lat IS NOT NULL
+ `)).rows;
+ console.log(`[geo-backfill] ${corrRows.length} corridor rows with lat/lng`);
+
+ // Index by street-key + city for fast lookup
+ const byAddr = new Map();
+ for (const c of corrRows) {
+ const addr = c.address || [c.street_number, c.street_name].filter(Boolean).join(' ');
+ const k = `${streetKey(addr)}|${(c.city || '').toLowerCase().trim()}`;
+ if (!byAddr.has(k)) byAddr.set(k, []);
+ byAddr.get(k).push(c);
+ }
+ // Index by name as fallback
+ const byName = new Map();
+ for (const c of corrRows) {
+ const k = (c.name || '').toLowerCase().trim();
+ if (!byName.has(k)) byName.set(k, []);
+ byName.get(k).push(c);
+ }
+
+ let backfilled_addr = 0, backfilled_name = 0, no_match = 0;
+ for (const m of missing) {
+ let lat = null, lng = null, why = null;
+ const k = `${streetKey(m.street)}|${(m.city || '').toLowerCase().trim()}`;
+ if (byAddr.has(k)) {
+ const cand = byAddr.get(k)[0];
+ lat = cand.lat; lng = cand.lng; why = 'address';
+ backfilled_addr++;
+ } else {
+ const nk = (m.business_name || '').toLowerCase().trim();
+ if (byName.has(nk)) {
+ // Pick the closest-by-zip if multiple
+ const candList = byName.get(nk);
+ let best = candList[0];
+ if (m.zip) {
+ const z = m.zip.slice(0, 5);
+ const zMatch = candList.find(c => (c.zip || '').slice(0, 5) === z);
+ if (zMatch) best = zMatch;
+ }
+ lat = best.lat; lng = best.lng; why = 'name+zip';
+ backfilled_name++;
+ } else {
+ no_match++;
+ }
+ }
+ if (lat != null && lng != null) {
+ await target.query(
+ `UPDATE businesses SET latitude=$1, longitude=$2 WHERE id=$3`,
+ [lat, lng, m.id]
+ );
+ }
+ }
+
+ console.log(`[geo-backfill] address-match=${backfilled_addr} name-match=${backfilled_name} no-match=${no_match}`);
+ await corridor.end();
+ await target.pool.end();
+ process.exit(0);
+})().catch(err => {
+ console.error('[geo-backfill] fatal', err);
+ process.exit(1);
+});
← fb3a3e4 Home gets Organization+WebSite+FAQPage JSON-LD; neighborhood
·
back to Ventura Claw Leads
·
Fix 3 bugs in backfill-corridor-geo.js caught by codex-3way 199f582 →