Using the Denali spatial aggregates on the client

One of the interesting spatial features in SQL Server Denali is the inclusion of spatial aggregates, namely CollectionAggregate, ConvexHullAggregate, EnvelopeAggregate, and UnionAggregate. Here's an example of unioning two squares together to make a rectangle.

create table t1 (g geometry)

insert t1 values('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))')
insert t1 values('POLYGON((1 0, 2 0, 2 1, 1 1, 1 0))')

select geometry::UnionAggregate(g) from t1

Beside being useful, they are interesting because its the first use of SQLCLR-based aggregates (that I'm aware of) as part of the SQL Server database engine code itself.

The what's new spatial whitepaper has an interesting comment about the spatial aggregates: "The new aggregates are exposed in SQL Server only and are not exposed in the underlying spatial library". I asked (spatial) Ed about this and pointed out that you can use the spatial aggregates (or any .NET-based user-defined aggregate, for that matter) on the client as well as the server. You just need to find the correct class in the library in the spatial library, which is public (and hopefully it will stay public in the released version). Here's the same aggregation in client-side code.

//collection of SqlGeometry
List<SqlGeometry> glist = new List<SqlGeometry>();
glist.Add(SqlGeometry.Parse("POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))"));
glist.Add(SqlGeometry.Parse("POLYGON((1 0, 2 0, 2 1, 1 1, 1 0))"));

GeometryUnionAggregate agg = new GeometryUnionAggregate();
// call the appropriate methods on the aggregate
foreach (SqlGeometry geom in glist)
SqlGeometry theanswer = agg.Terminate();
Console.WriteLine("answer is {0}", theanswer);

The only strange thing is that, in T-SQL, the aggregate appears to be a static property on the geometry class (you use geometry::UnionAggregate to invoke it in T-SQL). But using reflection on the SqlGeometry class in the library reveals no such public (or private) property. But that's fine; you CAN use the spatial aggregates directly; you just need to know the name of the aggregate class. Conceivably, you could even parallelize the client-side aggregation and call Merge() at the appropriate time.



Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.