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

March 31, 2022

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?

>Solution :

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))
)