Union all geometry in a SQL Server table like GeomUnion in Postgres

Question

Just to clarify up-front: I'm talking about unioning geometry, not the SQL keyword UNION.

I'm trying to move some spatial data from Postgres with PostGIS to SQL Server 2008. It was fine until I saw a statement like this:

SELECT GeomUnion(the_geom) FROM some_table

This unions all geometry in that column and return it as one result (similar to how COUNT works). As far I know, SQL Server only has the STUnion function, which unions one geometry with another. Is there any way to do something similar to the Postgres way?

If it helps, the STUnion function works like this:

SELECT first_geometry_column.STUnion(second_geometry_column) FROM some_table

Melanous

Disclaimer: I haven't worked with spatial data :). Can you not use Common table expression and STUnion in combination to get the desired GeomUnion functionality ? – Hellenistic

Answer 1

The way I ended up doing this is with variables:

DECLARE @Shape GEOMETRY
SET @Shape = GEOMETRY::STGeomFromText('GEOMETRYCOLLECTION EMPTY', @MySrid)

SELECT @Shape = @Shape.STUnion(Shape)
  FROM MyShapeTable

It's not as nice, but it works.

Melanous

And it gets really expensive for more than a handful of geometries. – Marvelous

Answer 2

Is the UnionAggregate function SQL2012 only?

SELECT geography::UnionAggregate( geometry ) FROM some_table

Hmm guess so. http://technet.microsoft.com/en-us/library/ff929095.aspx

Jeana

Thank you for the info. Saved a lot of time! FYI: For geometry data it is geometry::UnionAggregate(geometry). – Olmsted

This is absolutely the answer to the original question, for SQL 2012 and above. – Cardiograph

Answer 3

Your best option is to create a CLR function to support the aggregate. There are a couple of existing solutions:

• Sql spatial tools
• Spatail aggregate

Vernverna

You're correct - that's the best option for SQL 2008 but it's very slow and causes small geometry errors. Thankfully, SQL2012 is lightning fast and doesn't have geometry issues. MS have stated that they are not going to fix the geometry issues in SQL2008. – Sememe