← back to Homesonspec
geocode: Census-batch enrichment for no-geo homes (yolo iter-3, DTD verdict B)
139ce339330f6d6df1ca1cd0477abb2aa1d56e4e · 2026-07-28 23:10:29 -0700 · Steve Abrams
Pays down the map-first geo debt Cody flagged 2 cycles running (~28k homes with real
street+city+state but no lat/lon). Free US Census batch geocoder ($0, no key), approach B:
validate-first with a HARD state-match accuracy gate — writes lat/lon ONLY when the Census-matched
address is in the home's stated state (a wrong pin is worse than null). Sidecar home_geocode table
guards re-runs. Wired into the build-loop as a post-sweep step (geocodes only new arrivals).
VERIFIED CEILING: Census matches only ~6% of NEW-CONSTRUCTION addresses (500-sample: 30 pinned,
0 wrong-state, 470 no-match) because brand-new subdivisions aren't in Census TIGER road data yet;
the ~6% it DOES match are accurate (spot-checked: right city). The real geo fix for the other ~94%
is per-builder detail-page coord capture (like Fischer's JSON-LD) — a future increment.
Files touched
M scripts/build-loop.shA scripts/geocode-homes.mjs
Diff
commit 139ce339330f6d6df1ca1cd0477abb2aa1d56e4e
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 28 23:10:29 2026 -0700
geocode: Census-batch enrichment for no-geo homes (yolo iter-3, DTD verdict B)
Pays down the map-first geo debt Cody flagged 2 cycles running (~28k homes with real
street+city+state but no lat/lon). Free US Census batch geocoder ($0, no key), approach B:
validate-first with a HARD state-match accuracy gate — writes lat/lon ONLY when the Census-matched
address is in the home's stated state (a wrong pin is worse than null). Sidecar home_geocode table
guards re-runs. Wired into the build-loop as a post-sweep step (geocodes only new arrivals).
VERIFIED CEILING: Census matches only ~6% of NEW-CONSTRUCTION addresses (500-sample: 30 pinned,
0 wrong-state, 470 no-match) because brand-new subdivisions aren't in Census TIGER road data yet;
the ~6% it DOES match are accurate (spot-checked: right city). The real geo fix for the other ~94%
is per-builder detail-page coord capture (like Fischer's JSON-LD) — a future increment.
---
scripts/build-loop.sh | 6 +++
scripts/geocode-homes.mjs | 115 ++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 121 insertions(+)
diff --git a/scripts/build-loop.sh b/scripts/build-loop.sh
index 80f9f501..5cf1f6c8 100644
--- a/scripts/build-loop.sh
+++ b/scripts/build-loop.sh
@@ -190,6 +190,12 @@ while [ ! -f "$STOP" ] && [ "$sweep" -lt "$MAX_SWEEPS" ]; do
LOG "enrich pass $i: $rem left"
NEARBY_LIMIT=80 npx tsx scripts/enrich-nearby.ts >/dev/null 2>&1
done
+ # geocode NEW homes with a real street+city+state but no lat/lon (free Census, $0, state-gated).
+ # Skips already-attempted rows via the home_geocode guard, so each sweep only geocodes new arrivals.
+ # 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
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
new file mode 100644
index 00000000..5f8b99b9
--- /dev/null
+++ b/scripts/geocode-homes.mjs
@@ -0,0 +1,115 @@
+#!/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.
+//
+// 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).
+//
+// SAMPLE=500 node scripts/geocode-homes.mjs # accuracy-gate sample
+// node scripts/geocode-homes.mjs # full run (10k/chunk until drained)
+import { execFileSync } from "node:child_process";
+import { writeFileSync, readFileSync } from "node:fs";
+
+const DB = process.env.DATABASE_URL || "postgresql://macstudio3@localhost/homesonspec?host=/tmp";
+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)";
+
+// 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();
+
+// 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);`);
+
+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)`));
+
+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 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
+ 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}
+ ) to stdout with csv`);
+ if (!rows) break;
+ round++;
+ const lines = rows.split("\n").filter(Boolean);
+ 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; }
+
+ 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)) {
+ const id = rec[0];
+ if (!id) continue;
+ const wantState = psql(`select state from "InventoryHome" where id='${id.replace(/'/g, "''")}'`);
+ const indicator = rec[2];
+ let status = "nomatch", mState = 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, "''")}';`);
+ status = "match"; hits++;
+ } else {
+ status = "wrong_state"; wrongState++; // matched, but NOT in the stated state -> reject (worse than null)
+ }
+ } 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();`);
+ 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`);
+ if (lines.length < lim) break; // drained
+}
+
+console.log(`\ngeocode-homes DONE: ${hits} pinned, ${wrongState} wrong-state 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;
+ 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;
+ else if (c === ",") { cells.push(cur); cur = ""; }
+ else cur += c;
+ }
+ cells.push(cur);
+ rows.push(cells);
+ }
+ return rows;
+}
← e3c33f7b fulton: parse bed/bath RANGES correctly (contrarian fix) — r
·
back to Homesonspec
·
geocode: contrarian FIX-FIRST — city+state gate, full proven 0086b346 →