Posted inデータベース

PostgreSQLとPostGISで扱う地理空間GISデータ：保存・インデックス作成・クエリ実行

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

背景と導入理由：近傍検索の課題を解決する

数年前、私はある小売チェーン向けの店舗検索機能を初めて構築しました。当時のアプローチは今思えばあまりに単純でした。DECIMAL(9,6) 型の2つの標準カラム（latitude と longitude）に座標を保存し、手書きのHaversine（球面三角法）SQL計算式で距離を算出していたのです。テスト環境の20箇所の拠点では問題なく動作しました。しかし、12万件の実店舗データを抱えるステージング環境へデプロイした途端、クエリあたりのレスポンスタイムは1,800ミリ秒を超え、CPU使用率は95%に急上昇し、わずかな負荷でデータベースが停止してしまいました。

なぜ失敗したのでしょうか？標準的なB-treeインデックスは、単一のスカラー次元に沿ってデータを整列します。数値IDやアルファベット順の文字列、タイムスタンプの処理には極めて優れています。

しかし、現実世界の地理空間は回転楕円体面上での2次元的な絞り込みを必要とします。B-treeインデックスは緯度または経度を個別にフィルタリングすることはできても、数万行もの候補レコードをスキャンすることなく双方を同時に評価することはできません。また、平面幾何学的な三角関数は地球の歪曲（曲率）を無視するため、実際の距離に大きな歪みが生じます。

PostGISはこの根本的な限界を打破します。PostgreSQLのオープンソース空間拡張であるPostGISは、平面投影用の GEOMETRY と、球面・楕円体計算用の GEOGRAPHY という専用の空間データ型を追加します。さらに、最適化された数百もの空間解析関数とGiST（Generalized Search Tree）インデックスを提供します。空間計算をPostGISに委ねることで、アプリケーション層から何百行もの壊れやすい数学的処理を排除し、負荷の高い計算をデータベースエンジンへ直接オフロードできます。

実際の開発現場では、地理データがそのまま取り込める綺麗な状態で届くことは稀です。クライアントの位置情報は、雑多なCSVダンプやフォーマットされていないスプレッドシートとして送られてくることがよくあります。バッチ取り込み用にCSVの各行を構造化されたJSONペイロードへ正規化する際、私は toolcraft.app/ja/tools/data/csv-to-json を活用しています。外部サーバーへデータをアップロードせずブラウザ上で直接処理されるため、インポート準備を安全かつ迅速に進められます。

インストール：PostGISの環境構築

PostGISはデータベース内の拡張機能として動作するため、既存のPostgreSQLクラスターへ追加インストールします。PostgreSQL 16が動作するUbuntuまたはDebian環境では、apt 経由で公式パッケージをインストールします。

# パッケージリストを更新し、PostgreSQL 16用PostGISをインストール
sudo apt-get update
sudo apt-get install -y postgresql-16-postgis-3

Dockerを使用したローカル環境の場合は、PostGIS設定済みの公式イメージを起動します。

docker run --name postgis-demo \
  -e POSTGRES_PASSWORD=secretpassword \
  -e POSTGRES_DB=gis_db \
  -p 5432:5432 \
  -d postgis/postgis:16-3.4

次に、psql で対象のデータベースインスタンスに接続し、拡張機能を登録します。

-- 対象のデータベースに切り替え
\c gis_db

-- PostGIS空間エンジンを有効化
CREATE EXTENSION IF NOT EXISTS postgis;

-- インストールが成功したことを確認
SELECT PostGIS_Full_Version();

このクエリにより、有効なPostGISビルドとリンクされたGEOS、PROJ、LibXMLの各ライブラリバージョンが出力されます。この情報が表示されれば、データベースで空間データを処理する準備は完了です。

設定：スキーマ、データ保存、空間インデックス

空間スキーマを設計する際は、適切なデータ型とSRID（空間参照系識別子）の選択が重要です。標準的なGPS座標を扱う一般的なグローバルアプリケーションでは、SRID 4326（WGS 84）が業界標準として使われます。

PostGISには主に2つの空間プリミティブが用意されています。

  • GEOMETRY: 座標を平面のユークリッド直交座標系にマッピングします。局所的な地域マップや州レベルの投影（State Planeなど）、CADアプリケーションで高速な計算を実現します。
  • GEOGRAPHY: WGS 84回転楕円体面上の大円距離をメートル単位で直接計算します。GPSポイント、配送半径の検索、全国規模の店舗検索などに最適です。

1. テーブルスキーマの作成

以下は、GEOGRAPHY(Point, 4326) を使用した本番環境向けのカフェ店舗情報スキーマです。

