3

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?

4
  • could you post the WKB geometries of those two entries?
    – geozelot
    Mar 16, 2019 at 11:28
  • And the OS version? Lots of people run Linux on Azure. Mar 17, 2019 at 8:48
  • @ThingumaBob: This is one of them: SRID=4326;MULTIPOLYGON(((6.625539714341757E10 2.121995794E-314,-79.4405401677212 -8.483985467320476E12,4.2439915834E-314 8.487983176E-314,6.625539714341757E10 2.121995794E-314)),((-79.44054016772121 43.709715347456374,-79.4405401677212 43.70971534745637,-79.44054016772121 43.70971534745637,-79.44054016772121 43.709715347456374)))
    – Ask613
    Mar 17, 2019 at 12:05
  • @JohnPowell It is a managed instance of PostgreSQL in Azure so I do not know the OS version.
    – Ask613
    Mar 17, 2019 at 12:07

1 Answer 1

8

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.