`scripts/geocode-installers.js`

105 lines
#!/usr/bin/env node
// Geocode installers using city/state via Nominatim (OpenStreetMap, free, no key).
// Rate-limited to 1 req/sec per Nominatim's usage policy. Cached so reruns are cheap.
// Resolution is city-level; multiple installers in the same city get a small jitter
// (~±0.5 mile) so map pins don't stack.

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

const CACHE_PATH = path.join(__dirname, '..', 'data', 'geo-cache.json');
const UA = 'NationalPaperHangers/0.1 (https://nationalpaperhangers.com; ops@nationalpaperhangers.com)';
const NOMINATIM = 'https://nominatim.openstreetmap.org/search';
const REQ_INTERVAL_MS = 1100; // be polite

function jitter(seed) {
  // Deterministic jitter: same installer.id always gets the same offset.
  // ±0.008° ≈ ±0.55 miles. Enough to spread a 6-installer city without
  // moving anyone out of frame.
  const r1 = Math.sin(seed * 12.9898) * 43758.5453; // PRNG-ish
  const r2 = Math.sin(seed * 78.233)  * 43758.5453;
  const f1 = r1 - Math.floor(r1);
  const f2 = r2 - Math.floor(r2);
  return [(f1 - 0.5) * 0.016, (f2 - 0.5) * 0.016];
}

async function loadCache() {
  try { return JSON.parse(await fs.readFile(CACHE_PATH, 'utf8')); }
  catch { return {}; }
}
async function saveCache(c) {
  await fs.writeFile(CACHE_PATH, JSON.stringify(c, null, 2));
}

const sleep = (ms) => new Promise(r => setTimeout(r, ms));

async function geocode(city, state) {
  const params = new URLSearchParams({
    city, state, country: 'United States', format: 'json', limit: '1',
  });
  const res = await fetch(`${NOMINATIM}?${params}`, { headers: { 'User-Agent': UA } });
  if (!res.ok) throw new Error(`nominatim ${res.status}`);
  const arr = await res.json();
  if (!arr.length) return null;
  return { lat: parseFloat(arr[0].lat), lng: parseFloat(arr[0].lon) };
}

async function main() {
  const cache = await loadCache();
  const rows = await db.many(
    `SELECT id, city, state FROM installers
      WHERE city IS NOT NULL AND state IS NOT NULL
        AND (latitude IS NULL OR longitude IS NULL)
      ORDER BY id`
  );
  console.log(`installers needing geocoding: ${rows.length}`);

  const pairs = new Map();
  for (const r of rows) {
    const key = `${r.city.trim()}|${r.state.trim().toUpperCase()}`;
    if (!pairs.has(key)) pairs.set(key, { city: r.city.trim(), state: r.state.trim().toUpperCase(), ids: [] });
    pairs.get(key).ids.push(r.id);
  }
  console.log(`unique city/state pairs: ${pairs.size}`);

  let resolvedPairs = 0, missPairs = 0, updatedRows = 0, idx = 0;
  const total = pairs.size;
  for (const [key, info] of pairs.entries()) {
    idx += 1;
    let coord = cache[key];
    if (!coord) {
      try {
        coord = await geocode(info.city, info.state);
      } catch (e) {
        console.warn(`[${idx}/${total}] ${key} geocode error: ${e.message}`);
        coord = null;
      }
      cache[key] = coord || { miss: true };
      if ((idx % 25) === 0) await saveCache(cache);
      await sleep(REQ_INTERVAL_MS);
    }
    if (!coord || coord.miss) { missPairs += 1; continue; }
    resolvedPairs += 1;
    for (const id of info.ids) {
      const [dlat, dlng] = jitter(id);
      await db.query(
        `UPDATE installers
            SET latitude = $1, longitude = $2,
                geo_accuracy = 'city',
                geocoded_at = now()
          WHERE id = $3`,
        [coord.lat + dlat, coord.lng + dlng, id]
      );
      updatedRows += 1;
    }
    if ((idx % 10) === 0) console.log(`[${idx}/${total}] ${key} → ${coord.lat.toFixed(3)},${coord.lng.toFixed(3)} (${info.ids.length} installers)`);
  }
  await saveCache(cache);
  console.log(`\ndone. pairs resolved=${resolvedPairs} miss=${missPairs}; rows updated=${updatedRows}`);
  process.exit(0);
}

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