PostGIS - Parse coordinates of st_isvalidreason() to a geometry column

Question

With PostGIS, I'm using st_isvalidreason() function to check topologicals errors in my entities. When something is topologically wrong, it returns me something like:

Self-intersection[969880.222423769 6274973.28067924]

First I have the reason and after, surrounding by brackets I have coordinates of the error. You can notice that this brackets aren't a real ARRAY, because it's full text and there is no delimiter.

I wan't to use this coordinates to make a geometry column and display it in a GIS (like QGIS) like a point layer.

But how to parse this coordinates information to build a geometry column ?

Ideally I'd like it to look like st_point(969880.222423769,6274973.28067924).

Answer 1

The ST_IsValidDetail https://postgis.net/docs/ST_IsValidDetail.html function returns the location of the error as geometry. It can be converted into WKT with ST_AsText if needed.

select ST_IsValidReason(ST_GeomFromText('POLYGON (( 340 440, 460 440, 460 320, 400 320, 440 380, 360 380, 400 320, 340 320, 340 440 ))'))
as reason, 
ST_AsText(location(ST_IsValidDetail(
ST_GeomFromText('POLYGON (( 340 440, 460 440, 460 320, 400 320, 440 380, 360 380, 400 320, 340 320, 340 440 ))')
))) as place;

Result:
"Ring Self-intersection[400 320]"   "POINT(400 320)"