Get a SQLite database of St. Louis parcel data

stlparcels

# 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'"