`scripts/census-batch-geocode.js`

50 lines
'use strict';
// Sweep unmapped listings through the free US Census BATCH geocoder (up to 10k/upload,
// no rate limit). Purpose-built for bulk — the fix for what Nominatim throttled. $0.
const { Pool } = require('pg');
const pool = new Pool({ host: '/tmp', database: 'crunified' });
const CENSUS_URL = 'https://geocoding.geo.census.gov/geocoder/locations/addressbatch';
const csvq = (s) => '"' + String(s == null ? '' : s).replace(/"/g, "'").replace(/[\r\n]+/g, ' ') + '"';

// minimal CSV line parser (handles quoted fields)
function parseLine(line) {
  const out = []; let cur = '', q = false;
  for (let i = 0; i < line.length; i++) { const c = line[i];
    if (q) { if (c === '"') { if (line[i + 1] === '"') { cur += '"'; i++; } else q = false; } else cur += c; }
    else if (c === '"') q = true; else if (c === ',') { out.push(cur); cur = ''; } else cur += c; }
  out.push(cur); return out;
}

(async () => {
  const { rows } = await pool.query(
    `SELECT id, address, city, state, zip FROM listings WHERE lat IS NULL AND address IS NOT NULL AND address<>'' ORDER BY id`);
  console.log(`${rows.length} addresses to batch-geocode`);
  if (!rows.length) { await pool.end(); return; }

  const csv = rows.map(r => [r.id, csvq(r.address), csvq(r.city), csvq(r.state || 'CA'), csvq(r.zip)].join(',')).join('\n');
  const fd = new FormData();
  fd.append('benchmark', 'Public_AR_Current');
  fd.append('addressFile', new Blob([csv], { type: 'text/csv' }), 'addrs.csv');

  console.log('uploading to Census batch geocoder…');
  const res = await fetch(CENSUS_URL, { method: 'POST', body: fd, signal: AbortSignal.timeout(180000) });
  const text = await res.text();
  if (!res.ok) { console.log('census error', res.status, text.slice(0, 200)); await pool.end(); return; }

  let matched = 0, nomatch = 0;
  for (const line of text.split('\n')) {
    if (!line.trim()) continue;
    const f = parseLine(line);               // id, input, status, matchtype, matchedAddr, "lon,lat", tiger, side
    const id = +f[0], status = f[2], coords = f[5];
    if ((status === 'Match' || status === 'Tie') && coords && coords.includes(',')) {
      const [lon, lat] = coords.split(',').map(Number);
      if (lat && lon) { await pool.query('UPDATE listings SET lat=$2, lng=$3 WHERE id=$1', [id, lat, lon]); matched++; continue; }
    }
    nomatch++;
  }
  const [{ mapped, total }] = (await pool.query(`SELECT count(*) FILTER (WHERE lat IS NOT NULL) mapped, count(*) total FROM listings`)).rows;
  console.log(`Census matched ${matched}, no-match ${nomatch}`);
  console.log(`coverage now: ${mapped}/${total} (${(100 * mapped / total).toFixed(1)}%)`);
  await pool.end();
})();