`scripts/backfill-corridor-geo.js`

128 lines
#!/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. Skip rows whose
  // streetKey() comes back empty — otherwise every empty-addr row
  // collapses into a single "|city" bucket and matches unrelated
  // corridor entries with the same gap, producing wrong lat/lng.
  // (PROSECUTOR caught this in the codex-3way debate.)
  const byAddr = new Map();
  for (const c of corrRows) {
    const addr = c.address || [c.street_number, c.street_name].filter(Boolean).join(' ');
    const sk = streetKey(addr);
    if (!sk) continue;
    const k = `${sk}|${(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 msk = streetKey(m.street);
    // Same null-street-key guard on the lookup side — otherwise empty-addr
    // VC rows match the wrong corridor row.
    const k = msk ? `${msk}|${(m.city || '').toLowerCase().trim()}` : null;
    if (k && 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(async (err) => {
  console.error('[geo-backfill] fatal', err);
  // Close pools before exit — leaving them open can hold the process up to
  // the idleTimeoutMillis. Best-effort: catch and swallow close errors.
  // (PROSECUTOR caught this in the codex-3way debate.)
  try { await corridor.end(); } catch (_) {}
  try { await target.pool.end(); } catch (_) {}
  process.exit(1);
});