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 ASSELECTrow_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 ASSELECTrow_number() over() AS gid,g.geom::geometry(polygon, SRID) AS geomFROM(SELECT(ST_Dump(ST_Intersection(poly1.geom, poly2.geom))).geomFROMpoly1, poly2WHEREST_Overlaps(poly1.geom, poly2.geom) = 't' OR ST_Contains(poly1.geom, poly2.geom) = 't' OR ST_Contains(poly1.geom, poly2.geom) = 't')AS g;
أكثر...
CREATE TABLE poly_intersection ASSELECTrow_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 ASSELECTrow_number() over() AS gid,g.geom::geometry(polygon, SRID) AS geomFROM(SELECT(ST_Dump(ST_Intersection(poly1.geom, poly2.geom))).geomFROMpoly1, poly2WHEREST_Overlaps(poly1.geom, poly2.geom) = 't' OR ST_Contains(poly1.geom, poly2.geom) = 't' OR ST_Contains(poly1.geom, poly2.geom) = 't')AS g;
أكثر...