Posted inデータベース

緯度経度を超えて：PostgreSQLでPostGISを極めるためのプロ向けガイド

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

単純なデータ型を超えて

キャリアの大部分において、私は文字列、整数、タイムスタンプの世界で生きてきました。しかし、クライアントから「近くの店舗を検索する」機能や、ロンドンのような都市全体で10kmの配送半径を計算してほしいと頼まれた途端、事態は複雑になります。多くのチームが、緯度と経度を2つの別々のfloatカラムとして保存することでこれを解決しようとするのを見てきました。基本的な保存にはそれで十分ですが、複雑な空間結合を実行したり、地球の曲面を考慮した距離計算が必要になったりすると、その手法は無残にも失敗します。

MySQL、PostgreSQL、MongoDBを扱ってきた中で、それぞれの位置情報の扱い方を見てきました。MySQLの空間サポートは向上しており、MongoDBも基本的なGeoJSONには適していますが、本格的な処理が必要な場合、私はいつもPostGISに戻ります。これは単なるプラグインではありません。PostgreSQLを完全な空間エンジンへと変え、OGC標準に厳密に準拠しながら、データベースに形状や距離として考えさせるようにするものです。

シンプルな配送トラッカーを構築する場合でも、複雑な都市計画ツールを作成する場合でも、PostGISは不可欠です。アプリケーションコード内でハバーサイン（Haversine）公式の入り組んだスパゲッティコードを書く手間から解放してくれます。

環境の構築

PostGISは、標準的なPostgreSQL의インストールの上に構築されます。Ubuntu 22.04や同様のDebianベースのシステムを使用している場合は、使用しているPostgreSQLのバージョンに対応するパッケージをインストールするだけです。

まず、バージョンを確認します：

psql --version

PostgreSQL 15を使用している場合は、次のコマンドを実行します：

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

ローカル開発にはDockerを強くお勧めします。postgis/postgisイメージを使用するのが最短ルートです。これには、幾何学計算用のGEOS、データ変換用のGDAL、投影変換用のPROJなどの必須ライブラリがあらかじめ組み込まれています。

docker run --name spatial-db -e POSTGRES_PASSWORD=mysecret -d postgis/postgis

空間フレームワークのセットアップ

バイナリのインストールは準備の半分に過ぎません。特定のデータベース内でエクステンションを手動で有効にする必要があります。スキーマをクリーンに保つために、通常は空間プロジェクト専用のデータベースを作成します。

-- データベースに接続
CREATE DATABASE mapping_app;
\c mapping_app

-- PostGISエクステンションを有効化
CREATE EXTENSION postgis;

バージョンをチェックしてセットアップを確認します。これにより、すべての依存関係が正しくリンクされていることが保証されます。

SELECT postgis_full_version();

Geometry型 vs. Geography型：どちらを選ぶべきか？

初心者が特につまずきやすいのが、GEOMETRYとGEOGRAPHYの選択です。この選択は精度と速度の両方に影響します。

  • Geometry: 平面的なデカルト座標系を使用します。非常に高速です。データが狭い範囲（オフィスのフロアプランなど）に限定されている場合や、特定の地図投影法を使用している場合に適しています。
  • Geography: 地球の曲率を考慮します。ニューヨークからロンドンまでの5,500kmの距離を、複雑な投影法を気にせずに計算したい場合に適しています。

ほとんどのウェブ地図では、SRID 4326を使用します。これは、GPS、Google Maps、OpenStreetMapで使用されているWGS 84標準です。

-- 施設情報（POI）用のテーブルを作成
CREATE TABLE locations (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  geom GEOGRAPHY(Point, 4326)
);

初めての空間クエリを実行する

いよいよ本番です。システムにデータを投入しましょう。コーヒーショップをマッピングすると仮定します。ST_GeogFromText関数を使用して、標準的な文字列を空間オブジェクトに変換します。

INSERT INTO locations (name, geom)
VALUES ('ブルーボトル ブルックリン店', ST_GeogFromText('SRID=4326;POINT(-73.9352 40.7306)'));

クエリを実行する際に真の力が発揮されます。ユーザーから半径5km以内にあるすべてのコーヒーショップを検索する必要があるとします。PythonやNode.jsで数千行を取得して距離を計算する代わりに、データベースに一括で処理を任せましょう。

SELECT name 
FROM locations 
WHERE ST_DWithin(geom, ST_GeogFromText('SRID=4326;POINT(-73.94 40.74)'), 5000);

GISTインデックスで高速化する

データセットが数ポイントから1,000万行に増えると、パフォーマンスが急低下します。空間データは多次元であるため、標準的なB-treeインデックスは機能しません。そこで、GIST (Generalized Search Tree) インデックスが必要になります。

CREATE INDEX idx_locations_geom ON locations USING GIST (geom);

このインデックスは「バウンディングボックス」を使用して、無関係なデータを瞬時に破棄します。適切なGISTインデックスを使用すれば、以前は3秒かかっていたクエリを20ms未満に短縮できることがあります。

本番環境でのヘルスチェック

本番環境では、常にspatial_ref_sysテーブルを監視してください。このテーブルが空だと、空間結合に失敗します。また、クエリに対してEXPLAIN ANALYZEを実行してください。大きなテーブルで「Seq Scan」が表示される場合は、SRIDの指定漏れや型の不一致により、インデックスが使用されていない可能性があります。

最後に

PostGISには数百の関数があり、威圧的に感じるかもしれません。しかし、ひるむ必要はありません。現代のウェブ要件の90%は、ST_Distance、ST_DWithin、ST_Intersectsをマスターするだけで対応可能です。まずはシンプルにGEOGRAPHYから始めましょう。生のパフォーマンスや特定の投影法が必要になったときに初めてGEOMETRYに切り替えてください。ロジックをデータベースに移行してコードをクリーンに保位、数学的な計算はPostgreSQLに任せましょう。

Share:
Tags: