Geek Answers Handbook

How to combine multiple LineString lines into a single line collection

I use SQL Server 2008 and the data type Geometryto store a list of UK roads that I imported from the STRATEGI Order Examination .

Each road is divided into several lines, each of which contains one line (A Linestring, consisting of one segment). For example, the A369 consists of 18 separate lines, as shown in the figure below:

Screen capture of current linestrings

What I would like to do, it collects all the individual lines containing parts of the road, and create a new line that contains all the combined individual lines as a single line.

In other words, running the code SELECT * FROM Structure WHERE Name = 'A369'will only return one line, but still draws the road shown in the image above.

+4
source share
2 answers

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
+6
source

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
+2
source

More articles:


All Articles