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;
  • 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. – John Powell Jun 1 '15 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 '15 at 14:13
  • 1
    True. You can use WHERE ST_Intersects(a.geom, b.geom) AND NOT ST_Touches(a.geom, b.geom); – John Powell Jun 1 '15 at 14:14
  • Thank you, I've to test if this improves the performance of my query. – Lunar Sea Jun 1 '15 at 14:17
  • 1
    Another option for encoding these relationship is to use ST_Relate and an intersection matrix. – John Powell Jun 1 '15 at 14:47
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;
  • This does the job. Most likely there is no solution without any subquery. – Lunar Sea Jun 1 '15 at 18:38

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.