`scripts/enrich-whatcom-situs.mjs`

128 lines
#!/usr/bin/env node
/**
 * Enrich Whatcom County WA (53073) parcels with situs address + owner + land use
 * from the WhatcomCo_Property parcel layer (2-layer join: Sales geo_id → Property geo_id).
 *
 * Fills NULL-only fields — never regresses existing values.
 * Run: node scripts/enrich-whatcom-situs.mjs [--dry-run]
 *
 * TK-10777 — Whatcom WA situs/owner join
 */

import pg from 'pg';
const { Pool } = pg;

const DRY_RUN = process.argv.includes('--dry-run');
const PROPERTY_LAYER = 'https://gis.whatcomcounty.us/arcgis/rest/services/EnterprisePublishing/WhatcomCo_Property/MapServer/0';
const BATCH = 200;   // well under maxRecordCount=1000

const pool = new Pool({
  connectionString: process.env.DATABASE_URL || 'postgresql:///usre?host=/tmp',
  max: 4,
});

function s(v) { const t = v == null ? '' : String(v).trim(); return t || null; }
function buildAddress(a) {
  return [s(a.situs_num), s(a.situs_street_prefix), s(a.situs_street), s(a.situs_unit)]
    .filter(Boolean).join(' ').trim() || null;
}
function normAddr(addr) {
  if (!addr) return null;
  return addr.toUpperCase().replace(/\s+/g, ' ').replace(/[.,]/g, '').trim();
}

async function fetchPropertyBatch(gids) {
  const escaped = gids.map(g => `'${g.replace(/'/g, "''")}'`).join(',');
  const where = `geo_id IN (${escaped})`;
  // Use POST to avoid URL-length limits with large batches (200 16-char IDs ≈ 4KB).
  const body = new URLSearchParams({
    where,
    outFields: 'geo_id,title_owner_name,property_use_description,situs_num,situs_street_prefix,situs_street,situs_unit,situs_city',
    resultRecordCount: String(BATCH + 10),
    f: 'json',
  });
  const res = await fetch(`${PROPERTY_LAYER}/query`, {
    method: 'POST',
    headers: { 'Content-Type': 'application/x-www-form-urlencoded' },
    body: body.toString(),
    signal: AbortSignal.timeout(60_000),
  });
  if (!res.ok) throw new Error(`Property layer HTTP ${res.status}`);
  const j = await res.json();
  if (j.error) throw new Error(`Property layer ArcGIS error: ${JSON.stringify(j.error)}`);
  return j.features || [];
}

async function main() {
  console.log(`[enrich-whatcom-situs] ${DRY_RUN ? 'DRY-RUN mode' : 'LIVE mode'}`);

  // Get all Whatcom parcels that are missing address OR owner OR use_desc
  const { rows: parcels } = await pool.query(
    `SELECT source_id FROM parcel WHERE county_fips = '53073' ORDER BY source_id`
  );
  console.log(`[enrich-whatcom-situs] Found ${parcels.length} total Whatcom parcels`);

  const gids = parcels.map(r => r.source_id);
  let enriched = 0;
  let skipped = 0;

  for (let i = 0; i < gids.length; i += BATCH) {
    const batch = gids.slice(i, i + BATCH);
    let features;
    try {
      features = await fetchPropertyBatch(batch);
    } catch (e) {
      console.error(`[enrich-whatcom-situs] Batch ${i}-${i + batch.length} fetch error:`, e.message);
      // Continue with next batch rather than aborting the whole run
      skipped += batch.length;
      continue;
    }

    const byGid = new Map();
    for (const f of features) {
      const a = f.attributes;
      const gid = s(a.geo_id);
      if (!gid) continue;
      byGid.set(gid, {
        address: buildAddress(a),
        city: s(a.situs_city),
        owner_name: s(a.title_owner_name),
        use_desc: s(a.property_use_description),
      });
    }

    let batchEnriched = 0;
    for (const gid of batch) {
      const prop = byGid.get(gid);
      if (!prop) { skipped++; continue; }
      if (!prop.address && !prop.city && !prop.owner_name && !prop.use_desc) { skipped++; continue; }

      if (!DRY_RUN) {
        await pool.query(
          `UPDATE parcel SET
             address      = COALESCE(address, $1),
             norm_address = COALESCE(norm_address, $2),
             city         = COALESCE(city, $3),
             owner_name   = COALESCE(owner_name, $4),
             use_desc     = COALESCE(use_desc, $5)
           WHERE county_fips = '53073' AND source_id = $6
             AND (address IS NULL OR city IS NULL OR owner_name IS NULL OR use_desc IS NULL)`,
          [prop.address, normAddr(prop.address), prop.city, prop.owner_name, prop.use_desc, gid]
        );
      }
      batchEnriched++;
    }

    enriched += batchEnriched;
    console.log(`[enrich-whatcom-situs] Batch ${i + batch.length}/${gids.length} — enriched so far: ${enriched}`);

    // Small pause to be polite to the county ArcGIS server
    if (i + BATCH < gids.length) await new Promise(r => setTimeout(r, 300));
  }

  console.log(`\n[enrich-whatcom-situs] DONE — ${enriched} parcels enriched, ${skipped} skipped (no property record found or all fields already populated)`);
  await pool.end();
}

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