README.md

How to create this file

1. First you'll need to download the latest parcel data (shapefiles and extended parcel records) and generate a SQLite database:

# download the shapefiles and parcel data, merge it all together, and dump it into a database
curl -L "https://www.stlouis-mo.gov/data/upload/data-files/prcl_shape.zip" -o prcl.zip && \
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.db prcl.shp -t_srs EPSG:4326

The result is a SQLite database that contains both the geometry data and a table called prcl that contains a single row for each parcel.

2. Next you'll need an older copy of the parcel data for comparison. The latest version comes from par.zip in the command above, but unfortunately the city doesn't host older copies. I happen to have a copy from 2018, which is available in this repo. You can convert its contents to SQLite like this:

$ ogr2ogr -f SQLite prcl_2018.db prcl_2018.dbf

3. Finally, insert the data from the 2018 prcl_2018 table into the original SQLite DB from above:

$ sqlite3 prcl_2018.db ".dump prcl_2018" | sqlite3 prcl.db

4. Now you can generate the GeoJSON like this:

$ ogr2ogr -f GeoJSON tgs.geojson prcl.db -sql "SELECT IIF(prcl.numunits > prcl_2018.numunits, '#007617', '#c80004') AS 'stroke', IIF(prcl.numunits > prcl_2018.numunits, '#00aa22', '#ff3d41') AS 'fill', prcl.geometry, prcl.handle, prcl.numunits AS 'numunits (2020)', prcl_2018.numunits AS 'numunits (2018)' FROM prcl LEFT JOIN prcl_2018 on prcl.handle = prcl_2018.handle WHERE prcl.numunits <> prcl_2018.numunits"

Some ideas for interesting search clauses:

1. Filter by zoning. For example, show only residential properties using WHERE prcl.zoning1 IN ('A', 'B', 'C', 'D', 'E').
2. Filter by vacancy. For example, show only buildings for which numunits went to zero using WHERE prcl.numunits = 0.
3. FIlter by neighborhood. For example, show only properties in Tower Grove South using WHERE prcl.NBRHD = '15'.

city.geojson

prcl_2018.dbf

tgs.geojson

vacant.geojson