Created
April 25, 2017 14:45
-
-
Save MarHoff/a813e63c50b289e4b5a240e9f812c004 to your computer and use it in GitHub Desktop.
Find overlapping slivers inside a postgis geometry table
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Given that "mytable" have at least two field "id" and "geom" (Polygon/Multipolygon) | |
WITH | |
temp AS ( | |
SELECT distinct (st_dump(st_intersection(a.geom, b.geom))).geom AS geom --Use of distinct to remove symetrical shapes and st_dump().geom to get standard types | |
FROM mytable a JOIN mytable b | |
ON a.id <> b.id AND st_intersects(a.geom, b.geom) --Filter to only test distinct object that intersects | |
WHERE st_geometrytype(st_intersection(a.geom, b.geom)) = 'ST_GeometryCollection'::text --Filter to only include collections because areas are returned as collections | |
) | |
SELECT row_number() OVER () AS id, temp.geom | |
FROM temp | |
WHERE st_geometrytype(temp.geom) = 'ST_Polygon'::text --Filter to keep only polygon as line and point are normal topological intersection objects |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment