緯度経度を使って２地点の距離をSQLで求める

はじめに

複数地点を現在地からの距離順に並び替える機能が必要になり、DBからデータを取得する際に２地点の距離をSQLで計算する方法を調べました。

環境

DBはPostgreSQLを使用しています。

２地点の距離を求めるSQL

２地点の距離の計算方法はヒュベニの公式を参考にしました。
（ヒュベニの公式自体の説明は省くので気になる方はご自身で調べてください）

地点１
緯度：lat_1
経度：lon_1

地点2
緯度：lat_2
経度：lon_2

距離の単位：メートル

SQRT(
    (
        6334834 / SQRT(
            (
                1 - 0.006674 * SIN((lat_1 + lat_2) / 2 * PI() / 180) ^ 2
            ) ^ 3
        ) * (lat_1 - lat_2) * PI() / 180
    ) ^ 2 +
    (
        6377397 / SQRT(
            1 - 0.006674 * SIN((lat_1 + lat_2) / 2 * PI() / 180) ^ 2
        ) * COS((lat_1 + lat_2) / 2 * PI() / 180) * (lon_1 - lon_2) * PI() / 180
    ) ^ 2
) AS distance

実際の利用ケース

登録されている複数地点を対象の地点から近い順に並び替えて出力します

テーブル作成＆データ追加

CREATE TABLE spots (
  name VARCHAR(255) NOT NULL,
  latitude DECIMAL(10, 6) NOT NULL,
  longitude DECIMAL(10, 6) NOT NULL
);

INSERT INTO spots (name, latitude, longitude)
VALUES
  ('東京', 35.689487, 139.691711),
  ('大阪', 34.693738, 135.502165),
  ('名古屋', 35.181446, 136.906398),
  ('札幌', 43.062096, 141.354376),
  ('福岡', 33.590355, 130.401716),
  ('神戸', 34.691308, 135.183025),
  ('京都', 35.011636, 135.768029),
  ('横浜', 35.443708, 139.638026),
  ('広島', 34.385203, 132.455293),
  ('仙台', 38.268215, 140.869356);

東京からの距離順で並び替え

WITH tokyo AS (
  SELECT latitude, longitude FROM spots WHERE name = '東京'
)
SELECT *,
  ROUND(
    SQRT(
      (
        6334834 / SQRT(
          (1 - 0.006674 * SIN((latitude / 2 + (SELECT latitude FROM tokyo) / 2) * PI() / 180) ^ 2) ^ 3
        ) * (latitude - (SELECT latitude FROM tokyo)) * PI() / 180
      ) ^ 2 +
      (
        6377397 / SQRT(
          1 - 0.006674 * SIN((latitude / 2 + (SELECT latitude FROM tokyo) / 2) * PI() / 180) ^ 2
        ) * COS((latitude / 2 + (SELECT latitude FROM tokyo) / 2) * PI() / 180) * (longitude - (SELECT longitude FROM tokyo)) * PI() / 180
      ) ^ 2
    ), 6) AS distance
FROM spots
ORDER BY distance;

結果

name	latitude	longitude	distance
東京	35.689487	139.691711	0
横浜	35.443708	139.638026	27697.361382
名古屋	35.181446	136.906398	259086.002356
仙台	38.268215	140.869356	304750.97765
京都	35.011636	135.768029	364450.988478
大阪	34.693738	135.502165	397185.843698
神戸	34.691308	135.183025	425254.325911
広島	34.385203	132.455293	675887.658347
札幌	43.062096	141.354376	830879.957288
福岡	33.590355	130.401716	882918.526929