Checking for Contiguous Districts with PostGIS
I thought I’d toss this out for two reasons: (1) it took my tiny brain a whole curse-filled afternoon to figure this out, and (2) on the off chance somebody with a larger brain swings by this Internet-backwater blog and can point out a better way to do it.
Here’s the problem: I have a polygon layer, where each polygon is assigned a district number. Part of the rules for the districts state that each district must be contiguous - all polygons assigned to a district must touch each other.
Here’s my godawful SQL:
Starting from the middle and working our way out:
- We group by our district number so we get a record for each district (in this case, 6 records). We aggregate the geometry with ST_Collect, which returns a geometry collection for each district. Then we ST_Buffer each geometry collection by a minimal amount (units here are feet). This creates a polygon around each contiguous body in the geometry collection of each record.
- Then we get the number of geometries in each record via ST_NumGeometries. If the polygons in the district weren’t contiguous, you’ll get a value greater than 1.
- OK, I know, the last thing is just lazy. I get a count of the number of records that are greater than 1. If my return is 0, all districts are contiguous. If it isn’t 0, I have a problem.
I found going with ST_Collect and ST_Buffer to be considerably faster than going with ST_Union (on my craptop, ~890ms vs ~2.9s), and since I don’t need dissolved geometry beyond the test it made sense. I’m hoping it’ll run faster on the production server. Postgres/PostGIS has spoiled me - even sub-second response times seem slow when they start to creep up on the 1 second mark.