`src/ingest/parcels/bexar_tx.ts`

193 lines
/**
 * Bexar County TX (San Antonio, FIPS 48029) parcel ingest — dedicated
 * full-universe adapter (applies the Wake/Miami-Dade/Tarrant DTD verdict B
 * precedent: a rich full-universe keyless ArcGIS county gets a dedicated
 * adapter, not the sale-cursor generic one, so the real OWNER + physical-detail
 * fields are preserved).
 *
 * Source: the City of San Antonio open-data hosted "BCAD_Parcels" FeatureServer
 * (BCAD = Bexar Central Appraisal District roll; keyless, maxRecordCount 2000,
 * supportsPagination, $0):
 *   https://services.arcgis.com/g1fRTDLeMgspWrYp/arcgis/rest/services/BCAD_Parcels/FeatureServer/0
 * The layer serves outSR=4326 server-side, so parcel centroids come back as
 * clean WGS84 lat/lng (the raw geometry is Texas South Central State Plane,
 * wkid 2278, US survey feet — we let the server reproject rather than doing it
 * ourselves).
 *
 * Coverage: OWNER_NAME (99%, 715,613 of 721,005 real parcels), SITUS (full
 * "STREET, CITY, STATE ZIP" property address, 99% — we parse street/city/zip
 * from it because the mailing addr_* fields are the OWNER's mailing address, not
 * the property location), GBA_Living (living-area sqft, 87%), LandSqft, state_cd
 * (use code), legal_desc, legal_acre.
 *
 * This CoSA-hosted BCAD open view carries NO appraised value, NO year-built, NO
 * beds/baths, and NO sale/deed feed. (The authoritative maps.bcad.org
 * PAMapSearch service DOES carry appraised value + owner, but it is a
 * point-and-click MapSearch service that blocks bulk paging — no resultOffset,
 * no orderBy — so it is not a safe full-universe backfill source. We take the
 * reliably-pageable CoSA hosted layer, matching the Tarrant precedent of
 * choosing the pageable full-universe copy.) Because there is no priced-sale
 * feed here, this county is parcel-DETAIL-only (like Salt Lake UT): zero
 * parcel_event rows are written — never a fabricated sale.
 *
 * source_id = PropID (the BCAD property account id). Rows with PropID <= 0 are
 * geometry-only / placeholder stubs (blank owner, PropID=-1030 etc.) and are
 * skipped.
 *
 * Run: NODE_OPTIONS=--max-old-space-size=4096 npm run ingest:parcels bexar
 */
import { pool } from '../../../db/pool.ts';
import { openRun, closeRun } from '../run.ts';
import { upsertParcels, registerParcelSource, registerSourceFieldMap, normAddress, type ParcelUpsertRow } from './upsert.ts';

const FIPS = '48029';
const SOURCE_KEY = 'bexar_tx';
const LAYER = 'https://services.arcgis.com/g1fRTDLeMgspWrYp/arcgis/rest/services/BCAD_Parcels/FeatureServer/0';
const WHERE = 'PropID>0';
const PAGE = 2000;
// TK-50 field-level provenance: our parcel field → the exact source attribute it
// maps from. address/city/zip are PARSED out of the single Situs string. No
// appraised value / year / beds-baths / sale in this CoSA-hosted BCAD view.
const FIELD_MAP: Record<string, string> = {
  source_id: 'PropID', address: 'Situs (street parsed)', city: 'Situs (city parsed)',
  zip: 'Situs (zip parsed)', sqft: 'GBA_Living', use_desc: 'state_cd', owner_name: 'Owner_Name',
};

const s = (v: unknown): string | null => { const t = v == null ? '' : String(v).trim(); return t ? t : null; };
const num = (v: unknown): number | null => { const x = Number(v); return Number.isFinite(x) && x !== 0 ? x : null; };

/**
 * The Situs field is a full formatted property address: "STREET, CITY, STATE ZIP"
 * (e.g. "309 ALBANY ST, ALAMO HEIGHTS, TX 78209"). Parse out the street line, the
 * city, and the 5-digit zip. Returns nulls on any shape we don't recognize rather
 * than guessing.
 */
function parseSitus(v: unknown): { street: string | null; city: string | null; zip: string | null } {
  const t = s(v);
  if (!t) return { street: null, city: null, zip: null };
  const parts = t.split(',').map(p => p.trim()).filter(Boolean);
  // expected: [street, city, "STATE ZIP"] (3 parts) — but street may itself contain
  // no comma, and some rows are "STREET, CITY TX 78xxx" (2 parts). Handle both.
  let street: string | null = null, city: string | null = null, zip: string | null = null;
  const zipM = t.match(/\b(\d{5})(?:-\d{4})?\s*$/);
  if (zipM) zip = zipM[1];
  if (parts.length >= 3) {
    street = parts[0] || null;
    city = parts[1] || null;
  } else if (parts.length === 2) {
    street = parts[0] || null;
    // second part is "CITY TX 78209" — strip the trailing state + zip
    city = parts[1].replace(/\s+TX\s+\d{5}(?:-\d{4})?\s*$/i, '').trim() || null;
  } else {
    street = parts[0] || null;
  }
  return { street, city, zip };
}

/** plain vertex-average of ring[0] — NOT a true polygon centroid (fine for a map
 *  pin; multipart parcels only get the first ring). Geometry is already WGS84
 *  because we request outSR=4326. */
function centroid(geom: any): [number | null, number | null] {
  const ring = geom?.rings?.[0]; if (!Array.isArray(ring) || !ring.length) return [null, null];
  let sx = 0, sy = 0; for (const [x, y] of ring) { sx += x; sy += y; }
  return [+(sx / ring.length).toFixed(6), +(sy / ring.length).toFixed(6)];
}

