How To Combine Multiple Linestring Rows Into A Single Row Collection

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

Solution 1:

Just use .STUnion

BEGIN-- create a test tableDECLARE@testTABLE(seg GEOMETRY);
INSERTINTO@testVALUES(geometry::STGeomFromText('LINESTRING (0 0, 50 100)', 0))
INSERTINTO@testVALUES(geometry::STGeomFromText('LINESTRING (50 100, 100 200)', 0))
INSERTINTO@testVALUES(geometry::STGeomFromText('LINESTRING (100 200, 150 300)', 0))
--SELECT seg.STAsText() FROM @testDECLARE@geom GEOMETRY
SELECT@geom= (SELECT TOP 1 seg FROM@test)
-- union all the linestring pointsSELECT@geom=@geom.STUnion([seg]) FROM@test-- do what you want with the resultsSELECT@geom
print(@geom.STAsText())
END

Solution 2:

In SQL 2012 you can use UnionAggregate

SELECT geometry::UnionAggregate(shape) FROMTable

or if you have a geography column

SELECT geography ::UnionAggregate(shape) FROMTable