Сохранить стартовые точки в UnionAggregate

Типы геометрии не записывают/кодируют направленность. Линии, которые вы ему даете, могут считаться «ненаправленными» или «двунаправленными». Это возвращает 1:

select geometry::STGeomFromText('LINESTRING(1 2, 3 4)',0).STEquals(
       geometry::STGeomFromText('LINESTRING(3 4, 1 2)',0))

Итак, то, что вы ищете, недоступно с использованием этих типов. Вы считаете «точки старта» особенными. Я предлагаю вам отдельно записать их как отдельные POINTs.

Тем не менее, это делает весь результирующий код более уродливым — вы должны обрабатывать эти пары данных вместе:

DECLARE @Geom TABLE 
(
   start geometry, 
   shape geometry, 
   shapeType nvarchar(50) 
); 

INSERT INTO @Geom(start,shape,shapeType) 
VALUES('POINT(1 2)','LINESTRING(1 2, 3 4)', 'A'), 
('POINT(3.2 4)','LINESTRING(3.2 4, 7 8)', 'B'); 

SELECT *
FROM @Geom

SELECT
    geometry::UnionAggregate(start).ToString(), geometry::UnionAggregate(shape).ToString(),
    geometry::UnionAggregate(start), geometry::UnionAggregate(shape)
FROM @Geom;

На этом этапе вы мая решаете прекратить прямое использование типа geography — вы можете создать CLR UDT, который ссылается на SqlGeography (поверхность CLR того же типа) и использует его внутри, но также отслеживает его «направленность», все вместе, и начните использовать это вместо этого.

Однако вряд ли вы захотите раскрыть все методы geography в этой обертке — вам придется выбирать свои сражения. И, конечно же, поскольку в ваших результатах появляется не SQL Server geography, вы не сможете воспользоваться вкладкой «Пространственные результаты» в Management Studio.

Единственное место, где, как мне кажется, существует некоторая «направленность» в этих типах, — это правило левой руки для устранения неоднозначности форм geography.

Изначально я предложил...

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

...ты получаешь:

Отталкиваясь от идеи Клея о передаче GeometryCollection, я реализовал надежную версию, которая будет принимать любую комбинацию POINT, MULTIPOINT, LINESTRING, MULTILINESTRING и удалять любые соприкасающиеся конечные точки внутри @Tolerance и создавать POINT, LINESTRING, MULTILINESTRING

Вот демонстрация того, как это работает (обратите внимание, как допуск 0 и 0,1 влияет на 2-й и 3-й выход):

DECLARE @GeometryCollection GEOMETRY = GEOMETRY::STGeomFromText('GEOMETRYCOLLECTION (LINESTRING (1 2, 3 4), LINESTRING (3 4, 100 100), LINESTRING (9 8, 3 4), LINESTRING (3 4, 1 2), POINT(1 2), POINT(1 2), POINT(1 2))',0)
SELECT [dbo].[fnSimplifyToLine](@GeometryCollection, 0).ToString();
--Output: MULTILINESTRING ((1 2, 3 4, 100 100), (9 8, 3 4, 1 2))

SET @GeometryCollection  = GEOMETRY::STGeomFromText('GEOMETRYCOLLECTION (LINESTRING (1 2, 3 4.1), LINESTRING (3 4, 9 9, 6 1))',0)
SELECT [dbo].[fnSimplifyToLine](@GeometryCollection, 0).ToString()
--Output: MULTILINESTRING ((1 2, 3 4.1), (3 4, 9 9, 6 1))

SET @GeometryCollection  = GEOMETRY::STGeomFromText('GEOMETRYCOLLECTION (LINESTRING (1 2, 3 4.1), LINESTRING (3 4, 9 9, 6 1))',0)
SELECT [dbo].[fnSimplifyToLine](@GeometryCollection, 0.1).ToString()
--Output: LINESTRING (1 2, 3 4.1, 9 9, 6 1)

SET @GeometryCollection  = GEOMETRY::STGeomFromText('GEOMETRYCOLLECTION (POINT(1 2))',0)
SELECT [dbo].[fnSimplifyToLine](@GeometryCollection, 0).ToString()
--Output: POINT (1 2)

SET @GeometryCollection  = GEOMETRY::STGeomFromText('GEOMETRYCOLLECTION (MULTIPOINT((1 2), (2 3)))',0)
SELECT [dbo].[fnSimplifyToLine](@GeometryCollection, 0).ToString()
--Output:  (1 2, 2 3)

Сначала мне пришлось создать рекурсивную функцию CTE, которая берет геометрию и извлекает все точки.

CREATE FUNCTION [dbo].[fnGetPoints]
(   
    @Geometry GEOMETRY
)
RETURNS TABLE 
AS
RETURN 
(

    WITH GeometryPoints(N, Point) AS ( 
        SELECT 
            CAST(1 AS DECIMAL(9,2)) as N
            ,@Geometry.STPointN(1) as Point
        UNION ALL
        SELECT 
            CAST(N + 1.0 AS DECIMAL(9,2)) as N
            ,@Geometry.STPointN(N + 1) as Point
        FROM GeometryPoints GP
        WHERE N < @Geometry.STNumPoints()  
    )

    SELECT *
    FROM GeometryPoints
)

