on Code

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:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

select count(*) as contiguity from
(
     SELECT ST_NumGeometries(the_geom) as mynum, district from
     (
          select
          st_buffer(ST_Collect(vp.the_geom), 2) as the_geom,
          vp.district
          from voting_precincts vp
          group by vp.district
      ) as foo
      group by district, mynum
) as foo
where mynum > 1

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.