Skip to content

Instantly share code, notes, and snippets.

@wayneburkett wayneburkett/everylotpvd
Last active Nov 8, 2019

Embed
What would you like to do?
Create an everylotbot for Providence, RI
# download the shapefile and dump it into a database
mkdir tmp && \
curl -L "https://data.providenceri.gov/api/geospatial/78bu-i8at?method=export&format=Shapefile" -o tmp/prcl.zip && \
cd tmp && \
unzip prcl.zip && \
ogr2ogr -f SQLite prcl_raw.db geo_export_*.shp -t_srs EPSG:4326
# create a table with everything you'd need to run an everylot bot
# http://fakeisthenewreal.org/everylot/
# change the table name to whatever the downloads give you (it's randomized)
ogr2ogr -f SQLite pvd.db prcl_raw.db -nln lots -dialect sqlite \
-sql "SELECT substr('000'||plat, -3, 3) || '-' || substr('0000'||lot, -4, 4) AS id, \
ROUND(X(ST_Centroid(GeomFromWKB(Geometry))), 5) lon, \
ROUND(Y(ST_Centroid(GeomFromWKB(Geometry))), 5) lat, \
parc_add AS address, \
0 zip, \
0 tweeted \
FROM geo_export_b8115a8c_eebd_4cb2_b8de_628c9161b835 \
WHERE id IS NOT NULL \
GROUP BY plat, lot \
ORDER BY plat, lot ASC"
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.