Reputation: 8609

SQL: Union of polygons

I have a table with a single column of geometry type, containing polygons. How do I get the union of all the polygons in the table?

Upvotes: 4

Answers (4)

Alastair Aitchison

Reputation: 3532

In SQL Server 2012:

SELECT geometry::UnionAggregate(geomcolumn) FROM YourTable;

In SQL Server 2008/R2:

DECLARE @g = geometry::STGeomFromText('GEOMETRYCOLLECTION EMPTY', YourSRID);
SELECT @g = @g.STUnion(geomcolum) FROM YourTable;

Upvotes: 14

Ben Thul

Reputation: 32687

This worked for me:

CREATE TABLE #g (i INT IDENTITY, a geometry)
INSERT INTO #g (a)
VALUES
    (geometry::STGeomFromText(
        'POLYGON((0 0, 3 0, 3 3, 0 3, 0 0))', 0)
    ),
    (geometry::STGeomFromText(
        'POLYGON((5 2, 7 2, 7 0, 5 0, 5 2))', 0)
    )

DECLARE @g geometry
SELECT TOP 1 @g = a FROM [#g]
SELECT @g = @g.STUnion(a) FROM #g

SELECT @g

So, apparently, the STUnion method returns null when either the instance on which it's being called or the operand is null, hence the select top 1 hack.

Upvotes: 4