[postgis-tickets] [PostGIS] #5392: Aggregate ST_Union fails to join some "circles" when radius is too large

[PostGIS]

#5392: Aggregate ST_Union fails to join some "circles" when radius is too large
---------------------+---------------------------
 Reporter:  rotten   |      Owner:  pramsey
     Type:  defect   |     Status:  new
 Priority:  medium   |  Milestone:  PostGIS 3.3.3
Component:  postgis  |    Version:  3.3.x
 Keywords:           |
---------------------+---------------------------
 PostGIS 3.3.2 on PostgreSQL 14.7

 Using ST_Buffer to create two "Circles" from arbitrary points on the
 globe, and then using ST_Union as an aggregate function to combine them
 will fail when the radius exceeds some value (slightly different depending
 on where the points are on the globe).

 I've tried to make these examples as simple as I can.

 For example, this case works:
 {{{
 -- radius <= 290085 good
 with kpc as (
     select
         ST_Buffer(
             ST_Point(-166.9324011::float8, 65.154889::float8,
 4326)::geography,
             290085::integer
         )::geometry as location
 ),
 tonga as (
     select
         ST_Buffer(
             ST_Point(-177.2519031::float8, -18.5973127::float8,
 4326)::geography,
             290085::integer
         )::geometry as location
 ),
 all_circles as (
     select location from kpc
     union all
     select location from tonga
 ),
 joined_circles as (
     select
         ST_Union(location) as circle_pair
     from
         all_circles
 )
 select
     290085::integer as radius,
     ST_Distance(
         ST_Point(-166.9324011::float8, 65.154889::float8,
 4326)::geography,
         ST_Point(-177.2519031::float8, -18.5973127::float8,
 4326)::geography
     ) as kpc_tonga_distance,
     ST_AsEWKT(circle_pair)
 from
     joined_circles
 ;
 }}}

 However, if I increase the radius one more meter, it fails:
 {{{
 -- radius >= 290086 bad
 with kpc as (
     select
         ST_Buffer(
             ST_Point(-166.9324011::float8, 65.154889::float8,
 4326)::geography,
             290086::integer
         )::geometry as location
 ),
 tonga as (
     select
         ST_Buffer(
             ST_Point(-177.2519031::float8, -18.5973127::float8,
 4326)::geography,
             290086::integer
         )::geometry as location
 ),
 all_circles as (
     select location from kpc
     union all
     select location from tonga
 ),
 joined_circles as (
     select
         ST_Union(location) as circle_pair
     from
         all_circles
 )
 select
     290086::integer as radius,
     ST_Distance(
         ST_Point(-166.9324011::float8, 65.154889::float8,
 4326)::geography,
         ST_Point(-177.2519031::float8, -18.5973127::float8,
 4326)::geography
     ) as kpc_tonga_distance,
     ST_AsEWKT(circle_pair)
 from
     joined_circles
 ;
 }}}
 This is the error message:
 > psql:st_union_issue.sql:77: ERROR:  lwgeom_unaryunion_prec: GEOS Error:
 TopologyException: side location conflict at -174.563034509468
 -18.071220344085667. This can occur if the input geometry is invalid.


 Even though I know that ST_Buffer emitted valid geometries (I was able to
 check this with ST_IsValid), if I add a `ST_MakeValid` to the query that
 just failed, it works:

 {{{
 -- ST_MakeValid fixes it, even though the individual circles are valid
 with kpc as (
     select
         ST_Buffer(
             ST_Point(-166.9324011::float8, 65.154889::float8,
 4326)::geography,
             290086::integer
         )::geometry as location
 ),
 tonga as (
     select
         ST_Buffer(
             ST_Point(-177.2519031::float8, -18.5973127::float8,
 4326)::geography,
             290086::integer
         )::geometry as location
 ),
 all_circles as (
     select location from kpc
     union all
     select location from tonga
 ),
 joined_circles as (
     select
         ST_Union(ST_MakeValid(location)) as circle_pair
     from
         all_circles
 )
 select
     290086::integer as radius,
     ST_Distance(
         ST_Point(-166.9324011::float8, 65.154889::float8,
 4326)::geography,
         ST_Point(-177.2519031::float8, -18.5973127::float8,
 4326)::geography
     ) as kpc_tonga_distance,
     ST_AsEWKT(circle_pair)
 from
     joined_circles
 ;
 }}}

 Interestingly, if I don't use ST_Union as an Aggregate function, I don't
 need ST_MakeValid to get the joined circles to work.
 {{{
 -- non aggregate union fixes it
 with kpc as (
     select
         ST_Buffer(
             ST_Point(-166.9324011::float8, 65.154889::float8,
 4326)::geography,
             290086::integer
         )::geometry as location
 ),
 tonga as (
     select
         ST_Buffer(
             ST_Point(-177.2519031::float8, -18.5973127::float8,
 4326)::geography,
             290086::integer
         )::geometry as location
 ),
 joined_circles as (
     select
         ST_Union(kpc.location, tonga.location) as circle_pair
     from
         kpc, tonga
 )
 select
     290086::integer as radius,
     ST_Distance(
         ST_Point(-166.9324011::float8, 65.154889::float8,
 4326)::geography,
         ST_Point(-177.2519031::float8, -18.5973127::float8,
 4326)::geography
     ) as kpc_tonga_distance,
     ST_AsEWKT(circle_pair)
 from
     joined_circles
 ;
 }}}

 Note that the circles are not yet overlapping when they fail.  It also
 doesn't seem to matter if they cross the international date line.  Radius
 seems to be the key determinant as to when a joined pair of circles will
 start failing.

 It also doesn't matter how many segments I add to the circles.  The radius
 failing threshold for any two points remains the same.
-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/5392>
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.