如何用T-SQL按SensorID分组将经纬度点转为地理折线？

我来帮你搞定这个问题！你之前用游标遇到的麻烦——比如多个独立结果集、点串重复、变量错误——其实可以用更高效的分组聚合方法解决，而且完美适配创建视图的需求。

先说说你游标代码的问题 #

• 每次循环没有重置@BuildString，导致不同SensorID的经纬度点会串在一起，生成错误的折线
• 变量名不匹配：比如FETCH NEXT FROM db_cursor INTO LongAndLats应该是@SensorID，@name也没有定义
• 游标会返回多个独立的结果集，没法直接用来创建视图

推荐方案1：用STRING_AGG（SQL Server 2017+） #

这是最简洁高效的方法，直接分组聚合点，一步生成折线，适合创建视图：

CREATE VIEW dbo.SensorPolylines
AS
SELECT
    SensorID,
    geography::STLineFromText(
        'LINESTRING(' + 
        STRING_AGG(CONCAT(Longitude, ' ', Latitude), ',') WITHIN GROUP (ORDER BY SortOrder) + 
        ')', 
        4326
    ) AS Polyline
FROM dbo.LongAndLats
GROUP BY SensorID

关键说明： #

• STRING_AGG会按照SortOrder的顺序，把每个SensorID下的经纬度拼接成经度 纬度, 经度 纬度,...的格式
• 用LINESTRING()包裹后，通过geography::STLineFromText转换成地理对象，SRID 4326是GPS常用的WGS84坐标系
• 分组后直接得到每个SensorID对应的一条折线记录，完全满足视图的需求

兼容旧版本SQL Server（2016及以下） #

如果你的SQL Server版本不支持STRING_AGG，可以用FOR XML PATH来实现字符串聚合：

CREATE VIEW dbo.SensorPolylines
AS
SELECT
    SensorID,
    geography::STLineFromText(
        'LINESTRING(' + 
        STUFF(
            (SELECT ',' + CONCAT(Longitude, ' ', Latitude)
             FROM dbo.LongAndLats AS L2
             WHERE L2.SensorID = L1.SensorID
             ORDER BY L2.SortOrder
             FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 
            1, 1, ''
        ) + 
        ')', 
        4326
    ) AS Polyline
FROM dbo.LongAndLats AS L1
GROUP BY SensorID

说明： #

• FOR XML PATH('')会把每个SensorID的点拼接成字符串，STUFF用来去掉开头多余的逗号
• 最终效果和STRING_AGG完全一致，只是写法稍复杂

如果你一定要用游标（仅作参考，不推荐） #

如果坚持要用游标，需要修正变量初始化和变量名的问题：

DECLARE @SensorID BIGINT -- 和表中SensorID的类型保持一致（表中是bigint）
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 @SensorID

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 每次循环必须重置字符串，避免累积之前的点
    SET @BuildString = ''
    
    SELECT @BuildString = COALESCE(@BuildString + ',', '') + CAST(Longitude AS NVARCHAR(50)) + ' ' + CAST(Latitude AS NVARCHAR(50))
    FROM dbo.LongAndLats
    WHERE SensorID = @SensorID
    ORDER BY SortOrder

    SET @BuildString = 'LINESTRING(' + @BuildString + ')';
    SET @LineFromPoints = geography::STLineFromText(@BuildString, 4326);

    SELECT @LineFromPoints AS Polyline, @SensorID AS SensorID

    FETCH NEXT FROM db_cursor INTO @SensorID
END

CLOSE db_cursor
DEALLOCATE db_cursor

不过还是建议用前面的聚合方法，游标性能差，而且没法直接生成视图。

内容的提问来源于stack exchange，提问作者user3799279