1

I want to ensure that the result of a ST_Intersection(polygon1, polygon2) is also a polygon. I initially started by filtering with a ST_Overlaps(polygon1, polygon2) but that seems to only ensure that both features intersect while neither feature contains the other.

Besides filtering by checking the result of the intersection operation e.g. ST_Dimension(ST_Intersection(polygon1, polygon2)) > 1 is there a way to filter polygons by ensuring that they share some internal space that is not a boundary?

ST_Crosses(polygon1, polygon2) actually does perform the filtering that I want.

Improve this question
  • Could you add in a WHERE clause where you check the resulting area is greater than 0? – djq Dec 23 '14 at 1:31
  • Overlaps should be right for you. From en.wikipedia.org/wiki/DE-9IM: a overlaps b, they have some but not all points in common, they have the same dimension, and the intersection of the interiors of the two geometries has the same dimension as the geometries themselves. You may want to add Equals if the two polygons can be identical. – user30184 Dec 23 '14 at 10:37
  • So ST_Crosses actually does the trick I was running up against another issue in my query which was causing this problem. – raphael Dec 24 '14 at 4:36
1

The simplest way is to use ST_GeometryType. In your case, to check for polygons only, you can do,

SELECT ST_Intersection(polygon1, polygon2) FROM ..... 
WHERE ST_GeometryType(ST_Intersection(polygon1, polygon2)) = 'ST_Polygon';

For more complicated scenarios, you could also look at ST_Relate and the DE-9IM model, but I think that is probably overkill for what you need.

EDIT: To avoid intersecting polygon A with polygon B and polygon B with polygon A in a large table, and still ensuring that you only end up with a polygon as the intersection, you can do:

SELECT ST_Intersection(a.geom, b.geom) 
FROM some_table a, some_table b
WHERE ST_GeometryType(ST_Intersection(a.geom, b.geom)) = 'ST_Polygon'
AND a.id > b.id;

assuming you have a primary/unique key called id.

Improve this answer
  • I have checked out ST_Relate but I have yet to truly understand the matrix. I guess my concern is whether the query plan recognizes the intersection operation happening twice and only performs it once. I'm performing this on about 300K polygons. – raphael Dec 23 '14 at 2:54
  • That is a slightly different question. The query planner will not recognize that, but you can avoid comparing polygons twice in a spatial self join, answer updated. Agreed, ST_Relate matrices are hard to understand. – John Powell Dec 23 '14 at 9:31

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.