Opened 12 months ago
Last modified 12 months ago
#5606 closed defect
Valid polygons in SQL Server are invalid in POSTGIS — at Initial Version
| Reported by: | samnull10 | Owned by: | pramsey |
|---|---|---|---|
| Priority: | high | Milestone: | PostGIS 3.4.1 |
| Component: | postgis | Version: | 3.4.x |
| Keywords: | Cc: |
Description
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 0103000000010000000B0000002080AF164F525AC050279DF768173F40E8EA3C3450525AC03041682B99153F4028DDA6AA36515AC01079F12F96153F40C0BDA37A36515AC02062D2F12E193F40C80F5B9BC2515AC03049D24C2F193F409C2EDACFC1515AC020F8C23E63173F40B0E33F777E515AC000C5710164173F405CD5CDF07F515AC030138EB177163F4090AF17ECC1515AC09072533D78163F409C2EDACFC1515AC020F8C23E63173F402080AF164F525AC050279DF768173F40 0103000000010000000B00000064E1D385E0195CC0D8F9F8C310354140808114CDE7195CC0580EE870203541402890B701E4195CC0601C874D26354140F4396B48E0195CC078934B881E354140E4096446DD195CC0C02D98581635414014B637A9DC195CC08058F19E273541402052B947FE195CC0389BBECC27354140806C5003FF195CC0580ACD7D0B354140086FABCDDD195CC0A8A28E7A07354140E4096446DD195CC0C02D98581635414064E1D385E0195CC0D8F9F8C310354140 0103000000010000000B000000806C901EC65B5AC0401ED84FD10E3F40E47A0784C55B5AC060C844ECC10F3F40D021234C835B5AC030B86EEFBF0F3F4044135E0B855B5AC050DBA43ACE0E3F40F431B32AFD5A5AC0304A3467CE0E3F40248F80D4FD5A5AC0006FC2FC96113F40A8BA58CF0B5C5AC0F0C0B10E97113F40E40D52B80A5C5AC04042990EE90D3F401CDBC7B1855B5AC0C0418A46E60D3F4044135E0B855B5AC050DBA43ACE0E3F40806C901EC65B5AC0401ED84FD10E3F40 0103000000010000000B000000B0F28BB241185BC0F82995BD13554040BC5E8EAF41185BC0C8F1D3E33755404050189F926B185BC0305BE1AC34554040503772A56A185BC0F82D8731B75440403460893A65185BC050172CDFB75440401CBF326724185BC038AA9E22C0544040ECFEA0E424185BC0C062131A3A554040B0F28BB241185BC0F82995BD1355404028870B1D34185BC0984D0D55ED544040300DC9B741185BC0601EA785DE544040B0F28BB241185BC0F82995BD13554040 0103000000010000000B000000C8A71C6D5E7C57C090E5947BDF773E4008C0F986027C57C0306F357B387A3E40B09F016C557B57C0B05175D9F9783E40507A725CAC7B57C0704F3CFFA7763E40A03C858C227B57C0D031084EAB763E40E0CA5101297B57C090E792816E7A3E40849896EEB47B57C090765EE15F7A3E406C829E89CD7C57C040FEFEFF457A3E40E850AF29CA7C57C070C0DBCD9D763E40507A725CAC7B57C0704F3CFFA7763E40C8A71C6D5E7C57C090E5947BDF773E40 010300000001000000170000006C9B23D7BF1D5AC068DF84CBF51F4040C86D49AE051E5AC080ED5250F51F404000ACD239061E5AC050841F326B204040A4C6071A4C1E5AC0901804C36A20404028F43DFA911E5AC0C82066526A2040406CFA8F59911E5AC0F8EE7258F41F40401CB4EC834B1E5AC0284725D5F41F4040E0F5CDEE4A1E5AC0B872AAAD7F1F4040CC0663BB901E5AC06058FF337F1F404044BA731C901E5AC0885B0B0E0A1F40409090715A4A1E5AC0F0AB2C830A1F40403C2B15C6491E5AC020592C57951E4040D08FE30D041E5AC07005C6C7951E404064AEF054BE1D5AC0B8B15F38961E4040F812BF9C781D5AC0005EF9A8961E404038542812791D5AC090B88ADC0B1F4040B86F8F87791D5AC0289F9E11811F4040AC0CA755BF1D5AC0F09CF89A801F404014B1EAD4BE1D5AC0785A6C6A0B1F4040D8666F98041E5AC01896CDF60A1F4040BC50FC22051E5AC00001D325801F4040AC0CA755BF1D5AC0F09CF89A801F40406C9B23D7BF1D5AC068DF84CBF51F4040 010300000001000000170000007C9B77FC74095AC090A5A42E294D4040783DC394BB095AC0C85654ED284D4040AC2D3776BB095AC098F9D640A04D4040A048E0D774095AC080ECAE86A04D404004DD49372E095AC0280589CCA04D404030DC225E2E095AC04868726E294D4040B08891C0E7085AC0B8C4BFAC294D4040A405F095E7085AC0086CDE10A14D40408421D26CE7085AC0E8F6F95F164E404038A56D102E095AC0189639DC154E4040E82809B474095AC048357958154E40407CBFA557BB095AC078D4B8D4144E40404C3040FB010A5AC0600D784F144E404054B48814020A5AC0A07A7CF99F4D4040D4ECCC2D020A5AC038567FAA284D404064225B4C020A5AC0589BF753B14C404008FF266A020A5AC0B0BA6DFD394C4040408DE5D7BB095AC0F87233373A4C4040542EA54575095AC08805F7703A4C4040A8A962B32E095AC0D0BDBCAA3A4C40405C4CC3882E095AC0084DD60BB24C404030DC225E2E095AC04868726E294D40407C9B77FC74095AC090A5A42E294D4040 01030000000100000019000000A4E1688219E759C0C8AE06F853864040587987E3D2E659C0F80EC725548640408CC5A1448CE659C0A82E095254864040D0CB76258CE659C04836FC80DD854040D0CDD7C2D2E659C09816BA54DD854040B4E2396019E759C0B01C7A28DD854040EC5C8C3F19E759C00895F55E66854040F4A0ECA2D2E659C0A828B58966854040307989058CE659C00056F4B2668540402831676745E659C088D1AEDA6685404060C342C9FEE559C0104D690267854040F043ECE6FEE559C078D1F8D4DD8540404C761A06FFE559C040488BAA548640407C37BEA545E659C09028497E54864040D05601CB45E659C0D0CA0DBEE4864040E870676B8CE659C0B826B1CCE48640406052CA0BD3E659C0A08254DBE486404098592FAC19E759C080DEF7E9E48640402CB8703760E759C088D5EADBE48640409C29B3C2A6E759C088CCDDCDE48640409C256A97A6E759C0B0495A6B5486404088F37A74A6E759C0A861C195DD854040FCDAFAE95FE759C048665B5EDD854040B4E2396019E759C0B01C7A28DD854040A4E1688219E759C0C8AE06F853864040 01030000000100000019000000A89DEA5B49A959C000C6E28D85213F40DC46BBEB1BA959C0B0BC7C7636223F406C206627EAA859C0C0CF9CF5A9213F40C86F617515A959C0C0D5EF06F4203F405473A9DFDFA859C080396EF65C203F406442E9C40BA959C0601A8D23A61F3F4058D24C55ECA859C0402D96944E1F3F40E89912D4E6A859C030C8E04C3F1F3F4098BB91D7D5A859C06022F543111F3F4054B751CFC8A859C0F04770E1481F3F40B47B1409C1A859C0402554CE691F3F40D86C68A77FA859C030F9FEFE7B203F405091CBFE27A859C0D0A2F616E7213F4000139E2193A859C02091FFA117233F4010873476FEA859C0D0B4A64E47243F40F4F60AB255A959C0F06C097FDA223F40D45A53B155A959C000BC017DDA223F40E49404D168A959C0A0A7B93D8A223F4040E22B7581A959C080DF91AE22223F40F472B970A9A959C060B28B587A213F407084679DACA959C03079A4EA6C213F405836681F93A959C02052BAFE21213F403C9F1F4B41A959C0300CEAF83B203F40C86F617515A959C0C0D5EF06F4203F40A89DEA5B49A959C000C6E28D85213F40 01030000000100000019000000AC45F9ECFAC958C0781F4227C763424018D02615FAC958C008F8E3003E644240E07ABEADAEC958C048CDF80D3F644240E8A4353BAFC958C08080FDF6C6634240C007B15E65C958C0204F76C6C6634240F84D5B3565C958C0F053943940644240DC9DE51465C958C028CF35999F6442403004FB0D65C958C0502FEFE6B3644240180D4DCA7EC958C0F0E4E5B3B364424044B317A9AEC958C078415CBDB364424014D53B3FF9C958C02071E8CAB364424018462A5443CA58C0F8EEEFD6B36442405478FF6E8DCA58C0505A8AE1B3644240884D4221D6CA58C0900B91EAB3644240E40D411FD6CA58C0F83E3AE73A6442407009FCEBD9CA58C0A8470CD5296442401024F8D7D9CA58C098A820A4C663424064595D728FCA58C0A0B745F4C6634240502C0AFD44CA58C0102ACA42C7634240ACBBABE444CA58C0785A357750634240E89D7E0EFBC958C0C837A85850634240ECD27798C5C958C0C03F3BD350634240D8C82BC6AFC958C04024EDFA50634240E8A4353BAFC958C08080FDF6C6634240AC45F9ECFAC958C0781F4227C7634240
sample WKB format from SQL Server