SQL Server(T-SQL)にて、空間データ型(geograhy)で遊んだ備忘録

SQL Serverは空間データ型をサポートしており、主に平面を扱うgeometry型、GPSの緯度経度座標を扱うgeography型があります。
一見すごく取っ付きにくそうなのですが、Geography型を使ってみたら非常に楽しかったので備忘録です。

docs.microsoft.com

Geography型でGPS座標(緯度経度)間の距離を測る

緯度経度情報から2点間の距離を割り出してみたいと思います。
データは下記のようにしました。

CREATE TABLE [dbo].[Locations](
    [Id] [int] PRIMARY KEY IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [GeographicalPoint] [geography] NOT NULL,
)

INSERT INTO [dbo].[Locations] ([Name] ,[GeographicalPoint])
VALUES
  /* geography::STGeomFromText('POINT(X Y)', 4326)  -> POINT(Longitude Latitude)*/
  ('Tokyo', geography::STGeomFromText('POINT(139.691710 35.689500 )', 4326)),
  ('London', geography::STGeomFromText('POINT(-0.125740  51.508530 )', 4326)),
  ('NewYork', geography::STGeomFromText('POINT(-75.499900 43.000350 )', 4326)),
  /* geography::Point(Latitude, Longitude, 4326)) */
  ('Paris', geography::Point(48.856610, 2.351499, 4326)),
  ('SanFrancisco', geography::Point(37.774930,-122.419420, 4326) ),
  ('Sydney', geography::Point(-33.854816,151.216454, 4326) )

各地点の経緯度情報は下記サイトを参考にしました。経緯度は測地規格によって微妙に異なるらしいのですが、今回は無視します。

Distance calculator - Calculate the distance online!

注意すべき点がいくつかあって、まずgeography::以降のSTGeomFromTextやPointなどはケースセンシティブであるということです。C++でいうスコープ解決演算子のように見える::もSQLでは馴染みがないですね。
次にgeography::STGeomFromText('POINT(139.691710 35.689500 )', 4326))のPOINT()内はスペース区切りで「経度緯度」の順番です。(X Y)順だからだと勝手に解釈しています。
一方で、geography::Point(48.856610, 2.351499, 4326))は、カンマ区切りで「緯度, 経度」の順番です。

STGeomFromText (geography データ型) - SQL Server | Microsoft Docs

Point (geography データ型) - SQL Server | Microsoft Docs

末尾の4326はSRIDなるものらしい。全然詳しくないのですが、Qiitaの次の記事が分かりやすかったです。

qiita.com

このデータを、私が今いる名古屋との距離が近い順に並べるには次のようにします。

DECLARE @nagoya geography;  
SET @nagoya = geography::Point(35.181470,136.906410, 4326)

SELECT
    [Id]
  , [Name]
  , l.GeographicalPoint.STAsText() as point
  , l.GeographicalPoint.STDistance(@nagoya) / 1000 as distanceKM
FROM [dbo].[Locations] as l
ORDER BY l.GeographicalPoint.STDistance(@nagoya)

Id	Name	point	distanceKM
1	Tokyo	POINT (139.69171 35.6895)	259.10509946053
6	Sydney	POINT (151.216454 -33.854816)	7786.41428717273
5	SanFrancisco	POINT (-122.41942 37.77493)	8526.70386372486
2	London	POINT (-0.12574 51.50853)	9528.70550900901
4	Paris	POINT (2.351499 48.85661)	9669.98253332005
3	NewYork	POINT (-75.4999 43.00035)	10744.9177005019

「シドニーって思ったより遠いんだなあ」とかバカみたいなことを考えていました。
確認のため、次のサイトで各拠点までの距離を調べてみましたが、愛知→東京で「258.66 km」、名古屋→NewYorkで「10,724.15 km」となりました。

Distance calculator - Calculate the distance online!

この違いは上記のSRIDなるものの違いや、計算方法の違いからくるものかなーと考えています。
まあまあ似ている数字が出たので満足しています。

テーブル内の最短距離を調べる

SELECT
    l1.[Name] as 'From'
  , l2.[Name] as 'To'
  , l1.GeographicalPoint.STDistance(l2.GeographicalPoint) / 1000 as distanceKM
FROM [dbo].[Locations] as l1
CROSS JOIN [dbo].[Locations] as l2
WHERE l1.Id < l2.Id
ORDER BY l1.GeographicalPoint.STDistance(l2.GeographicalPoint)

From	To	distanceKM
London	Paris	343.927441995637
NewYork	SanFrancisco	3973.60727555493
London	NewYork	5525.6215190837
NewYork	Paris	5804.8331826432
Tokyo	Sydney	7791.54980584456
Tokyo	SanFrancisco	8289.54068231193
London	SanFrancisco	8638.73863730526
Paris	SanFrancisco	8976.1447156021
Tokyo	London	9582.13214932394
Tokyo	Paris	9735.694408348
Tokyo	NewYork	10590.1675062711
SanFrancisco	Sydney	11933.0540579063
NewYork	Sydney	15874.5691539395
Paris	Sydney	16957.0527782495
London	Sydney	16988.4071900213

おまけ

今まで2点間の距離が必要な時は、緯度経度をfloat型で持ち、以下のようなユーザー定義関数(拾い物)で距離を計算していました。

CREATE FUNCTION [dbo].[fnCalcDistanceKM](@lat1 FLOAT, @lat2 FLOAT, @lon1 FLOAT, @lon2 FLOAT)
 RETURNS FLOAT 
AS
BEGIN
RETURN ACOS(SIN(PI()*@lat1/180.0)*SIN(PI()*@lat2/180.0)+COS(PI()*@lat1/180.0)*COS(PI()*@lat2/180.0)*COS(PI()*@lon2/180.0-PI()*@lon1/180.0))*6371
END

SQL Server 2017まで、ユーザー定義スカラー関数はパラレル化されないので、いろんな観点からこのような処理はあまり好ましくはないですね。
で、SQL Server 2019からはユーザー定義スカラー関数がインライン化されるそうですね。楽しみ。

docs.microsoft.com