Check this geometry, please:

ST_GeomFromText('MULTIPOLYGON Z (((228696.8 338223.3 0,228563.8 338278.5 0,228579.6 338194.2 0,228678.5 338298.6 0,228573 338078.6 0,228512.3 338355.7 0,228696.8 338223.3 0)))') as geom

There's 4 'Self-intersection' points, but the location(ST_IsValidDetail(geom)) select only one

SELECT location(ST_IsValidDetail(geom)) as geom, reason(ST_IsValidDetail(geom))

How can I get all points?

Something like: ST_DumpInValidReason


I believe that in no way at the moment. IsValidDetail gives additional information about validity and knowing one error location is usually enough.

Your wish is understandable from the point of view of someone who tries to correct the geometries by editing them manually but obviously the author of the IsValidDetail has had other priorities.

Here is the source code from a similar ST_IsValidDetail function of H2 Spatial https://github.com/orbisgis/h2gis/blob/v1.2.4/h2spatial-ext/src/main/java/org/h2gis/h2spatialext/function/spatial/properties/ST_IsValidDetail.java and it shows that the result is only one point

details[2] = GF.createPoint(error.getCoordinate());

Interestingly PostGIS and OpenJUMP report different locations for the error even OpenJUMP is using the JTS function and PostGIS is using GEOS which is ported from JTS.


POINT Z (228643.440954767 338261.591260644 0)


POINT ( 228632.46264019504 338250.00242301676)

enter image description here


Thanks Cyril, but this is an example for lots of them... It result for some but not for all

enter image description here


Your task is solved in two steps:

step 1)

create table exper_table as SELECT (ST_DumpPoints(ST_MakeValid (ST_GeomFromText ('MULTIPOLYGON Z((( 228696.8 338223.3 0, 228563.8 338278.5 0, 228579.6 338194.2 0, 228678.5 338298.6 0, 228573 338078.6 0, 228512.3 338355.7 0, 228696.8 338223.3 0)))') ))).geom

ALTER TABLE exper_table ADD COLUMN id serial not null;

and step 2)

First Shot:

DELETE FROM exper_table WHERE id IN (SELECT min(id) as id_point FROM exper_table group by geom);

Second Shot:

DELETE FROM exper_table WHERE id IN (SELECT min(id) as id_point FROM exper_table group by geom);


SELECT ST_AsText(geom) from exper_table;

Still to understand how many times to shoot, :-) ... either cartridges can end, or "right points",

(Most likely, this may depend on how many times the same point coincides with the corresponding incorrect point ...)

Success to you

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.