Posted inDatabase

PostGIS & PostgreSQL: 地理空間データ（Geospatial）処理のための「強力な武器」

Database tutorial - IT technology blog
Database tutorial - IT technology blog

深夜2時、「最寄りのドライバー検索」という課題

スマートフォンが激しく震えました。フードデリバリーを運営するクライアントのシステムで、ドライバー検索APIがタイムアウトエラーを連発しているとの通知です。ログを確認すると、200万件以上のデータに対してハバサイン（Haversine）公式を用いて距離を計算するSQLクエリが、必死に処理を続けていました。サーバーのCPU使用率は、わずか数十件の同時リクエストで100%に達していました。

地図アプリを開発し始めたばかりの頃は、座標（緯度・経度）を2つのFloatカラムとして扱い、高校数学の知識で計算してしまいがちです。数百件程度のデータならこれでも動きます。しかし、データが数十万、数百万件に達すると、パフォーマンス面で大惨事を招きます。その時の唯一の解決策は、急いで PostGIS へ移行することでした。

PostGISは単なる拡張機能ではありません。それはPostgreSQLを真の空間データベースへと変貌させます。球体上の点（Point）、線（Line）、多角形（Polygon）の性質を深く理解しており、手動で計算する代わりに、低レイヤーで最適化されたアルゴリズムによってデータベース側で処理を行うことができます。

Geometry vs Geography: 最初から間違えないために

コードを書き始める前に、面積や距離の計算ミスを防ぐため、これら2つのデータ型の違いを明確に理解しておく必要があります。

  • Geometry: 平面（デカルト座標系）に使用されます。地球を紙のように平らなものとして扱います。計算が非常に高速で、建物の図面や狭い範囲のエリアに適しています。
  • Geography: 球体に使用されます。地球の曲率を考慮します。GrabやShopeeFoodのようなアプリで正確なGPS距離を計算する場合、こちらが必須の選択肢となります。

また、SRID (Spatial Reference System Identifier) も忘れてはいけません。グローバルなGPSデータの場合、常に SRID 4326 (WGS 84標準) を使用してください。SRIDの設定を間違えると、すべての計算が無意味になってしまいます。

実践：PostGISのインストールと設定

1. 拡張機能のインストール

Ubuntuを使用している場合は、apt経由で直接インストールできます。このプロセスは30秒ほどで完了します。

sudo apt-get update
sudo apt-get install postgis postgresql-15-postgis-3

OSレイヤーでパッケージをインストールした後、データベース内で有効化する必要があります。注意点として、この拡張機能は使用するデータベースごとに個別に有効化する必要があります。

-- データベースに接続
CREATE EXTENSION postgis;

-- バージョンを確認
SELECT postgis_full_version();

2. テーブルの作成

lat（緯度）と lng（経度）を分ける代わりに、単一の GEOGRAPHY カラムにまとめましょう。これにより、データの管理がすっきりし、組み込み関数を活用できるようになります。

CREATE TABLE cafes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    location GEOGRAPHY(POINT, 4326)
);

3. 座標データの追加

データを挿入する際、順序が極めて重要です。経度（Longitude）が先、緯度（Latitude）が後 です。この順序を間違えると、あなたのカフェは東京から南極まで「飛んで」いってしまうかもしれません。

INSERT INTO cafes (name, location)
VALUES ('コン・カフェ', ST_SetSRID(ST_MakePoint(105.8523, 21.0285), 4326));

インポート前に地点リストのCSVファイルをJSON形式に素早く変換したい場合は、toolcraft.appのコンバーターをよく利用します。このツールはクライアントサイドで動作するため、顧客データの機密性が完全に保たれます。

半径検索（Radius Search）を一瞬で実行

ここがPostGISの真骨頂です。例えば、ユーザーの位置から半径2km以内にあるカフェを探す場合は次のようになります。

SELECT name, 
       ST_Distance(location, ST_SetSRID(ST_MakePoint(105.8500, 21.0300), 4326)::geography) as distance
FROM cafes
WHERE ST_DWithin(location, ST_SetSRID(ST_MakePoint(105.8500, 21.0300), 4326)::geography, 2000)
ORDER BY distance;

ST_DWithin 関数は単なるフィルタリング用ではありません。インデックスとシームレスに動作するように設計されており、無関係な空間領域を即座にスキップできます。

GiSTインデックスによるパフォーマンス最適化

100万行を超えるデータに対してインデックスなしでクエリを投げれば、サーバーは再び悲鳴を上げるでしょう。地理データの場合、通常のB-Treeインデックスは役に立ちません。GiST (Generalized Search Tree) を使用する必要があります。

CREATE INDEX idx_cafes_location ON cafes USING GIST(location);

このインデックスは空間を小さな四角形（R-Tree）に分割します。実際に、あるシステムでこの一行を追加しただけで、クエリ時間が8秒から45ミリ秒に短縮された経験があります。

実務から得た教訓

長年、大規模な地図システムを運用してきた中で、3つの重要な注意点に気づきました。

  1. パフォーマンスの検討: Geographyは正確ですが、Geometryよりもリソースを消費します。アプリが特定の区や小さな市の中だけで動作する場合は、Geometryの方が最適な選択肢となります。
  2. 測定単位: Geographyにおける ST_DWithin は メートル 単位を使用します。一方、Geometryでは SRID の単位（通常は度 – degree）を使用します。多くの開発者がこの混同により「検索結果が出てこない」というバグに直面しています。
  3. データベースのクリーニング: 配達員の座標を常に更新するテーブルでは、大量のデッドタプルが発生します。システムが低速化しないよう、Autovacuum が十分に強力に設定されていることを確認してください。

結びに

位置情報を扱うアプリを作りたいなら、PostGISは業界標準の選択肢です。アプリケーションコードで数学的な公式を自作しようとせず、その重荷はデータベースに任せましょう。SRIDやGiSTに慣れるまでは少し時間がかかるかもしれませんが、それによって得られる安定性と速度には、十分すぎるほどの価値があります。

Share:
Tags: