[postgis-users] ST_Union results in empty polygon

[Arnaud L.]

Le 26-03-18 à 20:05, Jonathan McCormack a écrit :
> We've seen occasional inconsistencies from ST_Union where the resulting 
> polygon is empty or clearly cannot be the result of the constituent 
> geometries.  These issues can often be resolved by applying a very 
> slight buffer, though sometimes the reverse is true, where the ST_Union 
> of the unbuffered geometries is fine, but applying the buffer causes the 
> incorrect geometry.

Hi Jonathan
The right trick used to be to apply a "0" buffer to the geometry, not a 
small one. In your test case, the "0" buffer works.
But since Postgis 2.1 this is deprecated, and there is know an 
ST_MakeValid function that should work even better (it does essentially 
what ST_Buffer(geom, 0) does, plus some other tricks).
http://postgis.net/docs/ST_MakeValid.html

WITH poly AS (
   SELECT
     ST_SetSRID( 
'0106000020E610000001000000010300000001000000A7000000CD3D1C03F6A55DC0D41D2C1355424140CC3D1C03F6A55DC0CD1D2C1355424140239DEF1BF2A55DC07A999FD63A424140239DEF1BF2A55DC079999FD63A42414035EBA671EFA55DC06B333EEC2842414033EBA671EFA55DC062333EEC284241405D4D1316EBA55DC0DFEC39A10B424140504C1316EBA55DC0A5E139A10B424140724C1316EBA55DC03AD639A10B424140C14D1316EBA55DC01CCB39A10B4241402F501316EBA55DC0C6C039A10B424140A0531316EBA55DC0A9B739A10B424140F0571316EBA55DC02CB039A10B424140EE5C1316EBA55DC0A0AA39A10B42414063621316EBA55DC043A739A10B424140A47BD0A731A65DC0D7F503C4EF414140A57BD0A731A65DC0D6F503C4EF4141400738FF063AA65DC00D12C975EC4141400CCB2AE142A65DC07879FEF6E8414140B66CB7A851A65DC056C81121E3414140B76CB7A851A65DC055C81121E3414140EE1E0DE599A65DC0255C6B9BC6414140F8230DE599A65DC01B5B6B9BC641414004290DE599A65DC0EF5B6B9BC6414140E72D0DE599A65DC09A5E6B9BC641414077320DE599A65DC005636B9BC64141408A360DE599A65DC008696B9BC6414140FF390DE599A65DC071706B9BC6414140B63C0DE599A65DC0FE786B9BC6414140993E0DE599A65DC065826B9BC6414140DE96C5E8BDA65DC09073BA96B8424140E297C5E8BDA65DC0F57DBA96B8424140E297C5E8BDA65DC08D88BA96B8424140DE96C5E8BDA65DC0F392BA96B8424140DF94C5E8BDA65DC0C39CBA96B8424140F991C5E8BDA65DC0A1A5BA96B8424140478EC5E8BDA65DC038ADBA96B8424140EC89C5E8BDA65DC03FB3BA96B84241401285C5E8BDA65DC07FB7BA96B8424140E77FC5E8BDA65DC0CEB9BA96B84241409B7AC5E8BDA65DC016BABA96B84241406275C5E8BDA65DC056B8BA96B84241406275C5E8BDA65DC055B8BA96B84241404BD02E07BBA65DC037B7CF09B74241404BD02E07BBA65DC038B7CF09B742414001CC2E07BBA65DC021B4CF09B742414072F12D29B8A65DC0DF04B96AB4424140DC2699A7B4A65DC051CACCE4B1424140492599A7B4A65DC013C9CCE4B1424140561A48E8A8A65DC0B95BB8C4A7424140C7A71D079FA65DC099C19E00A042414036A61D079FA65DC041C09E00A0424140AF20786899A65DC09E5F71C29A424140A51F786899A65DC0985E71C29A42414039CD3D4890A65DC03799224A9142414059CC3D4890A65DC04498224A914241404EF03C6A8DA65DC078B6A90B8E424140C9ED3C6A8DA65DC03EB3A90B8E4241400A8772A98BA65DC07774BD858B4241408F8472A98BA65DC07170BD858B424140F33E92278AA65DC014CE18C588424140F23D92278AA65DC027CC18C588424140EF808AE983A65DC04C8D7B277C424140F97C8AE983A65DC026837B277C42414078DF570781A65DC0E038C9A67242414030BF7C297DA65DC08BF51626694241406C37BC257AA65DC0F6B4D9A8644241406C37BC257AA65DC0F7B4D9A8644241403E33BC257AA65DC048ADD9A864424140CA54B5C576A65DC059F84D065D4241400779C34870A65DC0A2FE90EA514241403A78C34870A65DC036FD90EA514241408A7745666FA65DC085EE224750424140FBFF2DE969A65DC0133B47A65942414019F297FE66A65DC0B31DAB0160424140A0DACEA664A65DC03A1AAE266542414038D9CEA664A65DC0191DAE2665424140CBD26B2961A65DC0E8E02DC56B424140D5214F085EA65DC0AB7DC9A672424140ACC38AC95CA65DC0DC9E226777424140FA47D6C95AA65DC0AD1822A4844241401F4ECAC559A65DC0C8C0476C8D424140F01BFCC959A65DC0BC58978AA3424140971BFCC959A65DC08161978AA34241408D1AFCC959A65DC00D6A978AA3424140B9EC93A858A65DC0BC8DCF63AA42414046EA93A858A65DC08498CF63AA424140FACA9D6557A65DC07D8254A6AE4241409BC99D6557A65DC0AC8654A6AE424140845761C655A65DC077D49123B34241409C5461C655A65DC042DB9123B3424140325161C655A65DC006E19123B3424140F29C50A953A65DC060675227B64241409C7D5A6652A65DC03D3E4D83B842414022785A6652A65DC033464D83B8424140B914962751A65DC04F9B9FE3B94241402912962751A65DC0CD9D9FE3B9424140D59D59884FA65DC0A9F3F143BB424140CF9759884FA65DC037F7F143BB4241405D65F1664EA65DC0E9C79BA8BB424140585DF1664EA65DC052C89BA8BB424140BEE7B4C74CA65DC00AFDF143BB424140A0E2B4C74CA65DC0CBFAF143BB424140D0DDB4C74CA65DC0A6F6F143BB424140F356064A4BA65DC0EDC783A0B9424140F356064A4BA65DC0ECC783A0B9424140AE52064A4BA65DC020C283A0B942414079229E284AA65DC0F8355DC2B742414032C2D9E948A65DC0F7A642E8B642414050BBD9E948A65DC0F69F42E8B64241405199E3A647A65DC092834623B54241404A14352946A65DC01C4666A1B3424140EA12352946A65DC09E4466A1B3424140954AA62943A65DC004043528B042414037DC630841A65DC00C0C2924AF424140C26C21E73EA65DC0CF616F80AF4241401CAD92E73BA65DC0014397C7B04241404851C8263AA65DC02F3282E4B3424140E9EF096A39A65DC0F3D51AA1B54241404CED096A39A65DC052DB1AA1B54241402B9BB18737A65DC0F1B52101B9424140C494B18737A65DC082BE2101B9424140A1EE0CC734A65DC0A4F59BA8BB424140C1E80CC734A65DC0BEF99BA8BB4241409A41680632A65DC0546260E7BC4241403F40680632A65DC0DF6260E7BC42414079BFBCE52BA65DC0A8E2A208BF42414002BCBCE52BA65DC069E3A208BF424140C46D85EA27A65DC0D73DE964BF4241406C6985EA27A65DC08C3DE964BF42414085E5D34721A65DC0700809E3BD42414026E3D34721A65DC0B00709E3BD424140C470A6091CA65DC03F9EC6C1BB424140E96AA6091CA65DC0769AC6C1BB424140276CAFE817A65DC00B797905B84241400E6AAFE817A65DC0ED767905B8424140C2E5EEE414A65DC0FDAB72A5B44241403D6D46E913A65DC021C0E483B4424140A964C80613A65DC0DDDBD444B5424140EBF28B6711A65DC01B501766B74241402AED8B6711A65DC0DC551766B7424140E7E68B6711A65DC0C8581766B742414013CCAA070DA65DC029E5BF61B8424140396EE6C80BA65DC024162201B942414002CE410809A65DC034DBB682BC42414097B14BC507A65DC0F849F9A3BE424140257BAAB406A65DC013B10EDAC14241401C7BAAB406A65DC02FB10EDAC1424140F8B01C5A06A65DC08D11B8E9C2424140A5CE677315A65DC0A1CA9A6828434140ABCF677315A65DC036D59A6828434140A3CF677315A65DC0FDDF9A68284341408FCE677315A65DC08CEA9A682843414078CC677315A65DC07BF49A682843414073C9677315A65DC069FD9A68284341409FC5677315A65DC0FD049B682843414020C1677315A65DC0ED0A9B682843414022BC677315A65DC0000F9B6828434140D8B6677315A65DC00B119B682843414075B1677315A65DC0FC109B68284341402DAC677315A65DC0D40E9B682843414035A7677315A65DC0A60A9B6828434140BFA2677315A65DC09D049B6828434140F49E677315A65DC0F4FC9A6828434140FC9B677315A65DC0F5F39A6828434140F399677315A65DC0FBE99A6828434140CD3D1C03F6A55DC0D41D2C1355424140'::geometry
       ,4326) geom1,
     ST_SetSRID( 
'0106000020E6100000010000000103000000010000009D00000019F297FE66A65DC0B31DAB0160424140A0DACEA664A65DC03A1AAE266542414038D9CEA664A65DC0191DAE2665424140CBD26B2961A65DC0E8E02DC56B424140D5214F085EA65DC0AB7DC9A672424140ACC38AC95CA65DC0DC9E226777424140FA47D6C95AA65DC0AD1822A4844241401F4ECAC559A65DC0C8C0476C8D424140F01BFCC959A65DC0BC58978AA3424140971BFCC959A65DC08161978AA34241408D1AFCC959A65DC00D6A978AA3424140B9EC93A858A65DC0BC8DCF63AA42414046EA93A858A65DC08498CF63AA424140FACA9D6557A65DC07D8254A6AE4241409BC99D6557A65DC0AC8654A6AE424140845761C655A65DC077D49123B34241409C5461C655A65DC042DB9123B3424140325161C655A65DC006E19123B3424140F29C50A953A65DC060675227B64241409C7D5A6652A65DC03D3E4D83B842414022785A6652A65DC033464D83B8424140B914962751A65DC04F9B9FE3B94241402912962751A65DC0CD9D9FE3B9424140D59D59884FA65DC0A9F3F143BB424140CF9759884FA65DC037F7F143BB424140CF9352094FA65DC053EE2070BB4241405D65F1664EA65DC0E9C79BA8BB424140585DF1664EA65DC052C89BA8BB424140BEE7B4C74CA65DC00AFDF143BB424140A0E2B4C74CA65DC0CBFAF143BB424140D0DDB4C74CA65DC0A6F6F143BB424140F356064A4BA65DC0EDC783A0B9424140F356064A4BA65DC0ECC783A0B9424140AE52064A4BA65DC020C283A0B942414079229E284AA65DC0F8355DC2B742414032C2D9E948A65DC0F7A642E8B642414050BBD9E948A65DC0F69F42E8B64241405199E3A647A65DC092834623B54241404A14352946A65DC01C4666A1B3424140EA12352946A65DC09E4466A1B3424140954AA62943A65DC004043528B042414037DC630841A65DC00C0C2924AF424140C26C21E73EA65DC0CF616F80AF4241401CAD92E73BA65DC0014397C7B04241404851C8263AA65DC02F3282E4B3424140E9EF096A39A65DC0F3D51AA1B54241404CED096A39A65DC052DB1AA1B54241402B9BB18737A65DC0F1B52101B9424140C494B18737A65DC082BE2101B9424140A1EE0CC734A65DC0A4F59BA8BB424140C1E80CC734A65DC0BEF99BA8BB4241409A41680632A65DC0546260E7BC4241403F40680632A65DC0DF6260E7BC42414079BFBCE52BA65DC0A8E2A208BF42414002BCBCE52BA65DC069E3A208BF424140C46D85EA27A65DC0D73DE964BF4241406C6985EA27A65DC08C3DE964BF42414085E5D34721A65DC0700809E3BD42414026E3D34721A65DC0B00709E3BD424140C470A6091CA65DC03F9EC6C1BB424140E96AA6091CA65DC0769AC6C1BB424140276CAFE817A65DC00B797905B84241400E6AAFE817A65DC0ED767905B8424140C2E5EEE414A65DC0FDAB72A5B44241403D6D46E913A65DC021C0E483B4424140A964C80613A65DC0DDDBD444B5424140EBF28B6711A65DC01B501766B74241402AED8B6711A65DC0DC551766B7424140E7E68B6711A65DC0C8581766B742414013CCAA070DA65DC029E5BF61B8424140396EE6C80BA65DC024162201B942414002CE410809A65DC034DBB682BC42414097B14BC507A65DC0F849F9A3BE424140257BAAB406A65DC013B10EDAC14241401C7BAAB406A65DC02FB10EDAC1424140F8B01C5A06A65DC08D11B8E9C2424140A5CE677315A65DC0A1CA9A6828434140ABCF677315A65DC036D59A6828434140A3CF677315A65DC0FDDF9A68284341408FCE677315A65DC08CEA9A682843414078CC677315A65DC07BF49A682843414073C9677315A65DC069FD9A68284341409FC5677315A65DC0FD049B682843414020C1677315A65DC0ED0A9B682843414022BC677315A65DC0000F9B6828434140D8B6677315A65DC00B119B682843414075B1677315A65DC0FC109B68284341402DAC677315A65DC0D40E9B682843414035A7677315A65DC0A60A9B6828434140BFA2677315A65DC09D049B6828434140F49E677315A65DC0F4FC9A6828434140FC9B677315A65DC0F5F39A6828434140F399677315A65DC0FBE99A6828434140DA1553F4F9A55DC0F7E135936F4241405D4D1316EBA55DC0DFEC39A10B424140504C1316EBA55DC0A5E139A10B424140724C1316EBA55DC03AD639A10B424140C14D1316EBA55DC01CCB39A10B4241402F501316EBA55DC0C6C039A10B424140A0531316EBA55DC0A9B739A10B424140F0571316EBA55DC02CB039A10B424140EE5C1316EBA55DC0A0AA39A10B42414063621316EBA55DC043A739A10B424140EE1E0DE599A65DC0255C6B9BC6414140F8230DE599A65DC01B5B6B9BC641414004290DE599A65DC0EF5B6B9BC6414140E72D0DE599A65DC09A5E6B9BC641414077320DE599A65DC005636B9BC64141408A360DE599A65DC008696B9BC6414140FF390DE599A65DC071706B9BC6414140B63C0DE599A65DC0FE786B9BC6414140993E0DE599A65DC065826B9BC6414140DE96C5E8BDA65DC09073BA96B8424140E297C5E8BDA65DC0F57DBA96B8424140E297C5E8BDA65DC08D88BA96B8424140DE96C5E8BDA65DC0F392BA96B8424140DF94C5E8BDA65DC0C39CBA96B8424140F991C5E8BDA65DC0A1A5BA96B8424140478EC5E8BDA65DC038ADBA96B8424140EC89C5E8BDA65DC03FB3BA96B84241401285C5E8BDA65DC07FB7BA96B8424140E77FC5E8BDA65DC0CEB9BA96B84241409B7AC5E8BDA65DC016BABA96B84241406275C5E8BDA65DC056B8BA96B84241406275C5E8BDA65DC055B8BA96B84241404BD02E07BBA65DC037B7CF09B74241404BD02E07BBA65DC038B7CF09B742414001CC2E07BBA65DC021B4CF09B742414072F12D29B8A65DC0DF04B96AB4424140DC2699A7B4A65DC051CACCE4B1424140492599A7B4A65DC013C9CCE4B1424140561A48E8A8A65DC0B95BB8C4A7424140C7A71D079FA65DC099C19E00A042414036A61D079FA65DC041C09E00A0424140AF20786899A65DC09E5F71C29A424140A51F786899A65DC0985E71C29A42414039CD3D4890A65DC03799224A9142414059CC3D4890A65DC04498224A914241404EF03C6A8DA65DC078B6A90B8E424140C9ED3C6A8DA65DC03EB3A90B8E4241400A8772A98BA65DC07774BD858B4241408F8472A98BA65DC07170BD858B424140F33E92278AA65DC014CE18C588424140F23D92278AA65DC027CC18C588424140EF808AE983A65DC04C8D7B277C424140F97C8AE983A65DC026837B277C42414078DF570781A65DC0E038C9A67242414030BF7C297DA65DC08BF51626694241406C37BC257AA65DC0F6B4D9A8644241406C37BC257AA65DC0F7B4D9A8644241403E33BC257AA65DC048ADD9A864424140CA54B5C576A65DC059F84D065D4241400779C34870A65DC0A2FE90EA514241403A78C34870A65DC036FD90EA514241408A7745666FA65DC085EE224750424140FBFF2DE969A65DC0133B47A65942414019F297FE66A65DC0B31DAB0160424140'::geometry
       ,4326) geom2)
SELECT ST_Area(geom1::geography) AS geom1_area,
   ST_Area(geom2::geography) AS geom2_area,
   ST_Area(ST_Union(geom1, geom2)::geography) AS g1g2_area_nobuffer,
   ST_Area(ST_Union(ST_Buffer(geom1, 0.0000000001), ST_Buffer(geom2, 
0.0000000001))::geography) AS g1g2_area_buffer,
   ST_Area(ST_Union(ST_Buffer(geom1, 0), ST_Buffer(geom2, 
0))::geography) AS g1g2_area_buffer0,
   ST_Area(ST_Union(ST_MakeValid(geom1), 
ST_MakeValid(geom2))::geography) AS g1g_area_makevalid
   FROM poly;


Best regards

--
Arnaud