2023-05-29

Snowflake UDFを使用した地理的距離の計算

Snowflakeでの地理的距離の計算

この記事では、Snowflakeで地理的距離を計算するためのユーザー定義関数（UDF）の実装方法を紹介します。

ユーザー定義関数（UDF）

データベースの文脈において、関数は入力を受け取り、処理を実行し、結果を返す再利用可能なコードの断片です。Snowflakeは、他の多くのデータベースシステムと同様に、さまざまな組み込み関数を提供しています。ただし、複雑なデータ処理タスクの特定の要件を満たすためには、組み込み関数だけでは十分ではない場合もあります。ここで、UDFが登場します。

UDFを使用すると、ユーザーはSnowflakeで独自の関数を定義することができます。これらの関数は、組み込み関数と同様に、複数のクエリやアプリケーションで再利用することができます。UDFは、繰り返し使用されるロジックをカプセル化するために特に有用です。

Haversine式

Haversine式は、2つの点の緯度と経度を考慮して、球体の表面上の2点間の大円距離を計算するために使用される数式です。この式は、短距離の場合には地球を球体として近似することができるため、地球上の距離を測定する際に重要です。

以下の数式で表されます。

a = \sin^2\left(\frac{\Delta \phi}{2}\right) + \cos(\phi_1) \cdot \cos(\phi_2) \cdot \sin^2\left(\frac{\Delta \lambda}{2}\right)

c = 2 \cdot \text{atan2}\left(\sqrt{a}, \sqrt{1-a}\right)

d = R \cdot c

ここで、

$\phi_1, \phi_2$ は点1と点2の緯度（弧度法）
$\Delta \phi$ は緯度の差（弧度法）
$\Delta \lambda$ は経度の差（弧度法）
$R$ は地球の半径（平均半径 = 6,371 km）
$d$ は2点間の大円距離（球体の表面上の距離）

この数式は、計算を3つのパートに分割しています。

$a$ は2点間のコードの半分の長さの2乗を計算
$c$ は弧度で表される角距離を表す
$d$ は地球の半径を使用して実際の距離を計算

SnowflakeでのUDFの実装

以下では、SnowflakeでHaversine式をUDFとして実装する手順を説明します。

Haversine Distance UDFの作成

Haversine式を実装したUDFを作成するためのSQLクエリを書いてみます。このUDFでは、2つの点の緯度と経度を含む4つのパラメータを渡します。関数はこれらの点の距離をキロメートルで返します。

以下は、このUDFを作成するための修正済みのSQLコードです。

sql

CREATE OR REPLACE FUNCTION HAVERSINE_DISTANCE(
  lat1 FLOAT,
  lon1 FLOAT,
  lat2 FLOAT,
  lon2 FLOAT
)
RETURNS FLOAT
LANGUAGE SQL
AS
$$
  -- Earth's radius in km
  6371.0 * 2 * ATAN2(
    SQRT(
      SIN(RADIANS(lat2 - lat1) / 2) * SIN(RADIANS(lat2 - lat1) / 2) +
      COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
      SIN(RADIANS(lon2 - lon1) / 2) * SIN(RADIANS(lon2 - lon1) / 2)
    ),
    SQRT(
      1 - (
        SIN(RADIANS(lat2 - lat1) / 2) * SIN(RADIANS(lat2 - lat1) / 2) +
        COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
        SIN(RADIANS(lon2 - lon1) / 2) * SIN(RADIANS(lon2 - lon1) / 2)
      )
    )
  )
$$;

このコードは、Haversine式の計算を直接埋め込んでいます。SIN、COS、ATAN2、RADIANSなどの組み込み関数を使用して必要な数学的操作を行っています。

Haversine Distance UDFの使用

UDFを作成したので、SQLクエリで任意の2つの地理的な点の距離を計算するために使用することができます。例えば、東京（35.6895N、139.6917E）とロサンゼルス（34.0522N、-118.2437E）の距離を計算してみます。

sql

SELECT HAVERSINE_DISTANCE(35.6895, 139.6917, 34.0522, -118.2437) AS distance_in_km;

	DISTANCE_IN_KM
1	8815.473355809

このクエリでは、作成したHAVERSINE_DISTANCE関数を使用し、東京とロサンゼルスの緯度と経度を渡して、これら2つの都市間の距離をキロメートルで計算しています。