`scripts/geocode-via-nominatim.js`

142 lines
#!/usr/bin/env node
// Forward-geocode every shop with an address but no lat/lng using OSM
// Nominatim. Free, 1 req/sec policy enforced. Runs idempotently — already-
// geocoded rows are skipped. Built to run overnight; safe to kill + restart.
//
// On hit: UPDATE businesses SET latitude/longitude + source_data_json.
// On miss: record the attempt in source_data_json.geocode_attempt='miss'
// so we don't re-hit Nominatim every night for an unresolvable address.
//
//   node scripts/geocode-via-nominatim.js                # full run
//   LIMIT=500 node scripts/geocode-via-nominatim.js      # cap for testing
//   DRY=1 node scripts/geocode-via-nominatim.js          # show queries only
//
// Nominatim's usage policy:
//   - max 1 req/sec
//   - require valid User-Agent + email
//   - identify yourself in the UA string
//   - cache results (we do — DB write is the cache)
// https://operations.osmfoundation.org/policies/nominatim/

import 'dotenv/config';
import { query, one } from '../src/lib/db.js';

const NOMINATIM = 'https://nominatim.openstreetmap.org/search';
const UA = 'la-salon-directory/1.0 (steveabramsdesigns@gmail.com)';
const SLEEP_MS = 1100; // 1.1s — slight buffer above 1 req/sec
const LIMIT = process.env.LIMIT ? parseInt(process.env.LIMIT, 10) : null;
const DRY = process.env.DRY === '1';

function ts() { return new Date().toISOString().replace('T', ' ').slice(0, 19); }
function log(m) { console.log(`[${ts()}] ${m}`); }
function sleep(ms) { return new Promise(r => setTimeout(r, ms)); }

// Build the canonical Nominatim query string. Cleans BBCB ALL-CAPS to
// title-case to match OSM's data shape, drops obvious unit/suite suffixes
// that confuse the geocoder.
function buildQuery(b) {
  const addr = String(b.address || '').trim()
    .replace(/\s*(STE|SUITE|UNIT|APT|#)\s*[\w-]+/gi, '')   // drop "STE 100", "#5", etc.
    .replace(/\s+/g, ' ')
    .toLowerCase()
    .replace(/\b\w/g, c => c.toUpperCase());                // title-case
  const city = (b.city || '').trim().toLowerCase().replace(/\b\w/g, c => c.toUpperCase());
  const zip = (b.zip || '').trim().slice(0, 5);
  return [addr, city, 'CA', zip].filter(Boolean).join(', ');
}

async function geocode(b) {
  const q = buildQuery(b);
  if (!q || q.length < 5) return { hit: false, reason: 'no-address' };
  const url = `${NOMINATIM}?format=jsonv2&limit=1&addressdetails=0&countrycodes=us&q=${encodeURIComponent(q)}`;
  if (DRY) {
    return { hit: true, dry: true, q, url };
  }
  let res;
  try {
    res = await fetch(url, { headers: { 'User-Agent': UA, 'Accept': 'application/json' } });
  } catch (e) {
    return { hit: false, reason: 'fetch-error: ' + (e.message || '').slice(0, 80) };
  }
  if (!res.ok) {
    if (res.status === 429) return { hit: false, reason: '429-throttled' };
    return { hit: false, reason: 'http-' + res.status };
  }
  const json = await res.json().catch(() => null);
  if (!Array.isArray(json) || json.length === 0) return { hit: false, reason: 'no-result' };
  const top = json[0];
  const lat = parseFloat(top.lat), lon = parseFloat(top.lon);
  if (!Number.isFinite(lat) || !Number.isFinite(lon)) return { hit: false, reason: 'bad-coords' };
  // Sanity-check: result should be in CA-ish lat/lng box (roughly 32.5-42 lat,
  // -124.5 to -114 lng). Drops out-of-state false positives that Nominatim
  // sometimes returns when the in-state address has no exact match.
  if (lat < 32.0 || lat > 42.5 || lon < -125 || lon > -113) {
    return { hit: false, reason: 'out-of-CA: ' + lat.toFixed(2) + ',' + lon.toFixed(2) };
  }
  return { hit: true, lat, lon, importance: top.importance, display: top.display_name };
}

async function main() {
  log(`mode=${DRY ? 'DRY' : 'WRITE'}${LIMIT ? ` limit=${LIMIT}` : ''}`);
  const rows = await query(
    `SELECT id, slug, name, address, city, state, zip
       FROM businesses
      WHERE latitude IS NULL
        AND address IS NOT NULL AND address <> ''
        AND city IS NOT NULL AND city <> ''
        AND (source_data_json->>'geocode_attempt') IS NULL
      ORDER BY id
      ${LIMIT ? `LIMIT ${LIMIT}` : ''}`
  );
  log(`fetched ${rows.rows.length} candidate rows · estimated runtime: ${(rows.rows.length * SLEEP_MS / 1000 / 60).toFixed(0)} min`);

  let hits = 0, misses = 0, errored = 0;
  let i = 0;
  for (const r of rows.rows) {
    i++;
    const result = await geocode(r);
    if (result.hit && !result.dry) {
      await query(
        `UPDATE businesses
            SET latitude = $1, longitude = $2,
                source_data_json = jsonb_set(jsonb_set(COALESCE(source_data_json,'{}'::jsonb),
                  '{geocode_source}', '"nominatim"'),
                  '{geocode_attempt}', '"hit"'),
                updated_at = now()
          WHERE id = $3`,
        [result.lat, result.lon, r.id]
      );
      hits++;
    } else if (result.dry) {
      hits++;
    } else if (!DRY) {
      // Mark the miss so we don't retry forever.
      await query(
        `UPDATE businesses
            SET source_data_json = jsonb_set(COALESCE(source_data_json,'{}'::jsonb),
                  '{geocode_attempt}', to_jsonb($1::text)),
                updated_at = now()
          WHERE id = $2`,
        [`miss:${result.reason || '?'}`, r.id]
      );
      misses++;
    } else {
      misses++;
    }
    if (i % 50 === 0) log(`  …${i}/${rows.rows.length} hits=${hits} misses=${misses}`);
    if (DRY && i >= 5) {
      log(`DRY mode — sample queries shown, exiting after first 5`);
      break;
    }
    if (!DRY) await sleep(SLEEP_MS);
  }
  log(`DONE: scanned=${i} hits=${hits} misses=${misses} errored=${errored}`);
  if (!DRY) {
    const tally = await one(`SELECT COUNT(*) FILTER (WHERE latitude IS NOT NULL) AS geocoded,
                                    COUNT(*) AS total FROM businesses`);
    log(`POST: ${tally.geocoded}/${tally.total} businesses geocoded (${(tally.geocoded/tally.total*100).toFixed(1)}%)`);
  }
}

main().catch(e => { console.error(e); process.exit(2); });