Problem with checking for Valid Geometries for PostGIS

Question

I have used this PostGIS in different environments many times without issue:

update my_table set geom = ST_MakeValid(geom) where ST_IsValid(geom) = false;

However in Azure I keep seeing the following response:

ERROR:  GEOS isvalid() threw an error!

This is using PostGIS 2.3.2 r15302 and GEOS 3.6.1-CAPI-1.10.1 r4317

In case it was because geom was null I tried the following:

update my_table set geom = ST_MakeValid(geom) 
where geom is not null and ST_IsValid(geom) = false;

However I get the same error.

The above was in psql. I tried DBeaver as well (I have given up on PgAdmin4) and there I see the following slightly different output:

SQL Error [XX000]: ERROR: GEOS isvalid() threw an error!

Googling GEOS threw an error seems to point to invalid geometries and suggests the user use ST_IsValid() but in this case that is the function causing the issue.

How do I fix the issue when ST_IsValid() itself throws an error?

Update:

Through searching through hundreds of thousands of entries in the table, I found two with errors.

If I try

update my_table set geom = ST_MakeValid(geom) where id in (1234, 5678)

then I see the following error

ERROR: GEOSisValid(): IllegalArgumentException: Exponent out of bounds

How do I fix either error?

Answer 1

I am still not sure how/why the geometry has an issue, or why the GEOS functions are throwing errors, but I figured out how to clean up the bad data.

Given that both ST_IsValid() and ST_MakeValid() did not work, I cleaned the geometry as follows:

• Convert it to GeoJSON with ST_AsGeoJSON()
• Convert it back to a geometry with ST_GeomFromGeoJSON()
• At this point it is still invalid, but ST_IsValid() works
• Because it is invalid use ST_MakeValid()
• Set the SRID back to the desired one with ST_SetSRID()

Which results in

update my_table 
set geom = ST_SetSRID(ST_MakeValid(ST_GeomFromGeoJSON(ST_AsGeoJSON(geom))), 4326)
where id in (1234, 5678)

The resulting geometry is valid.