PostgreSQLで座標データを保存し二つのレコードの間の距離を取る方法

こんにちは、さるまりんです。

PostgreSQLは座標データを扱えるということで、二つの点を入れてその間の距離をとってみました。

PostGISのインストール

まずは地理空間データを扱うための拡張機能PostGISをPostgreSQLにインストールします。

CREATE EXTENSION postgis;

でいけます。

実行してみました。

postgres=# CREATE EXTENSION postgis;
CREATE EXTENSION

テーブルの作成

座標（緯度・経度）を保存するためのテーブルを作成します。geometry型を使って座標データを保存します。

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    coordinates GEOMETRY(Point, 4326) -- SRID 4326はWGS 84（GPSの座標系）
);

SRID 4326はGPS座標で標準的に使用される地理座標系で経度・緯度を使用します。

実行してみました。

postgres=# CREATE TABLE locations (
postgres(#        id SERIAL PRIMARY KEY,
postgres(#        name VARCHAR(100),
postgres(#        coordinates GEOMETRY(Point, 4326) -- SRID 4326はWGS 84（GPSの座標系）
postgres(#    );

データの投入

作成したテーブルに以下のSQLでデータを入れます。

INSERT INTO locations (name, coordinates)
VALUES ('甲子園球場', ST_SetSRID(ST_MakePoint(135.3622, 34.7214 ), 4326)); -- 甲子園球場 (経度, 緯度)

INSERT INTO locations (name, coordinates)
VALUES ('神宮球場', ST_SetSRID(ST_MakePoint(139.7178, 35.6746), 4326)); -- 神宮球場 (経度, 緯度)

甲子園球場と神宮球場の座標データです。

実行してみました。

postgres=# INSERT INTO locations (name, coordinates)
postgres-# VALUES ('甲子園球場', ST_SetSRID(ST_MakePoint(135.3622, 34.7214 ), 4326)); -- 甲子園球場 (経度, 緯度)
INSERT 0 1
postgres=# 
postgres=# INSERT INTO locations (name, coordinates)
postgres-# VALUES ('神宮球場', ST_SetSRID(ST_MakePoint(139.7178, 35.6746), 4326)); -- 神宮球場 (経度, 緯度)
INSERT 0 1

距離の計算

二つの座標間の距離を計算するにはST_Distance関数を使用します。返してくる距離はデフォルトでメートル単位です。詳しいことはおいておきますが、地球の曲率を考慮したHaversine距離(大円距離)を計算するために、geography型に変換するのが一般的なようなので以下のSQLで上の2点間の距離を計算取得します。

SELECT
    a.name AS location_a,
    b.name AS location_b,
    ST_Distance(
        a.coordinates::geography,
        b.coordinates::geography
    ) AS distance_meters
FROM
    locations a,
    locations b
WHERE
    a.name = '甲子園球場' AND b.name = '神宮球場';

実行してみました。

postgres=# SELECT
postgres-#     a.name AS location_a,
postgres-#     b.name AS location_b,
postgres-#     ST_Distance(
postgres(#         a.coordinates::geography,
postgres(#         b.coordinates::geography
postgres(#     ) AS distance_meters
postgres-# FROM
postgres-#     locations a,
postgres-#     locations b
postgres-# WHERE
postgres-#     a.name = '甲子園球場' AND b.name = '神宮球場';
 location_a | location_b | distance_meters 
------------+------------+-----------------
 甲子園球場 | 神宮球場   | 410468.16051758
(1 row)

上の結果はmで取得されていますので、410kmぐらいということですね。

これは直線距離なので実際に道を歩いたりしていくとこれよりも大きくなるでしょうけど、現在地から10キロ範囲にあるお店を探す等の処理に使うことができそうですね。

読んでくださってありがとうございました。
それではまた！