値でグループ化し、T-SQLの各グループのポイント（緯度と経度）から地理ポリラインを作成します

debugcn 投稿 Dev

user3799279

同様の質問がここで尋ねられました：

その質問をさらに進めると、次のようなテーブルスキーマがあります。

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

サンプルデータは次のようになります。

TSQLを使用してこれらのポイントを各SensorIDの地理ポリラインに変換するにはどうすればよいですか（各SensorIDの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のすべてのペアを返すビューが必要です。私の現在のアプローチがうまくいくかどうかはわかりません。提案や例をいただければ幸いです。

Lukasz Szozda

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デモ

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連結/インデックス/ 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：

避けるために：

ユーザー定義ルーチンまたは集約「地理」の実行中に.NETFrameworkエラーが発生しました。

System.FormatException：24117：十分なポイントがないため、LineString入力は無効です。LineStringには少なくとも2つのポイントが必要です。

追加でき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

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]