PostGIS – How to Find Self-Intersection in PostGIS

postgispostgis-2.4postgresqlself-intersection

I've developed an query to find self-intersection points by using PostgreSQL 10 and PostGIS 2.4 database. The query took long time (aprx 90mins) to produce the result from 3,122 polygons. The query is:

SELECT * FROM (SELECT DISTINCT ST_AsText((ST_DumpPoints(ST_Node( ST_ExteriorRing(geom)))).geom) pnt, id FROM "Table_Polygon" ) AA WHERE aa.pnt not in (SELECT DISTINCT ST_AsText((ST_DumpPoints( ST_ExteriorRing(geom))).geom) pnt FROM "Table_Polygon" )

The vertices returned from the two sub queries are 4,09,122 and 4,09,120 respectively. Only the additional two vertices from 1st the sub query will be result as self-intersection points

enter image description here

The result is:

enter image description here

Are there any alternative PostGIS functions which can be used to optimize this query to get the quick result from this huge volume of data?

Best Answer

Did you think about adding a where St_IsValid(geom) = False ?

This may avoid to look for geometry wich are valid and then doesn't have self intersection issue. If you have performance issues, be sure to use a spatial index like Gist also.

You can also look for some work-around with St_MakeValid.

Related Question