问题描述
我在SQL Server 2008 R2中有一些表.
I have a some tables in SQL Server 2008 R2.
我想创建视图以将其发布在Geoserver上.但是我对几何有疑问.我有线段的几何形状.例如，该段来自同一行:
I want to create view to publish it on Geoserver. But I have a problem with geometry. I have a geometry of segments of line. For example this segments from same line:
0xE610000001040C000000F01472A59E5A524032C7F2AE7AAA4E40AC1E300F995A52403C889D2974AA4E408C666
5FB905A5240213F1BB96EAA4E406A12BC218D5A5240B7F0BC546CAA4E404A404CC2855A5240ACAA97DF69AA4E40
4E2A1A6B7F5A5240D7A546E867AA4E402B85402E715A5240BAC0E5B166AA4E400EA0DFF76F5A52405AB8ACC266A
A4E402E3883BF5F5A5240D7A546E867AA4E403AE63C635F5A5240D7A546E867AA4E40043A9336555A5240D2393F
C571AA4E405CCCCF0D4D5A5240A75B76887FAA4E4001000000010000000001000000FFFFFFFF0000000002
0xE610000001040A000000F01472A59E5A524032C7F2AE7AAA4E4011C47938815A52401DE4F56052AA4E40145D1
77E705A52401364045438AA4E404B00FE29555A5240DE74CB0EF1A94E4059130B7C455A5240FED2A23EC9A94E40
C9B08A37325A52407444BE4BA9A94E402B323A20095A524049D576137CA94E408B1A4CC3F0595240342C465D6BA
94E40E0F3C308E1595240F487669E5CA94E40B169A510C8595240CBBA7F2C44A94E400100000001000000000100
0000FFFFFFFF0000000002
要选择细分，我使用此查询:
To select segments i use this query:
SELECT
Segment_ID = Segment_ID.Segment_ID,
GEOMETRY::STGeomFromText(Track.Track.STAsText(),4326) as the_geom,
Road.Name as Road_Name,
Road.Road_ID as Road_Id
FROM dbo.Road
LEFT JOIN Segment_ID ON Road.Road_ID = Segment_ID.Road_ID
LEFT JOIN Track ON Segment_ID.Segment_ID = Track.Segment_ID
是否可以在单个查询中将细分合并到一个功能中?
Is it possible to join segments in one feature in single query?
更新
我发现了函数 STUnion ，但不知道如何使用它可以在单个查询中加入细分.
I found function STUnion but don't know how to use it to join segments in single query.
UPDATE2
我想我要谈谈表的结构.我有3张桌子:
I think i gonna talk about tables structure. I have 3 tables:
跟踪
Segment_ID(character),
Track (geometry)
Segment_ID
Segment_ID
Segment_ID(character),
Road_ID(character)
路
Road_ID(character),
Road_Name(character)
在查询结果中，我想获取Road_Name以及此路段的单个要素中连接的所有路段.
但是仍然不能写不能查询(
And in query result i eant to get Road_Name and all segment connected in single feature for this Road.
But still cant write cant query(
UPDATE3
解决方案虽然可行，但需要花费很多时间.我的意思是，当我按执行查询时，我会在2秒内得到结果，但查询仍然可以在5分钟左右工作. 有可能使其更快. Bercouse我从该查询创建视图并将其添加到Geoserver，但是由于获取超时错误，因此无法在图层预览"中获取图层.
Solution in answer works but its take alot of time. I mean when i press execute query i get result within 2seconds but query still work about 5 minutes. Its possible to make it faster. Bercouse i create view from this query and add it to Geoserver but i cant get layer in Layers Preview becouse get Timeout error.
我看到了一些有趣的东西.在表Road中，我有76行.当我按执行查询时，我得到75行，而5分钟后，我得到所有76行.所以最后一次迭代中的问题.有什么问题吗?
I see something interesting. In table Road i have 76 rows. When i press execute query i get 75 rows and after 5 minutes i get all 76 rows. So problem in last iteration. What can be wrong?
推荐答案
STUnion确实是您得救的道路(ha！). STUnion是一种处理几何和地理CLR类型的方法.您通常将其作为gInstance.STUnion(othergInstance)调用.也就是说，假设您的上面的查询生成了适当的几何实例，则此递归CTE解决方案应该可以工作:
STUnion is indeed your road (ha!) to salvation. STUnion is a method on the geometry and geography CLR types. You'd typically invoke it as gInstance.STUnion(othergInstance). That said, assuming that your query above generates the appropriate geometry instances, this recursive CTE solution should work:
with by_segment as (
SELECT
Road.Road_Id,
GEOMETRY::STGeomFromText(Track.Track.STAsText(),4326) as the_geom,
row_number() over (partition by Road.road_id order by Road.Segment_Id) as [rn],
count(*) over (partition by Road.road_id) as [c]
FROM dbo.Road
LEFT JOIN Segment_ID ON Road.Road_ID = Segment_ID.Road_ID
LEFT JOIN Track ON Segment_ID.Segment_ID = Track.Segment_ID
),
roads_by_segment as (
select
road_id,
the_geom,
[rn],
[c]
from by_segment
where [rn] = 1
union all
select
[a].road_id,
[a].the_geom.STUnion([b].the_geom),
[b].[rn],
[b].[c]
from by_segment as [a]
inner join roads_by_segment as [b]
on [a].Road_ID = b.Road_ID
and [a].[rn] = [b].[rn]+1
)
select * from roads_by_segment where [rn] = [c]
这篇关于如何在SQL Server的单个功能中连接线?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！