0

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?

|improve this question
6

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.

|improve this answer
  • Or perhaps postgis.net/docs/ST_IsValidReason.html or postgis.net/docs/ST_IsValidReason.html – user30184 Dec 11 '18 at 10:05
  • 3
    in fact, SELECT ST_MakeValid(geom) AS geom FROM <layer>; should split self-intersecting polygons at their intersecting vertices. pedantic note: while obligatory for performance in general, a spatial index would make no difference with OPs query... – ThingumaBob Dec 11 '18 at 10:40
  • @Maximilien: Many thanks. It helps lot to reduce the computation speed. – venkat Dec 19 '18 at 6:28
  • For Line geometry, ST_IsValid is returning as true always. ST_IsSimple(geom) =false is working fine for line geometry – venkat Dec 24 '18 at 12:08

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.