← back to Nationalrealestate
TK-5: add 4 metro parcel adapters — Dallas TX, Harris TX, Clark NV, Philadelphia PA
709c1829c29439e1ec640000d534d69ea7bbc952 · 2026-08-10 21:56:29 -0700 · Steve Abrams
Dedicated full-universe adapters (Wake/Tarrant DTD-B precedent), all keyless $0,
verified end-to-end vs local usre DB (real fetch->map->upsert->event path, bounded,
test rows purged). Full backfill + Kamatera deploy remain Steve-gated.
- dallas_tx.ts (48113, ~549k): DCAD City-mirror Tax Account Points, owner ~95% +
TRUE market value ~93%, POINT geom; no sqft/year/sale -> 0 events (Bexar+value).
- harris_tx.ts (48201, ~1.55M, largest in system): HCAD MapServer, owner ~98% +
market value ~98% + lot sqft + deed date (new_owner_date) -> deed events amount NULL.
- clark_nv.ts (32003, ~840k): CC_PARCELS_SHP layer 1, owner ~90% + PRICED sale
(SALEPRICE+SALEDATE+DOCNO ~81%) + year built ~82%; assessed value absent (left NULL,
not fabricated). orderBy OBJECTID_1 (true system id); zip=ZIPMAIN (not packed ZIPCODE).
- philadelphia_pa.ts (42101, ~584k): first Carto SQL adapter, owner ~100% + market
value ~100% + PRICED sale ~99% + year ~92% + sqft ~92% + BEDS/BATHS (first full-universe
county w/ beds/baths) + zoning. Nominal $1 transfers kept per shared price guard.
+2 new states (NV, PA). engine.ts registers dallas/harris/clark/philadelphia.
Contrarian-gated: caught + fixed Clark ZIPCODE->ZIPMAIN packed-9-digit bug.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A src/ingest/parcels/clark_nv.tsA src/ingest/parcels/dallas_tx.tsM src/ingest/parcels/engine.tsA src/ingest/parcels/harris_tx.tsA src/ingest/parcels/philadelphia_pa.ts
Diff
commit 709c1829c29439e1ec640000d534d69ea7bbc952
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Mon Aug 10 21:56:29 2026 -0700
TK-5: add 4 metro parcel adapters — Dallas TX, Harris TX, Clark NV, Philadelphia PA
Dedicated full-universe adapters (Wake/Tarrant DTD-B precedent), all keyless $0,
verified end-to-end vs local usre DB (real fetch->map->upsert->event path, bounded,
test rows purged). Full backfill + Kamatera deploy remain Steve-gated.
- dallas_tx.ts (48113, ~549k): DCAD City-mirror Tax Account Points, owner ~95% +
TRUE market value ~93%, POINT geom; no sqft/year/sale -> 0 events (Bexar+value).
- harris_tx.ts (48201, ~1.55M, largest in system): HCAD MapServer, owner ~98% +
market value ~98% + lot sqft + deed date (new_owner_date) -> deed events amount NULL.
- clark_nv.ts (32003, ~840k): CC_PARCELS_SHP layer 1, owner ~90% + PRICED sale
(SALEPRICE+SALEDATE+DOCNO ~81%) + year built ~82%; assessed value absent (left NULL,
not fabricated). orderBy OBJECTID_1 (true system id); zip=ZIPMAIN (not packed ZIPCODE).
- philadelphia_pa.ts (42101, ~584k): first Carto SQL adapter, owner ~100% + market
value ~100% + PRICED sale ~99% + year ~92% + sqft ~92% + BEDS/BATHS (first full-universe
county w/ beds/baths) + zoning. Nominal $1 transfers kept per shared price guard.
+2 new states (NV, PA). engine.ts registers dallas/harris/clark/philadelphia.
Contrarian-gated: caught + fixed Clark ZIPCODE->ZIPMAIN packed-9-digit bug.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
src/ingest/parcels/clark_nv.ts | 185 ++++++++++++++++++++++++++++++++++
src/ingest/parcels/dallas_tx.ts | 154 ++++++++++++++++++++++++++++
src/ingest/parcels/engine.ts | 4 +
src/ingest/parcels/harris_tx.ts | 184 +++++++++++++++++++++++++++++++++
src/ingest/parcels/philadelphia_pa.ts | 167 ++++++++++++++++++++++++++++++
5 files changed, 694 insertions(+)
diff --git a/src/ingest/parcels/clark_nv.ts b/src/ingest/parcels/clark_nv.ts
new file mode 100644
index 0000000..7455e08
--- /dev/null
+++ b/src/ingest/parcels/clark_nv.ts
@@ -0,0 +1,185 @@
+/**
+ * Clark County NV (Las Vegas, FIPS 32003) parcel ingest — dedicated full-universe
+ * adapter (applies the Wake/Miami-Dade DTD verdict B precedent).
+ *
+ * Source: the Clark County GIS Office keyless hosted ArcGIS FeatureServer ($0,
+ * supportsPagination, 2k/page). NOTE the parcels layer is at index 1, not 0:
+ * https://services1.arcgis.com/F1v0ufATbBQScMtY/arcgis/rest/services/CC_PARCELS_SHP/FeatureServer/1
+ * (The county's gisgate.co.clark.nv.us REST server and the Hub open-data host are
+ * both blocked/private to headless; this hosted layer is the reachable path.)
+ *
+ * A rich PRICED-SALE county: OWNER (~90%), ADDRESS (situs, ~90%), SALEPRICE
+ * (real dollars, ~81%) + SALEDATE (YYYYMMDD) + DOCNO (recorded-doc number),
+ * CONSTYR (year built, ~82%), LOTSQFT (lot sqft), LUCODE (land-use code). The one
+ * gap: assessed/market VALUE is NOT in this GIS layer (the IMPVAL/LANDVAL columns
+ * exist but are 100% zero) — so land/improvement/total value are left NULL rather
+ * than fabricated. This is still richer than the parcel-detail-only TX counties
+ * because it carries a genuine priced last-sale.
+ *
+ * source_id = PARCEL (the APN). Rows with no PARCEL are skipped.
+ *
+ * Run: NODE_OPTIONS=--max-old-space-size=4096 npm run ingest:parcels clark
+ */
+import { pool, query } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import { upsertParcels, registerParcelSource, registerSourceFieldMap, normAddress, type ParcelUpsertRow } from './upsert.ts';
+import { sanitizeEventDate } from './date_guard.ts';
+import { sanitizeSalePrice } from './price_guard.ts';
+
+const FIPS = '32003';
+const SOURCE_KEY = 'clark_nv';
+const LAYER = 'https://services1.arcgis.com/F1v0ufATbBQScMtY/arcgis/rest/services/CC_PARCELS_SHP/FeatureServer/1';
+const PAGE = 2000;
+// TK-50 field-level provenance. No value mapping — the assessed-value columns in
+// this feed are all zero (see header); we do not map a fabricated value.
+const FIELD_MAP: Record<string, string> = {
+ source_id: 'PARCEL', address: 'ADDRESS', city: 'STRCITY', zip: 'ZIPMAIN',
+ year_built: 'CONSTYR', use_desc: 'LUCODE', owner_name: 'OWNER',
+ last_sale_price: 'SALEPRICE', last_sale_date: 'SALEDATE',
+};
+
+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; };
+
+/** SALEDATE is a "YYYYMMDD" string → ISO 'YYYY-MM-DD', then the shared sanity
+ * guard (future / pre-1900 / non-calendar reject). Null on malformed. */
+function saleDateToISO(v: unknown): string | null {
+ const t = s(v);
+ if (!t) return null;
+ const m = t.match(/^(\d{4})(\d{2})(\d{2})$/);
+ if (!m) return null;
+ return sanitizeEventDate(`${m[1]}-${m[2]}-${m[3]}`);
+}
+
+/** plain vertex-average of ring[0] — NOT a true polygon centroid (fine for a map
+ * pin; multipart parcels only get the first ring). */
+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)];
+}
+
+// NOTE: use ZIPMAIN (the clean 5-digit zip), NOT ZIPCODE — ZIPCODE is a packed
+// 9-digit integer (zip+plus4, e.g. 891490000) that would store as garbage.
+const OUT = ['PARCEL', 'OWNER', 'ADDRESS', 'STRCITY', 'ZIPMAIN', 'CONSTYR', 'LOTSQFT',
+ 'LUCODE', 'LANDUSE', 'SALEPRICE', 'SALEDATE', 'SALETYPE', 'DOCNO', 'DOCDATE'].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);
+ // page by the layer's system-maintained unique id (OBJECTID_1, NOT OBJECTID) so
+ // resultOffset paging is stable + complete across the full ~840k universe. The
+ // low-id head is blank-owner shells (real but empty parcels); real owner/sale
+ // rows fill in beyond it — a full run captures all ~754k owned parcels.
+ u.searchParams.set('orderByFields', 'OBJECTID_1 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(`clark ${res.status}: ${(await res.text()).slice(0, 140)}`);
+ const j: any = await res.json();
+ if (j.error) throw new Error(`clark 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 ingestClark(opts: { maxPages?: number } = {}): Promise<{ upserted: number }> {
+ const runId = await openRun('parcel_clark_nv', LAYER);
+ const fetchedAt = new Date().toISOString();
+ try {
+ await registerSourceFieldMap(SOURCE_KEY, FIELD_MAP, 'Clark County NV GIS Office (CC_PARCELS_SHP hosted ArcGIS layer)');
+ let offset = 0, page = 0, seen = 0, skipped = 0, upserted = 0, sales = 0;
+ for (;;) {
+ const feats = await fetchPage(offset);
+ if (!feats.length) break;
+ const batch: ParcelUpsertRow[] = [];
+ const events: { apn: string; price: number; date: string; docno: string | null }[] = [];
+ for (const f of feats) {
+ const a = f.attributes || {};
+ const apn = s(a.PARCEL);
+ if (!apn) { skipped++; continue; }
+ seen++;
+ const [lng, lat] = centroid(f.geometry);
+ // fixed-width address string ("000713 E LAKE MEAD BLVD") → normAddress
+ // collapses the internal padding.
+ const addr = s(a.ADDRESS);
+ const norm = addr ? normAddress(addr) : null;
+ const salePrice = sanitizeSalePrice(a.SALEPRICE);
+ const saleDate = saleDateToISO(a.SALEDATE);
+ const docno = s(a.DOCNO);
+ batch.push({
+ county_fips: FIPS, source_id: apn,
+ address: norm, norm_address: norm,
+ // ZIPMAIN is an integer 5-digit zip — pad defensively (NV zips don't lead
+ // with 0, but keep it safe). STRCITY is a Clark internal abbreviation
+ // ('LV','NLV','HEND','MES'...), NOT a display city — noted in extra below.
+ city: s(a.STRCITY), zip: a.ZIPMAIN ? String(a.ZIPMAIN).padStart(5, '0') : null,
+ lat, lng,
+ year_built: num(a.CONSTYR), sqft: null, beds: null, baths: null, units: null,
+ use_desc: s(a.LUCODE),
+ // assessed value not present in this feed (columns all zero) — leave NULL.
+ land_value: null, improvement_value: null, total_value: null,
+ tax_year: null, owner_name: s(a.OWNER), zoning: null,
+ last_sale_date: saleDate, last_sale_price: salePrice,
+ extra: JSON.stringify({
+ land_use: s(a.LANDUSE) || undefined,
+ lot_sqft: num(a.LOTSQFT) || undefined,
+ sale_type: s(a.SALETYPE) || undefined,
+ doc_date: s(a.DOCDATE) || undefined,
+ city_abbrev: s(a.STRCITY) || undefined, // STRCITY is a Clark abbreviation (LV/NLV/HEND/MES), not a display-ready city name
+ note: 'Clark County NV GIS — owner + priced last-sale + year built + use; assessed value NOT in this GIS layer (value columns all zero, left NULL); city is a Clark internal abbreviation',
+ }),
+ // ── TK-50 field-level provenance (record-level: one feature → this row) ──
+ sourceKey: SOURCE_KEY,
+ sourceUrl: `${LAYER}/query?where=${encodeURIComponent(`PARCEL='${apn}'`)}&outFields=*&f=html`,
+ fetchedAt,
+ rawSource: JSON.stringify(a),
+ });
+ if (salePrice && saleDate) events.push({ apn, price: salePrice, date: saleDate, docno });
+ }
+ upserted += await upsertParcels(batch);
+ // priced last-sale events (amount = SALEPRICE). doc_number = DOCNO (real
+ // recorded-document number) when present, else the sale date.
+ for (let i = 0; i < events.length; i += 500) {
+ const chunk = events.slice(i, i + 500);
+ await query(
+ `INSERT INTO parcel_event (county_fips, source_id, event_type, event_date, amount, doc_type, doc_number, source, source_url, detail)
+ VALUES ${chunk.map((_, j) => { const b = j * 9; return `($${b + 1},$${b + 2},'sale',$${b + 3}::date,$${b + 4},$${b + 5},$${b + 6},$${b + 7},$${b + 8},$${b + 9}::jsonb)`; }).join(',')}
+ ON CONFLICT (county_fips, source_id, event_type, event_date, doc_number) DO NOTHING`,
+ chunk.flatMap(e => [FIPS, e.apn, e.date, e.price, 'Deed', e.docno ?? e.date,
+ 'parcel_clark_nv', `https://maps.clarkcountynv.gov/assessor/AssessorParcelDetail/parcel.aspx?instance=pcl1&parcel=${e.apn}`, JSON.stringify({ docno: e.docno })]),
+ );
+ sales += chunk.length;
+ }
+ offset += feats.length;
+ page++;
+ if (seen % 100000 < feats.length) console.log(`[clark] ${upserted} upserted, ${sales} sales, ${skipped} skipped`);
+ if (opts.maxPages && page >= opts.maxPages) break;
+ if (feats.length < PAGE) break;
+ }
+ if (!opts.maxPages) {
+ if (upserted < 780000) throw new Error(`only ${upserted} Clark parcels (expected ~840k) — layer/paging drift?`);
+ const n = await registerParcelSource(FIPS, LAYER,
+ `Clark County NV (Las Vegas) GIS Office parcels (CC_PARCELS_SHP hosted ArcGIS layer) — ${upserted} parcels: owner (~90%), situs address, priced last-sale (SALEPRICE + SALEDATE + DOCNO, ~81%) → ${sales} parcel_event sale rows, year built (CONSTYR, ~82%), lot sqft, land-use code. Assessed/market value NOT in this GIS layer (value columns all zero, left NULL). ${skipped} PARCEL-less rows skipped.`);
+ console.log(`[clark] registry ${n}`);
+ }
+ await closeRun(runId, 'ok', { upserted, skipped, notes: `Clark NV: ${upserted} parcels, ${sales} sales, ${skipped} skipped${opts.maxPages ? ' (bounded verify)' : ''}` });
+ console.log(`[clark] ok: ${upserted} parcels, ${sales} sales, ${skipped} skipped${opts.maxPages ? ` (bounded ${opts.maxPages}p)` : ''}`);
+ 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]}`) {
+ ingestClark().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
+}
diff --git a/src/ingest/parcels/dallas_tx.ts b/src/ingest/parcels/dallas_tx.ts
new file mode 100644
index 0000000..30ab3da
--- /dev/null
+++ b/src/ingest/parcels/dallas_tx.ts
@@ -0,0 +1,154 @@
+/**
+ * Dallas County TX (Dallas, FIPS 48113) parcel ingest — dedicated full-universe
+ * adapter (applies the Wake/Tarrant DTD verdict B precedent).
+ *
+ * Source: the City of Dallas GIS mirror of the DCAD appraisal-account file — the
+ * "Tax Account Points" layer (keyless, supportsPagination, 2k/page, POINT geom,
+ * $0):
+ * https://services2.arcgis.com/rwnOSbfKSwyTBcwN/arcgis/rest/services/CRMHostedLayers/FeatureServer/0
+ * (DCAD's own hosts — maps.dcad.org / gis.dcad.org / dallascad.org — 404 /
+ * connection-refuse to headless; this City mirror is the reachable full-county
+ * account universe, ~548,830 accounts.)
+ *
+ * Coverage: Owner1 (~95%), situs address (SiteAddrNum + SiteStreetname, ~95%),
+ * LandVal + ImpVal + TotalVal (TRUE market/total value, ~93%), PropClass (use,
+ * ~94%), AppraisalYr (tax year), CityJuris (city). This is a parcel-detail +
+ * VALUE feed with NO sqft, NO year_built, and NO structured sale/deed feed (deed
+ * instrument text lives unparsed inside Legal4) → like Bexar, ZERO parcel_event
+ * rows are written; no sale is ever fabricated.
+ *
+ * source_id = AccountID (the 17-digit DCAD account). Rows with no AccountID
+ * (mineral / personal-property shells) are skipped.
+ *
+ * Run: NODE_OPTIONS=--max-old-space-size=4096 npm run ingest:parcels dallas
+ */
+import { pool } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import { upsertParcels, registerParcelSource, registerSourceFieldMap, normAddress, type ParcelUpsertRow } from './upsert.ts';
+
+const FIPS = '48113';
+const SOURCE_KEY = 'dallas_tx';
+const LAYER = 'https://services2.arcgis.com/rwnOSbfKSwyTBcwN/arcgis/rest/services/CRMHostedLayers/FeatureServer/0';
+const PAGE = 2000;
+// TK-50 field-level provenance. total_value maps from TotalVal (TRUE market/total).
+const FIELD_MAP: Record<string, string> = {
+ source_id: 'AccountID', address: 'SiteAddrNum+SiteStreetname', city: 'CityJuris', tax_year: 'AppraisalYr',
+ use_desc: 'PropClass', land_value: 'LandVal', improvement_value: 'ImpVal', total_value: 'TotalVal',
+ owner_name: 'Owner1',
+};
+
+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; };
+
+/** situs address = SiteAddrNum + ' ' + SiteStreetname (some SiteStreetname values
+ * carry a trailing space — trim/collapse). */
+function situsAddr(a: any): string | null {
+ const joined = [s(a.SiteAddrNum), s(a.SiteStreetname)].filter((x): x is string => !!x).join(' ').replace(/\s+/g, ' ').trim();
+ return joined || null;
+}
+
+/** This layer is a POINT layer — geometry is {x,y} in WGS84 (outSR=4326), not
+ * polygon rings. Return [lng, lat]. */
+function pointLngLat(geom: any): [number | null, number | null] {
+ const x = Number(geom?.x), y = Number(geom?.y);
+ if (!Number.isFinite(x) || !Number.isFinite(y) || (x === 0 && y === 0)) return [null, null];
+ return [+x.toFixed(6), +y.toFixed(6)];
+}
+
+const OUT = ['AccountID', 'ParcelID', 'Owner1', 'Owner2', 'SiteAddrNum', 'SiteStreetname',
+ 'CityJuris', 'CountyJuris', 'AppraisalYr', 'LandVal', 'ImpVal', 'TotalVal',
+ 'PropClass', 'BldgClass', 'ResCom', 'NbhdCode'].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, { headers: { 'User-Agent': 'Mozilla/5.0 (usre-parcel-ingest)' }, signal: AbortSignal.timeout(120_000) });
+ if (!res.ok) throw new Error(`dallas ${res.status}: ${(await res.text()).slice(0, 140)}`);
+ const j: any = await res.json();
+ if (j.error) throw new Error(`dallas 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 ingestDallas(opts: { maxPages?: number } = {}): Promise<{ upserted: number }> {
+ const runId = await openRun('parcel_dallas_tx', LAYER);
+ const fetchedAt = new Date().toISOString();
+ try {
+ await registerSourceFieldMap(SOURCE_KEY, FIELD_MAP, 'Dallas County TX (DCAD) via City of Dallas GIS Tax Account Points mirror');
+ let offset = 0, page = 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 || {};
+ const acct = s(a.AccountID);
+ if (!acct) { skipped++; continue; }
+ seen++;
+ const [lng, lat] = pointLngLat(f.geometry);
+ const addr = situsAddr(a);
+ const norm = addr ? normAddress(addr) : null;
+ batch.push({
+ county_fips: FIPS, source_id: acct,
+ address: norm, norm_address: norm,
+ city: s(a.CityJuris), zip: null,
+ lat, lng,
+ year_built: null, sqft: null, beds: null, baths: null, units: null,
+ use_desc: s(a.PropClass),
+ land_value: num(a.LandVal), improvement_value: num(a.ImpVal), total_value: num(a.TotalVal),
+ tax_year: s(a.AppraisalYr), owner_name: s(a.Owner1), zoning: null,
+ // no sale/deed feed in this layer → no last-sale; never fabricated.
+ last_sale_date: null, last_sale_price: null,
+ extra: JSON.stringify({
+ parcel_id: s(a.ParcelID) || undefined,
+ owner_2: s(a.Owner2) || undefined,
+ bldg_class: s(a.BldgClass) || undefined,
+ res_com: s(a.ResCom) || undefined,
+ nbhd: s(a.NbhdCode) || undefined,
+ county_juris: s(a.CountyJuris) || undefined,
+ note: 'Dallas County TX (DCAD) via City of Dallas GIS Tax Account Points mirror — TRUE market/total value; no sqft, year built, or sale feed in this layer (deed text lives unparsed in Legal4)',
+ }),
+ // ── TK-50 field-level provenance (record-level: one feature → this row) ──
+ sourceKey: SOURCE_KEY,
+ sourceUrl: `${LAYER}/query?where=${encodeURIComponent(`AccountID='${acct}'`)}&outFields=*&f=html`,
+ fetchedAt,
+ rawSource: JSON.stringify(a),
+ });
+ }
+ upserted += await upsertParcels(batch);
+ offset += feats.length;
+ page++;
+ if (seen % 100000 < feats.length) console.log(`[dallas] ${upserted} upserted, ${skipped} stubs skipped`);
+ if (opts.maxPages && page >= opts.maxPages) break;
+ if (feats.length < PAGE) break;
+ }
+ if (!opts.maxPages) {
+ if (upserted < 480000) throw new Error(`only ${upserted} Dallas parcels (expected ~549k) — layer/paging drift?`);
+ const n = await registerParcelSource(FIPS, LAYER,
+ `Dallas County TX (Dallas) DCAD appraisal accounts via City of Dallas GIS Tax Account Points mirror — ${upserted} parcels: owner (~95%), situs address, LAND+IMP + TRUE MARKET/total value (~93%), property-class use, appraisal year, jurisdiction city. No sqft, year built, beds/baths, or sale/deed feed in this layer (deed text unparsed in Legal4) → ZERO parcel_event rows (no fabricated sales). ${skipped} account-less (mineral/personal-property) rows skipped.`);
+ console.log(`[dallas] registry ${n}`);
+ }
+ await closeRun(runId, 'ok', { upserted, skipped, notes: `Dallas TX: ${upserted} parcels, ${skipped} stubs${opts.maxPages ? ' (bounded verify)' : ''}` });
+ console.log(`[dallas] ok: ${upserted} parcels, 0 events, ${skipped} stubs${opts.maxPages ? ` (bounded ${opts.maxPages}p)` : ''}`);
+ 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]}`) {
+ ingestDallas().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
+}
diff --git a/src/ingest/parcels/engine.ts b/src/ingest/parcels/engine.ts
index 9deedff..c61cc47 100644
--- a/src/ingest/parcels/engine.ts
+++ b/src/ingest/parcels/engine.ts
@@ -17,6 +17,10 @@ const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: nu
wake: async () => ({ run: (await import('./wake_nc.ts')).ingestWake }),
tarrant: async () => ({ run: (await import('./tarrant_tx.ts')).ingestTarrant }),
bexar: async () => ({ run: (await import('./bexar_tx.ts')).ingestBexar }),
+ dallas: async () => ({ run: (await import('./dallas_tx.ts')).ingestDallas }),
+ harris: async () => ({ run: (await import('./harris_tx.ts')).ingestHarris }),
+ clark: async () => ({ run: (await import('./clark_nv.ts')).ingestClark }),
+ philadelphia: async () => ({ run: (await import('./philadelphia_pa.ts')).ingestPhiladelphia }),
fulton: async () => ({ run: (await import('./fulton_ga.ts')).ingestFulton }),
saltlake: async () => ({ run: (await import('./saltlake_ut.ts')).ingestSaltLake }),
sandiego: async () => { const m = await import('./sandiego_ca.ts'); return { run: () => m.ingestSanDiego('poway') }; },
diff --git a/src/ingest/parcels/harris_tx.ts b/src/ingest/parcels/harris_tx.ts
new file mode 100644
index 0000000..547c1d0
--- /dev/null
+++ b/src/ingest/parcels/harris_tx.ts
@@ -0,0 +1,184 @@
+/**
+ * Harris County TX (Houston, FIPS 48201) parcel ingest — dedicated full-universe
+ * adapter (applies the Wake/Tarrant DTD verdict B precedent). The single largest
+ * county in the system: ~1.55M parcels (3rd-largest US county).
+ *
+ * Source: the Harris County public GIS server, the HCAD/Parcels MapServer layer 0
+ * (keyless, supportsPagination, 1k/page, $0):
+ * https://www.gis.hctx.net/arcgis/rest/services/HCAD/Parcels/MapServer/0
+ * The full HCAD appraisal roll (owner + all value columns + land_use + legal) is
+ * live in this single layer — NO bulk-text-download detour is needed (the
+ * services.arcgis.com "COH" hosted layers are project subsets, not the county
+ * universe; only www.gis.hctx.net carries the full feed).
+ *
+ * Coverage: owner_name_1 (~98%), situs address (~98%, from the site_str_* parts),
+ * total_market_val (~98%, TRUE market value), land_value + bld_value, land_sqft
+ * (~66%, LOT area — this feed has no building sqft), land_use + state_class (use),
+ * new_owner_date (~98%, an ownership-change/deed DATE, epoch-ms — NO sale price
+ * in this feed). Like Tarrant, deed events are recorded honestly (event_type
+ * 'deed', amount NULL — never a fabricated price). No year_built or beds/baths.
+ *
+ * source_id = HCAD_NUM (the 13-digit HCAD account). Rows with no HCAD_NUM are
+ * geometry-only stubs and are skipped.
+ *
+ * Run: NODE_OPTIONS=--max-old-space-size=4096 npm run ingest:parcels harris
+ */
+import { pool, query } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import { upsertParcels, registerParcelSource, registerSourceFieldMap, normAddress, type ParcelUpsertRow } from './upsert.ts';
+import { sanitizeEventDate } from './date_guard.ts';
+
+const FIPS = '48201';
+const SOURCE_KEY = 'harris_tx';
+const LAYER = 'https://www.gis.hctx.net/arcgis/rest/services/HCAD/Parcels/MapServer/0';
+const PAGE = 1000; // HCAD maxRecordCount is 1000
+// TK-50 field-level provenance. total_value maps from total_market_val (TRUE
+// market). This feed carries a deed/ownership-change DATE but no sale price.
+const FIELD_MAP: Record<string, string> = {
+ source_id: 'HCAD_NUM', city: 'site_city', zip: 'site_zip',
+ sqft: 'land_sqft', use_desc: 'dscr',
+ land_value: 'land_value', improvement_value: 'bld_value', total_value: 'total_market_val',
+ owner_name: 'owner_name_1', last_sale_date: 'new_owner_date',
+};
+
+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; };
+// new_owner_date is an esri date (epoch-ms) — reject <=0 and sub-1971 near-epoch
+// artifacts, then run the shared future/pre-1900/non-calendar guard.
+const epochToISO = (v: unknown): string | null => { const x = Number(v); if (!Number.isFinite(x) || x < 31_536_000_000) return null; return sanitizeEventDate(new Date(x).toISOString().slice(0, 10)); };
+
+/** Assemble the situs address from HCAD's decomposed street parts, collapsing the
+ * gaps left by empty prefix/suffix/direction components. */
+function situsAddr(a: any): string | null {
+ const parts = [a.site_str_num, a.site_str_pfx, a.site_str_name, a.site_str_sfx, a.site_str_sfx_dir]
+ .map(s).filter((x): x is string => !!x);
+ const joined = parts.join(' ').replace(/\s+/g, ' ').trim();
+ return joined || null;
+}
+
+/** plain vertex-average of ring[0] — NOT a true polygon centroid (fine for a map
+ * pin; multipart parcels only get the first ring). */
+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 = ['HCAD_NUM', 'owner_name_1', 'owner_name_2',
+ 'site_str_num', 'site_str_pfx', 'site_str_name', 'site_str_sfx', 'site_str_sfx_dir',
+ 'site_city', 'site_zip', 'land_value', 'bld_value', 'total_market_val', 'total_appraised_val',
+ 'land_sqft', 'land_use', 'state_class', 'dscr', 'new_owner_date'].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, { headers: { 'User-Agent': 'Mozilla/5.0 (usre-parcel-ingest)' }, signal: AbortSignal.timeout(120_000) });
+ if (!res.ok) throw new Error(`harris ${res.status}: ${(await res.text()).slice(0, 140)}`);
+ const j: any = await res.json();
+ if (j.error) throw new Error(`harris 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 ingestHarris(opts: { maxPages?: number } = {}): Promise<{ upserted: number }> {
+ const runId = await openRun('parcel_harris_tx', LAYER);
+ const fetchedAt = new Date().toISOString();
+ try {
+ await registerSourceFieldMap(SOURCE_KEY, FIELD_MAP, 'Harris County TX (HCAD) via Harris County public GIS (HCAD/Parcels MapServer)');
+ let offset = 0, page = 0, seen = 0, skipped = 0, upserted = 0, deeds = 0;
+ for (;;) {
+ const feats = await fetchPage(offset);
+ if (!feats.length) break;
+ const batch: ParcelUpsertRow[] = [];
+ const events: { acct: string; date: string }[] = [];
+ for (const f of feats) {
+ const a = f.attributes || {};
+ const acct = s(a.HCAD_NUM);
+ if (!acct) { skipped++; continue; }
+ seen++;
+ const [lng, lat] = centroid(f.geometry);
+ const addr = situsAddr(a);
+ const norm = addr ? normAddress(addr) : null;
+ const deedDate = epochToISO(a.new_owner_date);
+ // use: prefer the human-readable land-use description, fall back to the
+ // state class code (A1/X1/...) so use_desc is never left blank when a code exists.
+ const use = s(a.dscr) ?? s(a.state_class);
+ batch.push({
+ county_fips: FIPS, source_id: acct,
+ address: norm, norm_address: norm,
+ city: s(a.site_city), zip: s(a.site_zip),
+ lat, lng,
+ // land_sqft is LOT area (this feed has no building/living sqft); carry it
+ // in extra rather than mislabeling it as building sqft.
+ year_built: null, sqft: null, beds: null, baths: null, units: null,
+ use_desc: use,
+ land_value: num(a.land_value), improvement_value: num(a.bld_value), total_value: num(a.total_market_val),
+ tax_year: null, owner_name: s(a.owner_name_1), zoning: null,
+ // ownership-change date, NOT a priced sale — record the date, leave price NULL.
+ last_sale_date: deedDate, last_sale_price: null,
+ extra: JSON.stringify({
+ owner_2: s(a.owner_name_2) || undefined,
+ appraised_value: num(a.total_appraised_val) || undefined,
+ land_use: s(a.land_use) || undefined,
+ state_class: s(a.state_class) || undefined,
+ land_sqft: num(a.land_sqft) || undefined,
+ note: 'Harris County TX (HCAD) via county public GIS — TRUE market value; land_sqft is LOT area; no building sqft/year/beds/baths and no sale PRICE (deed date only) in this feed',
+ }),
+ // ── TK-50 field-level provenance (record-level: one feature → this row) ──
+ sourceKey: SOURCE_KEY,
+ sourceUrl: `${LAYER}/query?where=${encodeURIComponent(`HCAD_NUM='${acct}'`)}&outFields=*&f=html`,
+ fetchedAt,
+ rawSource: JSON.stringify(a),
+ });
+ if (deedDate) events.push({ acct, date: deedDate });
+ }
+ upserted += await upsertParcels(batch);
+ // deed/ownership-change events (no amount — this feed has no price). j*8 (8
+ // bind params; the 'sale'/amount slot is a hardcoded NULL).
+ for (let i = 0; i < events.length; i += 500) {
+ const chunk = events.slice(i, i + 500);
+ await query(
+ `INSERT INTO parcel_event (county_fips, source_id, event_type, event_date, amount, doc_type, doc_number, source, source_url, detail)
+ VALUES ${chunk.map((_, j) => { const b = j * 8; return `($${b + 1},$${b + 2},'deed',$${b + 3}::date,NULL,$${b + 4},$${b + 5},$${b + 6},$${b + 7},$${b + 8}::jsonb)`; }).join(',')}
+ ON CONFLICT (county_fips, source_id, event_type, event_date, doc_number) DO NOTHING`,
+ chunk.flatMap(e => [FIPS, e.acct, e.date, 'Deed', e.date,
+ 'parcel_harris_tx', `https://public.hcad.org/records/details.asp?acct=${e.acct}`, JSON.stringify({ note: 'ownership-change date only, no price in HCAD GIS feed' })]),
+ );
+ deeds += chunk.length;
+ }
+ offset += feats.length;
+ page++;
+ if (seen % 100000 < feats.length) console.log(`[harris] ${upserted} upserted, ${deeds} deeds, ${skipped} stubs`);
+ if (opts.maxPages && page >= opts.maxPages) break;
+ if (feats.length < PAGE) break;
+ }
+ if (!opts.maxPages) {
+ if (upserted < 1400000) throw new Error(`only ${upserted} Harris parcels (expected ~1.55M) — layer/paging drift?`);
+ const n = await registerParcelSource(FIPS, LAYER,
+ `Harris County TX (Houston) HCAD appraisal roll via Harris County public GIS (HCAD/Parcels MapServer) — ${upserted} parcels: owner (~98%), situs address, LAND+BLDG + TRUE MARKET value (~98%) + appraised value, lot sqft (~66%), land-use + state class, deed/ownership-change date (~98%) → ${deeds} parcel_event deed rows. No building sqft, year built, beds/baths, or sale PRICE in this feed (deed date only, amount NULL). ${skipped} geometry-only stubs skipped.`);
+ console.log(`[harris] registry ${n}`);
+ }
+ await closeRun(runId, 'ok', { upserted, skipped, notes: `Harris TX: ${upserted} parcels, ${deeds} deed events, ${skipped} stubs${opts.maxPages ? ' (bounded verify)' : ''}` });
+ console.log(`[harris] ok: ${upserted} parcels, ${deeds} deed events, ${skipped} stubs${opts.maxPages ? ` (bounded ${opts.maxPages}p)` : ''}`);
+ 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]}`) {
+ ingestHarris().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
+}
diff --git a/src/ingest/parcels/philadelphia_pa.ts b/src/ingest/parcels/philadelphia_pa.ts
new file mode 100644
index 0000000..7c0c685
--- /dev/null
+++ b/src/ingest/parcels/philadelphia_pa.ts
@@ -0,0 +1,167 @@
+/**
+ * Philadelphia PA (FIPS 42101) parcel ingest — dedicated full-universe adapter
+ * (applies the Wake/Miami-Dade DTD verdict B precedent: a rich full-universe
+ * keyless source gets a dedicated adapter so the real OWNER + market value +
+ * priced sale are preserved).
+ *
+ * Source: the Philadelphia OPA (Office of Property Assessment) "opa_properties_
+ * public" dataset on OpenDataPhilly, served by the keyless Carto SQL API ($0):
+ * https://phl.carto.com/api/v2/sql
+ * This is the FIRST adapter over a Carto SQL feed (all prior adapters are ArcGIS
+ * or Socrata). Pagination is plain SQL LIMIT/OFFSET over a stable ORDER BY
+ * parcel_number; geometry is a PostGIS point, so lat/lng come back as computed
+ * ST_Y/ST_X columns (no centroid math needed).
+ *
+ * Richest county in the system: owner_1 (~100%), location (situs address),
+ * market_value (~100%, TRUE market value), sale_price + sale_date (~99%, a real
+ * priced last-sale — 73% arm's-length >$1, the rest nominal $1 family/deed
+ * transfers which the shared price guard keeps as legitimate low-consideration
+ * sales), year_built (92%), total_livable_area (sqft, 92%), number_of_bedrooms
+ * + number_of_bathrooms (the ONLY full-universe county so far with beds/baths),
+ * zoning, category_code_description (use). taxable_land/taxable_building (the
+ * post-abatement taxable split) are carried in extra.
+ *
+ * source_id = parcel_number (the canonical OPA account, unique + stable).
+ *
+ * Run: NODE_OPTIONS=--max-old-space-size=4096 npm run ingest:parcels philadelphia
+ */
+import { pool, query } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+import { upsertParcels, registerParcelSource, registerSourceFieldMap, normAddress, type ParcelUpsertRow } from './upsert.ts';
+import { sanitizeEventDate } from './date_guard.ts';
+import { sanitizeSalePrice } from './price_guard.ts';
+
+const FIPS = '42101';
+const SOURCE_KEY = 'philadelphia_pa';
+const SQL_API = 'https://phl.carto.com/api/v2/sql';
+const TABLE = 'opa_properties_public';
+const PAGE = 2000;
+// TK-50 field-level provenance: our parcel field → the exact source column.
+const FIELD_MAP: Record<string, string> = {
+ source_id: 'parcel_number', address: 'location', zip: 'zip_code',
+ year_built: 'year_built', sqft: 'total_livable_area', beds: 'number_of_bedrooms',
+ baths: 'number_of_bathrooms', use_desc: 'category_code_description', zoning: 'zoning',
+ total_value: 'market_value', owner_name: 'owner_1',
+ last_sale_price: 'sale_price', last_sale_date: 'sale_date',
+};
+
+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; };
+// year_built is a varchar in OPA ("1960", occasionally "" / "0") — accept a plain
+// 3-4 digit year, reject everything else.
+const yearNum = (v: unknown): number | null => { const t = s(v); if (!t || !/^\d{3,4}$/.test(t)) return null; const y = Number(t); return y > 0 ? y : null; };
+// sale_date is a timestamptz ("2024-06-17T04:00:00Z") — take the calendar date,
+// then run it through the shared future/pre-1900/non-calendar guard.
+const saleDateToISO = (v: unknown): string | null => { const t = s(v); if (!t) return null; return sanitizeEventDate(t.slice(0, 10)); };
+
+// The exact columns pulled per row (lat/lng are computed from the_geom).
+const COLS = ['parcel_number', 'owner_1', 'owner_2', 'location', 'zip_code',
+ 'market_value', 'taxable_land', 'taxable_building', 'sale_price', 'sale_date',
+ 'year_built', 'total_livable_area', 'total_area', 'category_code_description',
+ 'number_of_bedrooms', 'number_of_bathrooms', 'zoning',
+ 'ST_Y(the_geom) AS lat', 'ST_X(the_geom) AS lng'].join(',');
+
+async function fetchPage(offset: number): Promise<any[]> {
+ const sql = `SELECT ${COLS} FROM ${TABLE} ORDER BY parcel_number LIMIT ${PAGE} OFFSET ${offset}`;
+ const u = new URL(SQL_API);
+ u.searchParams.set('q', sql);
+ 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(`philly ${res.status}: ${(await res.text()).slice(0, 140)}`);
+ const j: any = await res.json();
+ if (j.error) throw new Error(`philly error: ${JSON.stringify(j.error).slice(0, 140)}`);
+ return j.rows || [];
+ } catch (e) { lastErr = e; if (a < 2) await new Promise(r => setTimeout(r, 2000 * (a + 1))); }
+ }
+ throw lastErr;
+}
+
+export async function ingestPhiladelphia(opts: { maxPages?: number } = {}): Promise<{ upserted: number }> {
+ const runId = await openRun('parcel_philadelphia_pa', SQL_API);
+ const fetchedAt = new Date().toISOString();
+ try {
+ await registerSourceFieldMap(SOURCE_KEY, FIELD_MAP, 'Philadelphia OPA (opa_properties_public) via keyless Carto SQL API');
+ let offset = 0, page = 0, seen = 0, upserted = 0, sales = 0;
+ for (;;) {
+ const rows = await fetchPage(offset);
+ if (!rows.length) break;
+ const batch: ParcelUpsertRow[] = [];
+ const events: { id: string; price: number; date: string }[] = [];
+ for (const a of rows) {
+ const id = s(a.parcel_number);
+ if (!id) continue;
+ seen++;
+ const addr = s(a.location);
+ const norm = addr ? normAddress(addr) : null;
+ const salePrice = sanitizeSalePrice(a.sale_price);
+ const saleDate = saleDateToISO(a.sale_date);
+ const lat = num(a.lat), lng = num(a.lng);
+ batch.push({
+ county_fips: FIPS, source_id: id,
+ address: norm, norm_address: norm,
+ // Philadelphia is a consolidated city-county; the feed carries no situs
+ // city column (every parcel is Philadelphia) — set it explicitly.
+ city: 'PHILADELPHIA', zip: s(a.zip_code),
+ lat, lng,
+ year_built: yearNum(a.year_built), sqft: num(a.total_livable_area),
+ beds: num(a.number_of_bedrooms), baths: num(a.number_of_bathrooms), units: null,
+ use_desc: s(a.category_code_description),
+ land_value: null, improvement_value: null, total_value: num(a.market_value),
+ tax_year: null, owner_name: s(a.owner_1), zoning: s(a.zoning),
+ last_sale_date: saleDate, last_sale_price: salePrice,
+ extra: JSON.stringify({
+ owner_2: s(a.owner_2) || undefined,
+ taxable_land: num(a.taxable_land) || undefined,
+ taxable_building: num(a.taxable_building) || undefined,
+ land_sqft: num(a.total_area) || undefined,
+ note: 'Philadelphia OPA (opa_properties_public) via Carto SQL — market value + priced last-sale + beds/baths + zoning; taxable_* are the post-abatement taxable split, not the market land/building split',
+ }),
+ // ── TK-50 field-level provenance (record-level: one row → this parcel) ──
+ sourceKey: SOURCE_KEY,
+ sourceUrl: `${SQL_API}?q=${encodeURIComponent(`SELECT * FROM ${TABLE} WHERE parcel_number='${id}'`)}`,
+ fetchedAt,
+ rawSource: JSON.stringify(a),
+ });
+ if (salePrice && saleDate) events.push({ id, price: salePrice, date: saleDate });
+ }
+ upserted += await upsertParcels(batch);
+ // record priced last-sale events (amount = sale_price). doc_number = sale_date
+ // (this feed carries no deed book/page in the public dataset), matching the
+ // (county,source_id,event_type,event_date,doc_number) dedup key.
+ for (let i = 0; i < events.length; i += 500) {
+ const chunk = events.slice(i, i + 500);
+ await query(
+ `INSERT INTO parcel_event (county_fips, source_id, event_type, event_date, amount, doc_type, doc_number, source, source_url, detail)
+ VALUES ${chunk.map((_, j) => { const b = j * 9; return `($${b + 1},$${b + 2},'sale',$${b + 3}::date,$${b + 4},$${b + 5},$${b + 6},$${b + 7},$${b + 8},$${b + 9}::jsonb)`; }).join(',')}
+ ON CONFLICT (county_fips, source_id, event_type, event_date, doc_number) DO NOTHING`,
+ chunk.flatMap(e => [FIPS, e.id, e.date, e.price, 'Deed', e.date,
+ 'parcel_philadelphia_pa', `https://property.phila.gov/?p=${e.id}`, JSON.stringify({ nominal: e.price <= 100 || undefined })]),
+ );
+ sales += chunk.length;
+ }
+ offset += rows.length;
+ page++;
+ if (seen % 100000 < rows.length) console.log(`[philly] ${upserted} upserted, ${sales} sales`);
+ if (opts.maxPages && page >= opts.maxPages) break;
+ if (rows.length < PAGE) break;
+ }
+ if (!opts.maxPages) {
+ if (upserted < 500000) throw new Error(`only ${upserted} Philadelphia parcels (expected ~584k) — Carto/paging drift?`);
+ const n = await registerParcelSource(FIPS, SQL_API,
+ `Philadelphia OPA (opa_properties_public) via keyless Carto SQL API — ${upserted} parcels: owner, situs address, TRUE market value (~100%), priced last-sale (price+date, ~99%) → ${sales} parcel_event sale rows, year built (92%), living-area sqft (92%), beds + baths (first full-universe county with beds/baths), zoning, use. Nominal $1/family transfers kept as legitimate low-consideration sales.`);
+ console.log(`[philly] registry ${n}`);
+ }
+ await closeRun(runId, 'ok', { upserted, notes: `Philadelphia PA: ${upserted} parcels, ${sales} sales${opts.maxPages ? ' (bounded verify)' : ''}` });
+ console.log(`[philly] ok: ${upserted} parcels, ${sales} sales${opts.maxPages ? ` (bounded ${opts.maxPages}p)` : ''}`);
+ 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]}`) {
+ ingestPhiladelphia().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
+}
← 9a7dd5f auto-data-snapshot: 2026-08-10T21:49:05 (1 data files) — PAR
·
back to Nationalrealestate
·
TK-5: add 2 metro parcel adapters — Hennepin MN (Minneapolis c112f07 →