4

I'm using SQL Server 2008 and the Geometry datatype to store a list of UK a roads, which I've imported from the Ordanance Survey STRATEGI data set.

Each road is split into multiple rows, which each contain a single line (A Linestring made up of one segment). For instance the A369 is made up of 18 seperate lines, as shown in the image below:

Screen capture of current linestrings

What I'd like to do it collect all the seperate rows containing portions of a road and create a new row which holds all the combined individual lines as one linestring.

In other words running the code SELECT * FROM Structure WHERE Name = 'A369' would return only one row, but still drawn the road seen in the image above.

Peter Bridger
  • 9,123
  • 14
  • 57
  • 89
  • 2
    There is a discussion about this here: http://stackoverflow.com/questions/3293190/union-all-geometry-in-a-sql-server-table-like-geomunion-in-postgres – Mikpa Nov 22 '11 at 12:56
  • Been looking for this too but I can't see any other (good) way that adding a CLR function. – Asken Nov 23 '11 at 08:58

2 Answers2

7

Just use .STUnion

BEGIN
-- create a test table
DECLARE @test TABLE(seg GEOMETRY);
INSERT INTO @test VALUES(geometry::STGeomFromText('LINESTRING (0 0, 50 100)', 0))
INSERT INTO @test VALUES(geometry::STGeomFromText('LINESTRING (50 100, 100 200)', 0))
INSERT INTO @test VALUES(geometry::STGeomFromText('LINESTRING (100 200, 150 300)', 0))
--SELECT seg.STAsText() FROM @test
DECLARE @geom GEOMETRY
SELECT @geom = (SELECT TOP 1 seg FROM @test)
-- union all the linestring points
SELECT @geom = @geom.STUnion([seg]) FROM @test
-- do what you want with the results
SELECT @geom
print(@geom.STAsText())
END
Louis Ricci
  • 20,804
  • 5
  • 48
  • 62
3

In SQL 2012 you can use UnionAggregate

SELECT geometry::UnionAggregate(shape) FROM Table

or if you have a geography column

SELECT geography ::UnionAggregate(shape) FROM Table
gls123
  • 5,467
  • 2
  • 28
  • 28