按值分组并从点(纬度和经度)中为T-SQL中的每个组创建地理折线 [英] Group by value and create geography polyline from points (latitude and longitude) for each group in T-SQL
问题描述
有人在这里问过类似的问题:
A similar question has been asked here:
再提一个问题,我有一个如下所示的表模式:
Taking that question further, I have a table schema that looks like this:
CREATE TABLE [dbo].[LongAndLats](
[Longitude] [float] NULL,
[Latitude] [float] NULL,
[SortOrder] [bigint] NULL,
[SensorID] [bigint] NULL,
)
样本数据如下:
如何使用TSQL将这些点转换为每个SensorID的地理折线 (这样我将为每个SensorID都有一个SensorID/折线记录)?
How can I convert these points into a geography polyline for each SensorID using TSQL (so that I would have a SensorID/Polyline record for each SensorID)?
我尝试使用db_cursor,但是每个组都有一个单独的结果集(我认为地理位置可能是相同的).这段代码:
I've tried using a db_cursor but I get a separate result set for each group (and I think the geographies might be the same). This code:
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/折线对的视图.我不知道我当前的方法会奏效.我将不胜感激任何建议或示例.
Ultimately, I'd like to have a view returning all of the SensorID/Polyline pairs. I don't know that my current approach is going to work. I would appreciate any suggestions or examples.
推荐答案
在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,但是每个组都有一个单独的结果集
I've tried using a db_cursor but I get a separate result set for each group
请避免使用光标,每行以分号结尾并停止使用:
Please avoid cursors, end each line with semicolon and stop using:
SELECT @BuildString = COALESCE(@BuildString + ',', '')
+ CAST([Longitude] AS NVARCHAR(50)) + ' ' + CAST([Latitude]
AS NVARCHAR(50))
FROM [LongAndLats]
WHERE SensorID = @SensorID
ORDER BY SortOrder;
上面的构造看起来不错,但是可能导致不确定的行为.更多信息: nvarchar串联/索引/nvarchar(max)莫名其妙的行为
Construct above may look ok, but it could lead to undefined behaviour. More info: nvarchar concatenation / index / nvarchar(max) inexplicable behavior
SQL Server 2012版本:
SQL Server 2012 version:
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
要避免:
在执行用户定义的例程或聚合地理位置"期间发生.NET Framework错误:
A .NET Framework error occurred during execution of user-defined routine or aggregate "geography":
System.FormatException:24117: LineString输入无效,因为它没有足够的点. LineString必须至少有两个点.
System.FormatException: 24117: The LineString input is not valid because it does not have enough points. A LineString must have at least two points.
您可以添加HAVING COUNT(*) > 1
;
最终
如果您有垃圾数据",则将其过滤掉(或在该列上添加CHECK
约束):
If you have "garbage data", just filter it out(or add CHECK
constraint on that column):
纬度值必须在-90到90度之间"
"Latitude values must be between -90 and 90 degrees"
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
这篇关于按值分组并从点(纬度和经度)中为T-SQL中的每个组创建地理折线的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!