如何将多个LineString行合并为一个行集合 [英] How to combine multiple LineString rows into a single row collection
问题描述
我正在使用SQL Server 2008和Geometry
数据类型来存储从
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.
每条道路分为多行,每行包含一行(由一个路段组成的Linestring
).例如,A369由18条单独的线组成,如下图所示:
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:
我要执行的操作是收集包含道路部分的所有单独行,并创建一个新行,其中将所有组合的单独行作为一个线串保存.
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.
换句话说,运行代码SELECT * FROM Structure WHERE Name = 'A369'
将仅返回一行,但仍会绘制上图所示的道路.
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.
推荐答案
只需使用.STUnion
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
这篇关于如何将多个LineString行合并为一个行集合的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!