Сгруппируйте по значению и создайте географическую полилинию из точек (широта и долгота) для каждой группы в T-SQL

Question

Подобный вопрос был задан здесь:

Создание географической полилинии из точек в T-SQL

Продолжая этот вопрос, у меня есть схема таблицы, которая выглядит следующим образом:

CREATE TABLE [dbo].[LongAndLats](
[Longitude] [float] NULL,
[Latitude] [float] NULL,
[SortOrder] [bigint] NULL,
[SensorID] [bigint] NULL,
)

Пример данных выглядит так:

Как я могу преобразовать эти точки в географическую полилинию для каждого SensorID, используя TSQL (чтобы у меня была запись SensorID/Polyline для каждого SensorID)?

Я пытался использовать db_cursor, но я получаю отдельный набор результатов для каждой группы (и я думаю, что география может быть одинаковым). Этот код:

DECLARE @SensorID VARCHAR(2000)
DECLARE @LineFromPoints geography
DECLARE @BuildString NVARCHAR(MAX)

DECLARE db_cursor CURSOR FOR  
SELECT Distinct([SensorId]) 
FROM [dbo].[LongAndLats]

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO LongAndLats 

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SELECT @BuildString = COALESCE(@BuildString + ',', '') + CAST([Longitude] AS NVARCHAR(50)) + ' ' + CAST([Latitude] AS NVARCHAR(50))
       FROM [LongAndLats]
       WHERE SensorID = @SensorID
       ORDER BY SortOrder            

       SET @BuildString = 'LINESTRING(' + @BuildString + ')';   
       SET @LineFromPoints = geography::STLineFromText(@BuildString, 4326);
       SELECT @LineFromPoints As 'Geomerty', @name As 'SensorID' 

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Результаты в этом:

В конечном счете, я хотел бы получить представление, возвращающее все пары SensorID/Polyline. Я не знаю, что мой нынешний подход сработает. Буду признателен за любые предложения или примеры.

Answer 1

От SQL Server 2017+ Вы могли бы использовать:

SELECT geography::STLineFromText('LINESTRING(' + 
         STRING_AGG(CONCAT(Longitude, ' ' ,Latitude), ',') 
         WITHIN GROUP(ORDER BY SortOrder) + ')' , 4326) AS geometry
      ,SensorId
FROM dbo.LongAndLats
GROUP BY SensorId
HAVING COUNT(*) > 1;

DBFiddle Demo

---

Я пытался использовать db_cursor, но я получаю отдельный набор результатов для каждой группы

Пожалуйста, избегайте курсоров, заканчивайте каждую строку точкой с запятой и не используйте:

SELECT @BuildString = COALESCE(@BuildString + ',', '') 
       + CAST([Longitude] AS NVARCHAR(50)) + ' ' + CAST([Latitude] 
        AS NVARCHAR(50))
FROM [LongAndLats]
WHERE SensorID = @SensorID
ORDER BY SortOrder;  

Конструкция выше может выглядеть хорошо, но это может привести к неопределенному поведению. Дополнительная информация: конкатенация nvarchar / index / nvarchar(max) необъяснимое поведение

РЕДАКТИРОВАТЬ:

Версия SQL Server 2012:

SELECT geography::STLineFromText('LINESTRING(' 
      + STUFF(
             (SELECT ',' + CONCAT(Longitude, ' ' ,Latitude) 
              FROM dbo.LongAndLats t2
              WHERE t1.SensorId = t2.SensorId 
              ORDER BY SortOrder
              FOR XML PATH (''))
             , 1, 1, '')
       + ')' 
       , 4326) AS geometry, SensorId
FROM dbo.LongAndLats t1
GROUP BY SensorId
HAVING COUNT(*) > 1;

DBFiddle Demo2

EDIT2:

Избежать:

Ошибка.NET Framework произошла во время выполнения пользовательской подпрограммы или совокупной "географии":

System.FormatException: 24117: ввод LineString недопустим, поскольку у него недостаточно точек. Строка LineString должна иметь как минимум две точки.

Вы могли бы добавить HAVING COUNT(*) > 1;

ЗАКЛЮЧИТЕЛЬНОЕ РЕДАКТИРОВАНИЕ:

Если у вас есть "мусорные данные", просто отфильтруйте их (или добавьте CHECK ограничение на этот столбец):

"Значения широты должны быть от -90 до 90 градусов"

SELECT geography::STLineFromText('LINESTRING(' 
      + STUFF(
             (SELECT ',' + CONCAT(Longitude, ' ' ,Latitude) 
              FROM dbo.LongAndLats t2
              WHERE t1.SensorId = t2.SensorId 
                AND Latitude BETWEEN -90 and 90
                AND Longitude BETWEEN -180 AND 180
              ORDER BY SortOrder
              FOR XML PATH (''))
             , 1, 1, '')
       + ')' 
       , 4326) AS geometry, SensorId
FROM dbo.LongAndLats t1
WHERE Latitude BETWEEN -90 and 90
  AND Longitude BETWEEN -180 AND 180
GROUP BY SensorId
HAVING COUNT(*) > 1;

DBFiddle Demo3