Thursday, 27 December 2007

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)

7 comments:

  1. Great work.

    Regarding the unexpected lines, I suspect it is from a rounding issue in STUnion. This causes all sorts of problems.

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=379953&wa=wsignin1.0

    ReplyDelete
  2. Thanks Rick,
    wow it's funny when you read stuff that you forget writing! those lines were actually *my* bug - i wasn't "lifting my pen" between drawing the outer and inner 'geometries'. This was fixed in a later version of Geoquery 2008

    ReplyDelete
  3. i'm getting the following error when running a group by query

    ReplyDelete
  4. Msg 6522, Level 16, State 1, Line 1
    A .NET Framework error occurred during execution of user-defined routine or aggregate "UnionAggregate":
    System.ArgumentException: 24144: This operation cannot be completed because the instance is not valid. Use MakeValid to convert the instance to a valid instance. Note that MakeValid may cause the points of a geometry instance to shift slightly.
    System.ArgumentException:
    at Microsoft.SqlServer.Types.SqlGeometry.ThrowIfInvalid()
    at Microsoft.SqlServer.Types.SqlGeometry.STUnion(SqlGeometry other)
    at SilverlightEarth.Geoquery.SqlClr.UnionAggregate.Accumulate(SqlGeometry value)

    ReplyDelete
  5. fixed it by applying the MakeValid() function to the geom column:

    select dbo.UnionAggregate(geom.MakeValid())
    from county
    groupby stateid

    ReplyDelete
  6. Thanks a lot, this really saved a lot of pain. I was trying to use the SQLSpatialToll from Codeplex but that somehow didnt meet my requirement for geometry data type. This did though! You are a life saver :)

    ReplyDelete
  7. This comment has been removed by the author.

    ReplyDelete