Is there an Aggregate function that functions similarly to STUnion()?

Question

I see that there is something called STUnion() which will union the result of one geography type with another. But is is possible to do that over an entire data set like an aggregate function?

Or if not, is there a performant equivalent?

Explain what you expect to get when you *sum* up five values of type `geometry` or `geography` ...... — marc_s, Mar 31 '22 at 20:15
There's no such thing. What would you expect to get from adding the coordinates of Paris and London? — Panagiotis Kanavos, Mar 31 '22 at 20:16
You're asking about *sum*. That reduces multiple numbers to a single one by adding them. What do you expect that operation to produce on shapes? *Combining* shapes into one is what UNION does. In any case, you'll find all supported operations [in the docs](There's no such thing. What would you expect to get from adding the coordinates of Paris and London? You can find all supported operations [in the docs](https://learn.microsoft.com/en-us/sql/t-sql/spatial-geometry/ogc-methods-on-geometry-instances?view=sql-server-ver15).) — Panagiotis Kanavos, Mar 31 '22 at 20:23
That's not what you asked though. You asked about completely unrelated. Edit the question to ask what you really want — Panagiotis Kanavos, Mar 31 '22 at 20:25

score 3 · Accepted Answer · answered Mar 31 '22 at 20:28

SQL Server offers some aggregate methods on geometries, including UnionAggregate and CollectionAggregate that operate on more than 2 shapes.

From the UnionAggregate example :

-- Setup table variable for UnionAggregate example 
DECLARE @Geom TABLE 
( 
shape geometry, 
shapeType nvarchar(50) 
);
 
INSERT INTO @Geom(shape,shapeType) 
VALUES
('CURVEPOLYGON(CIRCULARSTRING(2 3, 4 1, 6 3, 4 5, 2 3))', 'Circle'), 
('POLYGON((1 1, 4 1, 4 5, 1 5, 1 1))', 'Rectangle'); 

-- Perform UnionAggregate on @Geom.shape column 
SELECT geometry::UnionAggregate(shape).ToString() 
FROM @Geom;

This produces

CURVEPOLYGON (COMPOUNDCURVE (
  (1 1, 4 1, 4.0000000000000071 1.0000000000000218), 
  CIRCULARSTRING (4.0000000000000071 1.0000000000000218, 
    5.4142135623730905 1.5857864376269268,
    6 3, 
    5.4142135623730905 4.4142135623730905, 
    4.0000000000000071 4.9999999999999947), 
  (4.0000000000000071 4.9999999999999947, 1 5, 1 1))
)

Is there an Aggregate function that functions similarly to STUnion()?

1 Answers1