← back to Nationalrealestate
TK-5: add 2 metro parcel adapters — Hennepin MN (Minneapolis), Detroit MI
c112f076f0c7cc66dbc96658c13008738dc8b482 · 2026-08-11 07:28:47 -0700 · Steve Abrams
Dedicated full-universe adapters (Wake/Tarrant DTD-B precedent), keyless $0,
verified end-to-end vs local usre DB (real fetch->map->upsert->event path, bounded,
test rows purged). +2 new states (MN, MI). Full backfill + Kamatera deploy Steve-gated.
- hennepin_mn.ts (27053, ~447k): MN Met Council 7-county aggregate (CO_NAME='HENNEPIN'),
owner 100% + EMV market value 96% + PRICED sale (SALE_VALUE/SALE_DATE) 88% + year 95%.
sqft NULL (FIN_SQ_FT empty for Hennepin, not fabricated). Assemblage guard: multi-parcel
sales sharing exact (date,price) flagged bulk + price nulled (verified real: 25 PINs/one $2.3M sale).
- detroit_mi.ts (26163, ~379k, Detroit-city subset of Wayne): Detroit_MP_Parcel_Authoritative,
owner (taxpayer_1) 100% + assessed value (MI SEV ~50% market) 74% + PRICED sale 75% + year 61%
+ building sqft (total_floor_area) 61% + zoning. lot sqft kept in extra.
Contrarian-gated (FIX FIRST): fixed Detroit sqft (was lot total_square_footage -> building
total_floor_area), Detroit event source_url (dead detroitparcels.com -> ArcGIS query URL),
added Hennepin assemblage guard, documented both in PARCEL-COVERAGE.md. engine.ts registers
hennepin/detroit. Fixed a premature comment-close (*/ inside a comment) caught by tsc.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M PARCEL-COVERAGE.mdA src/ingest/parcels/detroit_mi.tsM src/ingest/parcels/engine.tsA src/ingest/parcels/hennepin_mn.ts
Diff
commit c112f076f0c7cc66dbc96658c13008738dc8b482
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Aug 11 07:28:47 2026 -0700
TK-5: add 2 metro parcel adapters — Hennepin MN (Minneapolis), Detroit MI
Dedicated full-universe adapters (Wake/Tarrant DTD-B precedent), keyless $0,
verified end-to-end vs local usre DB (real fetch->map->upsert->event path, bounded,
test rows purged). +2 new states (MN, MI). Full backfill + Kamatera deploy Steve-gated.
- hennepin_mn.ts (27053, ~447k): MN Met Council 7-county aggregate (CO_NAME='HENNEPIN'),
owner 100% + EMV market value 96% + PRICED sale (SALE_VALUE/SALE_DATE) 88% + year 95%.
sqft NULL (FIN_SQ_FT empty for Hennepin, not fabricated). Assemblage guard: multi-parcel
sales sharing exact (date,price) flagged bulk + price nulled (verified real: 25 PINs/one $2.3M sale).
- detroit_mi.ts (26163, ~379k, Detroit-city subset of Wayne): Detroit_MP_Parcel_Authoritative,
owner (taxpayer_1) 100% + assessed value (MI SEV ~50% market) 74% + PRICED sale 75% + year 61%
+ building sqft (total_floor_area) 61% + zoning. lot sqft kept in extra.
Contrarian-gated (FIX FIRST): fixed Detroit sqft (was lot total_square_footage -> building
total_floor_area), Detroit event source_url (dead detroitparcels.com -> ArcGIS query URL),
added Hennepin assemblage guard, documented both in PARCEL-COVERAGE.md. engine.ts registers
hennepin/detroit. Fixed a premature comment-close (*/ inside a comment) caught by tsc.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
PARCEL-COVERAGE.md | 4 +
src/ingest/parcels/detroit_mi.ts | 176 ++++++++++++++++++++++++++++++++++
src/ingest/parcels/engine.ts | 2 +
src/ingest/parcels/hennepin_mn.ts | 197 ++++++++++++++++++++++++++++++++++++++
4 files changed, 379 insertions(+)
diff --git a/PARCEL-COVERAGE.md b/PARCEL-COVERAGE.md
index d6d0f01..53858c0 100644
--- a/PARCEL-COVERAGE.md
+++ b/PARCEL-COVERAGE.md
@@ -38,6 +38,8 @@ ingests continuously. Current state from `parcel` + `ingest_cursor` tables:
| TX | Harris (48201) | ~1,548,457 | 🆕 Adapter ready | Houston/HCAD; owner, market value, deed date (backfill Steve-gated) |
| NV | Clark (32003) | ~839,630 | 🆕 Adapter ready | Las Vegas; owner, **priced sale**, year built (backfill Steve-gated) |
| PA | Philadelphia (42101) | ~583,688 | 🆕 Adapter ready | Owner, market value, **priced sale**, year, **beds/baths**, zoning (backfill Steve-gated) |
+| MN | Hennepin (27053) | ~447,044 | 🆕 Adapter ready | Minneapolis; owner, market value (EMV), **priced sale**, year (backfill Steve-gated) |
+| MI | Detroit / Wayne (26163) | ~379,446 | 🆕 Adapter ready | Detroit-city subset; owner, assessed value (SEV), **priced sale**, year, sqft, zoning (backfill Steve-gated) |
| UT | Salt Lake (49035) | 391,370 | ✅ Complete | Market value; no owner/price |
| WA | King (53033) | 628,417 | ✅ Complete | Seattle; full deed history w/ prices |
| WA | Thurston (53067) | 80,124 | ✅ Complete | Olympia; free priced deeds |
@@ -62,6 +64,8 @@ ingests continuously. Current state from `parcel` + `ingest_cursor` tables:
| Dallas, TX — Dallas (48113) | ~548,830 | DCAD appraisal accounts via City-of-Dallas GIS `CRMHostedLayers` "Tax Account Points" (keyless ArcGIS, POINT geom, `outSR=4326`; `ingest:parcels dallas`) — **adapter built + bounded-verified, full backfill Steve-gated** | re-run adapter (remote-runnable, no GDAL) | **Ownership (Owner1 ~95%)**, situs address (`SiteAddrNum`+`SiteStreetname`), LAND+IMP + **TRUE MARKET/total value (~93%)**, property-class use, appraisal year, jurisdiction city, Map (point lat/lng). **No sqft / year-built / beds-baths / sale-or-deed feed** in this layer (deed instrument text lives unparsed in `Legal4`) → parcel-DETAIL+value (like Bexar but WITH value); **zero parcel_event rows** (no fabricated sales). DCAD's own hosts (`maps.dcad.org`/`gis.dcad.org`) 404 to headless — this City mirror is the reachable full-county account universe |
| Harris, TX — Houston (48201) | ~1,548,457 | HCAD appraisal roll via Harris County public GIS `HCAD/Parcels` MapServer (keyless ArcGIS, `outSR=4326`; `ingest:parcels harris`) — **adapter built + bounded-verified, full backfill Steve-gated** | re-run adapter (remote-runnable, no GDAL) | **Largest county in system (1.55M). Ownership (owner_name_1 ~98%)**, situs address (assembled from the `site_str_*` parts), LAND+BLDG + **TRUE MARKET value (~98%)** + appraised value, lot sqft (~66%), land-use + state class, **Property History: deed/ownership-change DATE (`new_owner_date`, ~98%, no price in this feed) → parcel_event `deed` rows w/ amount NULL**, Map (polygon centroid). **No building sqft / year-built / beds-baths / sale PRICE** (deed date only, like Tarrant). Owner+value live in ONE layer — no bulk-text-download detour; the `services.arcgis.com` COH layers are project subsets, only `www.gis.hctx.net` has the full feed |
| Clark, NV — Las Vegas (32003) | ~839,630 | Clark County GIS Office hosted `CC_PARCELS_SHP` FeatureServer **layer 1** (keyless ArcGIS, `orderBy OBJECTID_1`, `outSR=4326`; `ingest:parcels clark`) — **adapter built + bounded-verified, full backfill Steve-gated** | re-run adapter (remote-runnable, no GDAL) | **Ownership (OWNER ~90%)**, situs address (~90%), **Property History: PRICED last-sale (`SALEPRICE`+`SALEDATE`+recorded `DOCNO`, ~81%) → parcel_event `sale` rows w/ real amount**, year-built (`CONSTYR` ~82%), lot sqft, land-use code, Map (polygon centroid). **Assessed/market VALUE NOT in this GIS layer** (the value columns exist but are 100% zero — left NULL, never fabricated). Low-id head is blank-owner shells (real but empty parcels); gisgate REST + Hub open-data hosts are blocked/private to headless — this hosted layer is the reachable path |
+| Hennepin, MN — Minneapolis (27053) | ~447,044 | MN Met Council "Metropolitan 7-County Parcels" aggregate FeatureServer (keyless ArcGIS, `CO_NAME='HENNEPIN'` filter, `outSR=4326`; `ingest:parcels hennepin`) — **adapter built + bounded-verified, full backfill Steve-gated** | re-run adapter (remote-runnable, no GDAL) | **Ownership (OWNER_NAME 100%)**, situs address (assembled from ANUMBER + ST_ parts), city (CTU_NAME) + zip, **EMV land+bldg+TOTAL (estimated MARKET value, ~96%)**, year built (~95%), units, use class, **Property History: priced last-sale (SALE_VALUE + SALE_DATE, ~88%) → parcel_event `sale` rows**, Map (polygon centroid). **No building sqft** (FIN_SQ_FT not populated for Hennepin in this aggregate → left NULL) or beds/baths/zoning. Multi-parcel (assemblage/portfolio) sales sharing an exact (date, price) are flagged bulk + price nulled (Wake pattern; verified real — 25 PINs shared one $2.3M sale) |
+| Detroit, MI — Wayne County (26163) | ~379,446 | City of Detroit `Detroit_MP_Parcel_Authoritative` hosted FeatureServer (keyless ArcGIS, `outSR=4326`; `ingest:parcels detroit`) — **adapter built + bounded-verified, full backfill Steve-gated** | re-run adapter (remote-runnable, no GDAL) | **Detroit-city SUBSET of Wayne County (NOT all 43 Wayne municipalities). Ownership (taxpayer_1 100%)**, address + zip, use + property class, zoning, **MI assessed value (SEV ≈ 50% of market, ~74%)** + taxable value, year built (~61%), **building sqft (total_floor_area, ~61%; total_square_footage is LOT area, kept in extra)**, **Property History: priced last-sale (sale_price + sale_date, ~75%) → parcel_event `sale` rows**, Map (polygon centroid). Full-Wayne coverage is a later TODO; parcel_source is single-key-per-FIPS so a future full-Wayne adapter under 26163 would overwrite this Detroit entry — reconcile then |
| Philadelphia, PA (42101) | ~583,688 | Philadelphia OPA `opa_properties_public` via keyless **Carto SQL API** (`phl.carto.com/api/v2/sql`, LIMIT/OFFSET, `ST_Y/ST_X` centroids; `ingest:parcels philadelphia`) — **adapter built + bounded-verified, full backfill Steve-gated** | re-run adapter (remote-runnable, no GDAL) | **Richest county in system. Ownership (owner_1 ~100%)**, situs address, **TRUE market value (~100%)**, **Property History: PRICED last-sale (price+date, ~99%; 73% arm's-length >$1, rest nominal $1 family/deed transfers kept as legit low-consideration) → parcel_event `sale` rows w/ real amount**, year-built (~92%), living-area sqft (~92%), **beds + baths (the FIRST full-universe county with beds/baths)**, zoning, use, lat/lng direct from PostGIS. First adapter over a Carto SQL feed (all others are ArcGIS/Socrata). `taxable_land`/`taxable_building` (post-abatement split) carried in `extra` |
## Probed — gated or absent
diff --git a/src/ingest/parcels/detroit_mi.ts b/src/ingest/parcels/detroit_mi.ts
new file mode 100644
index 0000000..61c8fb6
--- /dev/null
+++ b/src/ingest/parcels/detroit_mi.ts
@@ -0,0 +1,176 @@
+/**
+ * Detroit MI parcel ingest — dedicated adapter (applies the Wake/Miami-Dade DTD
+ * verdict B precedent). SCOPE: the City of Detroit, which sits inside Wayne County
+ * (FIPS 26163) — so rows register under 26163, but this feed is Detroit-city only
+ * (~379k parcels), NOT all of Wayne County's 43 municipalities. Detroit is the
+ * reachable rich authoritative feed; a full-Wayne source is a later TODO.
+ *
+ * Source: the City of Detroit "Detroit_MP_Parcel_Authoritative" hosted ArcGIS
+ * FeatureServer (keyless, supportsPagination, 2k/page, $0):
+ * https://services2.arcgis.com/PpbvckyUgaYqseNQ/arcgis/rest/services/Detroit_MP_Parcel_Authoritative/FeatureServer/0
+ *
+ * A rich PRICED-SALE feed: taxpayer_1 (owner, 100%), address + zip, use_code_
+ * description + property_class_description (use), zoning_district, amt_assessed_
+ * value + amt_taxable_value (MI State Equalized Value — assessed ≈ 50% of market,
+ * ~74%), year_built (~61%), total_floor_area (BUILDING sqft, ~61%), sale_price +
+ * sale_date (a real priced last-sale, ~75%). total_square_footage is the LOT area
+ * (≈ acreage × 43,560), carried in extra as lot_sqft — NOT the building sqft. MI
+ * assessed value is SEV (~half of market) —
+ * recorded honestly as total_value with a note, not inflated to a market figure.
+ *
+ * source_id = parcel_id.
+ *
+ * Run: NODE_OPTIONS=--max-old-space-size=4096 npm run ingest:parcels detroit
+ */
+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 = '26163';
+const SOURCE_KEY = 'detroit_mi';
+const LAYER = 'https://services2.arcgis.com/PpbvckyUgaYqseNQ/arcgis/rest/services/Detroit_MP_Parcel_Authoritative/FeatureServer/0';
+const PAGE = 2000;
+// TK-50 field-level provenance. total_value maps from amt_assessed_value (MI SEV,
+// ~50% of market). This feed carries a priced sale (sale_price + sale_date).
+const FIELD_MAP: Record<string, string> = {
+ source_id: 'parcel_id', address: 'address', zip: 'zip_code',
+ year_built: 'year_built', sqft: 'total_floor_area', use_desc: 'use_code_description',
+ zoning: 'zoning_district', total_value: 'amt_assessed_value',
+ owner_name: 'taxpayer_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; };
+// sale_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)); };
+
+/** 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 = ['parcel_id', 'address', 'zip_code', 'taxpayer_1', 'property_class_description',
+ 'use_code', 'use_code_description', 'zoning_district', 'year_built', 'building_style',
+ 'total_floor_area', 'total_square_footage', 'total_acreage',
+ 'amt_assessed_value', 'amt_taxable_value', 'tax_status_description', 'sale_price', 'sale_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(`detroit ${res.status}: ${(await res.text()).slice(0, 140)}`);
+ const j: any = await res.json();
+ if (j.error) throw new Error(`detroit 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 ingestDetroit(opts: { maxPages?: number } = {}): Promise<{ upserted: number }> {
+ const runId = await openRun('parcel_detroit_mi', LAYER);
+ const fetchedAt = new Date().toISOString();
+ try {
+ await registerSourceFieldMap(SOURCE_KEY, FIELD_MAP, 'City of Detroit (Wayne County MI) authoritative master parcel FeatureServer');
+ let offset = 0, page = 0, seen = 0, upserted = 0, sales = 0;
+ for (;;) {
+ const feats = await fetchPage(offset);
+ if (!feats.length) break;
+ const batch: ParcelUpsertRow[] = [];
+ const events: { pid: string; price: number; date: string }[] = [];
+ for (const f of feats) {
+ const a = f.attributes || {};
+ const pid = s(a.parcel_id);
+ if (!pid) continue;
+ seen++;
+ const [lng, lat] = centroid(f.geometry);
+ const addr = s(a.address);
+ const norm = addr ? normAddress(addr) : null;
+ const salePrice = sanitizeSalePrice(a.sale_price);
+ const saleDate = epochToISO(a.sale_date);
+ const use = s(a.use_code_description) ?? s(a.property_class_description);
+ batch.push({
+ county_fips: FIPS, source_id: pid,
+ address: norm, norm_address: norm,
+ city: 'DETROIT', zip: s(a.zip_code),
+ lat, lng,
+ // sqft = total_floor_area (BUILDING area); total_square_footage is the LOT
+ // (≈ acreage × 43,560) and goes to extra.lot_sqft — do not conflate them.
+ year_built: num(a.year_built), sqft: num(a.total_floor_area), beds: null, baths: null, units: null,
+ use_desc: use,
+ // MI assessed value = State Equalized Value (~50% of market) — recorded as
+ // total_value; the market equivalent would be ~2x. No land/building split.
+ land_value: null, improvement_value: null, total_value: num(a.amt_assessed_value),
+ tax_year: null, owner_name: s(a.taxpayer_1), zoning: s(a.zoning_district),
+ last_sale_date: saleDate, last_sale_price: salePrice,
+ extra: JSON.stringify({
+ property_class: s(a.property_class_description) || undefined,
+ taxable_value: num(a.amt_taxable_value) || undefined,
+ lot_sqft: num(a.total_square_footage) || undefined, // total_square_footage = LOT area
+ acreage: num(a.total_acreage) || undefined,
+ building_style: s(a.building_style) || undefined,
+ tax_status: s(a.tax_status_description) || undefined,
+ note: 'City of Detroit authoritative parcel (Wayne County MI, Detroit-city subset) — owner (taxpayer) + assessed value (MI SEV ≈ 50% of market) + priced last-sale + year + sqft + zoning',
+ }),
+ // ── TK-50 field-level provenance (record-level: one feature → this row) ──
+ sourceKey: SOURCE_KEY,
+ sourceUrl: `${LAYER}/query?where=${encodeURIComponent(`parcel_id='${pid}'`)}&outFields=*&f=html`,
+ fetchedAt,
+ rawSource: JSON.stringify(a),
+ });
+ if (salePrice && saleDate) events.push({ pid, price: salePrice, date: saleDate });
+ }
+ upserted += await upsertParcels(batch);
+ // priced last-sale events (amount = sale_price). doc_number = sale date (this
+ // feed carries no deed book/page).
+ 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.pid, e.date, e.price, 'Deed', e.date,
+ 'parcel_detroit_mi', `${LAYER}/query?where=${encodeURIComponent(`parcel_id='${e.pid}'`)}&outFields=*&f=html`, JSON.stringify({})]),
+ );
+ sales += chunk.length;
+ }
+ offset += feats.length;
+ page++;
+ if (seen % 100000 < feats.length) console.log(`[detroit] ${upserted} upserted, ${sales} sales`);
+ if (opts.maxPages && page >= opts.maxPages) break;
+ if (feats.length < PAGE) break;
+ }
+ if (!opts.maxPages) {
+ if (upserted < 340000) throw new Error(`only ${upserted} Detroit parcels (expected ~379k) — layer/paging drift?`);
+ const n = await registerParcelSource(FIPS, LAYER,
+ `City of Detroit (Wayne County MI, FIPS 26163 — Detroit-city subset, NOT full Wayne) authoritative master parcel FeatureServer — ${upserted} parcels: owner (taxpayer_1, 100%), address + zip, use + property class, zoning, MI assessed value (SEV ≈ 50% of market, ~74%) + taxable value, year built (~61%), building sqft (total_floor_area, ~61%; lot sqft in extra), priced last-sale (sale_price + sale_date, ~75%) → ${sales} parcel_event sale rows. Full-Wayne (43 municipalities) coverage is a later TODO (NOTE: parcel_source is single-key-per-FIPS, so a future full-Wayne adapter under 26163 would overwrite this Detroit entry — reconcile then).`);
+ console.log(`[detroit] registry ${n}`);
+ }
+ await closeRun(runId, 'ok', { upserted, notes: `Detroit MI: ${upserted} parcels, ${sales} sales${opts.maxPages ? ' (bounded verify)' : ''}` });
+ console.log(`[detroit] 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]}`) {
+ ingestDetroit().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 c61cc47..1997287 100644
--- a/src/ingest/parcels/engine.ts
+++ b/src/ingest/parcels/engine.ts
@@ -21,6 +21,8 @@ const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: nu
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 }),
+ hennepin: async () => ({ run: (await import('./hennepin_mn.ts')).ingestHennepin }),
+ detroit: async () => ({ run: (await import('./detroit_mi.ts')).ingestDetroit }),
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/hennepin_mn.ts b/src/ingest/parcels/hennepin_mn.ts
new file mode 100644
index 0000000..533c68e
--- /dev/null
+++ b/src/ingest/parcels/hennepin_mn.ts
@@ -0,0 +1,197 @@
+/**
+ * Hennepin County MN (Minneapolis, FIPS 27053) parcel ingest — dedicated
+ * full-universe adapter (applies the Wake/Miami-Dade DTD verdict B precedent).
+ *
+ * Source: the Minnesota Metropolitan Council "Metropolitan 7-County Parcels"
+ * aggregate FeatureServer (keyless, supportsPagination, 2k/page, $0):
+ * https://arcgis.metc.state.mn.us/data1/rest/services/parcels/Parcels_Aggregate/FeatureServer/0
+ * This is a 7-county metro aggregate — we filter server-side to CO_NAME='HENNEPIN'
+ * (= 447,044 parcels). The Met Council standardizes every metro county's assessor
+ * roll into one schema, so this is the authoritative full-universe Hennepin feed.
+ *
+ * A rich PRICED-SALE county: OWNER_NAME (100%), situs address (assembled from the
+ * ANUMBER + ST_ name parts), CTU_NAME (city) + ZIP, EMV_LAND + EMV_BLDG + EMV_TOTAL
+ * (Estimated Market Value — TRUE market, ~95%), TAX_YEAR, YEAR_BUILT (~94%),
+ * NUM_UNITS, USECLASS1 (use), SALE_VALUE + SALE_DATE (a real priced last-sale,
+ * ~87%). Note: the aggregate's FIN_SQ_FT is not populated for Hennepin (0 rows) —
+ * sqft is left NULL rather than mapped from an empty column.
+ *
+ * source_id = PIN (unique within Hennepin — count(PIN)=447,044=total).
+ *
+ * Run: NODE_OPTIONS=--max-old-space-size=4096 npm run ingest:parcels hennepin
+ */
+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 = '27053';
+const SOURCE_KEY = 'hennepin_mn';
+const LAYER = 'https://arcgis.metc.state.mn.us/data1/rest/services/parcels/Parcels_Aggregate/FeatureServer/0';
+const WHERE = "CO_NAME='HENNEPIN'";
+const PAGE = 2000;
+// TK-50 field-level provenance. total_value maps from EMV_TOTAL (Estimated Market
+// Value — true market). This feed carries a priced sale (SALE_VALUE + SALE_DATE).
+const FIELD_MAP: Record<string, string> = {
+ source_id: 'PIN', address: 'ANUMBER+ST_NAME', city: 'CTU_NAME', zip: 'ZIP',
+ year_built: 'YEAR_BUILT', units: 'NUM_UNITS', use_desc: 'USECLASS1', tax_year: 'TAX_YEAR',
+ land_value: 'EMV_LAND', improvement_value: 'EMV_BLDG', total_value: 'EMV_TOTAL',
+ owner_name: 'OWNER_NAME', last_sale_price: 'SALE_VALUE', 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; };
+// SALE_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 the standardized address parts, collapsing the
+ * gaps left by empty prefix/suffix/direction components. */
+function situsAddr(a: any): string | null {
+ const parts = [a.ANUMBERPRE, a.ANUMBER, a.ANUMBERSUF, a.ST_PRE_DIR, a.ST_PRE_TYP, a.ST_NAME, a.ST_POS_TYP, a.ST_POS_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 = ['PIN', 'OWNER_NAME', 'OWNER_MORE', 'ANUMBERPRE', 'ANUMBER', 'ANUMBERSUF',
+ 'ST_PRE_DIR', 'ST_PRE_TYP', 'ST_NAME', 'ST_POS_TYP', 'ST_POS_DIR', 'CTU_NAME', 'ZIP',
+ 'EMV_LAND', 'EMV_BLDG', 'EMV_TOTAL', 'TAX_YEAR', 'YEAR_BUILT', 'NUM_UNITS',
+ 'USECLASS1', 'HOME_STYLE', 'SALE_VALUE', 'SALE_DATE', 'ABB_LEGAL'].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(`hennepin ${res.status}: ${(await res.text()).slice(0, 140)}`);
+ const j: any = await res.json();
+ if (j.error) throw new Error(`hennepin 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 ingestHennepin(opts: { maxPages?: number } = {}): Promise<{ upserted: number }> {
+ const runId = await openRun('parcel_hennepin_mn', LAYER);
+ const fetchedAt = new Date().toISOString();
+ try {
+ await registerSourceFieldMap(SOURCE_KEY, FIELD_MAP, 'Hennepin County MN via MN Met Council Metropolitan 7-County Parcels aggregate');
+ let offset = 0, page = 0, seen = 0, upserted = 0, sales = 0;
+ for (;;) {
+ const feats = await fetchPage(offset);
+ if (!feats.length) break;
+ const batch: ParcelUpsertRow[] = [];
+ const events: { pin: string; price: number; date: string }[] = [];
+ for (const f of feats) {
+ const a = f.attributes || {};
+ const pin = s(a.PIN);
+ if (!pin) continue;
+ seen++;
+ const [lng, lat] = centroid(f.geometry);
+ const addr = situsAddr(a);
+ const norm = addr ? normAddress(addr) : null;
+ const salePrice = sanitizeSalePrice(a.SALE_VALUE);
+ const saleDate = epochToISO(a.SALE_DATE);
+ batch.push({
+ county_fips: FIPS, source_id: pin,
+ address: norm, norm_address: norm,
+ city: s(a.CTU_NAME), zip: s(a.ZIP),
+ lat, lng,
+ // FIN_SQ_FT is not populated for Hennepin in this aggregate — leave sqft NULL.
+ year_built: num(a.YEAR_BUILT), sqft: null, beds: null, baths: null, units: num(a.NUM_UNITS),
+ use_desc: s(a.USECLASS1),
+ land_value: num(a.EMV_LAND), improvement_value: num(a.EMV_BLDG), total_value: num(a.EMV_TOTAL),
+ tax_year: s(a.TAX_YEAR), owner_name: s(a.OWNER_NAME), zoning: null,
+ last_sale_date: saleDate, last_sale_price: salePrice,
+ extra: JSON.stringify({
+ owner_more: s(a.OWNER_MORE) || undefined,
+ home_style: s(a.HOME_STYLE) || undefined,
+ legal: s(a.ABB_LEGAL) || undefined,
+ note: 'Hennepin County MN via MN Met Council 7-county aggregate — EMV (estimated market value) + priced last-sale + year; sqft (FIN_SQ_FT) not populated for Hennepin in this feed',
+ }),
+ // ── TK-50 field-level provenance (record-level: one feature → this row) ──
+ sourceKey: SOURCE_KEY,
+ sourceUrl: `${LAYER}/query?where=${encodeURIComponent(`PIN='${pin}'`)}&outFields=*&f=html`,
+ fetchedAt,
+ rawSource: JSON.stringify(a),
+ });
+ if (salePrice && saleDate) events.push({ pin, price: salePrice, date: saleDate });
+ }
+ upserted += await upsertParcels(batch);
+ // priced last-sale events (amount = SALE_VALUE). doc_number = sale date (this
+ // aggregate carries no deed book/page).
+ 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.pin, e.date, e.price, 'Deed', e.date,
+ 'parcel_hennepin_mn', `${LAYER}/query?where=${encodeURIComponent(`PIN='${e.pin}'`)}&outFields=*&f=html`, JSON.stringify({})]),
+ );
+ sales += chunk.length;
+ }
+ offset += feats.length;
+ page++;
+ if (seen % 100000 < feats.length) console.log(`[hennepin] ${upserted} upserted, ${sales} sales`);
+ if (opts.maxPages && page >= opts.maxPages) break;
+ if (feats.length < PAGE) break;
+ }
+ let bulk = 0;
+ if (!opts.maxPages) {
+ if (upserted < 400000) throw new Error(`only ${upserted} Hennepin parcels (expected ~447k) — layer/paging/filter drift?`);
+ // in-DB assemblage guard: this feed has no deed book/page, so a portfolio /
+ // multi-parcel sale stamps the SAME (SALE_DATE, SALE_VALUE) onto every parcel
+ // in the deal (verified: 25 Hennepin PINs share 2021-06 @ $2.3M). Without this
+ // each parcel would record the full price → N-fold inflated sales. Mirror the
+ // Wake guard: any exact (last_sale_date, last_sale_price) shared by >1 parcel
+ // is un-allocatable → null the price + flag bulk (pkey join keeps it indexed).
+ const b = await query(
+ `WITH grp AS MATERIALIZED (
+ SELECT last_sale_date, last_sale_price, COUNT(*)::int AS cnt
+ FROM parcel WHERE county_fips=$1 AND last_sale_price IS NOT NULL AND last_sale_date IS NOT NULL
+ GROUP BY 1,2 HAVING COUNT(*) > 1),
+ targets AS MATERIALIZED (
+ SELECT p.source_id, g.cnt FROM parcel p
+ JOIN grp g ON p.last_sale_date=g.last_sale_date AND p.last_sale_price=g.last_sale_price
+ WHERE p.county_fips=$1)
+ UPDATE parcel p SET last_sale_price = NULL,
+ extra = p.extra || jsonb_build_object('bulk_sale', true, 'bulk_parcel_count', t.cnt)
+ FROM targets t WHERE p.county_fips=$1 AND p.source_id = t.source_id RETURNING 1`, [FIPS]);
+ bulk = b.rowCount ?? 0;
+ const n = await registerParcelSource(FIPS, LAYER,
+ `Hennepin County MN (Minneapolis) via MN Met Council Metropolitan 7-County Parcels aggregate (CO_NAME='HENNEPIN' filter) — ${upserted} parcels: owner (100%), situs address + city + zip, EMV land+bldg+TOTAL (estimated MARKET value, ~95%), year built (~94%), units, use class, priced last-sale (SALE_VALUE + SALE_DATE, ~87%) → ${sales} parcel_event sale rows. sqft (FIN_SQ_FT) not populated for Hennepin in this aggregate. No beds/baths or zoning. ${bulk} multi-parcel (assemblage/portfolio) sales flagged bulk + price nulled.`);
+ console.log(`[hennepin] registry ${n}, ${bulk} bulk-flagged`);
+ }
+ await closeRun(runId, 'ok', { upserted, notes: `Hennepin MN: ${upserted} parcels, ${sales} sales, ${bulk} bulk${opts.maxPages ? ' (bounded verify)' : ''}` });
+ console.log(`[hennepin] ok: ${upserted} parcels, ${sales} sales, ${bulk} bulk${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]}`) {
+ ingestHennepin().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
+}
← 709c182 TK-5: add 4 metro parcel adapters — Dallas TX, Harris TX, Cl
·
back to Nationalrealestate
·
TK-5: fix Hennepin assemblage guard to be prod-scale safe (A fcf4fb9 →