`scripts/geocode-backfill.js`

28 lines
'use strict';
// Backfill lat/lng for listings missing coords, using free OSM Nominatim (city-level ok).
// Polite: 1 req/sec, descriptive UA. $0.
const { pool, httpGet, sleep } = require('../crawl/base-crawler');

async function nominatim(qstr) {
  const url = 'https://nominatim.openstreetmap.org/search?format=json&limit=1&q=' + encodeURIComponent(qstr);
  const r = await httpGet(url, { timeout: 15000, headers: { 'User-Agent': 'SubleaseMarketplace/0.1 (agentabrams.com)' } });
  if (!r.ok) return null;
  try { const j = JSON.parse(r.text); return j[0] ? { lat: +j[0].lat, lng: +j[0].lon } : null; } catch { return null; }
}

(async () => {
  const { rows } = await pool.query(
    `SELECT id, address, city, state FROM listings WHERE (lat IS NULL OR lng IS NULL) AND (city IS NOT NULL OR address IS NOT NULL) ORDER BY id`);
  console.log(`${rows.length} listings need coords`);
  let done = 0;
  for (const r of rows) {
    const qstr = [r.address, r.city, r.state || 'CA', 'USA'].filter(Boolean).join(', ');
    const g = await nominatim(qstr);
    if (g) { await pool.query('UPDATE listings SET lat=$2, lng=$3 WHERE id=$1', [r.id, g.lat, g.lng]); done++; process.stdout.write('.'); }
    else process.stdout.write('x');
    await sleep(1100); // Nominatim policy
  }
  console.log(`\ngeocoded ${done}/${rows.length}`);
  await pool.end();
})();