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