Opened 8 months ago

Last modified 8 months ago

#5606 closed defect

Valid polygons in SQL Server are invalid in POSTGIS — at Version 1

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 samnull10)

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

Change History (2)

by samnull10, 8 months ago

Attachment: postgis_report.csv​ added

sample WKB format from SQL Server

comment:1 by samnull10, 8 months ago

Description: modified (diff)
Note: See TracTickets for help on using tickets.