A day in the life - PostGIS helps get crap off desk

Imgur

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!

Oh yeah:

ASAP!

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.

Imgur

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. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
#! /bin/bash

echo "Uploading annexations..." 
ogr2ogr \
-f Postgresql \
PG:"REDACTED" \
-lco GEOMETRY_NAME=geometry \
-lco DIM=2 \
-lco SPATIAL_INDEX=YES \
-nlt MULTIPOLYGON \
--config PG_USE_COPY YES \
-nln annexations \
-overwrite \
-sql "SELECT geometry, munic_name, effdate, year, area_name, orddate from annex" \
-dialect sqlite \
annex.shp

for filename in taxfull/*.shp; do
  s=${filename##*/}
  table=${s%.*}

  echo "Processing $table..." 
  ogr2ogr \
  -f Postgresql \
  PG:"REDACTED" \
  -sql "SELECT ST_Centroid(geometry), taxpid, cast(totalvalue as int) as totalvalue FROM $table" \
  -dialect sqlite \
  -lco GEOMETRY_NAME=geometry \
  -lco DIM=2 \
  -lco SPATIAL_INDEX=YES \
  -overwrite \
  --config PG_USE_COPY YES \
  -nln $table \
  -doo CLOSING_STATEMENTS="DELETE FROM $table a USING $table b WHERE a.ogc_fid < b.ogc_fid AND a.totalvalue = b.totalvalue AND a.taxpid = b.taxpid;" \
  $filename
done

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 ogr2ogr.

Now we’re firmly in SQL land. Let’s stick all of those parcel centroids in to a view.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE or REPLACE VIEW parcels AS
  SELECT 2018 as year, totalvalue, geometry FROM taxdata_2018
UNION ALL
  SELECT 2017 as year, totalvalue, geometry FROM taxdata2017
UNION ALL
  SELECT 2016 as year, totalvalue, geometry FROM taxdata_2016
UNION ALL
  SELECT 2015 as year, totalvalue, geometry FROM taxdata2015
UNION ALL
  SELECT 2014 as year, totalvalue, geometry FROM taxdata2014
UNION ALL
  SELECT 2013 as year, totalvalue, geometry FROM taxdata_2013
UNION ALL
  SELECT 2012 as year, totalvalue, geometry FROM taxdata_2012
UNION ALL
  SELECT 2011 as year, totalvalue, geometry FROM taxdata_2011
UNION ALL
  SELECT 2010 as year, totalvalue, geometry FROM taxdata_2010
UNION ALL
  SELECT 2009 as year, totalvalue, geometry FROM taxdata_2009;

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT 
  a.munic_name as municipality, 
  a.year_tax as year,
  sum(totalvalue)::money as value 
FROM 
  parcels p, annex a
WHERE 
  ST_Intersects(p.geometry, a.geometry)
  AND
  a.year_tax = p.year
  AND 
  totalvalue > 0
GROUP BY 
  a.munic_name, a.year_tax
ORDER BY 
  munic_name, a.year_tax;

237ms and booyah!

municipality year value
CHARLOTTE 2011 $255,514,808
CHARLOTTE 2013 $114,636,000
CHARLOTTE 2014 $1,753,800
CHARLOTTE 2015 $17,025,200
CHARLOTTE 2016 $785,600
CHARLOTTE 2017 $8,741,200
CHARLOTTE 2018 $7,433,100
CORNELIUS 2015 $1,345,385
CORNELIUS 2016 $1,486,800
etc…

Desk de-crapped. Just to make sure, I always recommend a thorough cleaning afterward.

Imgur