← back to Nationalrealestate
src/ingest/parcels/tarrant_tx.ts
191 lines
/**
* Tarrant County TX (Fort Worth, FIPS 48439) parcel ingest — dedicated
* full-universe adapter (applies the Wake/Miami-Dade DTD verdict B precedent:
* a rich full-universe keyless ArcGIS county gets a dedicated adapter, not the
* sale-cursor generic one, so the real OWNER + market-value fields are preserved).
*
* Source: the City of Fort Worth hosted "Parcels_Public_Vview" FeatureServer
* (keyless, maxRecordCount 2000, supportsPagination, $0):
* https://services5.arcgis.com/3ddLCBXe1bRt7mzj/arcgis/rest/services/Parcels_Public_Vview/FeatureServer/0
* This layer is MULTI-COUNTY (Tarrant + neighboring Denton/Parker/Johnson/Wise
* fringes) — we filter server-side to COUNTYNAME='Tarrant' = ~714,982 parcels.
* (The TAD county's own gis.tad.org endpoints 000 to plain curl; this CFW-hosted
* public view is the reachable authoritative copy of the same TAD appraisal roll.)
*
* Richest MARKET-value county in the system: OWNER_NAME (100%), SITUS_ADDR +
* CITYNAME (real city names), LAND_VAL + IMPR_VAL + MARKET_VALUE + APPRAISED_VALUE
* (99% have MARKET_VALUE>0 — TRUE market value, not the assessed fraction Wake
* ships), LIVING_AREA (sqft, 89%), YR_BUILT (90%), STATE_USE_CODE (use),
* DEED_DATE (98%, "MM-DD-YYYY" string — deed/ownership-change date, NO price in
* this feed). No beds/baths (TAD withholds them in the public view) and no sale
* PRICE, so deed events are recorded as event_type='deed' with amount=NULL — an
* honest ownership-change record, never a fabricated price.
*
* source_id = ACCOUNT (the 8-digit / R-prefixed TAD appraisal account). Rows with
* a null ACCOUNT are geometry-only stubs (TAXPIN-only) and are skipped.
*
* Run: NODE_OPTIONS=--max-old-space-size=4096 npm run ingest:parcels tarrant
*/
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 = '48439';
const SOURCE_KEY = 'tarrant_tx';
const LAYER = 'https://services5.arcgis.com/3ddLCBXe1bRt7mzj/arcgis/rest/services/Parcels_Public_Vview/FeatureServer/0';
const WHERE = "COUNTYNAME='Tarrant'";
const PAGE = 2000;
// TK-50 field-level provenance: our parcel field → the exact source attribute it
// maps from. total_value maps from MARKET_VALUE (true market, not the assessed
// fraction); this feed carries a deed DATE but no sale price.
const FIELD_MAP: Record<string, string> = {
source_id: 'ACCOUNT', address: 'SITUS_ADDR', city: 'CITYNAME', tax_year: 'TAX_YEAR',
year_built: 'YR_BUILT', sqft: 'LIVING_AREA', use_desc: 'STATE_USE_CODE',
land_value: 'LAND_VAL', improvement_value: 'IMPR_VAL', total_value: 'MARKET_VALUE',
owner_name: 'OWNER_NAME', last_sale_date: 'DEED_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; };
/**
* Parse the feed's "MM-DD-YYYY" deed-date string to ISO 'YYYY-MM-DD', then run
* it through the shared sanity guard (future / pre-1900 / non-calendar reject).
* Returns null on any malformed or implausible date.
*/
function deedDateToISO(v: unknown): string | null {
const t = s(v);
if (!t) return null;
const m = t.match(/^(\d{2})-(\d{2})-(\d{4})$/);
if (!m) return null;
// sentinel: the feed writes 12-31-1900 / 01-01-1900 as "no deed on record"
// (~18k Tarrant rows, ~2.5%). Any year <= 1900 here is a placeholder, not a
// real recorded deed — drop it (same class as Wake's all-zero-deed guard).
if (Number(m[3]) <= 1900) return null;
const iso = `${m[3]}-${m[1]}-${m[2]}`;
return sanitizeEventDate(iso);
}
/** 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 = ['ACCOUNT', 'TAXPIN', 'OWNER_NAME', 'OWNER_ADDRESS', 'SITUS_ADDR', 'CITYNAME', 'TAX_YEAR',
'YR_BUILT', 'LIVING_AREA', 'LAND_SQFT', 'STATE_USE_CODE', 'PROPERTY_CLASS_CODE',
'LAND_VAL', 'IMPR_VAL', 'MARKET_VALUE', 'APPRAISED_VALUE',
'DEED_DATE', 'DEED_BOOK', 'DEED_PAGE'].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(`tarrant ${res.status}: ${(await res.text()).slice(0, 140)}`);
const j: any = await res.json();
if (j.error) throw new Error(`tarrant 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 ingestTarrant(): Promise<{ upserted: number }> {
const runId = await openRun('parcel_tarrant_tx', LAYER);
// TK-50: run start time — the fetched_at stamped on every row this run touches.
const fetchedAt = new Date().toISOString();
try {
await registerSourceFieldMap(SOURCE_KEY, FIELD_MAP, 'Tarrant County TX (TAD) via City of Fort Worth public parcel view');
let offset = 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 || {};
// skip geometry-only stubs (no ACCOUNT = no CAMA row behind the polygon)
const acct = s(a.ACCOUNT);
if (!acct) { skipped++; continue; }
seen++;
const [lng, lat] = centroid(f.geometry);
const addr = s(a.SITUS_ADDR);
const norm = addr ? normAddress(addr) : null;
const deedDate = deedDateToISO(a.DEED_DATE);
const cityRaw = s(a.CITYNAME);
// the feed writes 'No City Limit' / ' ' for unincorporated parcels — null those
const city = cityRaw && cityRaw !== 'No City Limit' ? cityRaw : null;
batch.push({
county_fips: FIPS, source_id: acct,
address: norm, norm_address: norm,
city, zip: null,
lat, lng,
year_built: num(a.YR_BUILT), sqft: num(a.LIVING_AREA), beds: null, baths: null, units: null,
use_desc: s(a.STATE_USE_CODE),
land_value: num(a.LAND_VAL), improvement_value: num(a.IMPR_VAL), total_value: num(a.MARKET_VALUE),
tax_year: s(a.TAX_YEAR), owner_name: s(a.OWNER_NAME), zoning: null,
// deed date is an ownership-change date, NOT a priced sale — record the date
// as last_sale_date but leave last_sale_price NULL (this feed carries no price).
last_sale_date: deedDate, last_sale_price: null,
extra: JSON.stringify({
taxpin: s(a.TAXPIN) || undefined,
appraised_value: num(a.APPRAISED_VALUE) || undefined,
property_class: s(a.PROPERTY_CLASS_CODE) || undefined,
land_sqft: num(a.LAND_SQFT) || undefined,
note: 'Tarrant County TX (TAD) via City of Fort Worth public parcel view — TRUE market value; no beds/baths, 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(`ACCOUNT='${acct}'`)}&outFields=*&f=html`,
fetchedAt,
rawSource: JSON.stringify(a),
});
if (deedDate) events.push({ acct, date: deedDate });
}
upserted += await upsertParcels(batch);
// record deed/ownership-change events (no amount — this feed has no price).
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_tarrant_tx', `https://www.tad.org/property/?account=${e.acct}`, JSON.stringify({ note: 'deed date only, no price in TAD public feed' })]),
);
deeds += chunk.length;
}
offset += feats.length;
if (seen % 100000 < feats.length) console.log(`[tarrant] ${upserted} upserted, ${deeds} deeds, ${skipped} stubs skipped`);
if (feats.length < PAGE) break;
}
if (upserted < 650000) throw new Error(`only ${upserted} Tarrant parcels (expected ~715k) — layer/paging/filter drift?`);
const n = await registerParcelSource(FIPS, LAYER,
`Tarrant County TX (Fort Worth) TAD appraisal roll via City of Fort Worth public parcel view (COUNTYNAME='Tarrant' filter) — ${upserted} parcels: owner (100%), situs address + city, LAND+IMPR + TRUE MARKET value (99%) + appraised value, sqft (living area, 89%), year built (90%), state use code, deed/ownership-change date (98%) → ${deeds} parcel_event deed rows. No beds/baths or sale PRICE in this feed (deed date only, amount NULL). ${skipped} geometry-only stubs (null ACCOUNT) skipped.`);
await closeRun(runId, 'ok', { upserted, skipped, notes: `Tarrant TX: ${upserted} parcels, ${deeds} deed events, ${skipped} stubs skipped` });
console.log(`[tarrant] ok: ${upserted} parcels, ${deeds} deed events, ${skipped} stubs (registry ${n})`);
return { upserted };
} catch (e: any) {
await closeRun(runId, 'failed', { notes: String(e.message || e).slice(0, 500) });
throw e;
}
}
if (import.meta.url === `file://${process.argv[1]}`) {
ingestTarrant().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
}