[PostGIS] #5606: Valid polygons in SQL Server are invalid in POSTGIS

[PostGIS]

#5606: Valid polygons in SQL Server are invalid in POSTGIS
------------------------+---------------------------
  Reporter:  samnull10  |      Owner:  pramsey
      Type:  defect     |     Status:  new
  Priority:  high       |  Milestone:  PostGIS 3.4.1
 Component:  postgis    |    Version:  3.4.x
Resolution:             |   Keywords:
------------------------+---------------------------
Description changed by samnull10:

Old 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

New 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
 01030000000100000019000000A89DEA5B49A959C000C6E28D85213F40DC46BBEB1BA959C0B0BC7C7636223F406C206627EAA859C0C0CF9CF5A9213F40C86F617515A959C0C0D5EF06F4203F405473A9DFDFA859C080396EF65C203F406442E9C40BA959C0601A8D23A61F3F4058D24C55ECA859C0402D96944E1F3F40E89912D4E6A859C030C8E04C3F1F3F4098BB91D7D5A859C06022F543111F3F4054B751CFC8A859C0F04770E1481F3F40B47B1409C1A859C0402554CE691F3F40D86C68A77FA859C030F9FEFE7B203F405091CBFE27A859C0D0A2F616E7213F4000139E2193A859C02091FFA117233F4010873476FEA859C0D0B4A64E47243F40F4F60AB255A959C0F06C097FDA223F40D45A53B155A959C000BC017DDA223F40E49404D168A959C0A0A7B93D8A223F4040E22B7581A959C080DF91AE22223F40F472B970A9A959C060B28B587A213F407084679DACA959C03079A4EA6C213F405836681F93A959C02052BAFE21213F403C9F1F4B41A959C0300CEAF83B203F40C86F617515A959C0C0D5EF06F4203F40A89DEA5B49A959C000C6E28D85213F40
 01030000000100000019000000AC45F9ECFAC958C0781F4227C763424018D02615FAC958C008F8E3003E644240E07ABEADAEC958C048CDF80D3F644240E8A4353BAFC958C08080FDF6C6634240C007B15E65C958C0204F76C6C6634240F84D5B3565C958C0F053943940644240DC9DE51465C958C028CF35999F6442403004FB0D65C958C0502FEFE6B3644240180D4DCA7EC958C0F0E4E5B3B364424044B317A9AEC958C078415CBDB364424014D53B3FF9C958C02071E8CAB364424018462A5443CA58C0F8EEEFD6B36442405478FF6E8DCA58C0505A8AE1B3644240884D4221D6CA58C0900B91EAB3644240E40D411FD6CA58C0F83E3AE73A6442407009FCEBD9CA58C0A8470CD5296442401024F8D7D9CA58C098A820A4C663424064595D728FCA58C0A0B745F4C6634240502C0AFD44CA58C0102ACA42C7634240ACBBABE444CA58C0785A357750634240E89D7E0EFBC958C0C837A85850634240ECD27798C5C958C0C03F3BD350634240D8C82BC6AFC958C04024EDFA50634240E8A4353BAFC958C08080FDF6C6634240AC45F9ECFAC958C0781F4227C7634240

--
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5606#comment:1>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.