← back to Nationalrealestate
src/ingest/parcels/saltlake_ut.ts
150 lines
/**
* Salt Lake County UT (FIPS 49035) parcel ingest — dedicated full-universe adapter
* (DTD verdict B, 2026-07-26: broadened the rich-county hunt to a fresh state after
* NC/FL adjacent counties dead-ended).
*
* Source: Utah's standardized LIR (Land Information Records) parcel FeatureServer —
* a hosted, keyless, paginated ArcGIS service (394,610 parcels, polygon geom, $0):
* https://services1.arcgis.com/99lidPhWCzftIe9K/arcgis/rest/services/Parcels_SaltLake_LIR/FeatureServer/0
*
* Rich on VALUE + characteristics: PARCEL_ADD/CITY, TOTAL_MKT_VALUE + LAND_MKT_VALUE
* (market, not assessed), BLDG_SQFT, BUILT_YR, PROP_CLASS (use), PARCEL_ACRES, floors,
* construction material. Utah withholds OWNER name and there is no SALE feed in the
* public LIR layer — both recorded null. No sale ⇒ no bulk-deed logic needed.
*
* Run: NODE_OPTIONS=--max-old-space-size=4096 npm run ingest:parcels saltlake
*/
import { pool } from '../../../db/pool.ts';
import { openRun, closeRun } from '../run.ts';
import { upsertParcels, registerParcelSource, registerSourceFieldMap, normAddress, type ParcelUpsertRow } from './upsert.ts';
const FIPS = '49035';
const SOURCE_KEY = 'saltlake_ut';
const LAYER = 'https://services1.arcgis.com/99lidPhWCzftIe9K/arcgis/rest/services/Parcels_SaltLake_LIR/FeatureServer/0';
const PAGE = 2000;
// TK-50 field-level provenance: our parcel field → the exact source attribute it
// maps from. source_id prefers PARCEL_ID, else SERIAL_NUM; improvement_value is
// DERIVED (TOTAL_MKT_VALUE − LAND_MKT_VALUE). Utah LIR carries no owner or sale.
const FIELD_MAP: Record<string, string> = {
source_id: 'PARCEL_ID|SERIAL_NUM', address: 'PARCEL_ADD', city: 'PARCEL_CITY',
year_built: 'BUILT_YR', sqft: 'BLDG_SQFT', units: 'HOUSE_CNT', use_desc: 'PROP_CLASS|PROP_TYPE',
land_value: 'LAND_MKT_VALUE', total_value: 'TOTAL_MKT_VALUE',
improvement_value: 'TOTAL_MKT_VALUE-LAND_MKT_VALUE (derived)',
};
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; };
const yr = (v: unknown): number | null => { const x = Number(v); return Number.isFinite(x) && x > 1700 && x < 2100 ? x : null; };
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 = ['PARCEL_ID', 'SERIAL_NUM', 'PARCEL_ADD', 'PARCEL_CITY', 'TOTAL_MKT_VALUE', 'LAND_MKT_VALUE',
'BLDG_SQFT', 'BUILT_YR', 'EFFBUILT_YR', 'PROP_CLASS', 'PROP_TYPE', 'PARCEL_ACRES', 'FLOORS_CNT',
'PRIMARY_RES', 'HOUSE_CNT', 'CONST_MATERIAL', 'CURRENT_ASOF'].join(',');
async function fetchPage(offset: number): Promise<any[]> {
const u = new URL(LAYER + '/query');
u.searchParams.set('where', '1=1');
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, { signal: AbortSignal.timeout(120_000) });
if (!res.ok) throw new Error(`saltlake ${res.status}: ${(await res.text()).slice(0, 140)}`);
const j: any = await res.json();
if (j.error) throw new Error(`saltlake 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 ingestSaltLake(): Promise<{ upserted: number }> {
const runId = await openRun('parcel_saltlake_ut', 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, 'Salt Lake County UT — Utah LIR standardized parcel FeatureServer (keyless ArcGIS)');
let offset = 0, seen = 0, upserted = 0;
for (;;) {
const feats = await fetchPage(offset);
if (!feats.length) break;
// PARCEL_ID is NOT unique per feature (multi-part polygons / condo units share
// one id) — dedupe within the page so a single INSERT never has duplicate
// (county_fips, source_id) keys ("cannot affect row a second time"). Cross-page
// repeats are handled cleanly by ON CONFLICT DO UPDATE. Largest-value row wins.
const batchMap = new Map<string, ParcelUpsertRow>();
for (const f of feats) {
const a = f.attributes || {};
const pid = s(a.PARCEL_ID) || s(a.SERIAL_NUM); if (!pid) continue;
seen++;
const [lng, lat] = centroid(f.geometry);
const addr = s(a.PARCEL_ADD);
const norm = addr ? normAddress(addr) : null;
const total = num(a.TOTAL_MKT_VALUE);
const land = num(a.LAND_MKT_VALUE);
const row: ParcelUpsertRow = {
county_fips: FIPS, source_id: pid,
address: norm, norm_address: norm,
city: s(a.PARCEL_CITY), zip: null,
lat, lng,
year_built: yr(a.BUILT_YR), sqft: num(a.BLDG_SQFT), beds: null, baths: null, // BUILT_YR only; effective/renovation year lives in extra.eff_year_built
units: num(a.HOUSE_CNT),
use_desc: s(a.PROP_CLASS) || s(a.PROP_TYPE),
land_value: land, improvement_value: (total != null && land != null) ? Math.max(total - land, 0) : null,
total_value: total, tax_year: null,
owner_name: null, zoning: null,
last_sale_date: null, last_sale_price: null,
extra: JSON.stringify({
serial_num: s(a.SERIAL_NUM) || undefined, acres: num(a.PARCEL_ACRES) || undefined,
floors: num(a.FLOORS_CNT) || undefined, primary_res: s(a.PRIMARY_RES) || undefined,
const_material: s(a.CONST_MATERIAL) || undefined, eff_year_built: yr(a.EFFBUILT_YR) || undefined,
prop_type: s(a.PROP_TYPE) || undefined, as_of: s(a.CURRENT_ASOF) || undefined,
value_basis: total != null ? 'MARKET value (Utah LIR)' : undefined,
note: 'Utah LIR — market value + characteristics; no owner name or sale feed in the public statewide layer',
}),
// ── TK-50 field-level provenance (record-level: one feature → this row) ──
// address the record by whichever id field supplied source_id (PARCEL_ID
// preferred, else SERIAL_NUM) so the deep link resolves to this parcel.
sourceKey: SOURCE_KEY,
sourceUrl: `${LAYER}/query?where=${encodeURIComponent((s(a.PARCEL_ID) ? 'PARCEL_ID' : 'SERIAL_NUM') + `='${pid}'`)}&outFields=*&f=html`,
fetchedAt,
rawSource: JSON.stringify(a),
};
// skip attribute-less ghost features (ROW/easement polygons UT LIR exports with
// no value/address/sqft/use) — coords only, nothing a property lookup can use.
if (row.total_value == null && row.address == null && row.sqft == null && row.use_desc == null) continue;
const prev = batchMap.get(pid);
if (!prev || (row.total_value ?? 0) > (prev.total_value ?? 0)) batchMap.set(pid, row);
}
upserted += await upsertParcels([...batchMap.values()]);
offset += feats.length;
if (seen % 100000 < feats.length) console.log(`[saltlake] ${upserted} upserted`);
if (feats.length < PAGE) break;
}
if (upserted < 340000) throw new Error(`only ${upserted} Salt Lake parcels (expected ~394k) — layer/paging drift?`);
const n = await registerParcelSource(FIPS, LAYER,
`Salt Lake County UT — Utah LIR standardized parcel FeatureServer (keyless ArcGIS) — ${upserted} parcels: site address, MARKET total+land value, building sqft, year built, use class, acres, floors, construction material, polygon centroid. No owner name or sale feed in the public LIR layer.`);
await closeRun(runId, 'ok', { upserted, notes: `Salt Lake UT: ${upserted} parcels (value+characteristics, no owner/sale)` });
console.log(`[saltlake] ok: ${upserted} parcels (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]}`) {
ingestSaltLake().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
}