Opened 13 months ago
Closed 13 months ago
#5606 closed defect (wontfix)
Valid polygons in SQL Server are invalid in POSTGIS
Reported by: | samnull10 | Owned by: | pramsey |
---|---|---|---|
Priority: | high | Milestone: | PostGIS 3.4.1 |
Component: | postgis | Version: | 3.4.x |
Keywords: | Cc: |
Description (last modified by )
Encountered an edge case where some valid polygon shapes of type geometry and srid 4326 in SQL Server, are invalid in PostGIS when moving the shape column from SQL Server to PostGIS, either as WKB format or using geopandas
- PostGIS version
'POSTGIS="3.4.0 0874ea3" [EXTENSION] PGSQL="160" GEOS="3.12.0-CAPI-1.18.0" PROJ="6.3.1" LIBXML="2.9.10" LIBJSON="0.13.1" LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)"'
- Postgresql version
'PostgreSQL 16.0 (Ubuntu 16.0-1.pgdg20.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.2) 9.4.0, 64-bit'
- WKB query for shapes creation
SELECT ST_GeomFromWKB(decode(shape, 'hex'), 4326)
FROM table;
- Sample shapes from SQL Server in WKB format
0103000000010000000A000000348ADB2423795CC048C293B08B944640B4E1372523795CC0B0BB464C029546405884A5FEFD785CC000B03BBCEB944640AC1D91FEFD785CC0889039B08B94464094F89132D4785CC058C390C58B94464088819F52D4785CC0F09ED4DB029546400CD6BAD227795CC0B03925B8029546407440ADCA27795CC030416C9A8B944640AC1D91FEFD785CC0889039B08B944640348ADB2423795CC048C293B08B944640 0103000000010000000A000000781A43E279A959C0105AA50DEAEB474090DE1FD879A959C088E67C3CEAEB474090DE1FD879A959C040D2153CEAEB47405C86609E73A959C058A556C006EC47409CDB39A4C3A959C0F89CBF5F05EC47406C7FF6DCAFA959C080DE563891EB4740F88DEF587AA959C028FA493690EB4740D44E54D679A959C0D8668A3690EB474090DE1FD879A959C040D2153CEAEB4740781A43E279A959C0105AA50DEAEB4740 0103000000010000000A00000094680CB7DF0C5DC040A697A6B5664440A4C56BC1DF0C5DC090ECBBFCF0664440806F403CCC0C5DC0A05FBCFBF0664440783F2030CC0C5DC0A8F3EAA7B5664440F4A06EDACB0C5DC080FF82770E67444018F3CCFB1A0D5DC0C885C24E0E674440183E14321B0D5DC028D5C2E8966644400CF9604CCC0C5DC0E83D10E496664440783F2030CC0C5DC0A8F3EAA7B566444094680CB7DF0C5DC040A697A6B5664440 0103000000010000000A000000B8544002C8135CC03002610F3C2247401C28BB5CAE135CC0383C70A0502247408467FE75A7135CC008886EC84F224740B8544002C8135CC03002610F3C2247402C5621A6A3135CC070B462992D2247401419EB55A3135CC090E398095F22474050E43AA7C8135CC010726BF33B224740DC72ECA8A3135CC0101220D82B2247402C5621A6A3135CC088F630952D224740B8544002C8135CC03002610F3C224740 01030000000100000019000000A89DEA5B49A959C000C6E28D85213F40DC46BBEB1BA959C0B0BC7C7636223F406C206627EAA859C0C0CF9CF5A9213F40C86F617515A959C0C0D5EF06F4203F405473A9DFDFA859C080396EF65C203F406442E9C40BA959C0601A8D23A61F3F4058D24C55ECA859C0402D96944E1F3F40E89912D4E6A859C030C8E04C3F1F3F4098BB91D7D5A859C06022F543111F3F4054B751CFC8A859C0F04770E1481F3F40B47B1409C1A859C0402554CE691F3F40D86C68A77FA859C030F9FEFE7B203F405091CBFE27A859C0D0A2F616E7213F4000139E2193A859C02091FFA117233F4010873476FEA859C0D0B4A64E47243F40F4F60AB255A959C0F06C097FDA223F40D45A53B155A959C000BC017DDA223F40E49404D168A959C0A0A7B93D8A223F4040E22B7581A959C080DF91AE22223F40F472B970A9A959C060B28B587A213F407084679DACA959C03079A4EA6C213F405836681F93A959C02052BAFE21213F403C9F1F4B41A959C0300CEAF83B203F40C86F617515A959C0C0D5EF06F4203F40A89DEA5B49A959C000C6E28D85213F40 01030000000100000019000000AC45F9ECFAC958C0781F4227C763424018D02615FAC958C008F8E3003E644240E07ABEADAEC958C048CDF80D3F644240E8A4353BAFC958C08080FDF6C6634240C007B15E65C958C0204F76C6C6634240F84D5B3565C958C0F053943940644240DC9DE51465C958C028CF35999F6442403004FB0D65C958C0502FEFE6B3644240180D4DCA7EC958C0F0E4E5B3B364424044B317A9AEC958C078415CBDB364424014D53B3FF9C958C02071E8CAB364424018462A5443CA58C0F8EEEFD6B36442405478FF6E8DCA58C0505A8AE1B3644240884D4221D6CA58C0900B91EAB3644240E40D411FD6CA58C0F83E3AE73A6442407009FCEBD9CA58C0A8470CD5296442401024F8D7D9CA58C098A820A4C663424064595D728FCA58C0A0B745F4C6634240502C0AFD44CA58C0102ACA42C7634240ACBBABE444CA58C0785A357750634240E89D7E0EFBC958C0C837A85850634240ECD27798C5C958C0C03F3BD350634240D8C82BC6AFC958C04024EDFA50634240E8A4353BAFC958C08080FDF6C6634240AC45F9ECFAC958C0781F4227C7634240
Attachments (1)
Change History (5)
by , 13 months ago
Attachment: | postgis_report.csv added |
---|
comment:1 by , 13 months ago
Description: | modified (diff) |
---|
comment:2 by , 13 months ago
either as WKB format or using geopandas
Is the export feature to the "OGC Extended Well-Known Text (EWKT)" format functioning correctly?
comment:3 by , 13 months ago
All these cases are invalid because they are polygons with a single ring that self-touches when evaluated in a planar coordinate system (geometry
).
It may be that SQL Server is evaluating these for validity on the WGS84 geodetic spheroid, where perhaps the self-touches do not occur. Or possibly it uses a tolerance value in some way. AFAIK SQL Server does not allow "self-touching inverted rings" (i.e. it follows the OGC polygon model, not the ESRI polygon model). But possibly that restriction has been relaxed?
In any case, these are invalid in the PostGIS geometry model, so should be fixed using ST_MakeValid
before being used in functions.
comment:4 by , 13 months ago
Resolution: | → wontfix |
---|---|
Status: | new → closed |
sample WKB format from SQL Server