PostGIS 2.x doesn't allow to store some invalid polygon kinds

[Paco Calvo]

I've found that since PostGIS 2.0, some invalid invalid polygon kinds (reported as having unclosed rings, with error code XX000 in lwgeom_pg.c, line 162) cannot be stored, which in turn aborts the transaction at next insertion (error code 25P02). Using PostGIS 1.5 everything is stored with no hiccups.

After researching if this is a 'feature', 'breaking change' or 'bug' since PostGIS 2.0 and having found no documentation, I'd like to know if this is a known issue that could be resolved or not.

I've reproduced this again with PostGIS 2.2 x64 ("POSTGIS="2.2.0 r14208" GEOS="3.5.0-CAPI-1.9.0 r4090" SFCGAL="1.2.0" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.0.1, released 2015/09/15" LIBXML="2.7.8" LIBJSON="0.12" TOPOLOGY RASTER") and PostgreSQL 9.4.5 x64.

Thanks in advance.

[strk]

Personally, I'd call it a bug. We've had this discussion in the past, and finally settled to accept such invalidity in WKB form but not in WKT form. The WKB acceptance was needed to make restoring dumps possible.

I'm ready to re-open the discussion as nowadays we have GUCs (global unified configuration) that might be used to determine the behavior.

[robe]

I'm open too. Why is this flagged as a GEOS. Isn't it an issue in core PostGIS?

[Paco Calvo]

Hi robe, I selected 'liblwgeom' instead of 'postgis' component beacuse the catched exception informed of the error being thrown from line 162 in file lwgeom_pg.c.

[robe]

fcaa, no wasn't complaining about that. I was talking about the milestone, PostGIS GEOS. Those are fake milestones to say the issue is upstream and not fixed by a PostGIS upgrade. Seems it was mistake, so I'll switch to 2.2.1.

[Paco Calvo]

Replying to robe:

fcaa, no wasn't complaining about that. I was talking about the milestone, PostGIS GEOS. Those are fake milestones to say the issue is upstream and not fixed by a PostGIS upgrade. Seems it was mistake, so I'll switch to 2.2.1.

OK, thank you very much. I'll be waiting for Winnie having a new build when this isuue can be solved.

Any idea if this fix could be backported to 2.0 and/or 2.1?

[robe]

2.1 maybe depending on the size of the fix. For 2.0 less comfortable about as we try to make very minimalistic changes on that like only security fixes or crasher bugs. Again it depends on size of fix.

[pramsey]

We can't do any discussion *or* fixing if we don't get examples of the offending polygons or the use case. That is, is the failed inserting happening against WKB or text inputs? And, what polygons are failing? I'm not super chuffed to revisit the results around validity enforcing for text inputs, since we've had them, at this point, *forever*.

[Paco Calvo]

Invalid geometries as SHP and SQLite/SpatiaLite

[Paco Calvo]

Hi:

I've attached a sample of the invalid geometries, as SHP and as a SQLite/SpatiaLite database.

My use case is the following: 1.- The geometries are stored in a SQLite/SpatiaLite database. 2.- The geometries are read using SpatiaLite's ST_AsBinary() function (used 4.3.0a version, with LWGeom 2.1.7 and GEOS 3.5.0-CAPI-1.9.0 r4084). 3.- The geometries are inserted using PostGis's ST_GeomFromWkb() function.

The former steps work seamlessly with PostGIS 1.5, but not with PostGIS 2.x.

In the meantime, I've tried to reproduce it using PostGIS Shapefile Loader 2.2 and the SHP attached, and with a SQL script generated with ogr2ogr, but the geometries were loaded as expected. So, at this time I'm suspecting also of an issue with SpatiaLite's ST_AsBinary().

[pramsey]

So this is pretty narrowly an issue with SQL functions handling invalid inputs. GeomFrom*. We don't like invalid WKT, and we don't like invalid WKB, *IF* we find it in the context of a function.

This doesn't work:

select st_astext(st_geomfromwkb(decode('01060000000100000001030000000100000015000000BABCF3EDEFA61EC0F50E96F40EDE45405727A222F4A61EC04875A8E10EDE45400FA0F07506A71EC0A975B0420FDE454007A3506D09A71EC027768CC00FDE45406CA390D209A71EC08743825310DE4540A7A4900D0BA71EC0D51098D510DE4540A1A4F0070BA71EC0F4DECD2712DE4540DCA5F0420CA71EC039ACA39E12DE45404DA670B30CA71EC0AFACC31413DE454036A6F09C0CA71EC0E679F17E13DE454050A550B60BA71EC02D474BF913DE45408EA350F409A71EC0B547B38114DE4540E2A1D04808A71EC0D414F9D314DE4540C334828F01A71EC0CD479B9914DE4540FEBBF330EFA61EC02E47B3FA13DE45409655352FEFA61EC0FA13A3FA13DE45406BEF9E6BEFA61EC06412AB6312DE45400A89806FEFA61EC04912B44812DE45405356C5ECEFA61EC096A867FC0EDE4540BABCF3EDEFA61EC0274294F40EDE4540BABCF3EDEFA61EC08EA87EF40EDE4540','hex')));

And this does work

select '01060000000100000001030000000100000015000000BABCF3EDEFA61EC0F50E96F40EDE45405727A222F4A61EC04875A8E10EDE45400FA0F07506A71EC0A975B0420FDE454007A3506D09A71EC027768CC00FDE45406CA390D209A71EC08743825310DE4540A7A4900D0BA71EC0D51098D510DE4540A1A4F0070BA71EC0F4DECD2712DE4540DCA5F0420CA71EC039ACA39E12DE45404DA670B30CA71EC0AFACC31413DE454036A6F09C0CA71EC0E679F17E13DE454050A550B60BA71EC02D474BF913DE45408EA350F409A71EC0B547B38114DE4540E2A1D04808A71EC0D414F9D314DE4540C334828F01A71EC0CD479B9914DE4540FEBBF330EFA61EC02E47B3FA13DE45409655352FEFA61EC0FA13A3FA13DE45406BEF9E6BEFA61EC06412AB6312DE45400A89806FEFA61EC04912B44812DE45405356C5ECEFA61EC096A867FC0EDE4540BABCF3EDEFA61EC0274294F40EDE4540BABCF3EDEFA61EC08EA87EF40EDE4540'::geometry;

I can load the attached shape file without incident, since the loading is not using GeomFrom* functions. Presumably ogr2ogr can load the sqlite database too. I'm not sure who this inconsistency is inconveniencing, and I am loath to change a behaviour we've now had for quite some time.