SQL Server 2008 Geometry: STUnion Aggregate

There doesn't seem to be a lot of information about Geometry Aggregates, except for these posts on MSDN Forums, including How to calculate table all geometry extent?

Why might we want aggregate functions on the GEOMETRY spatial datatype? Here's an example of 'how to use' the STUnion() from MSDN:

DECLARE @g geometry;
DECLARE @h geometry;
SET @g = geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0);
SET @h = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STUnion(@h) -- Can only STUnion two Geometries

On the left are the two GEOMETRYs, on the right is the STUnion() result.



The problem with these simple examples is they don't scale well to 'real' spatial database queries where you need to use these functions across result-sets. For example, it would be much nicer if we could insert data as follows

INSERT INTO TestShapes VALUES (geometry::STGeomFromText('POLYGON((0 0, 0 2, 2 2, 2 0, 0 0))', 0))
INSERT INTO TestShapes VALUES (geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0))
-- Now 'Union' all the shapes
SELECT dbo.UnionAggregate(Shape) FROM TestShapes

On the left is the result of this new UnionAggregate query, on the left is the result after added another shape;
INSERT INTO TestShapes VALUES (geometry::STGeomFromText('POLYGON((3 3, 5 3, 5 5, 3 5, 3 3))', 0))
SELECT dbo.UnionAggregate(Shape) FROM TestShapes


DOWNLOAD the source (8k) to try out the two aggregates posted on MSDN, or follow these steps:

1. Download the code from MSDN, or my copy. NOTE: You must do all these steps on a PC with SQL Server 2008 (Katmai) installed to get the Microsoft.SqlServer.Types.dll assembly. I copied this assembly into C:\assemblies\.

2. Compile the C# SQLCLR functions in C:\assemblies\ via the Command Prompt

c:\windows\microsoft.net\framework\v3.5\csc /t:library /r:Microsoft.SqlServer.Types.dll C:\assemblies\aggregate.cs

3. Execute T-SQL to register the aggregate functions in SQL Server 2008
CREATE ASSEMBLY SpatialAggregate FROM 'c:\assemblies\Aggregate.dll'
go
CREATE AGGREGATE EnvelopeAggregate(@input GEOMETRY) RETURNS GEOMETRY
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.EnvelopeAggregate]
go
CREATE AGGREGATE UnionAggregate(@input GEOMETRY) RETURNS GEOMETRY
external name [SpatialAggregate].[SilverlightEarth.Geoquery.SqlClr.UnionAggregate]
go

sp_configure 'clr enabled', 1;
go
RECONFIGURE;
go

Here's another example of using the STUnion Aggregate, this time to outline continents on a GEOMETRY map. Note the thickness and color columns are a feature of Geoquery.

select continent, dbo.UnionAggregate(Location), 0.5 as thickness, 'Brown' as color
from countries2 where continent is not null and Location.STArea() > 40
group by continent
union all
select name, Location, 0.1 as thickness, 'Green' as color
from countries2

(Not sure why weird lines appear through some of the STUnion-ed geometries... something to investigate another day)