`scripts/geocode-census.js`

148 lines
// geocode-census.js — geocode every property (listing/condo/sfr) via the US Census BATCH geocoder. FREE, $0, no key.
//
// The Census batch endpoint takes up to 10,000 addresses per CSV and returns lon/lat — purpose-built for US
// addresses, no API key, no cost. We add lat/lng columns, fill them, then export data/map-points.json for the map.
//
//   node scripts/geocode-census.js            # geocode all un-geocoded rows, then export map-points.json
//   node scripts/geocode-census.js export      # just re-export map-points.json from already-geocoded rows
//
// Honest: unmatched addresses stay NULL (Census can't place every row) — the map plots what geocodes and the
// page shows "X of Y placed". Nothing is faked.
'use strict';
const { Pool } = require('pg');
const fs = require('fs');
const path = require('path');
const pool = new Pool({ host: '/tmp', port: 5432, database: 'cre', user: process.env.USER || 'stevestudio2' });
const ROOT = path.join(__dirname, '..');
const BENCHMARK = 'Public_AR_Current';
const ENDPOINT = 'https://geocoding.geo.census.gov/geocoder/locations/addressbatch';
const BATCH = 5000;                       // Census allows 10k; 5k keeps requests snappy + resumable.
const TABLES = ['listing', 'condo', 'sfr'];

