geocode: shea per-home coord backfill from detail pages (yolo iter-5 prong 2)

ee8b7a7cdce214a5be3b1d8b77f27bcb33119e29 · 2026-07-29 01:39:38 -0700 · Steve Abrams

Shea's qmi list page (adapter source) has no coords, but each home's DETAIL page (its stored
sourceUrl) carries schema.org GeoCoordinates. Bounded ~190-GET backfill, honest-UA, polite 4/s,
state-gated + US bbox. Result: 188/190 pinned, 0 wrong-state, all spot-checks accurate. $0.
Follow-up: a per-home detail-GET in the adapter (Fischer's pattern) would make NEW shea homes durable.

Files touched

A scripts/backfill-shea-geo.mjs

Diff

commit ee8b7a7cdce214a5be3b1d8b77f27bcb33119e29
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Jul 29 01:39:38 2026 -0700

    geocode: shea per-home coord backfill from detail pages (yolo iter-5 prong 2)
    
    Shea's qmi list page (adapter source) has no coords, but each home's DETAIL page (its stored
    sourceUrl) carries schema.org GeoCoordinates. Bounded ~190-GET backfill, honest-UA, polite 4/s,
    state-gated + US bbox. Result: 188/190 pinned, 0 wrong-state, all spot-checks accurate. $0.
    Follow-up: a per-home detail-GET in the adapter (Fischer's pattern) would make NEW shea homes durable.
---
 scripts/backfill-shea-geo.mjs | 46 +++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 46 insertions(+)

diff --git a/scripts/backfill-shea-geo.mjs b/scripts/backfill-shea-geo.mjs
new file mode 100644
index 00000000..0ad19152
--- /dev/null
+++ b/scripts/backfill-shea-geo.mjs
@@ -0,0 +1,46 @@
+#!/usr/bin/env node
+// Shea per-home coord backfill (yolo iter-5). Shea's qmi LIST page (the adapter's source) has no
+// coords, but each home's DETAIL page — which is the home's stored sourceUrl — carries schema.org
+// GeoCoordinates. Bounded (~190 homes), honest-UA, polite, $0. State-gated + US bbox (a wrong pin is
+// worse than null). NOTE: this is a one-time backfill; making NEW Shea homes get coords durably would
+// need a per-home detail-GET in the adapter (Fischer's pattern) — logged as a follow-up.
+import { execFileSync } from "node:child_process";
+
+const DB = process.env.DATABASE_URL || "postgresql://macstudio3@localhost/homesonspec?host=/tmp";
+const UA = "HomesOnSpecBot/0.1 (+homesonspec.com; site-owner-facts-only)";
+const psql = (sql) => execFileSync("psql", [DB, "-tAc", sql], { encoding: "utf8", maxBuffer: 64 * 1024 * 1024 }).trim();
+const q = (s) => String(s).replace(/'/g, "''");
+const sleep = (ms) => { const t = Date.now() + ms; while (Date.now() < t) {} }; // crude polite delay
+
+const rows = psql(`select h.id || E'\\t' || h.state || E'\\t' || h."sourceUrl"
+  from "InventoryHome" h join "Builder" b on b.id=h."builderId"
+  where b.slug='shea-homes' and h.status='PUBLISHED' and h.lat is null and h."sourceUrl" is not null`).split("\n").filter(Boolean);
+console.log(`shea geo backfill: ${rows.length} homes  · $0 (polite detail-page GETs)`);
+
+let hits = 0, wrongState = 0, noGeo = 0, updates = [];
+const flush = () => { if (updates.length) { psql(updates.join("\n")); updates = []; } };
+
+for (let i = 0; i < rows.length; i++) {
+  const [id, state, url] = rows[i].split("\t");
+  if (!url) continue;
+  let html = "";
+  try { html = execFileSync("curl", ["-s", "--max-time", "20", "-A", UA, url], { encoding: "utf8", maxBuffer: 16 * 1024 * 1024 }); }
+  catch { noGeo++; continue; }
+  // schema.org GeoCoordinates: "geo":{"@type":"GeoCoordinates","latitude":"..","longitude":".."}
+  const m = html.match(/"GeoCoordinates"[^}]*?"latitude"\s*:\s*"?(-?\d+(?:\.\d+)?)"?[^}]*?"longitude"\s*:\s*"?(-?\d+(?:\.\d+)?)"?/i)
+        || html.match(/"latitude"\s*:\s*"?(-?\d+(?:\.\d+)?)"?\s*,\s*"longitude"\s*:\s*"?(-?\d+(?:\.\d+)?)"?/i);
+  if (!m) { noGeo++; continue; }
+  const lat = Number(m[1]), lon = Number(m[2]);
+  if (!Number.isFinite(lat) || !Number.isFinite(lon) || lat < 15 || lat > 72 || lon < -180 || lon > -60) { noGeo++; continue; }
+  // state gate: the detail page's addressregion must match the home's stated state
+  const region = html.match(/"addressRegion"\s*:\s*"([A-Za-z]{2})"/i)?.[1]?.toUpperCase();
+  if (state && region && region !== state.toUpperCase()) { wrongState++; continue; }
+  updates.push(`update "InventoryHome" set lat=${lat}, lon=${lon} where id='${q(id)}' and lat is null;`);
+  hits++;
+  if (updates.length >= 50) flush();
+  if ((i + 1) % 40 === 0) console.log(`  ${i + 1}/${rows.length} · ${hits} pinned / ${wrongState} wrong-state / ${noGeo} no-geo`);
+  sleep(250); // ~4 req/s — polite to Shea's origin
+}
+flush();
+const pinned = psql(`select count(lat) from "InventoryHome" h join "Builder" b on b.slug='shea-homes' where b.id=h."builderId" and h.status='PUBLISHED'`);
+console.log(`\nshea geo DONE: ${hits} pinned, ${wrongState} wrong-state rejected, ${noGeo} no-geo. shea now geocoded: ${pinned}. $0.`);

← c806053d auto-save: 2026-07-29T01:34:44 (1 files) — scripts/backfill- · back to Homesonspec · pipeline: make evidence delete+create atomic (contrarian fix 11a5e5fa →