3

I'm looking for a query that returns overlapping areas of two tables with polygon geometries. Using row_number() over() the entries of the gid column are not unique in the case of dumped multipolygons. So I get some troubles when loading the layer into QGIS.

CREATE TABLE poly_intersection AS
SELECT
row_number() over() AS gid,
(ST_Dump(ST_Intersection(poly1.geom, poly2.geom))).geom::geometry(polygon, SRID) AS geom
 FROM poly1, poly2
 WHERE ST_Overlaps(poly1.geom, poly2.geom) = 't' OR ST_Contains(poly1.geom, poly2.geom) = 't' OR ST_Contains(poly2.geom, poly1.geom) = 't';

Meanwhile I've modified my query. But I wonder if there is a simpler solution.

CREATE TABLE poly_intersection AS
SELECT
row_number() over() AS gid,
g.geom::geometry(polygon, SRID) AS geom
FROM
(SELECT
(ST_Dump(ST_Intersection(poly1.geom, poly2.geom))).geom
FROM
poly1, poly2
WHERE
ST_Overlaps(poly1.geom, poly2.geom) = 't' OR ST_Contains(poly1.geom, poly2.geom) = 't' OR ST_Contains(poly1.geom, poly2.geom) = 't')
AS g;
7
  • Also, you can use (ST_Dump(....).path to get unique IDs, though there is nothing wrong with using row_number() over(). While there is a difference between ST_Covers and ST_Contains, in this case I'm not sure it makes any difference. And, you only want polygons that are fully contained, as you say overlapping, but seem to be including ST_Intersects, which would return all polygons from ST_Contains in both directions. Jun 1, 2015 at 13:49
  • I need all types of polygon intersections. AS far as I know ST_Intersects is TRUE for touching polygons too. ST_Intersection is returning linestrings or points in this case.
    – Lunar Sea
    Jun 1, 2015 at 14:13
  • 1
    True. You can use WHERE ST_Intersects(a.geom, b.geom) AND NOT ST_Touches(a.geom, b.geom); Jun 1, 2015 at 14:14
  • Thank you, I've to test if this improves the performance of my query.
    – Lunar Sea
    Jun 1, 2015 at 14:17
  • 1
    Another option for encoding these relationship is to use ST_Relate and an intersection matrix. Jun 1, 2015 at 14:47

1 Answer 1

3

ST_Intersects combined with NOT ST_Touches will produce what you are looking for more efficiently, than two version of ST_Contains and ST_Covers. You can avoid row_number() over() to generate the gid if you select (ST_Dump(....)).* in the subquery and then select g.path and g.geom from that query.

CREATE TABLE poly_intersection AS
SELECT
  g.path[1] as gid,
  g.geom::geometry(polygon, SRID) AS geom
FROM
(SELECT
   (ST_Dump(ST_Intersection(poly1.geom, poly2.geom))).*
FROM
  poly1, poly2
WHERE ST_Intersects(poly1.geom, poly2.geom) AND NOT ST_Touches(poly1.geom, poly2.geom) as g;
1
  • This does the job. Most likely there is no solution without any subquery.
    – Lunar Sea
    Jun 1, 2015 at 18:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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