Issue: Improve tests for review - Geometry test (ST_IsValid)

[Ludee]

This SQL-snipped is used to identify and locate invalid geometries:

SELECT  sub.id AS id,
    sub.error AS error,
    sub.error_reason AS error_reason,
    ST_SETSRID(location(ST_IsValidDetail(sub.geom)),3035) ::geometry(Point,3035) AS geom
FROM    (
    SELECT  id AS id,               -- PK
            ST_IsValid(geom) AS error,
            reason(ST_IsValidDetail(geom)) AS error_reason,
            geom AS geom
    FROM    schema.table            -- Table
    WHERE   version = 'v1'
    ) AS sub
WHERE   sub.error = FALSE;

• Does it work for all geometries? (Polygon, MultiPolygon, Lines..)
• Are there more PostGIS functions?
• What other checks can be included?

[klarareder]

I use this to identify invalid as above, null geometries, and collections: SELECT ‘non valide’ AS nb, count(*) FROM table WHERE NOT ST_IsValid(the_geom) UNION SELECT ‘geom nulle’ AS nb, count(*) FROM table WHERE the_geom is null UNION SELECT ‘collection’ AS nb, count(*) FROM table WHERE not ST_IsValid(the_geom) AND ST_GeometryType(ST_MakeValid(the_geom))=’ST_GeometryCollection’; source: https://www.sigterritoires.fr/index.php/en/how-to-rectify-the-geometry-of-a-postgis-table/ The later two are not real errors, but sometimes make things difficult.

[klarareder]

Are there more PostGIS functions?

There are many more PostGIS functions like St_Buffer etc. but they have nothing to do with invalid geometries.

[klarareder]

A general question I would be interested in would be: Do we allow invalid geometries in the database? If not, do people have to make them valid beforehand or is a St_MakeValid provided? In the second option -> St_MakeValid does something, but does it really fix the problem or does it generate geometries which are not what they were initially?

[klarareder]

Sometimes there are overlapping geometries, that is not invalid, but might not be desired. These can be selected with:

SELECT (ST_Dump(ST_Union(geom))).geom AS geom FROM schema.table_name;