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?

Improve this question
3
  • 2
    Please copy one problematic geometry as WKT into your question.
    – user30184
    Commented Aug 5, 2022 at 10:55
  • WKT geometry added
    – Leehan
    Commented Aug 5, 2022 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 gis.stackexchange.com/questions/173977/….
    – user30184
    Commented Aug 5, 2022 at 13:55

1 Answer 1

Reset to default
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
Improve this answer
2
  • 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
    Commented Aug 5, 2022 at 12:26
  • 2
    yes you are correct. You can make it valid: st_isvalid(st_makevalid(geom)) return True
    – Bera
    Commented Aug 5, 2022 at 12:29

Your Answer

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

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