← back to Homesonspec
feat: construction-pipeline layer — building-permit ingestion + Census geocoder
9075f7b03ae975d0ddaebfc8002a707f22ad7557 · 2026-07-28 07:55:03 -0700 · Steve
New leading-indicator layer: ingest new-residential building permits from free,
no-key city open-data APIs (Socrata) into building_permits — homes visible at the
permit stage, months before they list. SF + Seattle live (5.3k permits, geocoded,
current). Plus scripts/geocode-census.sh (free Census batch geocoder to backfill
home coords). $0, West Coast first. Next metros: LA/Sacramento/San Jose/Portland/Phoenix/Vegas.
Files touched
A scripts/geocode-census.shA scripts/ingest-permits.py
Diff
commit 9075f7b03ae975d0ddaebfc8002a707f22ad7557
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Jul 28 07:55:03 2026 -0700
feat: construction-pipeline layer — building-permit ingestion + Census geocoder
New leading-indicator layer: ingest new-residential building permits from free,
no-key city open-data APIs (Socrata) into building_permits — homes visible at the
permit stage, months before they list. SF + Seattle live (5.3k permits, geocoded,
current). Plus scripts/geocode-census.sh (free Census batch geocoder to backfill
home coords). $0, West Coast first. Next metros: LA/Sacramento/San Jose/Portland/Phoenix/Vegas.
---
scripts/geocode-census.sh | 45 +++++++++++++++++++++++++
scripts/ingest-permits.py | 86 +++++++++++++++++++++++++++++++++++++++++++++++
2 files changed, 131 insertions(+)
diff --git a/scripts/geocode-census.sh b/scripts/geocode-census.sh
new file mode 100644
index 0000000..f487f67
--- /dev/null
+++ b/scripts/geocode-census.sh
@@ -0,0 +1,45 @@
+#!/usr/bin/env bash
+# Backfill lat/lon on published homes that lack coordinates, via the free US Census
+# batch geocoder (no key, $0). Homes from crawl adapters (KB/DR-Horton/Tri-Pointe/
+# David-Weekley) carry street addresses but no geo; this puts them on the map AND
+# makes their communities enrichable. Also backfills each affected Community's
+# lat/lon from the median of its geocoded homes. Reversible (only sets NULL coords).
+# Usage: scripts/geocode-census.sh ["'CA','OR','WA'"] (default = West Coast)
+set -uo pipefail
+DBURL='postgresql://macstudio3@localhost/homesonspec?host=/tmp'
+STATES="${1:-'CA','OR','WA','NV','AZ','ID'}"
+IN=/tmp/geo_in.csv; OUT=/tmp/geo_out.csv
+psql "$DBURL" -tAc "copy (
+ select id, regexp_replace(street, '\s*(#|apt|ste|unit|lot|bldg).*\$','','i'), city, state, zip
+ from \"InventoryHome\"
+ where status='PUBLISHED' and lat is null and street is not null and zip is not null
+ and state in ($STATES)
+ limit 9500
+) to stdout with csv" > "$IN"
+N=$(wc -l < "$IN" | tr -d ' ')
+echo "geocoding $N addresses via US Census batch (free, \$0)…"
+[ "$N" -eq 0 ] && { echo "nothing to geocode"; exit 0; }
+curl -s --max-time 900 --form addressFile=@"$IN" --form benchmark=Public_AR_Current \
+ "https://geocoding.geo.census.gov/geocoder/locations/addressbatch" -o "$OUT"
+python3 - "$OUT" <<'PYEOF'
+import csv, sys
+matched = 0; ups = []
+for r in csv.reader(open(sys.argv[1])):
+ if len(r) < 6: continue
+ hid, ind, coord = r[0], r[2], r[5]
+ if ind == "Match" and "," in coord:
+ try: lon, lat = (float(x) for x in coord.split(",")[:2])
+ except: continue
+ ups.append("update \"InventoryHome\" set lat=%f, lon=%f where id='%s';" % (lat, lon, hid))
+ matched += 1
+open("/tmp/geo_up.sql", "w").write("begin;\n" + "\n".join(ups) + "\ncommit;\n")
+print("matched %d homes" % matched)
+PYEOF
+psql "$DBURL" -f /tmp/geo_up.sql >/dev/null 2>&1
+# backfill community lat/lon from the median of its now-geocoded homes (for enrichment)
+psql "$DBURL" -c "update \"Community\" c set lat=s.mlat, lon=s.mlon
+ from (select \"communityId\" cid, percentile_cont(0.5) within group (order by lat) mlat,
+ percentile_cont(0.5) within group (order by lon) mlon
+ from \"InventoryHome\" where lat is not null group by \"communityId\") s
+ where c.id=s.cid and c.lat is null;" >/dev/null 2>&1
+echo "applied + community coords backfilled."
diff --git a/scripts/ingest-permits.py b/scripts/ingest-permits.py
new file mode 100644
index 0000000..d29e195
--- /dev/null
+++ b/scripts/ingest-permits.py
@@ -0,0 +1,86 @@
+#!/usr/bin/env python3
+"""
+Construction-pipeline layer: ingest NEW-RESIDENTIAL building permits from free,
+no-key city open-data APIs (Socrata SODA) into the building_permits table.
+Leading indicator — these homes are being built months before they list.
+$0, no key. West Coast metros first. Reversible (INSERT ... ON CONFLICT DO NOTHING).
+"""
+import json, urllib.request, urllib.parse, subprocess, sys
+
+DBURL = "postgresql://macstudio3@localhost/homesonspec?host=/tmp"
+UA = "HomesOnSpecBot/0.1 (+https://homesonspec.com/bot; contact: data@homesonspec.com)"
+
+def num(v):
+ try:
+ f = float(str(v).replace("$", "").replace(",", "")); return f if f == f else None
+ except: return None
+
+# each city: (source, base_url, where, mapper(row)->dict)
+def sf_map(r):
+ loc = r.get("location") or {}
+ lat = num(r.get("latitude")) or (num(loc.get("latitude")) if isinstance(loc, dict) else None)
+ lon = num(r.get("longitude")) or (num(loc.get("longitude")) if isinstance(loc, dict) else None)
+ if lat is None and isinstance(loc, dict) and loc.get("coordinates"):
+ lon, lat = loc["coordinates"][0], loc["coordinates"][1]
+ st = " ".join(x for x in [r.get("street_number"), r.get("street_name"), r.get("street_suffix")] if x)
+ return dict(permit_number=r.get("permit_number") or r.get("permit_creation_date","")+st, address=st, city="San Francisco",
+ state="CA", zip=r.get("zipcode"), lat=lat, lon=lon, permit_type=r.get("permit_type_definition"),
+ work_class="New Construction", valuation=num(r.get("revised_cost") or r.get("estimated_cost")),
+ status=r.get("status"), filed_date=(r.get("filed_date") or "")[:10] or None,
+ issued_date=(r.get("issued_date") or "")[:10] or None, applicant=None)
+
+def sea_map(r):
+ return dict(permit_number=r.get("permitnum"), address=r.get("originaladdress1"), city=r.get("originalcity") or "Seattle",
+ state=r.get("originalstate") or "WA", zip=r.get("originalzip"), lat=num(r.get("latitude")), lon=num(r.get("longitude")),
+ permit_type=r.get("permittypedesc"), work_class=r.get("housingunitsadded") and "New Residential" or "New",
+ valuation=num(r.get("estprojectcost")), status=r.get("statuscurrent"),
+ filed_date=(r.get("applieddate") or "")[:10] or None, issued_date=(r.get("issueddate") or "")[:10] or None,
+ applicant=r.get("contractorcompanyname"))
+
+CITIES = [
+ ("sf", "https://data.sfgov.org/resource/i98e-djp9.json",
+ "permit_type_definition like '%new construction%' AND filed_date > '2022-01-01'", sf_map, "filed_date"),
+ ("seattle", "https://data.seattle.gov/resource/76t5-zqzr.json",
+ "permitclassmapped='Residential' AND permittypedesc like '%New%' AND issueddate > '2022-01-01'", sea_map, "issueddate"),
+]
+
+def esc(v):
+ if v is None: return "NULL"
+ if isinstance(v, (int, float)): return str(v)
+ return "'" + str(v).replace("'", "''") + "'"
+
+def main():
+ total = 0
+ for source, base, where, mapper, order in CITIES:
+ got = 0; offset = 0
+ while offset < 8000:
+ q = {"$limit": 1000, "$offset": offset, "$where": where, "$order": order + " DESC"}
+ url = base + "?" + urllib.parse.urlencode(q)
+ try:
+ req = urllib.request.Request(url, headers={"User-Agent": UA})
+ rows = json.load(urllib.request.urlopen(req, timeout=60))
+ except Exception as e:
+ print(f" {source} offset {offset} error: {e}"); break
+ if not rows: break
+ vals = []
+ for r in rows:
+ m = mapper(r)
+ if not m.get("permit_number"): continue
+ pid = f"{source}-{m['permit_number']}"
+ cols = ["id","source","permit_number","address","city","state","zip","lat","lon","permit_type","work_class","valuation","status","filed_date","issued_date","applicant"]
+ row = [pid, source, m["permit_number"], m.get("address"), m.get("city"), m.get("state"), m.get("zip"),
+ m.get("lat"), m.get("lon"), m.get("permit_type"), m.get("work_class"), m.get("valuation"),
+ m.get("status"), m.get("filed_date"), m.get("issued_date"), m.get("applicant")]
+ vals.append("(" + ",".join(esc(x) for x in row) + ")")
+ if vals:
+ sql = ('insert into building_permits (id,source,permit_number,address,city,state,zip,lat,lon,permit_type,work_class,valuation,status,filed_date,issued_date,applicant) values '
+ + ",".join(vals) + " on conflict (id) do nothing;")
+ subprocess.run(["psql", DBURL, "-q", "-c", sql], capture_output=True)
+ got += len(rows); offset += 1000
+ if len(rows) < 1000: break
+ print(f" {source}: fetched {got} new-residential permits")
+ total += got
+ print(f"total permit rows fetched: {total}")
+
+if __name__ == "__main__":
+ main()
← 89a47a0 build-loop: honor injected DATABASE_URL (portable to Kamater
·
back to Homesonspec
·
build-loop: source repo .env for DATABASE_URL (fixes PrismaC db9700f →