Затем я создал функцию, которая ПРИМЕНЯЕТ fnGetPoints к каждой геометрии в @GeometryCollection, чтобы получить матрицу точек. Использование оконной функции (LAG), чтобы найти места, где конечные точки находятся внутри @Tolerance, и удалить эти точки. Затем я сделал размазывание данных, чтобы объединить геометрии, где они имеют общие конечные точки.

CREATE FUNCTION [dbo].[fnSimplifyToLine] (@GeometryCollection GEOMETRY, @Tolerance DECIMAL(19,10))
RETURNS GEOMETRY
AS
BEGIN
    DECLARE @PointMatrix TABLE (
        PointId INT,
        LinestringId INT,
        GeometryIndex INT,
        GeometryType varchar(100),
        PointIndex INT,
        Point GEOMETRY,
        Duplicate BIT
    );

    DECLARE @Linestrings TABLE (
        LinestringId INT,
        PointArrayStr varchar(max)
    );

    WITH CollectionGeometries(N, Geom) AS ( 
        SELECT 
            CAST(1 AS DECIMAL(9,2)) as N
            ,@GeometryCollection.STGeometryN(1) as Geom
        UNION ALL
        SELECT 
            CAST(N + 1.0 AS DECIMAL(9,2)) as N
            , @GeometryCollection.STGeometryN(N + 1) as Geom
        FROM CollectionGeometries CG
        WHERE N < @GeometryCollection.STNumGeometries()
    ), PointMatrix AS (
        SELECT 
            ROW_NUMBER() OVER(ORDER BY G.N, P.N) as PointId
            ,G.N as GeometryIndex
            ,G.Geom.STGeometryType() as GeometryType
            ,P.N as PointIndex
            ,P.Point
        FROM CollectionGeometries G
        CROSS APPLY dbo.fnGetPoints(Geom) P
    )

    INSERT INTO @PointMatrix
    SELECT 
        PointId
        ,GeometryIndex as LinestringId
        ,GeometryIndex
        ,GeometryType
        ,PointIndex
        ,Point
        ,CASE 
            WHEN 
                GeometryIndex != LAG(GeometryIndex) OVER(ORDER BY PointId)
                AND ABS(Point.STX - LAG(Point.STX) OVER(ORDER BY PointId)) <= @Tolerance
                AND ABS(Point.STY - LAG(Point.STY) OVER(ORDER BY PointId)) <= @Tolerance
            THEN 1
            ELSE 0
        END as Duplicate
    FROM PointMatrix
    OPTION (MAXRECURSION 10000)



    -- POLYGON, MULTIPOLYGON, GEOMETRYCOLLECTION, CIRCULARSTRING, COMPOUNDCURVE, CURVEPOLYGON not supported
    IF EXISTS ( SELECT * FROM @PointMatrix WHERE GeometryType NOT IN ('POINT', 'MULTIPOINT', 'LINESTRING', 'MULTILINESTRING'))
        RETURN CAST('Geometries in @GeometryCollection must all be IN (''POINT'',''MULTIPOINT'', ''LINESTRING'', ''MULTILINESTRING'')' as GEOMETRY);

    DECLARE @SRID INT = (SELECT DISTINCT Point.STSrid FROM @PointMatrix)

    UPDATE @PointMatrix
    SET LinestringId = NULL
    WHERE GeometryIndex IN (
        SELECT GeometryIndex FROM @PointMatrix WHERE Duplicate = 1
    )

    DELETE @PointMatrix
    WHERE Duplicate = 1;

    -- Data smear
    WITH Cnt AS (
        SELECT PointId, Point, LinestringId,c=COUNT(LinestringId) OVER (ORDER BY PointId)
        FROM @PointMatrix
    ), SmearedLineStringId AS (
        SELECT PointId, Point, LinestringId=MAX(LinestringId) OVER (PARTITION BY c)
        FROM Cnt
    )

    INSERT @Linestrings
    SELECT 
        LinestringId
        ,'(' + 
                STUFF((
                    SELECT ',' + CAST(Point.STX as varchar(100)) + ' ' + CAST(Point.STY as varchar(100))
                    FROM SmearedLineStringId t2
                    WHERE t1.LinestringId = t2.LinestringId 
                    ORDER BY PointId
                    FOR XML PATH ('')
                ), 1, 1, '')
        + ')' as PointArray
    FROM SmearedLineStringId t1
    GROUP BY LinestringId

    DECLARE @Type varchar(100) = CASE 
        WHEN 1 =(SELECT COUNT(*) FROM @PointMatrix) THEN
            'POINT'
        WHEN 1 =(SELECT COUNT(*) FROM @Linestrings) THEN
            'LINESTRING'
        ELSE
            'MULTILINESTRING'
    END

    DECLARE @BeginParens char(1) = '(';
    DECLARE @EndParens char(1) = ')'

    IF @Type != 'MULTILINESTRING'
    BEGIN
        SET @BeginParens = '';
        SET @EndParens = '';
    END

    DECLARE @Wkt varchar(max) = @Type + @BeginParens + 
                STUFF((
                    SELECT ',' + PointArrayStr
                    FROM @Linestrings t2
                    ORDER BY LinestringId
                    FOR XML PATH ('')
                ), 1, 1, '')
            + @EndParens

    RETURN Geometry::STGeomFromText(@Wkt, @SRID)

END
GO