every-lot-cville

prepare.sh

#!/bin/bash

set -euo pipefail

rm -f parcels.db

curl -O https://widget.charlottesville.org/gis/zip_download/parcel_area.zip
unzip parcel_area.zip

layer=$(ogrinfo parcel_area_*.shp | grep '1: ' | awk '{print $2}')
source_table=$(ogrinfo parcel_area_*.shp "${layer}" -so \
  | grep 'Layer name: ' \
  | sed 's/Layer name: //')
ogr2ogr -f SQLite parcels-projected.db parcel_area_*.shp -t_srs EPSG:4326 -select PIN,GPIN

curl -o real_estate.csv https://opendata.arcgis.com/datasets/bc72d0590bf940ff952ab113f10a36a8_8.csv
sqlite3 parcels-projected.db <<EOF
CREATE TABLE real_estate (
  "RecordID_Int" INTEGER,
  "ParcelNumber" INTEGER,
  "StreetNumber" TEXT,
  "StreetName" TEXT,
  "Unit" TEXT,
  "StateCode" TEXT,
  "TaxType" TEXT,
  "Zone" TEXT,
  "TaxDist" TEXT,
  "Legal" TEXT,
  "Acreage" REAL,
  "GPIN" INEGERT
);
.mode csv
.import real_estate.csv real_estate
EOF

ogr2ogr -F SQLite -dialect sqlite parcels.db parcels-projected.db -nln lots \
  -sql "$(cat <<EOF
SELECT
  PIN AS id,
  ROUND(X(ST_Centroid(GeomFromWKB(Geometry))), 5) AS lon,
  ROUND(Y(ST_Centroid(GeomFromWKB(Geometry))), 5) AS lat,
  details.StreetNumber || ' ' || details.StreetName AS address,
  0 AS tweeted
FROM ${source_table} parcels
JOIN (
  SELECT
    *,
    ROW_NUMBER() OVER(PARTITION BY GPIN) AS idx
  FROM real_estate
) details ON parcels.GPIN = details.GPIN
WHERE details.idx = 1
EOF
)"