A day in the life - PostGIS helps get crap off desk
Crap landed on my desk. It bore the scent of people that grossly outrank me and came with the descriptor ASAP, a Latin term which means “poor planning”.
Tax assessment values for annexation areas summarized by jurisdiction based on the assessment year of the annexation!
Perhaps this sort of thing happens to you. When it does, your prime directive should be to get it off your desk as fast as you can. Enter PostGIS.
PostGIS is generally thought of in terms of servers, but I’ve found PostGIS to be an invaluable desktop GIS tool. Fire up one of the many great PostGIS Docker images (like this one) and you’re ready to do some serious crap-on-desk sanitation work.
The first thing I did was head over to our Open Mapping data portal and grabbed everything I needed - annexation history and parcels with tax assessment values for every year from 2009-2018. Yes, we have all that on our data portal. It’s maintained by a wonderful human named Scott and if any of you try to poach him I’ll stick my spork in your eye.
Next, GDAL, the Great Data Shoveler. Into PostGIS you go, bastards.
I tossed the annexation history in without smacking it around overly much. The parcels were a different story. First I used a bit of
SQL to get the centroid of each parcel rather than use the polygon. Converting to points makes for much faster processing, and it also eliminates parcel/annexation edge matching problems (we don’t believe in coincident lines in Mecklenburg County). After a year uploads, I run a
SQL statement (
-doo) on that table that deletes duplicates in our data caused by multiple ownership records. ~4 million spatial features across 11 layers, ~3 minutes, not counting time thumb wrestling with
Now we’re firmly in
SQL land. Let’s stick all of those parcel centroids in to a view.
CREATE or REPLACE VIEW parcels AS
At last, the analysis. Here I’m grouping by the municipality and tax year, summarizing the assessed values for each, where the parcel year matches the annexation year and the parcel points intersect the annexation polygon.
237ms and booyah!
Desk de-crapped. Just to make sure, I always recommend a thorough cleaning afterward.