← back to Homesonspec
workers: public-records nearby-enrichment stage (NCES schools + OSM places)
8dda3328e54d293f900a0f87936c443af2c09f52 · 2026-07-27 20:15:04 -0700 · Steve
Attaches objective nearby facts to communities from PUBLIC RECORDS instead of
builders' robots-disallowed nearby-schools/amenities pages: NCES CCD schools
(via Urban Institute API; no ratings -> Fair-Housing safe) + OpenStreetMap places
(ODbL, attributed). Keyed on Community.lat/lon, persisted to Community.amenities
with per-source provenance. Idempotent, op-N batched, $0. Verified on 40 real
Lennar/Toll communities (schools 100%, OSM places 25/35 with graceful fallback).
Files touched
A apps/workers/scripts/enrich-nearby.ts
Diff
commit 8dda3328e54d293f900a0f87936c443af2c09f52
Author: Steve <steve@designerwallcoverings.com>
Date: Mon Jul 27 20:15:04 2026 -0700
workers: public-records nearby-enrichment stage (NCES schools + OSM places)
Attaches objective nearby facts to communities from PUBLIC RECORDS instead of
builders' robots-disallowed nearby-schools/amenities pages: NCES CCD schools
(via Urban Institute API; no ratings -> Fair-Housing safe) + OpenStreetMap places
(ODbL, attributed). Keyed on Community.lat/lon, persisted to Community.amenities
with per-source provenance. Idempotent, op-N batched, $0. Verified on 40 real
Lennar/Toll communities (schools 100%, OSM places 25/35 with graceful fallback).
---
apps/workers/scripts/enrich-nearby.ts | 165 ++++++++++++++++++++++++++++++++++
1 file changed, 165 insertions(+)
diff --git a/apps/workers/scripts/enrich-nearby.ts b/apps/workers/scripts/enrich-nearby.ts
new file mode 100644
index 0000000..02be326
--- /dev/null
+++ b/apps/workers/scripts/enrich-nearby.ts
@@ -0,0 +1,165 @@
+/**
+ * Public-records "nearby" enrichment stage.
+ *
+ * WHY: builders' own nearby-schools / nearby-places / amenities pages are
+ * frequently robots.txt-disallowed (e.g. Lennar `/*nearby-schools`,
+ * `/*amenities`). Rather than scrape those blocked pages, we attach the SAME
+ * objective facts from PUBLIC RECORDS:
+ * - schools → NCES CCD via the Urban Institute Education Data API
+ * (public domain; objective only — NO ratings/quality → Fair-Housing safe)
+ * - places → OpenStreetMap / Overpass (ODbL; attribution recorded)
+ *
+ * Keyed on Community.lat/lon; writes to Community.amenities (JSON) with
+ * per-source attribution + retrievedAt. Idempotent (skips already-enriched),
+ * op-N batched (NEARBY_LIMIT, default 40), $0 (free public APIs).
+ *
+ * Run: DATABASE_URL=… NEARBY_LIMIT=40 pnpm --filter @homesonspec/workers exec tsx scripts/enrich-nearby.ts
+ */
+import { prisma, Prisma } from "@homesonspec/database";
+
+const YEAR = process.env.NCES_YEAR ?? "2022";
+const LIMIT = Number(process.env.NEARBY_LIMIT ?? 40);
+const SCHOOL_N = 6;
+const PLACE_RADIUS_M = 8000;
+const UA = "HomesOnSpecBot/0.1 (+https://homesonspec.com/bot; contact: data@homesonspec.com)";
+
+const STATE_FIPS: Record<string, number> = {
+ AL:1,AK:2,AZ:4,AR:5,CA:6,CO:8,CT:9,DE:10,DC:11,FL:12,GA:13,HI:15,ID:16,IL:17,IN:18,IA:19,
+ KS:20,KY:21,LA:22,ME:23,MD:24,MA:25,MI:26,MN:27,MS:28,MO:29,MT:30,NE:31,NV:32,NH:33,NJ:34,
+ NM:35,NY:36,NC:37,ND:38,OH:39,OK:40,OR:41,PA:42,RI:44,SC:45,SD:46,TN:47,TX:48,UT:49,VT:50,
+ VA:51,WA:53,WV:54,WI:55,WY:56,
+};
+const LEVEL: Record<number, string> = { 1: "Elementary", 2: "Middle", 3: "High", 4: "Other" };
+
+function milesBetween(aLat: number, aLon: number, bLat: number, bLon: number): number {
+ const R = 3958.8;
+ const p1 = (aLat * Math.PI) / 180, p2 = (bLat * Math.PI) / 180;
+ const dp = ((bLat - aLat) * Math.PI) / 180, dl = ((bLon - aLon) * Math.PI) / 180;
+ const x = Math.sin(dp / 2) ** 2 + Math.cos(p1) * Math.cos(p2) * Math.sin(dl / 2) ** 2;
+ return R * 2 * Math.asin(Math.sqrt(x));
+}
+
+interface School { ncessch: string; name: string; level: string; lat: number; lon: number; city: string | null }
+const stateSchoolCache = new Map<number, School[]>();
+
+async function schoolsForState(fips: number): Promise<School[]> {
+ const hit = stateSchoolCache.get(fips);
+ if (hit) return hit;
+ let out: School[] = [];
+ try {
+ const res = await fetch(`https://educationdata.urban.org/api/v1/schools/ccd/directory/${YEAR}/?fips=${fips}`, {
+ headers: { "User-Agent": UA, Accept: "application/json" },
+ signal: AbortSignal.timeout(45_000),
+ });
+ if (res.ok) {
+ const data: any = await res.json();
+ out = (data.results ?? [])
+ .filter((s: any) => s.latitude != null && s.longitude != null)
+ .map((s: any): School => ({
+ ncessch: String(s.ncessch),
+ name: s.school_name,
+ level: LEVEL[s.school_level] ?? "",
+ lat: s.latitude,
+ lon: s.longitude,
+ city: s.city_location ?? null,
+ }));
+ }
+ } catch {
+ /* fail-soft: no schools for this state this run */
+ }
+ stateSchoolCache.set(fips, out);
+ return out;
+}
+
+interface Place { name: string; type: string; distance_mi: number }
+let osmFails = 0;
+let osmDisabled = false; // circuit breaker: stop hammering Overpass once it's clearly busy
+async function placesNear(lat: number, lon: number): Promise<{ places: Place[]; ok: boolean }> {
+ if (osmDisabled) return { places: [], ok: false };
+ const q = `[out:json][timeout:25];(` +
+ `nwr(around:${PLACE_RADIUS_M},${lat},${lon})[amenity=hospital];` +
+ `nwr(around:${PLACE_RADIUS_M},${lat},${lon})[amenity=pharmacy];` +
+ `nwr(around:${PLACE_RADIUS_M},${lat},${lon})[amenity=library];` +
+ `nwr(around:${PLACE_RADIUS_M},${lat},${lon})[shop=supermarket];` +
+ `nwr(around:${PLACE_RADIUS_M},${lat},${lon})[leisure=park];` +
+ `);out center tags 60;`;
+ for (const ep of ["https://overpass-api.de/api/interpreter", "https://overpass.kumi.systems/api/interpreter"]) {
+ try {
+ const res = await fetch(ep, {
+ method: "POST",
+ headers: { "User-Agent": UA, "Content-Type": "application/x-www-form-urlencoded" },
+ body: "data=" + encodeURIComponent(q),
+ signal: AbortSignal.timeout(35_000),
+ });
+ if (!res.ok) continue;
+ const ct = res.headers.get("content-type") ?? "";
+ if (!ct.includes("json")) continue; // busy → XML error page
+ const data: any = await res.json();
+ const rows: Place[] = [];
+ for (const el of data.elements ?? []) {
+ const t = el.tags ?? {};
+ if (!t.name) continue;
+ const la = el.lat ?? el.center?.lat, lo = el.lon ?? el.center?.lon;
+ if (la == null) continue;
+ rows.push({ name: t.name, type: t.amenity || t.shop || t.leisure || "place", distance_mi: Math.round(milesBetween(lat, lon, la, lo) * 10) / 10 });
+ }
+ rows.sort((a, b) => a.distance_mi - b.distance_mi);
+ osmFails = 0;
+ return { places: rows.slice(0, 10), ok: true };
+ } catch {
+ /* try next mirror */
+ }
+ }
+ if (++osmFails >= 3) { osmDisabled = true; console.log(" (OSM/Overpass busy — disabling places for the rest of this run; schools unaffected)"); }
+ return { places: [], ok: false }; // OSM transiently unavailable — schools still land
+}
+
+async function main() {
+ const communities = await prisma.community.findMany({
+ where: { lat: { not: null }, lon: { not: null }, amenities: { equals: Prisma.DbNull } },
+ select: { id: true, name: true, city: true, state: true, lat: true, lon: true },
+ orderBy: { createdAt: "asc" },
+ take: LIMIT,
+ });
+ console.log(`enrich-nearby: ${communities.length} unenriched communities with coords (limit ${LIMIT}, NCES ${YEAR})`);
+ let enriched = 0, withSchools = 0, withPlaces = 0;
+ const nowIso = new Date().toISOString();
+
+ for (const c of communities) {
+ const lat = Number(c.lat), lon = Number(c.lon);
+ const fips = STATE_FIPS[(c.state ?? "").toUpperCase()];
+ let schools: any[] = [];
+ if (fips) {
+ const all = await schoolsForState(fips);
+ schools = all
+ .map((s) => ({ ...s, distance_mi: Math.round(milesBetween(lat, lon, s.lat, s.lon) * 10) / 10 }))
+ .sort((a, b) => a.distance_mi - b.distance_mi)
+ .slice(0, SCHOOL_N)
+ .map((s) => ({ name: s.name, level: s.level, distance_mi: s.distance_mi, city: s.city, ncessch: s.ncessch }));
+ }
+ const { places, ok: placesOk } = await placesNear(lat, lon);
+
+ const sources: any[] = [];
+ if (schools.length) sources.push({ field: "schools", source: "NCES Common Core of Data (US Dept of Education) via Urban Institute Education Data API", license: "public domain", retrievedAt: nowIso });
+ if (places.length) sources.push({ field: "places", source: "OpenStreetMap", license: "ODbL — © OpenStreetMap contributors", retrievedAt: nowIso });
+
+ const payload = {
+ schools,
+ places,
+ _note: "Objective public-records facts near this community. Schools are location facts only — no ratings (Fair Housing). Sourced from public records, NOT the builder site.",
+ _sources: sources,
+ _enrichedAt: nowIso,
+ _placesSourceStatus: placesOk ? "ok" : "unavailable-this-run",
+ };
+ await prisma.community.update({ where: { id: c.id }, data: { amenities: payload } });
+ enriched++;
+ if (schools.length) withSchools++;
+ if (places.length) withPlaces++;
+ console.log(` ✓ ${c.name}, ${c.city} ${c.state}: ${schools.length} schools, ${places.length} places${placesOk ? "" : " (OSM busy)"}`);
+ }
+
+ console.log(`\nDONE: enriched ${enriched} communities · ${withSchools} got schools · ${withPlaces} got places · $0 (public APIs)`);
+ await prisma.$disconnect();
+}
+
+main().catch(async (e) => { console.error(e); await prisma.$disconnect(); process.exit(1); });
← 7677fe3 collectors: enforce robots.txt in LiveFetcher (POLICY compli
·
back to Homesonspec
·
scripts: all-night autonomous build-loop (ingest x states + 48110bc →