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
The result is:
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?