postgis-spatial-sql

PostGIS & 空間 SQL

目的: 正しく、かつ高速な空間 SQL。繰り返し発生する 2 つの失敗パターンは、セマンティックなもの(geometry と geography、SRID の不一致 → 誤った結果)とパフォーマンス上のもの(インデックスが使われない → 数時間かかる結合)です。このスキルはその両方を防ぎます。

データベースが適切なツールとなる場合

ファイル/GeoPandas から PostGIS へ移行すべきなのは、次のいずれかに該当する場合です: フィーチャ数が数百万を超える、読み書きが同時並行で発生する、アドホックなクエリを繰り返す、その上に配信 API がある、あるいはトランザクションの整合性が必要である。GeoParquet に対する単発の分析スキャンであれば、DuckDB Spatial がしばしば最速のゼロインストールな手段です — 同じ SQL の考え方で、サーバーは不要です。

要件が不完全な場合は、このヒューリスティックを最終的な推奨事項にしてはいけません。まず、現在および予測されるデータ量、同時実行性、配信と変更のパターン、レイテンシ/SLA、配信のニーズ、および運用上のオーナーシップ(バックアップとリカバリを含む)を確認してください。実行可能な両方のバックエンドについて、代表的な取り込み、結合、読み取りクエリを定義してください。行数、結合のカーディナリティ、SRID、ジオメトリの妥当性、およびサンプル出力が一致して初めて、実行時間とリソース使用量を比較してください。このベンチマークと正確性の計画を今回の回答に含めてください。後で起草することを申し出るだけにしないでください。

スキーマの基本

この実行可能な例では、データが UTM ゾーン 33N に含まれていることを前提としています。EPSG:32633 は、実際の対象地域について検証済みの投影 CRS に置き換えてください。

CREATE TABLE parcels (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  parcel_no   text NOT NULL,
  landuse     text,
  area_m2     double precision,          -- unit in the name, always
  geom        geometry(MultiPolygon, 32633) NOT NULL
);
CREATE INDEX parcels_geom_gix ON parcels USING gist (geom);
ANALYZE parcels;

• geometry 列は完全に型付けする: geometry(MultiPolygon, SRID) — 型付けされていない geometry 列は、混在したゴミを平然と受け入れます。

• ロード時に Multi* へ昇格させる(ST_Multi)ことで、Polygon/MultiPolygon の混在が問題を引き起こすことはなくなります。

• geometry と geography: 地域分析には投影 SRID の geometry を使います(高速で、関数一式が利用可能)。対象範囲が地球規模/ゾーンをまたぐ場合で、投影法を選ばずにメートル単位を得たいときは geography(SRID 4326)を使います(低速で、利用できる関数は少なめ)。4326 の geometry に格納しておいて ST_Area を呼び出し m² を期待してはいけません — それは平方度です。

• 面積や長さの測定に EPSG:3857/Web Mercator を決して使わないでください。分析用 CRS がまだ不明な場合は、測地学的な結果を得るために 4326 の geography を使うか、いったん止めて検証済みのローカル CRS/等積 CRS を選定してください。歪みが既知の CRS を、実行可能な測定の代替手段として提示しないでください。

• 推奨する格納用 geometry 列は必ずその SRID で型付けしなければなりません。 繰り返し測定のために「2 つ目の投影済み geometry 列」を助言するのは、インデックスと値を投入する ST_Transform を伴う geometry(<Type>, <SRID>) として記述されるまで不完全です。修正策として推奨された型付けされていない列は、それが解決しようとした SRID 混在の問題を再び招き入れます:

  ALTER TABLE parcels ADD COLUMN geom_32633 geometry(MultiPolygon, 32633);
  UPDATE parcels SET geom_32633 = ST_Transform(geom, 32633);
  CREATE INDEX parcels_geom_32633_gix ON parcels USING gist (geom_32633);
  

• すべての geometry 列に GiST インデックスを、一括ロード後には ANALYZE を実行してください。BRIN は、空間的に整序された追記専用の巨大テーブルにのみ使います。

• ロード経路: ogr2ogr -f PostgreSQL、shp2pgsql、または GeoPandas の to_postgis(小規模/中規模)。COPY は INSERT より桁違いに高速です。

正しい空間述語

• ST_Intersects は「少しでも接する」場合、ST_Contains/ST_Within は包含関係、ST_DWithin(a, b, dist) は近接性に対して使います — ST_Distance(a,b) < dist は決して使わないでください(この形式はインデックスを利用できません)。
• 古典的な point-in-polygon 結合:

SELECT p.id, a.district
FROM points p
JOIN admin a ON ST_Intersects(a.geom, p.geom);   -- GiST on both sides

• 距離演算子を使った KNN 最近傍(インデックス支援あり):

SELECT h.id, h.name
FROM hospitals h
ORDER BY h.geom <-> (SELECT geom FROM incident WHERE id = 42)
LIMIT 3;

<-> は、最近の PostGIS では geometry に対して真の距離順序を提供します。テーブルが巨大な場合は、ST_DWithin で包んで検索範囲を制限してください。

パフォーマンスプレイブック

1. まず EXPLAIN (ANALYZE, BUFFERS) を実行する — GiST インデックスが使用されていることを確認します (「Index Scan ... _gix」を探します)。大きな空間結合で Seq Scan が出る場合は、 サーバーを大きくするのではなく、書き換えが必要です。
2. すべての述語の両辺で同じ SRID を使用する — 結合述語内の ST_Transform は インデックスの使用を妨げます。代わりに、変換済みでインデックス付きのコピーを 保存します。
3. 大きなポリゴンの問題: 国や流域サイズのジオメトリはインデックスの bbox を 無意味にします → ワークテーブルに ST_Subdivide します (これらに対する結合で 通常 10〜100 倍の高速化)。

