4

Using PostGIS, what is an efficient way to check whether a table with a geometry column has geometries that overlap each other?

This is my table:

  Column  |            Type             | Modifiers
---------+-----------------------------+-----------
 sts     | character(1)                |
 geom    | geometry(MultiPolygon,5070) |

I guess I could see whether:

SUM(ST_Area(geom))

is the same as:

SUM(ST_Area(ST_Union(geom)))

But that doesn't feel like it's likely to be the most efficient way to do it, especially since my table has about 40k features.

Any ideas?

One comment on a related question suggests one could "search for overlaps by their bounding boxes" - is that a possibility?

|improve this question
  • "Shapefile" is not a generic term for a spatial dataset. It is not possible to overlay a shapefile with a PostgreSQL table with PostGIS geometry using SQL. Once the shapefile is loaded in the database, it is no longer a shapefile. Please Edit the question to contain your entire SQL statement, since it's difficult to understand your meaning with only fragments. – Vince Jun 11 '17 at 22:04
  • 1
    I repeat, please edit the question to contain your full query. Hint: You need to use a self-join. – Vince Jun 12 '17 at 1:12
  • 1
    Why the close votes? I am trying to ask what seems to me to be a sensible question. Apologies if I've got anything wrong. – Richard Jun 12 '17 at 9:49
  • 1
    @Vince I'm sorry that my table does not reflect best practice, or that I didn't know I needed a self-join, I'm not a PostGIS expert, which is why I'm using these forums! I did post the approach that I was thinking of using, I'm aware it's not efficient hence the question. I've googled "spatial self join" but it's honestly not helping me a lot. I'm sorry, I'm still learning. – Richard Jun 12 '17 at 10:56
  • 3
    @Vince since it looks like we're being pedantic today, I'll point out that you can overlay a shapefile with a Postgres table using SQL. You just need a foreign data wrapper. – dbaston Jun 12 '17 at 12:37
10

In the situation where you only need to know whether a table contains any overlapping polygons, and you're not concerned with the size or abundance of overlaps, I recommend a query of the following form:

SELECT *
FROM my_table a
INNER JOIN my_table b ON 
   (a.geom && b.geom AND ST_Relate(a.geom, b.geom, '2********'))
WHERE a.ctid != b.ctid
LIMIT 1

Some pieces of this query to point out:

  • We're joining our input table to itself as a way of finding pairs of records in the table with a particular relationship.
  • We're including the && operator as part of our join condition. This operator returns true if the bounding boxes of its inputs intersect, which is a fast test that takes advantage of a spatial index.
  • The obscure part: we're also saying the two polygons involved in the join must have a relationship that satisfies the 2******** DE-9IM pattern. This is a way of checking whether the intersection of two polygons forms a polygon. (Confusingly, this is not necessarily what ST_Overlaps tells you, hence the use of the DE-9IM).
  • We prevent a row from being matched with itself by including a.ctid != b.ctid. The ctid is a unique, non-permanent row identifier that Postgres assigns to every row of every table. If your input data has a primary key, you could use a.id != b.id instead.
  • We add a LIMIT 1 at the end of the query so that Postgres can stop as soon as it's found a single overlap.

For this query to perform well, you'll need to have a spatial index on the geometry column. You can add one with CREATE INDEX ON my_table USING gist(geom) if you don't have one already.

|improve this answer
  • 2
    As the answer is intended for somebody with limited knowledge of the construct it may be of benefit to show the "simplified" solution using the "standard" ST_Overlaps, ST_Intersects etc. DE-9IM is a tricky construct to understand when one knows it - never mind when one's starting out in the matter. – Phil G Jun 12 '17 at 13:47
  • 1
    @PhilG I see the point, but in my experience it is better to memorize/write down the above DE-9IM than to try to remember its equivalent (ST_Overlaps(a.geom, b.geom) OR ST_Contains(a.geom, b.geom)) and then get the order of arguments correct for ST_Contains. – dbaston Jun 12 '17 at 14:27
  • Thanks! Hadn't come across && before. Clever to use LIMIT 1 so the query can stop as soon as it's found a single example. – Richard Jun 13 '17 at 6:00
  • Just curious: does the && expression get evaluated "non-greedily" before the ST_Relate, i.e. ST_Relate is not evaluated for any polygons without overlapping bounding boxes? I guess this makes the query a lot more efficient if so. – Richard Jun 13 '17 at 22:47
  • 1
    Correct, ST_Relate would not be evaluated if the bounding boxes did not overlap. – dbaston Jun 14 '17 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.