const OUT = ['PropID', 'Geo_id', 'Situs', 'Owner_Name', 'state_cd',
  'GBA_Living', 'LandSqft', 'legal_acre', 'legal_desc', 'Exemptions'].join(',');

async function fetchPage(offset: number): Promise<any[]> {
  const u = new URL(LAYER + '/query');
  u.searchParams.set('where', WHERE);
  u.searchParams.set('outFields', OUT);
  u.searchParams.set('orderByFields', 'OBJECTID ASC');
  u.searchParams.set('resultOffset', String(offset));
  u.searchParams.set('resultRecordCount', String(PAGE));
  u.searchParams.set('returnGeometry', 'true');
  u.searchParams.set('outSR', '4326');
  u.searchParams.set('f', 'json');
  let lastErr: any;
  for (let a = 0; a < 3; a++) {
    try {
      const res = await fetch(u, { headers: { 'User-Agent': 'Mozilla/5.0 (usre-parcel-ingest)' }, signal: AbortSignal.timeout(120_000) });
      if (!res.ok) throw new Error(`bexar ${res.status}: ${(await res.text()).slice(0, 140)}`);
      const j: any = await res.json();
      if (j.error) throw new Error(`bexar error: ${JSON.stringify(j.error).slice(0, 140)}`);
      return j.features || [];
    } catch (e) { lastErr = e; if (a < 2) await new Promise(r => setTimeout(r, 2000 * (a + 1))); }
  }
  throw lastErr;
}

export async function ingestBexar(): Promise<{ upserted: number }> {
  const runId = await openRun('parcel_bexar_tx', LAYER);
  // TK-50: run start time — the fetched_at stamped on every row this run touches.
  const fetchedAt = new Date().toISOString();
  try {
    await registerSourceFieldMap(SOURCE_KEY, FIELD_MAP, 'Bexar County TX (BCAD) via City of San Antonio open-data parcel view');
    let offset = 0, seen = 0, skipped = 0, upserted = 0;
    for (;;) {
      const feats = await fetchPage(offset);
      if (!feats.length) break;
      const batch: ParcelUpsertRow[] = [];
      for (const f of feats) {
        const a = f.attributes || {};
        // PropID>0 is the server-side filter, but guard again defensively: skip
        // any placeholder row with no real owner AND no situs (ghost polygon).
        const pid = num(a.PropID);
        if (!pid || pid <= 0) { skipped++; continue; }
        const owner = s(a.Owner_Name);
        const { street, city, zip } = parseSitus(a.Situs);
        if (!owner && !street) { skipped++; continue; }
        seen++;
        const [lng, lat] = centroid(f.geometry);
        const norm = street ? normAddress(street) : null;
        batch.push({
          county_fips: FIPS, source_id: String(pid),
          address: norm, norm_address: norm,
          city, zip,
          lat, lng,
          year_built: null, sqft: num(a.GBA_Living), beds: null, baths: null, units: null,
          use_desc: s(a.state_cd),
          land_value: null, improvement_value: null, total_value: null,
          tax_year: null, owner_name: owner, zoning: null,
          // no priced-sale / deed feed in this CoSA-hosted BCAD open view — leave
          // both sale fields NULL (never fabricate a sale date or price).
          last_sale_date: null, last_sale_price: null,
          extra: JSON.stringify({
            geo_id: s(a.Geo_id) || undefined,
            land_sqft: num(a.LandSqft) || undefined,
            legal_acre: num(a.legal_acre) || undefined,
            legal_desc: s(a.legal_desc) || undefined,
            exemptions: s(a.Exemptions) || undefined,
            note: 'Bexar County TX (BCAD) via City of San Antonio open-data parcel view — owner + situs + living-area sqft + use code; no appraised value / year-built / beds-baths / sale in this feed',
          }),
          // ── TK-50 field-level provenance (record-level: one feature → this row) ──
          sourceKey: SOURCE_KEY,
          sourceUrl: `${LAYER}/query?where=${encodeURIComponent(`PropID=${pid}`)}&outFields=*&f=html`,
          fetchedAt,
          rawSource: JSON.stringify(a),
        });
      }
      upserted += await upsertParcels(batch);
      offset += feats.length;
      if (seen % 100000 < feats.length) console.log(`[bexar] ${upserted} upserted, ${skipped} stubs skipped`);
      if (feats.length < PAGE) break;
    }
    if (upserted < 650000) throw new Error(`only ${upserted} Bexar parcels (expected ~721k) — layer/paging/filter drift?`);

    const n = await registerParcelSource(FIPS, LAYER,
      `Bexar County TX (San Antonio) BCAD appraisal roll via City of San Antonio open-data parcel view (PropID>0 filter) — ${upserted} parcels: owner (99%), situs property address parsed to street+city+zip (99%), living-area sqft (87%), land sqft, state use code, legal acreage/desc, Map (polygon centroid, WGS84 via outSR). No appraised value, year-built, beds/baths, or sale/deed feed in this CoSA-hosted BCAD open view (parcel-detail-only, like Salt Lake — zero fabricated events). ${skipped} placeholder/geometry-only stubs skipped.`);
    await closeRun(runId, 'ok', { upserted, skipped, notes: `Bexar TX: ${upserted} parcels, ${skipped} stubs skipped` });
    console.log(`[bexar] ok: ${upserted} parcels, ${skipped} stubs (registry ${n})`);
    return { upserted };
  } catch (e: any) {
    await closeRun(runId, 'failed', { notes: String(e.message || e).slice(0, 500) });
    throw e;
  }
}

if (import.meta.url === `file://${process.argv[1]}`) {
  ingestBexar().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
}