const csvCell = s => { s = String(s == null ? '' : s).replace(/"/g, ''); return /[,]/.test(s) ? `"${s}"` : s; };

// Census wants the primary street line only — strip unit designators that cause "Tie"/"No_Match".
const cleanStreet = s => String(s || '')
  .replace(/\s+#\s*[\w-]+/g, '')                                       // "123 Main St #4" -> "123 Main St"
  .replace(/\b(apt|unit|ste|suite|spc|space|bldg|fl|floor)\.?\s*[\w-]+\s*$/ig, '')  // trailing unit token
  .replace(/\s{2,}/g, ' ').trim();
// Skip rows that can never geocode (no street number, or junk like "Call Broker For Details!").
const geocodable = s => /^\d/.test(String(s || '').trim()) &&
  !/call broker|for details|^tbd\b|to be |\bplan\s*\d|inquire|^lot\b|^parcel\b/i.test(s || '');

async function ensureColumns() {
  for (const t of TABLES) {
    await pool.query(`ALTER TABLE ${t} ADD COLUMN IF NOT EXISTS lat double precision`);
    await pool.query(`ALTER TABLE ${t} ADD COLUMN IF NOT EXISTS lng double precision`);
  }
}

// One Census batch call. rows: [{key, street, city, state, zip}] -> Map key -> {lat,lng}.
async function geocodeBatch(rows) {
  const lines = rows.map(r => [r.key, csvCell(r.street), csvCell(r.city), csvCell(r.state || 'CA'), csvCell(r.zip)].join(','));
  const csv = lines.join('\n');
  const fd = new FormData();
  fd.append('benchmark', BENCHMARK);
  fd.append('addressFile', new Blob([csv], { type: 'text/csv' }), 'addrs.csv');
  const res = await fetch(ENDPOINT, { method: 'POST', body: fd });
  if (!res.ok) throw new Error('census HTTP ' + res.status);
  const text = await res.text();
  const out = new Map();
  // Census quoting is inconsistent, so don't trust column positions. Pull the id (leading numeric field) and the
  // lon,lat pair BY SHAPE off the raw line — LA County lon≈-117..-119, lat≈33..35. Immune to column drift.
  for (const line of text.split('\n')) {
    if (!line.trim() || !/,"Match",/.test(line)) continue;       // exact "Match" field (not "No_Match"/"Tie")
    const idm = line.match(/^"([^"]*)"/);                         // id is the leading quoted field — may be TEXT (crx…) or int
    const cm = line.match(/(-1(?:1[5-9]|2[0-1])\.\d+),\s*(3[2-6]\.\d+)/);   // lon,lat for the LA basin
    if (!idm || !cm) continue;
    const lng = Number(cm[1]), lat = Number(cm[2]);
    if (isFinite(lat) && isFinite(lng)) out.set(idm[1], { lat, lng });
  }
  return out;
}

async function geocodeTable(t) {
  const { rows } = await pool.query(
    `SELECT id, address street, city, zip FROM ${t} WHERE lat IS NULL AND address IS NOT NULL AND city IS NOT NULL`);
  if (!rows.length) { console.log(`  ${t}: nothing to geocode`); return { placed: 0, total: 0 }; }
  const usable = rows.filter(r => geocodable(r.street));
  if (rows.length - usable.length) console.log(`  ${t}: skipping ${rows.length - usable.length} un-geocodable rows (no street #/junk)`);
  let placed = 0;
  for (let i = 0; i < usable.length; i += BATCH) {
    const slice = usable.slice(i, i + BATCH).map(r => ({ key: String(r.id), street: cleanStreet(r.street), city: r.city, state: 'CA', zip: r.zip }));
    let result;
    try { result = await geocodeBatch(slice); }
    catch (e) { console.log(`  ${t} batch ${i}-${i + slice.length}: ${e.message} (skipping)`); continue; }
    // write matches back
    const vals = [];
    for (const [id, { lat, lng }] of result) if (isFinite(lat) && isFinite(lng))
      vals.push(`('${String(id).replace(/'/g, "''")}',${lat},${lng})`);   // id quoted as text — works for crx… string ids AND int ids
    if (vals.length) {
      await pool.query(`UPDATE ${t} AS x SET lat=v.lat, lng=v.lng FROM (VALUES ${vals.join(',')}) AS v(id,lat,lng) WHERE x.id::text=v.id`);
      placed += vals.length;
    }
    console.log(`  ${t}: ${Math.min(i + BATCH, usable.length)}/${usable.length} processed, +${vals.length} placed (${placed} total)`);
  }
  return { placed, total: usable.length };
}

// Color buckets by property type. Commercial listings carry a granular type; condo/sfr are their own buckets.
function bucket(kind, type) {
  if (kind === 'sfr') return 'SFR';
  if (kind === 'condo') return 'Condo';
  const t = String(type || '').toLowerCase();
  if (t.includes('multi')) return 'Multifamily';
  if (t.includes('office')) return 'Office';
  if (t.includes('retail')) return 'Retail';
  if (t.includes('indust')) return 'Industrial';
  if (t.includes('mixed')) return 'Mixed-use';
  if (t.includes('land')) return 'Land';
  return 'Other';
}

async function exportPoints() {
  const points = [];
  // assumable-FHA/VA heuristic, keyed `${src}-${property_id}` → {likelihood, rate}. Only medium/high (actionable).
  const assum = new Map();
  try {
    const { rows } = await pool.query(
      `SELECT src, property_id, assumable_likelihood lk, est_assumable_rate rate FROM assumable_estimate WHERE assumable_likelihood<>'low'`);
    for (const r of rows) assum.set(`${r.src}-${r.property_id}`, { lk: r.lk, rate: r.rate != null ? +r.rate : null });
  } catch (_) {}
  const grab = async (t, kind, sel) => {
    const { rows } = await pool.query(
      `SELECT id, lat, lng, address, city, zip, price, ${sel} FROM ${t} WHERE lat IS NOT NULL AND lng IS NOT NULL`);
    for (const r of rows) {
      const a = assum.get(`${kind}-${r.id}`);
      points.push({
        id: `${kind}-${r.id}`, kind, lat: r.lat, lng: r.lng,
        address: r.address, city: r.city, zip: r.zip || '', price: r.price ? +r.price : null,
        type: bucket(kind, r.type), source: r.source || null,
        ...(a ? { assum: { lk: a.lk, rate: a.rate } } : {}),   // assumable-FHA/VA estimate (heuristic, not title-verified)
      });
    }
  };
  await grab('listing', 'listing', 'type, source, units');
  await grab('condo', 'condo', "NULL type, source");
  await grab('sfr', 'sfr', "NULL type, source");
  const out = { generatedAt: new Date().toISOString(), count: points.length, points };
  const file = path.join(ROOT, 'data', 'map-points.json');
  fs.writeFileSync(file, JSON.stringify(out));
  // coverage summary
  const tot = {};
  for (const t of TABLES) tot[t] = (await pool.query(`SELECT count(*) c, count(lat) g FROM ${t}`)).rows[0];
  console.log(`\nexported ${points.length} placed points -> data/map-points.json`);
  for (const t of TABLES) console.log(`  ${t}: ${tot[t].g}/${tot[t].c} geocoded`);
  return out.count;
}

(async () => {
  const mode = process.argv[2];
  if (mode !== 'export') {
    await ensureColumns();
    for (const t of TABLES) { console.log(`geocoding ${t}...`); await geocodeTable(t); }
  }
  await exportPoints();
  await pool.end();
})().catch(e => { console.error('ERR', e.message); process.exit(1); });