Valid polygons in SQL Server are invalid in POSTGIS

[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

[samnull10]

sample WKB format from SQL Server

[ImreSamu]

either as WKB format or using geopandas

Is the export feature to the "OGC Extended Well-Known Text (EWKT)" format functioning correctly?

( see ​https://postgis.net/docs/ST_GeomFromEWKT.html )

[mdavis]

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.