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));
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
How can I more accurately identify which validity error a GeoJSON string suffers from so that I can filter out those nastier geometries?