`scripts/data-hound-census.js`

101 lines
#!/usr/bin/env node
// Data Hound #1 — Census Geocoder fallback for the 2,686 Nominatim misses
// and 7 transient fetch errors. Free US-only geocoder, no key, no rate
// limit (within reason — we still throttle to 5 req/sec to be polite).
// On hit: clear the prior 'miss:no-result' flag, write lat/lng,
//         mark source_data_json.geocode_attempt='census-hit'.
// On miss: bump source_data_json.geocode_attempt='miss:census-also-no-result'.
//
//   node scripts/data-hound-census.js
//   LIMIT=200 node scripts/data-hound-census.js
//   DRY=1 node scripts/data-hound-census.js

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

const CENSUS = 'https://geocoding.geo.census.gov/geocoder/locations/onelineaddress';
const SLEEP_MS = 220; // ~5/sec
const LIMIT = process.env.LIMIT ? parseInt(process.env.LIMIT, 10) : null;
const DRY = process.env.DRY === '1';

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

function buildOneLine(b) {
  const addr = String(b.address || '').trim()
    .replace(/\s*(STE|SUITE|UNIT|APT|BLDG|FL|FLR|RM|#)\s*[\w-]+/gi, '')
    .replace(/\s+/g, ' ').trim();
  if (!addr) return null;
  const city = String(b.city || '').trim();
  const zip = String(b.zip || '').trim();
  return [addr, city, 'CA', zip].filter(Boolean).join(', ');
}

async function censusLookup(oneline) {
  const u = `${CENSUS}?address=${encodeURIComponent(oneline)}&benchmark=Public_AR_Current&format=json`;
  const r = await fetch(u, { signal: AbortSignal.timeout(15000) });
  if (!r.ok) throw new Error(`HTTP ${r.status}`);
  const j = await r.json();
  const m = j?.result?.addressMatches?.[0];
  if (!m?.coordinates) return null;
  return { lat: m.coordinates.y, lon: m.coordinates.x, matchedAddress: m.matchedAddress };
}

async function run() {
  // Eligible: missing geocode AND prior attempt was nominatim miss/fetch-error/null
  const rows = await query(
    `SELECT id, slug, name, address, city, zip, source_data_json
       FROM businesses
      WHERE (latitude IS NULL OR longitude IS NULL)
        AND address IS NOT NULL AND address <> ''
      ORDER BY id
      ${LIMIT ? `LIMIT ${LIMIT}` : ''}`
  );
  log(`Census geocoder hound: ${rows.rows.length} candidates`);

  let hits = 0, misses = 0, errs = 0;
  for (const b of rows.rows) {
    const oneline = buildOneLine(b);
    if (!oneline) { misses++; continue; }
    if (DRY) { log(`DRY: ${oneline}`); await sleep(SLEEP_MS); continue; }

    try {
      const r = await censusLookup(oneline);
      if (r) {
        await query(
          `UPDATE businesses
              SET latitude  = $1,
                  longitude = $2,
                  source_data_json = jsonb_set(
                    COALESCE(source_data_json,'{}'::jsonb),
                    '{geocode_attempt}', '"census-hit"'::jsonb)
            WHERE id = $3`,
          [r.lat, r.lon, b.id]
        );
        hits++;
        if (hits % 25 === 0) log(`  ${hits} hits / ${misses} misses / ${errs} errors`);
      } else {
        await query(
          `UPDATE businesses
              SET source_data_json = jsonb_set(
                    COALESCE(source_data_json,'{}'::jsonb),
                    '{geocode_attempt}', '"miss:census-also-no-result"'::jsonb)
            WHERE id = $1`,
          [b.id]
        );
        misses++;
      }
    } catch (e) {
      errs++;
      if (errs % 10 === 0) log(`  ${errs} errors so far (last: ${e.message})`);
    }
    await sleep(SLEEP_MS);
  }

  log(`DONE — hits=${hits} misses=${misses} errors=${errs}`);
  process.exit(0);
}

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