次の例は countries(country_id, geom) を前提としています。

CREATE TABLE country_parts AS
SELECT c.country_id, part.geom
FROM countries AS c
CROSS JOIN LATERAL ST_Subdivide(c.geom, 256) AS part(geom);

CREATE INDEX country_parts_geom_gix ON country_parts USING gist (geom);
ANALYZE country_parts;

ST_Subdivide は集合を返す関数です。その結果に (ST_Subdivide(...)).geom としてアクセスしないでください。

4. データベース内での妥当性検証: ST_IsValid による監査、ST_MakeValid による修復、 書き込み元が信頼できない場合は CHECK (ST_IsValid(geom)) を追加します。
5. 分析用ではなく配信用に簡略化する: フル解像度のジオメトリを保持し、 Web 層向けに ST_SimplifyPreserveTopology によるコピーやベクタータイル (ST_AsMVT) を生成します。
6. 更新はトランザクションでバッチ処理し、頻繁な更新の後には VACUUM ANALYZE を 実行します。

一般的な分析パターン

-- Area-weighted aggregation (e.g., population into custom zones)
SELECT z.zone_id,
       SUM(b.pop * ST_Area(ST_Intersection(z.geom, b.geom)) / ST_Area(b.geom)) AS pop_est
FROM zones z JOIN blocks b ON ST_Intersects(z.geom, b.geom)
GROUP BY z.zone_id;

-- Dissolve with attribute
SELECT landuse, ST_Multi(ST_Union(geom))::geometry(MultiPolygon, 32633) AS geom
FROM parcels GROUP BY landuse;

面積加重補間は、ソース単位内で密度が一様であると仮定しています — 報告時にはこの仮定を明記してください。妥当性の修復は ST_MakeValid であり、 ST_Buffer(geom, 0) ではありません。

DuckDB Spatial クイックパス

INSTALL spatial; LOAD spatial;
SELECT a.name, count(*)
FROM 'admin.parquet' a, 'points.parquet' p
WHERE ST_Intersects(a.geom, p.geom)
GROUP BY a.name;

GeoParquet/Shapefile/GPKG を直接読み込み、デフォルトで並列処理します — サーバーを必要としない単発の大規模結合やパイプラインのステップに最適です。 GiST はありません。独自に結合を計画するため、想定せずベンチマークしてください。

検証プロトコル

1. 各結合/オーバーレイ CTE の後に、行数を確認するクエリを実行します。
2. 対象となるすべてのテーブルで SELECT DISTINCT ST_SRID(geom), GeometryType(geom) を 実行します — 1 つのクエリで 2 つの古典的なバグ群を排除できます。
3. 出力フィーチャを 5 つサンプリングし、ベースマップ上に描画します (QGIS は直接接続できます) — 数値は合格しても、ジオメトリが壊れていることがあります。
4. 実行可能として提示されたすべての sql フェンスは、構文とエイリアスの境界として扱います。 記述されたスキーマの前提の下で、上から下へ実行できなければなりません。 山括弧のプレースホルダー、省略記号、擬似コード、放棄された結合、 不完全なエイリアスをその中に入れないでください。SRID などのスキーマ値が 不明な場合は、それを尋ねるか、テンプレートをラベル付きの text ブロックに 保持してください。

落とし穴チェックリスト

• 4326 ジオメトリに対する ST_Area/ST_Length (平方度)。
• 面積や長さの測定に EPSG:3857/Web Mercator を使用する (系統的な歪み)。
• ST_DWithin ではなく ST_Distance < x を使用する (インデックスが使われない)。
• 結合述語内の ST_Transform。
• SRID が混在した型なしジオメトリ列。
• ST_Subdivide なしで国サイズのポリゴンを結合する。
• 妥当性の修復としての buffer(0) (パートが黙って失われる) — ST_MakeValid を使用する。
• フル解像度のジオメトリを Web クライアントに配信する。

実行契約

• ワークフロー: スキーマ、SRID、ジオメトリ型、サイズ、クエリの目的を調査し、述語とインデックスを選択し、監査可能な CTE を記述し、実行計画を調査し、結果を突き合わせ、安全に運用化します。
• 判断ルール: 並行、反復、またはトランザクションを伴う空間ワークロードには PostGIS を使用します。サーバーが価値を加えない限定的な単発の変換には、ファイルパイプラインまたは DuckDB Spatial を使用します。
• 検証プロトコル: SRID とジオメトリの不変条件を表明し、各結合で行数を確認し、インデックス付きの計画とタイミングを比較し、地図上でジオメトリをサンプリングし、境界のセマンティクスをテストします。
• 障害モード: SRID の混在、偶発的な多対多の爆発、無効なジオメトリ、インデックス化不可能な述語、geography/geometry の単位の混同、または説明のつかない計画の劣化がある場合は、リリースをブロックします。
• 成果物: 一貫した CTE/テーブルエイリアスを備えた自己完結型のパラメータ化された SQL またはマイグレーション、インデックスとその根拠、クエリ計画の証拠、行数の確認、サンプル検証、想定されるスキーマ、パフォーマンスに関する注記、ロールバックのガイダンス。
• ソースの鮮度: 関数や計画を選択する前に、デプロイされているデータベースと拡張機能のバージョンについて、信頼できるソースのレジストリ を参照してください。