Identifying the cause of NOT ST_IsValid()

Question

I have a table of raw GeoJSON in a Postgres database, with PostGIS installed. Some of this GeoJSON is unfortunately invalid, for varying reasons.

SELECT
    id, name, geometry
FROM shapes
WHERE
    NOT ST_IsValid(ST_GeomFromGeoJSON(geometry));

Sample log output:

NOTICE:  Self-intersection at or near point -98.12763909659509 40.524842899558408
NOTICE:  Ring Self-intersection at or near point -89.500197172165002 45.280231548720003
NOTICE:  Nested shells at or near point -80.15264234 34.224868579999999
NOTICE:  Duplicate Rings at or near point -80.155240000000006 34.217115999999997
NOTICE:  IllegalArgumentException: Invalid number of points in LinearRing found 3 - must be 0 or >= 4
NOTICE:  IllegalArgumentException: point array must contain 0 or >1 elements

Self-intersection is by far the most common error. I'd like to edit out the self-intersection completely, such as via ST_SimplifyPreserveTopology. However, when I attempt to do so:

SELECT
    id,
    name,
    geometry,
    ST_AsGeoJSON(ST_SimplifyPreserveTopology(ST_GeomFromGeoJSON(geometry), .00000001)) as new_geometry
FROM shapes
WHERE
    NOT ST_IsValid(ST_GeomFromGeoJSON(geometry));

Those IllegalArgumentExceptions kill the query:

ERROR:  First argument geometry could not be converted to GEOS: IllegalArgumentException: Invalid number of points in LinearRing found 3 - must be 0 or >= 4

I tried using ST_IsSimple to filter those geometries out, but that function also triggers an IllegalArgumentException.

How can I more accurately identify which validity error a GeoJSON string suffers from so that I can filter out those nastier geometries?

Answer 1

You can't, in general, use Postgis operations on invalid geometries. So something like simplification (as you've tried) won't work.

What you can try is ST_MakeValid.

However that is not going to magically give you back correct data. It will usually provide back dimensionally-valid data, but that may not be what you expect. In particular, note these two statements from the documentation:

In case of full or partial dimensional collapses, the output geometry may be a collection of lower-to-equal dimension geometries or a geometry of lower dimension.

Single polygons may become multi-geometries in case of self-intersections.

So you need to closely check the data after making it valid.

Answer 2

I think you should probably be using ST_IsValidDetail rather than just ST_IsValid. That way you can decide how to treat each group of invalid geometries individually.

So in your query to simplify the self intersections should be something like

SELECT
    id,
    name,
    geometry,
    ST_AsGeoJSON(ST_SimplifyPreserveTopology(ST_GeomFromGeoJSON(geometry), .00000001)) as new_geometry
FROM shapes
WHERE
    reason(ST_IsValidDetail(ST_GeomFromGeoJSON(geometry))) = 'Self-intersection';

Answer 3

There is no way to filter the query based on validity type via SQL. What you do have, due to the NOT ST_IsValid condition, is a pair of equal length ordered lists of invalid geometry notices and geometries.

These two lists can be manually correlated to identify which error each geometry suffers from. Filter out any IllegalArgumentException, and other queries should be able to execute.