← back to Homesonspec

geocode: one-time dr-horton coord backfill from CACHED snapshots (yolo iter-4)

fa040b5f7b246e6a5742fa835ab6533047f59893 · 2026-07-29 00:42:39 -0700 · Steve Abrams

Backfills the existing ~22.5k dr-horton homes' coords with ZERO HTTP: reads each RawSnapshot HTML
already on disk, extracts the community ld+json coord (same logic as the adapter), and UPDATEs homes
by exact sourceUrl match. Beats re-fetching thousands of pages (heavy, impolite, races the loop).
State-gated + US bbox. $0. Raised execFileSync maxBuffer for the 15,933-snapshot list query.

Files touched

• A scripts/backfill-drh-geo-from-cache.mjs

Diff

commit fa040b5f7b246e6a5742fa835ab6533047f59893
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Wed Jul 29 00:42:39 2026 -0700

    geocode: one-time dr-horton coord backfill from CACHED snapshots (yolo iter-4)
    
    Backfills the existing ~22.5k dr-horton homes' coords with ZERO HTTP: reads each RawSnapshot HTML
    already on disk, extracts the community ld+json coord (same logic as the adapter), and UPDATEs homes
    by exact sourceUrl match. Beats re-fetching thousands of pages (heavy, impolite, races the loop).
    State-gated + US bbox. $0. Raised execFileSync maxBuffer for the 15,933-snapshot list query.
---
 scripts/backfill-drh-geo-from-cache.mjs | 64 +++++++++++++++++++++++++++++++++
 1 file changed, 64 insertions(+)

diff --git a/scripts/backfill-drh-geo-from-cache.mjs b/scripts/backfill-drh-geo-from-cache.mjs
new file mode 100644
index 00000000..f382d4c5
--- /dev/null
+++ b/scripts/backfill-drh-geo-from-cache.mjs
@@ -0,0 +1,64 @@
+#!/usr/bin/env node
+// One-time backfill of dr-horton home coordinates FROM CACHED SNAPSHOTS — $0, ZERO HTTP.
+// The adapter now captures each community's in-feed ld+json coord (yolo iter-4), but the existing
+// ~22.5k dr-horton homes were extracted before that. Rather than re-fetch thousands of pages
+// (heavy + impolite + races the loop), read the RawSnapshot HTML already on disk, extract the
+// community coord, and UPDATE its homes by sourceUrl (exact match — no name ambiguity).
+// State-gated + coarse US bbox (a wrong pin is worse than null).
+import { execFileSync } from "node:child_process";
+import { readFileSync } from "node:fs";
+
+const DB = process.env.DATABASE_URL || "postgresql://macstudio3@localhost/homesonspec?host=/tmp";
+const psql = (sql) => execFileSync("psql", [DB, "-tAc", sql], { encoding: "utf8", maxBuffer: 256 * 1024 * 1024 }).trim();
+const q = (s) => String(s).replace(/'/g, "''");
+
+// Same community-coord logic as the dr-horton adapter (lowercase ld+json lat/lon; state-gated + US bbox).
+function communityGeo(html, state) {
+  const m = html.match(/"latitude"\s*:\s*(-?\d+(?:\.\d+)?)\s*,\s*"longitude"\s*:\s*(-?\d+(?:\.\d+)?)/);
+  if (!m) return null;
+  const lat = Number(m[1]), lon = Number(m[2]);
+  if (!Number.isFinite(lat) || !Number.isFinite(lon) || lat === 0 || lon === 0) return null;
+  if (lat < 15 || lat > 72 || lon < -180 || lon > -60) return null;
+  const region = html.match(/"addressregion"\s*:\s*"([A-Za-z]{2})"/i)?.[1]?.toUpperCase();
+  if (state && region && region !== String(state).toUpperCase()) return null;
+  return { lat, lon };
+}
+
+// dr-horton community-page snapshots (url path = /state/metro/city/community == 4 segments)
+const rows = psql(`select r.url || E'\\t' || r."storagePath" from "RawSnapshot" r
+  join "SourceRegistry" sr on sr.id=r."sourceId" where sr.key='dr-horton-site'`).split("\n").filter(Boolean);
+console.log(`dr-horton snapshots: ${rows.length}  · $0 (cache re-extract, no HTTP)`);
+
+let pinned = 0, communities = 0, skippedNoGeo = 0, missing = 0, updates = [];
+const flush = () => { if (updates.length) { psql(updates.join("\n")); updates = []; } };
+
+for (const line of rows) {
+  const [url, path] = line.split("\t");
+  if (!url || !path) continue;
+  const segs = url.replace(/^https?:\/\/[^/]+/, "").split("/").filter(Boolean);
+  if (segs.length !== 4) continue; // not a community page
+  // any still-ungeocoded homes for this community page? (also gives us the state for the gate)
+  const state = psql(`select state from "InventoryHome" h join "Builder" b on b.id=h."builderId"
+    where b.slug='dr-horton' and h."sourceUrl"='${q(url)}' and h.lat is null limit 1`);
+  if (!state) continue; // no ungeocoded homes here
+  let html;
+  try { html = readFileSync(path, "utf8"); } catch { missing++; continue; }
+  const g = communityGeo(html, state);
+  if (!g) { skippedNoGeo++; continue; }
+  communities++;
+  updates.push(`update "InventoryHome" set lat=${g.lat}, lon=${g.lon} where "sourceUrl"='${q(url)}' and lat is null;`);
+  // also the community record
+  updates.push(`update "Community" c set lat=${g.lat}, lon=${g.lon} from "InventoryHome" h
+    where h."communityId"=c.id and h."sourceUrl"='${q(url)}' and c.lat is null;`);
+  if (updates.length >= 200) { flush(); }
+  if (communities % 200 === 0) {
+    pinned = Number(psql(`select count(*) from "InventoryHome" h join "Builder" b on b.slug='dr-horton'
+      where b.id=h."builderId" and h.status='PUBLISHED' and h.lat is not null`));
+    console.log(`  ${communities} communities processed · dr-horton geocoded: ${pinned}`);
+  }
+}
+flush();
+pinned = Number(psql(`select count(*) from "InventoryHome" h join "Builder" b on b.slug='dr-horton'
+  where b.id=h."builderId" and h.status='PUBLISHED' and h.lat is not null`));
+console.log(`\nDONE: ${communities} communities geocoded, ${skippedNoGeo} had no in-feed coord, ${missing} snapshot files missing.`);
+console.log(`dr-horton now geocoded: ${pinned}. $0.`);

← d99677fd dr-horton: capture community coords from in-feed ld+json (yo  ·  back to Homesonspec  ·  publisher: promote lat/lon on the UPDATE branch too (contrar 12220514 →