← back to Nationalrealestate
src/ingest/parcels/cook_il.ts
185 lines
/**
* Cook County IL (Chicago, FIPS 17031) parcel ingest from the county's open
* Socrata portal (datacatalog.cookcountyil.gov, $0, no auth):
* pabr-t5kh Assessor - Parcel Universe (Current Year Only) 1.86M pins: class, township, zip, lat/lng
* 3723-97qp Assessor - Parcel Addresses (year=2026) situs address + owner/taxpayer name + mailing
* uzyt-m557 Assessor - Assessed Values (year=2025, certified) latest fully-certified assessment year
* x54s-btds Assessor - Single & Multi-Family Improvement year built, bldg sqft, beds, baths (1.1M res pins)
* Characteristics (year=2026)
* No zoning dataset (zoning is municipal in Cook) and no sale-price feed here —
* both recorded as pending. Values are ASSESSED values (10%/25% of market),
* not market value — noted in the payload.
*
* Run: npm run ingest:parcels cook (NODE_OPTIONS=--max-old-space-size=6144)
*/
import { pool } from '../../../db/pool.ts';
import { openRun, closeRun } from '../run.ts';
import { upsertParcels, registerParcelSource, registerSourceFieldMap, normAddress, type ParcelUpsertRow } from './upsert.ts';
const FIPS = '17031';
const SOURCE_KEY = 'cook_il';
const BASE = 'https://datacatalog.cookcountyil.gov/resource/';
const PAGE = 200000;
// TK-50 field-level provenance: our parcel field → the source attribute + which
// Socrata dataset it was joined from (by PIN). Values are ASSESSED (10%/25% of
// market), not market. No zoning or sale-price feed here.
const FIELD_MAP: Record<string, string> = {
source_id: 'pabr-t5kh.pin', lat: 'pabr-t5kh.lat', lng: 'pabr-t5kh.lon',
use_desc: 'pabr-t5kh.class (→ class-group label)',
address: '3723-97qp.prop_address_full', city: '3723-97qp.prop_address_city_name|pabr-t5kh.cook_municipality_name',
zip: '3723-97qp.prop_address_zipcode_1|pabr-t5kh.zip_code',
owner_name: '3723-97qp.owner_address_name|mail_address_name',
land_value: 'uzyt-m557.board_land|certified_land', improvement_value: 'uzyt-m557.board_bldg|certified_bldg',
total_value: 'uzyt-m557.board_tot|certified_tot',
year_built: 'x54s-btds.char_yrblt', sqft: 'x54s-btds.char_bldg_sf', beds: 'x54s-btds.char_beds',
baths: 'x54s-btds.char_fbath+char_hbath',
};
const CLASS_GROUP: Record<string, string> = {
'1': 'Vacant Land', '2': 'Residential (<7 units)', '3': 'Multi-Family (7+ units)',
'4': 'Not-for-Profit', '5': 'Commercial', '6': 'Industrial (incentive)',
'7': 'Commercial (incentive)', '8': 'Commercial/Industrial (incentive)', '9': 'Residential (incentive)',
'0': 'Exempt/Railroad',
};
const num = (v: unknown) => { const n = Number(v); return Number.isFinite(n) && n !== 0 ? n : null; };
async function fetchPage(dataset: string, params: string, offset: number, tries = 3): Promise<any[]> {
const url = `${BASE}${dataset}.json?${params}&$limit=${PAGE}&$offset=${offset}`;
for (let t = 1; ; t++) {
try {
const res = await fetch(url, { headers: { accept: 'application/json' } });
if (!res.ok) throw new Error(`${dataset} HTTP ${res.status} at offset ${offset}`);
return await res.json() as any[];
} catch (e) {
if (t >= tries) throw e;
console.log(`[cook] retry ${t} for ${dataset} @${offset}: ${e}`);
await new Promise(r => setTimeout(r, 5000 * t));
}
}
}
async function* pages(dataset: string, select: string, where?: string): AsyncGenerator<any[]> {
const params = `$select=${encodeURIComponent(select)}` +
(where ? `&$where=${encodeURIComponent(where)}` : '') + `&$order=row_id`;
for (let offset = 0; ; offset += PAGE) {
const data = await fetchPage(dataset, params, offset);
if (!data.length) return;
yield data;
if (data.length < PAGE) return;
}
}
export async function ingestCook(): Promise<{ upserted: number }> {
const runId = await openRun('parcel_cook_il', BASE + 'pabr-t5kh.json');
// 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, 'Cook County IL Assessor open data (parcel-universe pabr-t5kh joined with addresses 3723-97qp, assessed values uzyt-m557, res characteristics x54s-btds by PIN)');
// 1) situs address + owner/taxpayer name (current assessment year)
const addr = new Map<string, [addr: string, city: string, zip: string, owner: string, mailing: string]>();
for await (const page of pages('3723-97qp',
'pin,prop_address_full,prop_address_city_name,prop_address_zipcode_1,owner_address_name,mail_address_name,mail_address_full,mail_address_city_name,mail_address_state,mail_address_zipcode_1',
'year=2026')) {
for (const o of page) {
const mailing = [o.mail_address_full, o.mail_address_city_name, o.mail_address_state, o.mail_address_zipcode_1]
.map((s: string) => (s || '').trim()).filter(Boolean).join(', ');
addr.set(o.pin, [(o.prop_address_full || '').trim(), (o.prop_address_city_name || '').trim(),
(o.prop_address_zipcode_1 || '').trim(), (o.owner_address_name || o.mail_address_name || '').trim(), mailing]);
}
console.log(`[cook] addresses: ${addr.size}`);
}
// 2) latest fully-certified assessed values (2025; 2026 not yet mailed county-wide)
const vals = new Map<string, [land: number | null, bldg: number | null, tot: number | null]>();
for await (const page of pages('uzyt-m557',
'pin,certified_land,certified_bldg,certified_tot,board_land,board_bldg,board_tot', 'year=2025')) {
for (const o of page) {
vals.set(o.pin, [num(o.board_land) ?? num(o.certified_land), num(o.board_bldg) ?? num(o.certified_bldg),
num(o.board_tot) ?? num(o.certified_tot)]);
}
console.log(`[cook] values: ${vals.size}`);
}
// 3) residential improvement characteristics (largest card wins on multi-card pins)
const chars = new Map<string, [yrblt: number | null, sf: number | null, beds: number | null, baths: number | null]>();
for await (const page of pages('x54s-btds',
'pin,char_yrblt,char_bldg_sf,char_beds,char_fbath,char_hbath', 'year=2026')) {
for (const o of page) {
const sf = num(o.char_bldg_sf);
const prev = chars.get(o.pin);
if (prev && (prev[1] ?? 0) >= (sf ?? 0)) continue;
const baths = (Number(o.char_fbath) || 0) + 0.5 * (Number(o.char_hbath) || 0);
chars.set(o.pin, [num(o.char_yrblt), sf, num(o.char_beds), baths || null]);
}
console.log(`[cook] chars: ${chars.size}`);
}
// 4) parcel universe drives; join + upsert
let upserted = 0;
for await (const page of pages('pabr-t5kh',
'pin,class,township_name,zip_code,lon,lat,cook_municipality_name')) {
const batch: ParcelUpsertRow[] = [];
for (const o of page) {
if (!o.pin) continue;
const a = addr.get(o.pin);
const v = vals.get(o.pin);
const c = chars.get(o.pin);
const cls = (o.class || '').trim();
const group = CLASS_GROUP[cls[0]] || null;
const address = a?.[0] ? normAddress(a[0]) : null;
batch.push({
county_fips: FIPS, source_id: o.pin,
address, norm_address: address,
city: a?.[1] || (o.cook_municipality_name || '').trim() || null,
zip: a?.[2] || (o.zip_code || '').trim() || null,
lat: num(o.lat), lng: num(o.lon),
year_built: c?.[0] ?? null, sqft: c?.[1] ?? null, beds: c?.[2] ?? null, baths: c?.[3] ?? null,
units: null,
use_desc: group ? `${group} — Class ${cls}` : cls ? `Class ${cls}` : null,
land_value: v?.[0] ?? null, improvement_value: v?.[1] ?? null, total_value: v?.[2] ?? null,
tax_year: v ? '2025' : null,
owner_name: a?.[3] || null, zoning: null,
last_sale_date: null, last_sale_price: null,
extra: JSON.stringify({
class: cls, township: (o.township_name || '').trim() || undefined,
mailing_address: a?.[4] || undefined,
owner_source: a?.[3] ? 'owner/taxpayer name, Cook County Assessor parcel addresses (2026)' : undefined,
value_basis: v ? 'ASSESSED value (Cook County 2025 certified/BOR — 10% res / 25% comm of market value)' : undefined,
}),
// ── TK-50 field-level provenance (record-level: this PIN's joined datasets) ──
// Socrata parcel-universe record link (addressable by PIN); raw_source
// captures the actual source values we mapped from across the 4 datasets.
sourceKey: SOURCE_KEY,
sourceUrl: `${BASE}pabr-t5kh.json?pin=${encodeURIComponent(o.pin)}`,
fetchedAt,
rawSource: JSON.stringify({
universe: { pin: o.pin, class: cls, township_name: o.township_name, zip_code: o.zip_code,
lat: o.lat, lon: o.lon, cook_municipality_name: o.cook_municipality_name },
addresses: a ? { prop_address_full: a[0], prop_address_city_name: a[1],
prop_address_zipcode_1: a[2], owner_address_name: a[3], mailing: a[4] } : null,
values: v ? { land: v[0], bldg: v[1], tot: v[2], year: '2025' } : null,
characteristics: c ? { char_yrblt: c[0], char_bldg_sf: c[1], char_beds: c[2], baths: c[3] } : null,
}),
});
}
upserted += await upsertParcels(batch);
console.log(`[cook] ${upserted} upserted`);
}
if (upserted < 1500000) throw new Error(`only ${upserted} Cook parcels — expected ~1.86M, dataset drift?`);
const n = await registerParcelSource(FIPS, BASE + 'pabr-t5kh.json',
'Cook County IL Assessor open data — situs address + owner/taxpayer (3723-97qp 2026), certified ASSESSED values (uzyt-m557 2025), res characteristics (x54s-btds 2026), lat/lng+class (pabr-t5kh). No zoning (municipal) or sale-price feed.');
await closeRun(runId, 'ok', { upserted, notes: `Cook IL: ${upserted} parcels (addr ${addr.size}, vals ${vals.size}, chars ${chars.size})` });
console.log(`[cook] ok: ${upserted} parcels (registry ${n})`);
return { upserted };
} catch (e: any) {
await closeRun(runId, 'failed', { notes: String(e.message || e) });
throw e;
}
}
if (import.meta.url === `file://${process.argv[1]}`) {
ingestCook().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
}