`scripts/geocode-permits.sh`

48 lines
#!/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."