`scripts/census-fix-state.js`

51 lines
'use strict';
// Fix the wrong state='CA' stamp on the CRCP import: re-batch every CRCP sale listing through
// the Census geocoder and read the REAL state + zip from the matched address. Also fills any
// still-missing lat/lng. $0, one upload.
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, ' ') + '"';
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, zip FROM listings WHERE source_label='crcp' AND address IS NOT NULL AND address<>'' ORDER BY id`);
  console.log(`${rows.length} CRCP listings to re-resolve for state/zip`);
  const csv = rows.map(r => [r.id, csvq(r.address), csvq(r.city), csvq(''), 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…');
  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); await pool.end(); return; }

  let fixed = 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], matched = f[4] || '', coords = f[5] || '';
    if (status !== 'Match' && status !== 'Tie') continue;
    const parts = matched.split(',').map(s => s.trim());
    const st = parts.length >= 2 ? parts[parts.length - 2] : null;   // STATE
    const zp = parts.length >= 1 ? (parts[parts.length - 1] || '').match(/\d{5}/)?.[0] : null;
    let lat = null, lng = null;
    if (coords.includes(',')) { const [lo, la] = coords.split(',').map(Number); if (la && lo) { lat = la; lng = lo; } }
    if (st && /^[A-Z]{2}$/.test(st)) {
      await pool.query(
        `UPDATE listings SET state=$2, zip=COALESCE($3,zip), lat=COALESCE(lat,$4), lng=COALESCE(lng,$5) WHERE id=$1`,
        [id, st, zp || null, lat, lng]);
      fixed++;
    }
  }
  const byState = (await pool.query(`SELECT state, count(*) n FROM listings WHERE source_label='crcp' GROUP BY state ORDER BY n DESC LIMIT 8`)).rows;
  console.log(`state corrected on ${fixed} listings`);
  console.log('top states now:', byState.map(r => `${r.state}:${r.n}`).join('  '));
  await pool.end();
})();