← back to Nationalrealestate
Whatcom WA: add situs/owner join + SWDSales2020/2021 history layers (TK-10777)
1326a541a29dc3c782760690928556862b7ce76c · 2026-08-24 04:27:02 -0700 · steve@designerwallcoverings.com
- scripts/enrich-whatcom-situs.mjs: batch POSTs to WhatcomCo_Property (geo_id join),
fills address/city/owner_name/use_desc for all 5157 Whatcom parcels (4480 enriched)
- src/ingest/parcels/arcgis_sales.ts: add whatcom-2021 (layer /1) + whatcom-2020 (layer /0)
history source configs with history:true flag; add upsertParcelHistory import
- src/ingest/parcels/upsert.ts: add upsertParcelHistory() — COALESCE fill + date-guard
on last_sale_date/price (never regresses a newer sale with an older history record)
- src/ingest/parcels/engine.ts: route whatcom-2020 / whatcom-2021 adapters
- src/jobs/hourly_loop.ts: schedule deeds-whatcom-2021/2020 at 168h (weekly, historical)
Cost: $0 (ArcGIS REST is free)
Files touched
A scripts/enrich-whatcom-situs.mjsM src/ingest/parcels/arcgis_sales.tsM src/ingest/parcels/engine.tsM src/ingest/parcels/upsert.tsM src/jobs/hourly_loop.ts
Diff
commit 1326a541a29dc3c782760690928556862b7ce76c
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date: Mon Aug 24 04:27:02 2026 -0700
Whatcom WA: add situs/owner join + SWDSales2020/2021 history layers (TK-10777)
- scripts/enrich-whatcom-situs.mjs: batch POSTs to WhatcomCo_Property (geo_id join),
fills address/city/owner_name/use_desc for all 5157 Whatcom parcels (4480 enriched)
- src/ingest/parcels/arcgis_sales.ts: add whatcom-2021 (layer /1) + whatcom-2020 (layer /0)
history source configs with history:true flag; add upsertParcelHistory import
- src/ingest/parcels/upsert.ts: add upsertParcelHistory() — COALESCE fill + date-guard
on last_sale_date/price (never regresses a newer sale with an older history record)
- src/ingest/parcels/engine.ts: route whatcom-2020 / whatcom-2021 adapters
- src/jobs/hourly_loop.ts: schedule deeds-whatcom-2021/2020 at 168h (weekly, historical)
Cost: $0 (ArcGIS REST is free)
---
scripts/enrich-whatcom-situs.mjs | 127 +++++++++++++++++++++++++++++++++++++
src/ingest/parcels/arcgis_sales.ts | 39 ++++++++++--
src/ingest/parcels/engine.ts | 3 +
src/ingest/parcels/upsert.ts | Bin 6536 -> 8693 bytes
src/jobs/hourly_loop.ts | 5 +-
5 files changed, 167 insertions(+), 7 deletions(-)
diff --git a/scripts/enrich-whatcom-situs.mjs b/scripts/enrich-whatcom-situs.mjs
new file mode 100644
index 0000000..5a07782
--- /dev/null
+++ b/scripts/enrich-whatcom-situs.mjs
@@ -0,0 +1,127 @@
+#!/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); });
diff --git a/src/ingest/parcels/arcgis_sales.ts b/src/ingest/parcels/arcgis_sales.ts
index 15f9762..5a4f390 100644
--- a/src/ingest/parcels/arcgis_sales.ts
+++ b/src/ingest/parcels/arcgis_sales.ts
@@ -18,7 +18,7 @@ import { execFile } from 'node:child_process';
import { promisify } from 'node:util';
import { query } from '../../../db/pool.ts';
import { openRun, closeRun } from '../run.ts';
-import { upsertParcels, normAddress, registerParcelSource, registerSourceFieldMap, type ParcelUpsertRow } from './upsert.ts';
+import { upsertParcels, upsertParcelHistory, normAddress, registerParcelSource, registerSourceFieldMap, type ParcelUpsertRow } from './upsert.ts';
import { sanitizeEventDate } from './date_guard.ts';
import { sanitizeSalePrice } from './price_guard.ts';
@@ -39,6 +39,7 @@ interface Source {
key: string; label: string; layer: string; where: string; outFields: string; cursorKey: string;
geometry: boolean; orderBy: string; pageSize?: number; // a valid field for stable resultOffset paging (OID name varies)
centroid?: boolean; // request returnCentroid+outSR=4326 (lightweight lat/lng from f.centroid) without downloading full polygons — for geometry-only assessor rolls (e.g. Orange County)
+ history?: boolean; // TK-10777: history layer — use upsertParcelHistory (date-guarded) instead of the full-overwrite upsert
toRecords: (features: any[]) => Rec[];
// TK-50 field-level provenance: our parcel field → the exact source attribute
// it was mapped from in toRecords(). Registered into source_field_map at
@@ -642,10 +643,9 @@ const SOURCES: Record<string, Source> = {
// like Yakima): gid (16-char parcel geo_id → apn/source_id, also the deep-link key), pid,
// deed_date (epoch-ms → last_sale_date), sale_price (int), sale_type (WA excise deed code
// K/N/P/L/Q/M → doc_type), sale_id (int → doc_number), sale_land_acres (kept in raw).
- // This sales layer carries NO situs/owner/use (those live on WhatcomCo_Property; a future
- // one-pass-can't-join enhancement), so it's a lean price+date+doc feed. OID field reports
- // 'None' but orderBy=OBJECTID + resultOffset paging works; maxRecordCount=1000.
- key: 'whatcom', label: 'Whatcom County WA (Bellingham)', cursorKey: 'whatcom', geometry: false, orderBy: 'OBJECTID', pageSize: 1000,
+ // Situs/owner/use live on WhatcomCo_Property; enriched offline via scripts/enrich-whatcom-situs.mjs.
+ // OID field reports 'None' but orderBy=OBJECTID + resultOffset paging works; maxRecordCount=1000.
+ key: 'whatcom', label: 'Whatcom County WA (Bellingham) — SWDSales2022', cursorKey: 'whatcom', geometry: false, orderBy: 'OBJECTID', pageSize: 1000,
layer: 'https://gis.whatcomcounty.us/arcgis/rest/services/EnterprisePublishing/WhatcomCo_PropertySales/MapServer/2',
where: 'sale_price>1000',
// gid MUST be first — the engine derives the per-parcel GIS deep-link as where=<first>='<apn>'.
@@ -656,6 +656,32 @@ const SOURCES: Record<string, Source> = {
return { fips: '53073', apn, price: anyPrice(a.sale_price), saleDate: anyDate(a.deed_date),
docNum: s(a.sale_id), docType: clean(a.sale_type), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
+ 'whatcom-2021': {
+ // TK-10777: SWDSales2021 historical layer — same schema as whatcom (2022).
+ // history: true → engine uses upsertParcelHistory (date-guarded, never regresses a newer sale).
+ key: 'whatcom-2021', label: 'Whatcom County WA (Bellingham) — SWDSales2021', cursorKey: 'whatcom_2021', geometry: false, orderBy: 'OBJECTID', pageSize: 1000,
+ layer: 'https://gis.whatcomcounty.us/arcgis/rest/services/EnterprisePublishing/WhatcomCo_PropertySales/MapServer/1',
+ where: 'sale_price>1000', history: true,
+ outFields: 'gid,pid,deed_date,sale_id,sale_price,sale_type,sale_land_acres',
+ fieldMap: { source_id: 'gid', last_sale_price: 'sale_price', last_sale_date: 'deed_date',
+ doc_type: 'sale_type', doc_number: 'sale_id' },
+ toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.gid); if (!apn) return null;
+ return { fips: '53073', apn, price: anyPrice(a.sale_price), saleDate: anyDate(a.deed_date),
+ docNum: s(a.sale_id), docType: clean(a.sale_type), raw: a } as Rec; }).filter(Boolean) as Rec[],
+ },
+ 'whatcom-2020': {
+ // TK-10777: SWDSales2020 historical layer — same schema as whatcom (2022).
+ // history: true → engine uses upsertParcelHistory (date-guarded, never regresses a newer sale).
+ key: 'whatcom-2020', label: 'Whatcom County WA (Bellingham) — SWDSales2020', cursorKey: 'whatcom_2020', geometry: false, orderBy: 'OBJECTID', pageSize: 1000,
+ layer: 'https://gis.whatcomcounty.us/arcgis/rest/services/EnterprisePublishing/WhatcomCo_PropertySales/MapServer/0',
+ where: 'sale_price>1000', history: true,
+ outFields: 'gid,pid,deed_date,sale_id,sale_price,sale_type,sale_land_acres',
+ fieldMap: { source_id: 'gid', last_sale_price: 'sale_price', last_sale_date: 'deed_date',
+ doc_type: 'sale_type', doc_number: 'sale_id' },
+ toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.gid); if (!apn) return null;
+ return { fips: '53073', apn, price: anyPrice(a.sale_price), saleDate: anyDate(a.deed_date),
+ docNum: s(a.sale_id), docType: clean(a.sale_type), raw: a } as Rec; }).filter(Boolean) as Rec[],
+ },
};
const execFileP = promisify(execFile);
@@ -770,7 +796,8 @@ export function ingestArcgisSales(key: string) {
if (!prev || (r.last_sale_date || '') > (prev.last_sale_date || '')) byParcel.set(k, r);
}
const dedupRows = [...byParcel.values()];
- const up = await upsertParcels(dedupRows);
+ // History layers (e.g. Whatcom 2020/2021) use date-guarded upsert: never overwrite a newer sale.
+ const up = await (src.history ? upsertParcelHistory(dedupRows) : upsertParcels(dedupRows));
// sale events with price + parties + public-record link
for (let i = 0; i < events.length; i += 500) {
diff --git a/src/ingest/parcels/engine.ts b/src/ingest/parcels/engine.ts
index e361d67..f6a69db 100644
--- a/src/ingest/parcels/engine.ts
+++ b/src/ingest/parcels/engine.ts
@@ -63,6 +63,9 @@ const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: nu
nevada: async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('nevada') }; },
tulare: async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('tulare') }; },
whatcom: async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('whatcom') }; },
+ // TK-10777: Whatcom historical layers — history-safe (date-guarded) upsert
+ 'whatcom-2021': async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('whatcom-2021') }; },
+ 'whatcom-2020': async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('whatcom-2020') }; },
};
async function main() {
diff --git a/src/ingest/parcels/upsert.ts b/src/ingest/parcels/upsert.ts
index fe80c88..45283e6 100644
Binary files a/src/ingest/parcels/upsert.ts and b/src/ingest/parcels/upsert.ts differ
diff --git a/src/jobs/hourly_loop.ts b/src/jobs/hourly_loop.ts
index 617a5c9..1ff5e08 100644
--- a/src/jobs/hourly_loop.ts
+++ b/src/jobs/hourly_loop.ts
@@ -93,7 +93,10 @@ const JOBS: Job[] = [
{ name: 'parcels-nevada',script:'src/ingest/parcels/engine.ts', args: ['nevada'], minIntervalHours: 2, runSources: ['nevada'] },
{ name: 'parcels-tulare',script:'src/ingest/parcels/engine.ts', args: ['tulare'], minIntervalHours: 2, runSources: ['tulare'] },
// TK-16 pass 4: Whatcom County WA (Bellingham, 53073) — genuine priced-deed feed (WhatcomCo_PropertySales excise sales).
- { name: 'deeds-whatcom',script:'src/ingest/parcels/engine.ts', args: ['whatcom'], minIntervalHours: 3, runSources: ['whatcom'] },
+ { name: 'deeds-whatcom', script:'src/ingest/parcels/engine.ts', args: ['whatcom'], minIntervalHours: 3, runSources: ['whatcom'] },
+ // TK-10777: Whatcom history layers (2021/2020) — history-safe upsert, run weekly (data is static/historical).
+ { name: 'deeds-whatcom-2021',script:'src/ingest/parcels/engine.ts', args: ['whatcom-2021'], minIntervalHours: 168, runSources: ['whatcom_2021'] },
+ { name: 'deeds-whatcom-2020',script:'src/ingest/parcels/engine.ts', args: ['whatcom-2020'], minIntervalHours: 168, runSources: ['whatcom_2020'] },
// TK-5: full San Diego County (1.09M) via SANDAG — zoning + assessed value; was in engine but missing from rotation.
{ name: 'parcels-sd-sandag', script: 'src/ingest/parcels/engine.ts', args: ['san-diego-sandag'], minIntervalHours: 2, runSources: ['san_diego_sandag'] },
// TK-5: Santa Clara County CA (San Jose, 493k) — situs address; was in engine but missing from rotation.
← 17bb441 auto-data-snapshot: 2026-08-24T03:41:17 (1 data files) — dat
·
back to Nationalrealestate
·
TK-16: add Josephine County OR (41033) free priced-deed feed d7d2c15 →