6

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?

7

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.

  • Clarifications: Yes, most Postgis operations do not work on invalid geometries, but ST_Simplify will work fine, unless you have an IllegalArgumentException. The problem is, simplify does a poor job of repairing self intersection while preserving accuracy. ST_MakeValid therefore is more preferred, but often at the cost of ugly MultiPolygon results. Bad data either way. – Brad Koch Apr 4 '14 at 22:31
2
+50

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';
  • 2
    You can also use ST_IsValidReason. – Brad Koch Mar 18 '15 at 19:25
  • ST_IsValidReason is perfect - indicating a self-intersection and why; ST_IsValidDetail returns t,, which is difficult to understand (especially when ST_IsValid returns f. Do you know why? – David LeBauer Mar 18 '15 at 20:26
  • @BradKoch I agree ST_IsValidReason would be better. – MickyT Mar 18 '15 at 20:51
  • @David I'm not seeing that behaviour from ST_IsValidDetail. I'm using 2.1.3 at the moment – MickyT Mar 18 '15 at 20:52
0

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.

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.