用例1:
DECLARE @Geom TABLE
(
shape geometry,
shapeType nvarchar(50)
);
INSERT INTO @Geom(shape,shapeType)
VALUES('LINESTRING(1 2, 3 4)', 'A'),
('LINESTRING(3.2 4, 7 8)', 'B');
SELECT *
FROM @Geom
SELECT geometry::UnionAggregate(shape).ToString(), geometry::UnionAggregate(shape)
FROM @Geom;
输出的WKT为
MULTILINESTRING ((7 8, 3.2 4), (3 4, 1 2))当我想要的时候
MULTILINESTRING ((1 2, 3 4), (3.2 4, 7 8))“A”和“B”行的开头应分别为
(1 2)和(3.2 4)。UnionAggregate的这种行为似乎并不在乎几何的“方向”，以维持A联合B和B联合A是相同的结果。但是，我要在合并街道几何图形时保留起点/终点，并且希望所有LINESTRING沿其原始方向移动。这里讨论这个问题:https://social.msdn.microsoft.com/Forums/sqlserver/en-US/89e95366-3649-4294-a0bc-f3921598157f/union-of-linestrings-and-reversing-direction?forum=sqlspatial
他们似乎提出了关于检查最终结果的可能解决方案，但是我不清楚如何做到这一点。链接线程中暗示
我不清楚这到底是什么意思，但是我认为我不能认为UnionAggregate的结果总是与我想要的相反
如果很难了解方向意图，则可以在方向应遵循增加的M值的地方添加M个度量。
假设我有一种方法可以逆转直线上的点，该如何解决呢？
我发现了一个模仿
STUnion的函数，以增加对Z和M度量的支持:http://www.spatialdbadvisor.com/files/SQLServer.html#robo48，但是要注意的是“它们的方向可能会改变(例如，起点/起点关系)。”，这是我要避免的。编辑:
我还需要的功能是，当LINESTRING具有共享端点时，结果是连接LINESTRING
用例2:
DECLARE @Geom TABLE
(
shape geometry,
shapeType nvarchar(50)
);
INSERT INTO @Geom(shape,shapeType)
VALUES('LINESTRING(1 2, 3 4)', 'A'),
('LINESTRING(3 4, 7 8)', 'B');
SELECT *
FROM @Geom
SELECT geometry::UnionAggregate(shape).ToString(), geometry::UnionAggregate(shape)
FROM @Geom;
结果为WKT
LINESTRING (7 8, 3 4, 1 2)当我想要的时候
LINESTRING (1 2, 3 4, 7 8)尝试解决方案
Clay建议的
geometry::CollectionAggregate(shape).Reduce(0)解决了用例1。我尝试对行结果使用STUnion并使用空的线串，并且在工作时它会退回到不正确的顺序。我怀疑解决方案将是类似于ST_LineMerge的缩放器函数，该函数使用CollectionAggregate(MULTILINESTRING)的结果，然后在可以合并为一个LINESTRING且无法将几何返回不改变的情况下将这些点合并到一起
最佳答案
最初，我建议...
DECLARE @Geom TABLE
(
shape geometry,
shapeType nvarchar(50)
);
INSERT @Geom(shape,shapeType) VALUES
('LINESTRING(1 2, 3 4)', 'A'),
('LINESTRING(3.2 4, 7 8)', 'B');
SELECT * FROM @Geom
SELECT
geometry::CollectionAggregate(shape).Reduce(0).ToString(),
geometry::CollectionAggregate(shape).Reduce(0)
FROM @Geom
你得到:
...但是，我很清楚我给出的答案还不够好。例如，很难避免
Reduce()简化部分代码，我仍然喜欢CollectionAggregate，可以将原始的线阵列整合到一件事中，但是后来我发现，必须有一种构建必要的几何结构的方法。
我玩了几次，根据输入中是否存在不相交的
LineString元素，此迭代将评估为MultiLineString或LineString:create function dbo.SimplifyToLine( @geo geometry ) returns geometry as
begin
declare
@numSubGeos int = @geo.STNumGeometries(),
@subGeoIdx int = 1,
@sql nvarchar( max ) = N'',
@subGeo geometry,
@oldEndX float = -1.0e26,
@oldEndY float = -1.0e26,
@startX float,
@startY float,
@endX float,
@endY float,
@idx int,
@numPoints int,
@point geometry,
@segment int = 1,
@continue bit,
@result geometry,
@started bit = 0
declare
@geos table
(
Idx int primary key,
SubGeo geometry,
StartX decimal,
EndX decimal,
StartY decimal,
EndY decimal,
NumPoints int,
ContinueFromPrevious bit
)
declare
@multiLines table
(
Idx int primary key,
Segment nvarchar(max)
)
--> collect geometries and extents...
while ( @subGeoIdx <= @numSubGeos )
begin
select @subGeo = @geo.STGeometryN( @subGeoIdx )
select
@startX = @subGeo.STPointN( 1 ).STX,
@startY = @subGeo.STPointN( 1 ).STY,
@endX = @subGeo.STPointN( @subGeo.STNumPoints( ) ).STX,
@endY = @subGeo.STPointN( @subGeo.STNumPoints( ) ).STY
insert @geos values
(
@subGeoIdx,
@subGeo,
@startX,
@endX,
@startY,
@endY,
@subGeo.STNumPoints() ,
case when @subGeoIdx = 1 then 1 when @oldEndX = @startX and @oldEndY = @startY then 1 else 0 end
)
select
@oldEndX = @endX,
@oldEndY = @endY,
@subGeoIdx = @subGeoIdx + 1
end
if not exists ( select * from @geos where ContinueFromPrevious = 0 ) --> then all LineStrings are connected
begin
--> build a single LINESTRING( )...
select @sql = ''
declare c cursor for select SubGeo, StartX, EndX, StartY, EndY, NumPoints, ContinueFromPrevious from @geos order by Idx
open c
while ( 1 = 1 )
begin
fetch next from c into @subGeo, @startX, @endX, @startY, @endY, @numPoints, @continue
if @@fetch_status != 0 break;
select @idx = case when @started = 0 then 1 else 2 end, @started = 1 --> accrue all points, de-duplicating line ends...
while ( @idx <= @numPoints )
begin
select @point = @subGeo.STPointN( @idx )
select @sql += convert( nvarchar, @point.STX ) + N' ' + convert( nvarchar, @point.STY ) + N','
select @idx = @idx + 1
end
end
close c
deallocate c
select @sql = substring( @sql, 1, len( @sql ) -1 )
select @result = geometry::STGeomFromText(N'LINESTRING(' + @sql + N')', 0 )
end
else --> we have disjoint lines in the inputs...
begin
select @sql = N'', @started = 0
--> build a MULTILINESTRING((),()...) with line segements terminated at disjoint points..
declare c cursor for select SubGeo, StartX, EndX, StartY, EndY, NumPoints, ContinueFromPrevious from @geos order by Idx
open c
while ( 1=1 )
begin
fetch next from c into @subGeo, @startX, @endX, @startY, @endY, @numPoints, @continue
if @@fetch_status != 0 break;
if @continue = 1
begin
select @idx = case when @started = 0 then 1 else 2 end, @started = 1
while ( @idx <= @numPoints )
begin
select @point = @subGeo.STPointN( @idx )
select @sql += convert( nvarchar, @point.STX ) + N' ' + convert( nvarchar, @point.STY ) + N','
select @idx = @idx + 1
end
end
else
begin
insert @multiLines values ( @segment, substring( @sql, 1, len( @sql ) -1 ) ) --> collect the segment
select @idx = 1, @sql = N'', @segment = @segment + 1
while ( @idx <= @numPoints )
begin
select @point = @subGeo.STPointN( @idx )
select @sql += convert( nvarchar, @point.STX ) + N' ' + convert( nvarchar, @point.STY ) + N','
select @idx = @idx + 1
end
end
end
close c
deallocate c
insert @multiLines values ( @segment, substring( @sql, 1, len( @sql ) -1 ) )
select @sql = N''
select @sql += N'(' + Segment + N'),' from @multiLines order by Idx --> appends all segments
select @sql = substring( @sql, 1, len( @sql ) -1 )
select @result = geometry::STGeomFromText( 'MULTILINESTRING('+ @sql + N')', 1 )
end
...最后，鉴于:
DECLARE @Geom TABLE
(
shape geometry,
shapeType nvarchar(50)
);
INSERT @Geom(shape,shapeType) VALUES
('LINESTRING(1 2, 3 4)', 'A'),
('LINESTRING(3 4, 9 9)', 'B'), --> disjoint from here to the next LINESTRING
('LINESTRING(9 8, 3 4)', 'C'),
('LINESTRING(3 4, 1 2)', 'D');
select
dbo.SimplifyToLine(geometry::CollectionAggregate(shape)).ToString(),
dbo.SimplifyToLine(geometry::CollectionAggregate(shape))
from
@Geom
delete @Geom
INSERT @Geom(shape,shapeType) VALUES
('LINESTRING(1 2, 3 4)', 'A'),
('LINESTRING(3 4, 9 8)', 'B'),
('LINESTRING(9 8, 3 4)', 'C'),
('LINESTRING(3 4, 1 2)', 'D');
select
dbo.SimplifyToLine(geometry::CollectionAggregate(shape)).ToString(),
dbo.SimplifyToLine(geometry::CollectionAggregate(shape))
from
@Geom
...你得到:
关于sql-server - 在UnionAggregate中保留起点，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54740572/