← back to Nationalrealestate
src/ingest/parcels/detroit_mi.ts
177 lines
/**
* 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); });
}