`scripts/geocode-nominatim.js`

110 lines
#!/usr/bin/env node
/**
 * geocode-nominatim.js
 *
 * Backfill lat/lng for the 38 corridor-seeded rows the cross-reference
 * pass couldn't match. Hits OSM Nominatim (free, 1 req/sec policy). Idempotent
 * — only updates rows where latitude IS NULL.
 *
 * Nominatim policy: max 1 req/sec, real User-Agent required, no bulk geocoding.
 * 38 rows × 1.2s/req ≈ 50s total — well within fair use.
 */

require('dotenv').config({ path: require('path').resolve(__dirname, '..', '.env') });
const db = require('../lib/db');

const UA = 'ventura-claw-leads/0.1 (steve@designerwallcoverings.com)';
const SLEEP_MS = 1100; // > 1 req/sec

function sleep(ms) { return new Promise(r => setTimeout(r, ms)); }

// Strip unit/suite/space/office tokens — Nominatim wants
// "<number> <street>" only. LA BTRC addresses include things like
// "21021 Ventura Blvd Office #20" or "17047 1/2 Ventura Blvd" that
// confuse the geocoder.
function cleanStreet(s) {
  if (!s) return s;
  return s
    // Drop suite/unit-style suffixes anywhere in the string.
    .replace(/\s*\b(?:suite|ste|unit|apt|space|office|front|rear)\b\s*#?\s*\S*/gi, '')
    // Drop bare # tokens (e.g. "#P9", "#20", "#203").
    .replace(/\s*#\s*\S+/g, '')
    // "17047 1/2 Ventura Blvd" → "17047 Ventura Blvd"
    .replace(/(\d+)\s+\d+\/\d+\b/, '$1')
    .replace(/\s+,/g, ',')
    .replace(/\s+/g, ' ')
    .trim();
}

async function geocode(query) {
  const url = `https://nominatim.openstreetmap.org/search?q=${encodeURIComponent(query)}&format=json&limit=1&countrycodes=us`;
  const r = await fetch(url, { headers: { 'User-Agent': UA } });
  if (!r.ok) throw new Error(`nominatim ${r.status}`);
  const arr = await r.json();
  if (!arr.length) return null;
  return { lat: parseFloat(arr[0].lat), lng: parseFloat(arr[0].lon), display: arr[0].display_name };
}

(async () => {
  const rows = await db.many(`
    SELECT id, slug, business_name, street, city, zip
      FROM businesses
     WHERE source='public_records' AND status='active' AND latitude IS NULL
     ORDER BY id
  `);
  console.log(`[nominatim] ${rows.length} stragglers to geocode`);

  let ok = 0, miss = 0, err = 0;
  for (let i = 0; i < rows.length; i++) {
    const r = rows[i];
    const street = cleanStreet(r.street);
    // Try 2-step: (1) clean street + city + state + zip, (2) clean street + city + state.
    const queries = [
      `${street}, ${r.city}, CA ${r.zip || ''}`.trim(),
      `${street}, ${r.city}, CA`
    ];

    let result = null;
    for (const q of queries) {
      try {
        result = await geocode(q);
        if (result) break;
        await sleep(SLEEP_MS);
      } catch (e) {
        err++;
        console.error(`[${r.slug}] ${e.message}`);
      }
    }
    if (!result) {
      miss++;
      console.log(`  miss  ${r.slug}  (${street}, ${r.city})`);
      await sleep(SLEEP_MS);
      continue;
    }

    // Sanity-check: result must be inside SoCal bounding box. Otherwise reject.
    if (result.lat < 33.5 || result.lat > 34.5 || result.lng < -119.5 || result.lng > -117.5) {
      miss++;
      console.log(`  out-of-box  ${r.slug}  (${result.lat},${result.lng})`);
      await sleep(SLEEP_MS);
      continue;
    }

    await db.query(
      `UPDATE businesses SET latitude=$1, longitude=$2 WHERE id=$3 AND latitude IS NULL`,
      [result.lat, result.lng, r.id]
    );
    ok++;
    console.log(`  ok    ${r.slug}  (${result.lat.toFixed(5)}, ${result.lng.toFixed(5)})`);
    await sleep(SLEEP_MS);
  }

  console.log(`[nominatim] ok=${ok} miss=${miss} err=${err}`);
  await db.pool.end();
  process.exit(0);
})().catch(async (err) => {
  console.error('[nominatim] fatal', err);
  try { await db.pool.end(); } catch (_) {}
  process.exit(1);
});