CREATE TABLE coffee_shops (
    id SERIAL PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    address TEXT,
    location GEOGRAPHY(Point, 4326) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

2. 空間インデックス（GiST）の適用

本番環境において空間インデックスなしで座標クエリを実行してはなりません。PostGISは空間バウンディングボックス上に階層型R-tree構造を構築する GiST（Generalized Search Tree） を利用します。

CREATE INDEX idx_coffee_shops_location 
ON coffee_shops 
USING GIST (location);

このバウンディングボックスの階層構造により、PostgreSQLは探索ツリーの無関係なブランチを瞬時に切り捨て、テーブル全体のシーケンシャルスキャンを回避できます。

3. 座標の保存

重要なルールを覚えておきましょう。PostGISは直交座標の (経度, 緯度) の順序（X軸が先、Y軸が後）に従います。日常会話では直感的に「緯度・経度」と言いがちですが、この値を逆にしてしまうミスはGIS開発で最も頻発し、ニューヨークの座標が南極に飛んでしまうといった事態を招きます。

ポイントの生成には ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography を使用します。

-- 位置情報を挿入（経度が先: X = 経度, Y = 緯度）
INSERT INTO coffee_shops (name, address, location)
VALUES
    ('Central Perk', '90 Bedford St, New York', ST_SetSRID(ST_MakePoint(-74.0054, 40.7323), 4326)::geography),
    ('Midtown Roast', '350 5th Ave, New York', ST_SetSRID(ST_MakePoint(-73.9857, 40.7484), 4326)::geography),
    ('Brooklyn Beans', '100 Water St, Brooklyn', ST_SetSRID(ST_MakePoint(-73.9922, 40.7032), 4326)::geography),
    ('Uptown Espresso', '2880 Broadway, New York', ST_SetSRID(ST_MakePoint(-73.9645, 40.8082), 4326)::geography);

検証とモニタリング：実践的なクエリとパフォーマンスチューニング

データにインデックスを作成したら、バウンディングボックス検索や半径検索を実行して実際のクエリパフォーマンスを測定します。

1. 指定半径内のスポット検索（ST_DWithin）

ユーザーがエンパイア・ステート・ビルの座標 (-73.9851, 40.7488) にいて、徒歩2km（2,000メートル）以内にあるカフェを探しているとします。ST_DWithin 関数を使えば、この空間範囲を非常に高効率に評価できます。

SELECT 
    name,
    ROUND(ST_Distance(location, ST_SetSRID(ST_MakePoint(-73.9851, 40.7488), 4326)::geography)::numeric, 1) AS distance_meters
FROM coffee_shops
WHERE ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(-73.9851, 40.7488), 4326)::geography,
    2000 -- メートル単位の半径
)
ORDER BY distance_meters ASC;

対象カラムで GEOGRAPHY 型を使用しているため、距離は地球の回転楕円体に沿って自動的にメートル単位で計算されます。度数からメートルへの手動変換は一切不要です。

2. EXPLAIN ANALYZEによるインデックス利用状況の確認

PostgreSQLがGiSTインデックスを正しく利用しているか、フルテーブルスキャンにフォールバックしていないかを検証します。

EXPLAIN ANALYZE
SELECT id, name
FROM coffee_shops
WHERE ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(-73.9851, 40.7488), 4326)::geography,
    2000
);

正常に機能していれば、実行計画に Index Scan using idx_coffee_shops_location または Bitmap Index Scan と表示されます。50万行規模のデータセットでは、シーケンシャルスキャンに600〜900ミリ秒かかることが一般的ですが、GiSTインデックスが有効であれば同じクエリがわずか1.5〜3ミリ秒で完了します。

大規模テーブルで Seq Scan が発生している場合は、暗黙の型変換によってインデックスが無効化されていないか、あるいはインデックス対象のカラムを関数でラップしてしまっていないかを確認してください。

3. インデックス状態とディスク容量の監視

座標データの増加に伴う予期せぬキャッシュ圧迫を防ぐため、インデックスの肥大化や物理ストレージの使用状況を定期的に監視します。

SELECT
    relname AS object_name,
    pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
    pg_size_pretty(pg_relation_size(c.oid)) AS table_or_index_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname IN ('coffee_shops', 'idx_coffee_shops_location')
  AND n.nspname = 'public';

大量のデータを一括インポートした後は、VACUUM ANALYZE coffee_shops; を実行してください。これによりPostgreSQLのプランナ統計情報が更新され、コストベースオプティマイザがシーケンシャルスキャンではなくGiSTツリー探索を確実に選択するようになります。

Share:
Tags: