Skip to content

Instantly share code, notes, and snippets.

@wayneburkett wayneburkett/stlparcels
Last active Jan 1, 2019

Embed
What would you like to do?
Get a SQLite database of St. Louis parcel data
# download the shapefiles and parcel data, merge it all together, and dump it into a database
mkdir tmp && \
curl -L https://www.stlouis-mo.gov/data/upload/data-files/prcl_shape.zip -o tmp/prcl.zip && \
cd tmp && \
unzip prcl.zip && \
curl -LO https://www.stlouis-mo.gov/data/upload/data-files/par.zip && \
unzip par.zip && \
mv par.dbf prcl.dbf && \
ogr2ogr -f SQLite prcl_raw.db prcl.shp -t_srs EPSG:4326
# create a table with everything you'd need to run an stl everylot bot
# http://fakeisthenewreal.org/everylot/
ogr2ogr -f SQLite prcl.db prcl_raw.db -nln lots -dialect sqlite \
-sql "SELECT handle AS id, \
ROUND(X(ST_Centroid(GeomFromWKB(Geometry))), 5) lon, \
ROUND(Y(ST_Centroid(GeomFromWKB(Geometry))), 5) lat, \
siteaddr AS address, \
zip, \
nbrhd, \
bdg1strycd AS floors, \
0 tweeted \
FROM prcl \
ORDER BY handle ASC"
# create a .geojson file containing vacant parcels
ogr2ogr -f GeoJSON vacants.geojson prcl.shp -t_srs EPSG:4326 -select handle -where "vacantland='Y'"
# create a .geojson file containing parcels with out-of-state owners (i.e. neither MO or IL)
ogr2ogr -f GeoJSON absentee.geojson prcl.shp -t_srs EPSG:4326 -select handle,ownerstate -where "ownerstate!='MO' AND ownerstate!='IL'"
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.