← back to Homesonspec
feat: LA all-permit ingest + Census permit geocoder + searchable permit viewer
de505ba83b215ee20df24dcf833a320538580ece · 2026-07-28 08:24:17 -0700 · Steve
- ingest-permits.py: LA (LADBS, ALL types commercial+residential, ~539k) + per-city cap
- geocode-permits.sh: free Census batch geocoder for building_permits (94% hit on LA)
- permit-viewer.mjs: zero-dep searchable map viewer over building_permits (:9977)
Files touched
A scripts/geocode-permits.shM scripts/ingest-permits.pyA scripts/permit-viewer.mjs
Diff
commit de505ba83b215ee20df24dcf833a320538580ece
Author: Steve <steve@designerwallcoverings.com>
Date: Tue Jul 28 08:24:17 2026 -0700
feat: LA all-permit ingest + Census permit geocoder + searchable permit viewer
- ingest-permits.py: LA (LADBS, ALL types commercial+residential, ~539k) + per-city cap
- geocode-permits.sh: free Census batch geocoder for building_permits (94% hit on LA)
- permit-viewer.mjs: zero-dep searchable map viewer over building_permits (:9977)
---
scripts/geocode-permits.sh | 47 +++++++++++++++++++++++
scripts/ingest-permits.py | 22 +++++++++--
scripts/permit-viewer.mjs | 92 ++++++++++++++++++++++++++++++++++++++++++++++
3 files changed, 157 insertions(+), 4 deletions(-)
diff --git a/scripts/geocode-permits.sh b/scripts/geocode-permits.sh
new file mode 100644
index 0000000..ceb1034
--- /dev/null
+++ b/scripts/geocode-permits.sh
@@ -0,0 +1,47 @@
+#!/usr/bin/env bash
+# Geocode building_permits via the free US Census batch geocoder (no key, $0).
+# 10k-address chunks; marks geo_status ('match'/'nomatch') so unmatched rows don't
+# re-loop. LA permits are mostly on existing parcels → high hit rate expected.
+# Usage: scripts/geocode-permits.sh <source> <max_chunks> e.g. geocode-permits.sh la 6
+set -uo pipefail
+DBURL='postgresql://macstudio3@localhost/homesonspec?host=/tmp'
+SRC="${1:-la}"; MAXC="${2:-6}"
+IN=/tmp/pgeo_in.csv; OUT=/tmp/pgeo_out.csv
+c=0; total_match=0
+while [ "$c" -lt "$MAXC" ]; do
+ c=$((c+1))
+ psql "$DBURL" -tAc "copy (
+ select id, regexp_replace(address, '\s*(#|apt|ste|unit|bldg).*\$','','i'), city, state, zip
+ from building_permits
+ where source='$SRC' and geo_status is null and address is not null and address<>'' and zip is not null
+ limit 10000
+ ) to stdout with csv" > "$IN"
+ n=$(wc -l < "$IN" | tr -d ' ')
+ [ "$n" -eq 0 ] && { echo "chunk $c: nothing left"; break; }
+ echo "chunk $c: geocoding $n addresses…"
+ 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
+mups=[]; nups=[]
+for r in csv.reader(open(sys.argv[1])):
+ if len(r) < 3: continue
+ hid, ind = r[0], r[2]
+ if ind == "Match" and len(r) >= 6 and "," in r[5]:
+ try: lon, lat = (float(x) for x in r[5].split(",")[:2])
+ except: nups.append(hid); continue
+ mups.append("update building_permits set lat=%f, lon=%f, geo_status='match' where id='%s';" % (lat, lon, hid))
+ else:
+ nups.append(hid)
+sql = "begin;\n" + "\n".join(mups)
+if nups:
+ # mark the rest of this batch as attempted so they don't re-loop
+ ids = ",".join("'%s'" % x.replace("'", "''") for x in nups)
+ sql += "\nupdate building_permits set geo_status='nomatch' where id in (%s);" % ids
+sql += "\ncommit;\n"
+open("/tmp/pgeo_up.sql", "w").write(sql)
+print(" matched %d / %d" % (len(mups), len(mups) + len(nups)))
+PYEOF
+ psql "$DBURL" -f /tmp/pgeo_up.sql >/dev/null 2>&1
+done
+echo "geocoded source=$SRC: $(psql "$DBURL" -tAc "select count(*) from building_permits where source='$SRC' and lat is not null;") with coords now."
diff --git a/scripts/ingest-permits.py b/scripts/ingest-permits.py
index d29e195..5241b37 100644
--- a/scripts/ingest-permits.py
+++ b/scripts/ingest-permits.py
@@ -37,11 +37,23 @@ def sea_map(r):
filed_date=(r.get("applieddate") or "")[:10] or None, issued_date=(r.get("issueddate") or "")[:10] or None,
applicant=r.get("contractorcompanyname"))
+def la_map(r): # LADBS hbkd-qubn — addresses (no lat/lon; geocode later), all permit types
+ st = " ".join(x for x in [r.get("address_start"), r.get("street_name"), r.get("street_suffix")] if x)
+ pt = " / ".join(x for x in [r.get("permit_type"), r.get("permit_sub_type")] if x)
+ return dict(permit_number=r.get("pcis_permit"), address=st, city="Los Angeles", state="CA", zip=r.get("zip_code"),
+ lat=None, lon=None, permit_type=pt, work_class=r.get("permit_sub_type"), valuation=num(r.get("valuation")),
+ status="Issued" if r.get("issue_date") else None, filed_date=None,
+ issued_date=(r.get("issue_date") or "")[:10] or None,
+ applicant=r.get("contractors_business_name") or " ".join(x for x in [r.get("applicant_first_name"), r.get("applicant_last_name")] if x))
+
+# (source, url, where, mapper, order_field, max_rows) — LA = ALL permit types (commercial + residential)
CITIES = [
+ ("la", "https://data.lacity.org/resource/hbkd-qubn.json",
+ "issue_date > '2020-01-01'", la_map, "issue_date", 600000),
("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"),
+ "permit_type_definition like '%new construction%' AND filed_date > '2022-01-01'", sf_map, "filed_date", 8000),
("seattle", "https://data.seattle.gov/resource/76t5-zqzr.json",
- "permitclassmapped='Residential' AND permittypedesc like '%New%' AND issueddate > '2022-01-01'", sea_map, "issueddate"),
+ "permitclassmapped='Residential' AND permittypedesc like '%New%' AND issueddate > '2022-01-01'", sea_map, "issueddate", 8000),
]
def esc(v):
@@ -51,9 +63,11 @@ def esc(v):
def main():
total = 0
- for source, base, where, mapper, order in CITIES:
+ only = sys.argv[1] if len(sys.argv) > 1 else None
+ for source, base, where, mapper, order, max_rows in CITIES:
+ if only and source != only: continue
got = 0; offset = 0
- while offset < 8000:
+ while offset < max_rows:
q = {"$limit": 1000, "$offset": offset, "$where": where, "$order": order + " DESC"}
url = base + "?" + urllib.parse.urlencode(q)
try:
diff --git a/scripts/permit-viewer.mjs b/scripts/permit-viewer.mjs
new file mode 100644
index 0000000..be707f3
--- /dev/null
+++ b/scripts/permit-viewer.mjs
@@ -0,0 +1,92 @@
+// Searchable + mappable building-permit viewer (zero-dependency: Node http + psql).
+// Serves a search UI over the building_permits table with a Leaflet map of geocoded
+// permits. Local admin tool. Run: node scripts/permit-viewer.mjs (PORT env, default 9977)
+import { createServer } from "node:http";
+import { execFile } from "node:child_process";
+
+const DBURL = "postgresql://macstudio3@localhost/homesonspec?host=/tmp";
+const PORT = Number(process.env.PORT || 9977);
+const esc = (s) => String(s ?? "").replace(/'/g, "''").replace(/[;\\]/g, "").slice(0, 80);
+
+function q(sql) {
+ return new Promise((res) => {
+ execFile("psql", [DBURL, "-tAc", sql], { maxBuffer: 64 * 1024 * 1024 }, (e, out) => res(e ? "[]" : out.trim() || "[]"));
+ });
+}
+
+const PAGE = `<!doctype html><html><head><meta charset=utf8><title>HomesOnSpec — Building Permits</title>
+<meta name=viewport content="width=device-width,initial-scale=1">
+<link rel=stylesheet href="https://unpkg.com/leaflet@1.9.4/dist/leaflet.css">
+<style>
+:root{--bg:#0b141a;--pan:#111c24;--ink:#e8f0f0;--mut:#8fb0b0;--acc:#2dd4bf}
+*{box-sizing:border-box}body{margin:0;font:14px/1.4 -apple-system,Helvetica,Arial;background:var(--bg);color:var(--ink)}
+header{padding:14px 18px;border-bottom:1px solid #1e2f38}h1{margin:0;font-size:18px}.sub{color:var(--mut);font-size:12px;margin-top:2px}
+.wrap{display:flex;height:calc(100vh - 58px)}#list{width:46%;overflow:auto;border-right:1px solid #1e2f38}#map{flex:1}
+.ctrls{display:flex;gap:8px;padding:10px;position:sticky;top:0;background:var(--bg);border-bottom:1px solid #1e2f38;flex-wrap:wrap}
+input,select{background:var(--pan);border:1px solid #274049;color:var(--ink);border-radius:8px;padding:8px 10px;font-size:13px}
+input#q{flex:1;min-width:160px}.card{padding:10px 14px;border-bottom:1px solid #16242c;cursor:pointer}.card:hover{background:#0f1c23}
+.addr{font-weight:600}.meta{color:var(--mut);font-size:12px;margin-top:2px}.badge{display:inline-block;background:#183a36;color:var(--acc);border-radius:6px;padding:1px 7px;font-size:11px;margin-right:6px}
+.count{color:var(--mut);font-size:12px;padding:8px 14px}
+</style></head><body>
+<header><h1>HomesOnSpec — Building Permits</h1><div class=sub id=stat>loading…</div></header>
+<div class=wrap>
+ <div id=list>
+ <div class=ctrls>
+ <input id=q placeholder="search address, contractor, ZIP…">
+ <select id=type><option value="">All types</option></select>
+ <select id=minval><option value="0">Any value</option><option value=100000>$100k+</option><option value=500000>$500k+</option><option value=1000000>$1M+</option><option value=5000000>$5M+</option></select>
+ </div>
+ <div id=rows></div>
+ </div>
+ <div id=map></div>
+</div>
+<script src="https://unpkg.com/leaflet@1.9.4/dist/leaflet.js"></script>
+<script>
+const map=L.map('map').setView([34.02,-118.34],10);
+L.tileLayer('https://{s}.basemaps.cartocdn.com/dark_all/{z}/{x}/{y}.png',{maxZoom:19,attribution:'© OSM © CARTO'}).addTo(map);
+let layer=L.layerGroup().addTo(map);
+async function typeOpts(){const r=await fetch('/api/types').then(r=>r.json());const s=document.getElementById('type');r.forEach(t=>{const o=document.createElement('option');o.value=t;o.textContent=t;s.appendChild(o)})}
+async function load(){
+ const q=document.getElementById('q').value,ty=document.getElementById('type').value,mv=document.getElementById('minval').value;
+ const d=await fetch('/api/permits?q='+encodeURIComponent(q)+'&type='+encodeURIComponent(ty)+'&minval='+mv).then(r=>r.json());
+ document.getElementById('stat').textContent=d.total.toLocaleString()+' permits match · '+d.mapped.toLocaleString()+' mapped';
+ const rows=document.getElementById('rows');rows.innerHTML='';layer.clearLayers();const pts=[];
+ d.rows.forEach(p=>{
+ const c=document.createElement('div');c.className='card';
+ c.innerHTML='<div class=addr>'+(p.address||'—')+', '+(p.city||'')+' '+(p.zip||'')+'</div><div class=meta><span class=badge>'+(p.permit_type||'')+'</span>'+(p.valuation?'$'+Number(p.valuation).toLocaleString():'')+' · '+(p.status||'')+' · '+(p.issued_date||'')+' · '+(p.applicant||'')+'</div>';
+ if(p.lat){c.onclick=()=>map.setView([p.lat,p.lon],16);pts.push([p.lat,p.lon]);
+ L.circleMarker([p.lat,p.lon],{radius:4,color:'#fb923c',weight:1,fillOpacity:.7}).bindPopup('<b>'+(p.address||'')+'</b><br>'+(p.permit_type||'')+'<br>'+(p.valuation?'$'+Number(p.valuation).toLocaleString():'')).addTo(layer);}
+ rows.appendChild(c);
+ });
+ if(pts.length)map.fitBounds(pts,{maxZoom:13,padding:[30,30]});
+}
+document.getElementById('q').addEventListener('input',()=>{clearTimeout(window._t);window._t=setTimeout(load,350)});
+document.getElementById('type').onchange=load;document.getElementById('minval').onchange=load;
+typeOpts().then(load);
+</script></body></html>`;
+
+const server = createServer(async (req, res) => {
+ const u = new URL(req.url, "http://x");
+ if (u.pathname === "/") { res.setHeader("content-type", "text/html"); return res.end(PAGE); }
+ if (u.pathname === "/api/types") {
+ const j = await q(`select coalesce(json_agg(t),'[]') from (select distinct permit_type from building_permits where permit_type is not null order by 1 limit 60) x(t)`);
+ res.setHeader("content-type", "application/json"); return res.end(j);
+ }
+ if (u.pathname === "/api/permits") {
+ const term = esc(u.searchParams.get("q") || "");
+ const type = esc(u.searchParams.get("type") || "");
+ const minval = Number(u.searchParams.get("minval") || 0) || 0;
+ const w = ["1=1"];
+ if (term) w.push(`(address ilike '%${term}%' or applicant ilike '%${term}%' or zip='${term}' or permit_number ilike '%${term}%')`);
+ if (type) w.push(`permit_type='${type}'`);
+ if (minval) w.push(`valuation >= ${minval}`);
+ const where = w.join(" and ");
+ const total = (await q(`select count(*) from building_permits where ${where}`)).trim() || "0";
+ const mapped = (await q(`select count(*) from building_permits where ${where} and lat is not null`)).trim() || "0";
+ const rows = await q(`select coalesce(json_agg(t),'[]') from (select address,city,zip,permit_type,valuation,status,issued_date::text,applicant,lat,lon from building_permits where ${where} order by (lat is not null) desc, valuation desc nulls last limit 400) t`);
+ res.setHeader("content-type", "application/json");
+ return res.end(JSON.stringify({ total: Number(total), mapped: Number(mapped), rows: JSON.parse(rows) }));
+ }
+ res.statusCode = 404; res.end("not found");
+});
+server.listen(PORT, () => console.log(`permit-viewer on http://127.0.0.1:${PORT}`));
← b0103f2 admin: live ingestion dashboard (/ingestion) — auto-refreshi
·
back to Homesonspec
·
deploy-kamatera: admin block uses LE cert (admin now in cert 5064112 →