最初，我建议...
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)
)
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 )
begin
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
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
begin
select @sql = N'', @started = 0
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 ) )
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
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'),
('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
...你得到：
(0,0)。因此，结果首先显示了(7,8)，它比(3.2,4)更远离(0,0)。结果首先显示了(3,4)，因为它比(1,2)更远离(0,0)。因此，结果不会颠倒原始方向，也不是随机的，而是明确定义的。 - Vladimir Baranov