← back to Homesonspec
geocode: contrarian FIX-FIRST — city+state gate, full provenance, fail-fast timeout
0086b3461fa7c427934b446400e99e8c44da6407 · 2026-07-28 23:18:46 -0700 · Steve Abrams
Cody's gate (4/5 FIX-FIRST) caught three real holes, all reproduced + fixed:
1. State-only accuracy gate couldn't catch wrong-city-right-state (Austin->El Paso, both TX, 570mi).
Now gates on normalized CITY *and* STATE match; a city mismatch is rejected (left null), logged
as wrong_city. A wrong pin is worse than null.
2. home_geocode stored no provenance -> gate unauditable + unupgradable without a 28k re-run. Now
stores matched_address/matched_city/matched_state/matched_lat/matched_lon/geocoder on EVERY
attempt. Future geocoder upgrade = read query + WHERE geocoder!='x', not a DELETE.
3. Dead 'bbox second-line check' comment describing code that never existed -> removed (city gate
is stronger anyway). Build-loop geocode step: CURL_TIMEOUT=120 fail-fast (was 900s = a Census
outage could stall a sweep 15min) + logs to tmp/geocode.log (was /dev/null = invisible failures).
Provenance verified stored; sample pins spot-checked accurate (Richmond TX/Gainesville FL/Raeford NC).
Files touched
M scripts/build-loop.shM scripts/geocode-homes.mjs
Diff
commit 0086b3461fa7c427934b446400e99e8c44da6407
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 28 23:18:46 2026 -0700
geocode: contrarian FIX-FIRST — city+state gate, full provenance, fail-fast timeout
Cody's gate (4/5 FIX-FIRST) caught three real holes, all reproduced + fixed:
1. State-only accuracy gate couldn't catch wrong-city-right-state (Austin->El Paso, both TX, 570mi).
Now gates on normalized CITY *and* STATE match; a city mismatch is rejected (left null), logged
as wrong_city. A wrong pin is worse than null.
2. home_geocode stored no provenance -> gate unauditable + unupgradable without a 28k re-run. Now
stores matched_address/matched_city/matched_state/matched_lat/matched_lon/geocoder on EVERY
attempt. Future geocoder upgrade = read query + WHERE geocoder!='x', not a DELETE.
3. Dead 'bbox second-line check' comment describing code that never existed -> removed (city gate
is stronger anyway). Build-loop geocode step: CURL_TIMEOUT=120 fail-fast (was 900s = a Census
outage could stall a sweep 15min) + logs to tmp/geocode.log (was /dev/null = invisible failures).
Provenance verified stored; sample pins spot-checked accurate (Richmond TX/Gainesville FL/Raeford NC).
---
scripts/build-loop.sh | 5 +-
scripts/geocode-homes.mjs | 130 ++++++++++++++++++++++++++--------------------
2 files changed, 77 insertions(+), 58 deletions(-)
diff --git a/scripts/build-loop.sh b/scripts/build-loop.sh
index 5cf1f6c8..c5b80980 100644
--- a/scripts/build-loop.sh
+++ b/scripts/build-loop.sh
@@ -195,7 +195,10 @@ while [ ! -f "$STOP" ] && [ "$sweep" -lt "$MAX_SWEEPS" ]; do
# NOTE: Census only matches ~6% of NEW-CONSTRUCTION addresses (most aren't in TIGER yet) — this is
# the cheap partial pass; the real geo fix is per-builder detail-page coord capture (like Fischer).
geo_todo=$(PSQL "select count(*) from \"InventoryHome\" h where h.status='PUBLISHED' and h.lat is null and h.street is not null and h.street !~* '^lot ' and h.city is not null and h.state is not null and not exists (select 1 from home_geocode g where g.home_id=h.id);")
- if [ "${geo_todo:-0}" -gt 0 ]; then LOG "geocode: $geo_todo new homes"; node scripts/geocode-homes.mjs >/dev/null 2>&1; fi
+ # Only NEW arrivals reach here (home_geocode guard); the one-time 28k backfill is run manually.
+ # CURL_TIMEOUT=120 fails fast so a Census outage can never stall a sweep (Cody fix); log to file
+ # (not /dev/null) so failures are visible.
+ if [ "${geo_todo:-0}" -gt 0 ]; then LOG "geocode: $geo_todo new homes"; CURL_TIMEOUT=120 node scripts/geocode-homes.mjs >> "$ROOT/tmp/geocode.log" 2>&1; fi
s1=$(PSQL "select count(*) from \"InventoryHome\" where status='PUBLISHED';")
b1=$(PSQL "select count(distinct \"builderId\") from \"InventoryHome\" where status='PUBLISHED';")
enr=$(PSQL "select count(*) from \"Community\" where amenities is not null;")
diff --git a/scripts/geocode-homes.mjs b/scripts/geocode-homes.mjs
index 5f8b99b9..1a94c762 100644
--- a/scripts/geocode-homes.mjs
+++ b/scripts/geocode-homes.mjs
@@ -1,13 +1,16 @@
#!/usr/bin/env node
-// Geocode InventoryHome rows that have a real street+city+state but no lat/lon,
-// via the FREE US Census batch geocoder (no key, $0). Approach B (DTD iter-3, 5/5):
-// VALIDATE-FIRST with a hard STATE-MATCH accuracy gate — a coordinate is only written
-// when the Census-matched address is in the home's stated state; otherwise the row is
-// left null and logged, because a wrong pin is worse than null on a map-first product.
+// Geocode InventoryHome rows with a real street+city+state but no lat/lon, via the FREE
+// US Census batch geocoder (no key, $0). Approach B (DTD iter-3, 5/5) + contrarian FIX-FIRST:
//
-// A sidecar table `home_geocode` records every ATTEMPT (match | nomatch | wrong_state)
-// so re-runs skip already-tried rows (the geo_status guard, kept off InventoryHome to
-// avoid Prisma schema drift). Facts-only, honest UA. $0 (local + free Census API).
+// ACCURACY GATE — a coordinate is written ONLY when the Census-matched address is in the
+// home's stated STATE *and* CITY (both normalized). State-only was insufficient: a Texas
+// home mis-matched Austin->El Paso (same state, 570mi off) would have passed. A wrong pin is
+// worse than null on a map-first product, so a city mismatch is REJECTED (left null, logged).
+//
+// PROVENANCE — every attempt stores matched_address / matched_city / matched_state /
+// matched_lat / matched_lon / geocoder, so the gate is auditable AFTER the fact and a future
+// upgrade (better geocoder, distance gate) is a read-query, not a 28k re-run. `geocoder` lets a
+// later pass retry Census 'nomatch' rows with a different provider without a DELETE.
//
// SAMPLE=500 node scripts/geocode-homes.mjs # accuracy-gate sample
// node scripts/geocode-homes.mjs # full run (10k/chunk until drained)
@@ -18,98 +21,111 @@ const DB = process.env.DATABASE_URL || "postgresql://macstudio3@localhost/homeso
const SAMPLE = process.env.SAMPLE ? Number(process.env.SAMPLE) : null;
const CHUNK = SAMPLE ? Math.min(SAMPLE, 10000) : 10000;
const UA = "HomesOnSpecBot/0.1 (+homesonspec.com; site-owner-facts-only)";
+const CURL_TIMEOUT = process.env.CURL_TIMEOUT || "120"; // fail fast — never stall a build-loop sweep
-// US state bounding boxes (lon/lat) as a coarse second-line accuracy check on top of
-// the matched-state string test — catches a right-state-name-wrong-place match.
const psql = (sql) => execFileSync("psql", [DB, "-tAc", sql], { encoding: "utf8" }).trim();
+const q = (s) => String(s).replace(/'/g, "''");
+
+// Normalize a city for comparison: uppercase, strip "CITY OF"/"TOWN OF", expand common
+// abbreviations, drop punctuation + "(BALANCE)"/county noise Census sometimes appends.
+function normCity(s) {
+ return String(s || "").toUpperCase()
+ .replace(/\bCITY OF\b|\bTOWN OF\b|\bVILLAGE OF\b/g, "")
+ .replace(/\bST\.?\b/g, "SAINT").replace(/\bFT\.?\b/g, "FORT").replace(/\bMT\.?\b/g, "MOUNT")
+ .replace(/\bN\.?\b/g, "NORTH").replace(/\bS\.?\b/g, "SOUTH")
+ .replace(/\bE\.?\b/g, "EAST").replace(/\bW\.?\b/g, "WEST")
+ .replace(/\(.*?\)/g, "").replace(/[^A-Z ]/g, " ").replace(/\s+/g, " ").trim();
+}
-// one-time: sidecar attempt log
-psql(`create table if not exists home_geocode (home_id text primary key, attempted_at timestamptz default now(), status text, matched_state text);`);
+// Sidecar attempt log WITH provenance (contrarian FIX: matched_* stored for audit + upgrade).
+psql(`create table if not exists home_geocode (
+ home_id text primary key, attempted_at timestamptz default now(), status text,
+ geocoder text default 'census', matched_address text, matched_city text,
+ matched_state text, matched_lat double precision, matched_lon double precision);`);
+// idempotent column adds for an older-shaped table
+for (const col of ["geocoder text default 'census'", "matched_address text", "matched_city text",
+ "matched_lat double precision", "matched_lon double precision"]) {
+ psql(`alter table home_geocode add column if not exists ${col};`);
+}
-const total = () => Number(psql(`select count(*) from "InventoryHome" h
- where h.status='PUBLISHED' and h.lat is null and h.street is not null and h.street !~* '^lot '
- and h.city is not null and h.state is not null
- and not exists (select 1 from home_geocode g where g.home_id=h.id)`));
+const SEL_WHERE = `h.status='PUBLISHED' and h.lat is null and h.street is not null and h.street !~* '^lot '
+ and h.city is not null and h.state is not null and not exists (select 1 from home_geocode g where g.home_id=h.id)`;
-let hits = 0, wrongState = 0, noMatch = 0, processed = 0;
-const startRemaining = total();
-console.log(`geocode-homes: ${startRemaining} homes to attempt${SAMPLE ? ` (SAMPLE=${SAMPLE})` : ""} · $0 (free Census API)`);
+let hits = 0, wrongState = 0, wrongCity = 0, noMatch = 0, processed = 0, round = 0;
+console.log(`geocode-homes: ${psql(`select count(*) from "InventoryHome" h where ${SEL_WHERE}`)} homes to attempt${SAMPLE ? ` (SAMPLE=${SAMPLE})` : ""} · $0 (free Census API)`);
-let round = 0;
while (true) {
if (SAMPLE && processed >= SAMPLE) break;
const lim = SAMPLE ? Math.min(CHUNK, SAMPLE - processed) : CHUNK;
- // id,street,city,state,zip — Census one-line-per-address, no header
+ // id,street,city,state,zip — Census one-line-per-address, no header. Carry the stated city
+ // alongside via a lookup map so the gate can compare (Census echoes only id + input string).
const rows = psql(`copy (
select h.id, replace(coalesce(h.street,''),',',' '), replace(coalesce(h.city,''),',',' '), h.state, coalesce(h.zip,'')
- from "InventoryHome" h
- where h.status='PUBLISHED' and h.lat is null and h.street is not null and h.street !~* '^lot '
- and h.city is not null and h.state is not null
- and not exists (select 1 from home_geocode g where g.home_id=h.id)
- limit ${lim}
+ from "InventoryHome" h where ${SEL_WHERE} limit ${lim}
) to stdout with csv`);
if (!rows) break;
round++;
const lines = rows.split("\n").filter(Boolean);
+ // id -> {city,state} for the gate (parse our own CSV so we don't re-query per row)
+ const want = new Map();
+ for (const cells of csvRows(lines.join("\n"))) {
+ if (cells[0]) want.set(cells[0], { city: cells[2], state: cells[3] });
+ }
writeFileSync("/tmp/hgeo_in.csv", lines.join("\n") + "\n");
console.log(` chunk ${round}: geocoding ${lines.length}…`);
try {
- execFileSync("curl", ["-s", "--max-time", "900", "-A", UA,
- "--form", "addressFile=@/tmp/hgeo_in.csv",
- "--form", "benchmark=Public_AR_Current",
- "https://geocoding.geo.census.gov/geocoder/locations/addressbatch",
- "-o", "/tmp/hgeo_out.csv"], { encoding: "utf8" });
- } catch (e) { console.error(" census POST failed:", e.message); break; }
+ execFileSync("curl", ["-s", "--max-time", CURL_TIMEOUT, "-A", UA,
+ "--form", "addressFile=@/tmp/hgeo_in.csv", "--form", "benchmark=Public_AR_Current",
+ "https://geocoding.geo.census.gov/geocoder/locations/addressbatch", "-o", "/tmp/hgeo_out.csv"],
+ { encoding: "utf8" });
+ } catch (e) { console.error(` census POST failed (${e.message}) — leaving chunk unlogged for retry`); break; }
- const out = readFileSync("/tmp/hgeo_out.csv", "utf8");
const updates = [], logs = [];
- // Census CSV columns: id, input, indicator, matchtype, matched_address, "lon,lat", tiger, side
- for (const rec of csvRows(out)) {
+ for (const rec of csvRows(readFileSync("/tmp/hgeo_out.csv", "utf8"))) {
const id = rec[0];
- if (!id) continue;
- const wantState = psql(`select state from "InventoryHome" where id='${id.replace(/'/g, "''")}'`);
+ if (!id || !want.has(id)) continue;
+ const w = want.get(id);
const indicator = rec[2];
- let status = "nomatch", mState = null;
+ let status = "nomatch", mCity = null, mState = null, mLat = null, mLon = null, mAddr = rec[4] || null;
if (indicator === "Match" && rec[5] && rec[5].includes(",")) {
- // matched_address e.g. "123 MAIN ST, PHOENIX, AZ, 85001" -> state token
- const m = (rec[4] || "").match(/,\s*([A-Z]{2}),\s*\d{5}/);
- mState = m ? m[1] : null;
- const [lon, lat] = rec[5].split(",").map(Number);
- if (mState && wantState && mState.toUpperCase() === wantState.toUpperCase()
- && Number.isFinite(lat) && Number.isFinite(lon)) {
- updates.push(`update "InventoryHome" set lat=${lat}, lon=${lon} where id='${id.replace(/'/g, "''")}';`);
+ // matched_address e.g. "123 MAIN ST, PHOENIX, AZ, 85001"
+ const m = (rec[4] || "").match(/,\s*([^,]+),\s*([A-Z]{2}),\s*\d{5}/);
+ mCity = m ? m[1].trim() : null;
+ mState = m ? m[2] : null;
+ [mLon, mLat] = rec[5].split(",").map(Number);
+ const stateOk = mState && mState.toUpperCase() === String(w.state).toUpperCase();
+ const cityOk = mCity && normCity(mCity) === normCity(w.city);
+ if (stateOk && cityOk && Number.isFinite(mLat) && Number.isFinite(mLon)) {
+ updates.push(`update "InventoryHome" set lat=${mLat}, lon=${mLon} where id='${q(id)}';`);
status = "match"; hits++;
- } else {
- status = "wrong_state"; wrongState++; // matched, but NOT in the stated state -> reject (worse than null)
- }
+ } else if (!stateOk) { status = "wrong_state"; wrongState++; }
+ else { status = "wrong_city"; wrongCity++; } // right state, wrong city -> reject (Austin!=El Paso)
} else { noMatch++; }
- logs.push(`insert into home_geocode(home_id,status,matched_state) values ('${id.replace(/'/g, "''")}','${status}',${mState ? `'${mState}'` : "null"}) on conflict (home_id) do update set status=excluded.status, matched_state=excluded.matched_state, attempted_at=now();`);
+ logs.push(`insert into home_geocode(home_id,status,geocoder,matched_address,matched_city,matched_state,matched_lat,matched_lon) values ('${q(id)}','${status}','census',${mAddr ? `'${q(mAddr)}'` : "null"},${mCity ? `'${q(mCity)}'` : "null"},${mState ? `'${mState}'` : "null"},${mLat ?? "null"},${mLon ?? "null"}) on conflict (home_id) do update set status=excluded.status, matched_address=excluded.matched_address, matched_city=excluded.matched_city, matched_state=excluded.matched_state, matched_lat=excluded.matched_lat, matched_lon=excluded.matched_lon, attempted_at=now();`);
processed++;
}
if (updates.length) psql(updates.join("\n"));
if (logs.length) psql(logs.join("\n"));
- console.log(` +${updates.length} pinned · running: ${hits} match / ${wrongState} wrong-state(rejected) / ${noMatch} nomatch`);
+ console.log(` +${updates.length} pinned · running: ${hits} match / ${wrongState} wrong-state / ${wrongCity} wrong-city / ${noMatch} nomatch (all rejected but the matches)`);
if (lines.length < lim) break; // drained
}
-
-console.log(`\ngeocode-homes DONE: ${hits} pinned, ${wrongState} wrong-state rejected, ${noMatch} no-match (${processed} attempted). $0.`);
+console.log(`\ngeocode-homes DONE: ${hits} pinned, ${wrongState} wrong-state + ${wrongCity} wrong-city rejected, ${noMatch} no-match (${processed} attempted). $0.`);
// minimal CSV parser (Census output is quoted; handles commas inside quoted fields)
function csvRows(text) {
const rows = [];
for (const line of text.split("\n")) {
if (!line) continue;
- const cells = []; let cur = "", q = false;
+ const cells = []; let cur = "", quoted = false;
for (let i = 0; i < line.length; i++) {
const c = line[i];
- if (q) { if (c === '"') { if (line[i + 1] === '"') { cur += '"'; i++; } else q = false; } else cur += c; }
- else if (c === '"') q = true;
+ if (quoted) { if (c === '"') { if (line[i + 1] === '"') { cur += '"'; i++; } else quoted = false; } else cur += c; }
+ else if (c === '"') quoted = true;
else if (c === ",") { cells.push(cur); cur = ""; }
else cur += c;
}
- cells.push(cur);
- rows.push(cells);
+ cells.push(cur); rows.push(cells);
}
return rows;
}
← 139ce339 geocode: Census-batch enrichment for no-geo homes (yolo iter
·
back to Homesonspec
·
geocode: 2k-address chunks (was 10k) so bulk backfill fits i 13b17887 →