`scripts/reverse_geocode_orgs.js`

91 lines
#!/usr/bin/env node
/**
 * Reverse-geocode organizations that have lat/lng but no city or zip.
 *
 * Uses OSM Nominatim (free, polite 1 rps with required UA + email).
 * Backfills city + zip + address (if address was null) so cold-outreach CSV
 * can be sliced city-by-city for the sales workflow.
 *
 * Run:
 *   node scripts/reverse_geocode_orgs.js
 *   LIMIT=200 node scripts/reverse_geocode_orgs.js
 */
const { fetch } = require('undici');
const { pool, query } = require('../agents/shared/db');

const NOMINATIM = 'https://nominatim.openstreetmap.org/reverse';
const UA = process.env.USER_AGENT
  || 'ProfessionalDirectoryBot/0.1 (small-biz directory; contact: steve@designerwallcoverings.com)';
const RPS = 1;  // Nominatim ToS

let lastFetch = 0;
async function gate() {
  const minInterval = 1000 / RPS;
  const wait = Math.max(0, lastFetch + minInterval - Date.now());
  if (wait > 0) await new Promise(r => setTimeout(r, wait));
  lastFetch = Date.now();
}

async function reverseOne(lat, lng) {
  await gate();
  const url = `${NOMINATIM}?lat=${lat}&lon=${lng}&format=json&zoom=18&addressdetails=1`;
  try {
    const r = await fetch(url, {
      headers: { 'User-Agent': UA, 'Accept-Language': 'en-US' },
      signal: AbortSignal.timeout(10000),
    });
    if (!r.ok) return null;
    const j = await r.json();
    const a = j.address || {};
    return {
      city:    a.city || a.town || a.village || a.hamlet || a.suburb || a.neighbourhood || null,
      zip:     a.postcode || null,
      county:  a.county || null,
      address: j.display_name || null,
    };
  } catch (_) { return null; }
}

async function loadCandidates(limit) {
  const r = await query(`
    SELECT id, name, lat, lng, address, city, zip
      FROM organizations
     WHERE lat IS NOT NULL AND lng IS NOT NULL
       AND (city IS NULL OR zip IS NULL)
     ORDER BY id
     ${limit ? `LIMIT ${Number(limit)}` : ''}
  `, []);
  return r.rows;
}

async function main() {
  const limit = process.env.LIMIT ? Number(process.env.LIMIT) : null;
  const cands = await loadCandidates(limit);
  console.log(`[reverse] candidates=${cands.length} rps=${RPS}`);

  let ok = 0, miss = 0;
  for (const c of cands) {
    const r = await reverseOne(c.lat, c.lng);
    if (!r) { miss++; continue; }
    await query(`
      UPDATE organizations
         SET city    = COALESCE(city,    $2),
             zip     = COALESCE(zip,     $3),
             county  = COALESCE(county,  $4),
             updated_at = NOW()
       WHERE id = $1
    `, [c.id, r.city, r.zip, r.county]);
    ok++;
    if ((ok + miss) % 25 === 0) console.log(`[reverse] ok=${ok} miss=${miss}`);
  }

  console.log(`[reverse] done. ok=${ok} miss=${miss}`);
  await pool.end();
}

main().catch(async (err) => {
  console.error('[reverse] fatal:', err);
  try { await pool.end(); } catch (_) {}
  process.exit(1);
});