`scripts/geocode-census.sh`

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