1

PostGIS3.2, PostgreSQL 13

I have a table with multipolygons.

There is a constraint CHECK (st_isvalid(geom)) on it.

Run a query with st_ivalid(geom) returns no error fortunately !

When I try to transform geometries from EPSG:2154 to EPSG:4326 in an other column with the same constraint, PostGIS says Self-intersection at or near point...

The (first?) problem is that, for example, this original feature is obviously weird :

false polygon

Here is the WKT : MULTIPOLYGON(((856968.0809475727 6518031.45147972,856930.3185529013 6518034.334114722,856934.1697799985 6518064.89914313,856944.1786493242 6518064.033092295,856989.9199941442 6518060.081799399,856991.3227799159 6518059.973267407,857054.4690395503 6518054.579191504,857168.1567043017 6518044.903213004,857067.2389138227 6518053.492345106,857072.3044749431 6518022.810888314,857070.9721031557 6518015.4990919195,857068.8029368921 6518002.930926339,857063.1728536686 6517996.683402535,857052.3188981324 6517997.593314975,857052.4488856238 6518016.896457455,857052.5138793695 6518024.273247593,856995.1650229847 6518028.505965281,856995.1325261119 6518029.448374595,856988.3081828107 6518029.935827687,856968.0809475727 6518031.45147972)))

but like I said above, select... WHERE st_isvalid() = 'f' returns nothing.

So if the geometry seems to be valid at the start, it becomes false when I reproject it.

I can't correct thousand of geometries one by one. As far as I know, makevalid(geom) or buffer(geom, 0) are useless.

The only solution seems to delete the constraint check validity on the target column EPSG:4326 (not very desired).

Unless anyone has encountered and solved this issue?

Leehan
  • 1,236
  • 2
  • 10
  • 20
  • 2
    Please copy one problematic geometry as WKT into your question. – user30184 Aug 05 '22 at 10:55
  • WKT geometry added – Leehan Aug 05 '22 at 11:42
  • The input geometry is topologically valid but I no not believe that the spike is there intentionally. Maybe you can find some ideas for pre-processing from https://gis.stackexchange.com/questions/173977/how-to-remove-spikes-in-polygons-with-postgis. – user30184 Aug 05 '22 at 13:55

1 Answers1

2

It looks valid and I can transform it:

with cte as (
            select st_setsrid(st_geometryfromtext('MULTIPOLYGON(((856968.0809475727 6518031.45147972,856930.3185529013 6518034.334114722,856934.1697799985 6518064.89914313,856944.1786493242 6518064.033092295,856989.9199941442 6518060.081799399,856991.3227799159 6518059.973267407,857054.4690395503 6518054.579191504,857168.1567043017 6518044.903213004,857067.2389138227 6518053.492345106,857072.3044749431 6518022.810888314,857070.9721031557 6518015.4990919195,857068.8029368921 6518002.930926339,857063.1728536686 6517996.683402535,857052.3188981324 6517997.593314975,857052.4488856238 6518016.896457455,857052.5138793695 6518024.273247593,856995.1650229847 6518028.505965281,856995.1325261119 6518029.448374595,856988.3081828107 6518029.935827687,856968.0809475727 6518031.45147972)))') ,2154) as geom
            )

select st_isvalid(geom), st_transform(geom, 4326) as geom from cte

If you want to select valid geometries the syntax is:

where st_isvalid(geom) is True
BERA
  • 72,339
  • 13
  • 72
  • 161
  • But the transformation is not valid: select st_isvalid(st_transform(geom, 4326)) from cte ->false best is to use st_buffer(geom,0) after transforming – nepluisse Aug 05 '22 at 12:26
  • 2
    yes you are correct. You can make it valid: st_isvalid(st_makevalid(geom)) return True – BERA Aug 05 '22 at 12:29