SQL Academy · レッスン

空間結合と包含関係

Q: 「空間結合と包含関係」で何を学びますか？

どのポイントがどの領域に含まれるかを調べます ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

どのポイントがどの領域に含まれるかを調べます

「空間結合と包含関係」はCoddyKit上の無料SQL Academyレッスンです。これはレッスン3/4です。下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。

空間結合とは

空間結合は、一致するキーではなく地理的な関係に基づいて2つのテーブルを結合します。「このIDはあのIDと等しいか」と問う代わりに、「この点はあのポリゴンの内側にあるか」や「この2つの形状は重なっているか」といった問いに答えます。

PostGISは、geometry型と空間関数によってPostgreSQLを拡張し、このような結合を可能にします。結果は通常のSQL JOINと同じく、両方のテーブルの行を結合したものですが、条件が幾何学的な関係になります。

サンプルテーブルの設定

操作対象として2つのテーブルを作成します。citiesにはポイントの位置を、countriesにはポリゴンの境界を格納します。どちらも、SRID 4326（標準的なWGS84の緯度・経度）を指定したPostGISのGEOMETRY型を使用します。

SRID（Spatial Reference ID）は、PostGISが使用する座標系を指定します。SRID 4326はGPSデータで最も一般的に使用されます。

CREATE TABLE countries (
  id     SERIAL PRIMARY KEY,
  name   TEXT NOT NULL,
  border GEOMETRY(POLYGON, 4326)
);

CREATE TABLE cities (
  id       SERIAL PRIMARY KEY,
  name     TEXT NOT NULL,
  location GEOMETRY(POINT, 4326)
);

サンプルデータの挿入

Well-Known Text（WKT）形式からジオメトリ値を挿入するには、ST_GeomFromTextを使用します。WKTはジオメトリを表す標準的なテキスト形式です。ポイントにはPOINT(lon lat)、ポリゴンにはPOLYGON((...))を使用します。

WKTでは経度が緯度より先に来ることに注意してください。これは数学におけるX、Yの慣例に対応しています。

INSERT INTO countries (name, border) VALUES
  ('France', ST_GeomFromText('POLYGON((-5 42, 8 42, 8 51, -5 51, -5 42))', 4326)),
  ('Spain',  ST_GeomFromText('POLYGON((-9 36, 3 36, 3 44, -9 44, -9 36))', 4326));

INSERT INTO cities (name, location) VALUES
  ('Paris',    ST_GeomFromText('POINT(2.35 48.85)',   4326)),
  ('Madrid',   ST_GeomFromText('POINT(-3.70 40.42)',  4326)),
  ('Bordeaux', ST_GeomFromText('POINT(-0.58 44.84)',  4326)),
  ('Lisbon',   ST_GeomFromText('POINT(-9.14 38.72)',  4326));

ST_Contains：基本関数

ST_Contains(geometry A, geometry B)は、ジオメトリAがジオメトリBを完全に包含している場合にTRUEを返します。今回の用途では、都市のポイントが国のポリゴン内にある場合に、ST_Contains(country.border, city.location)がtrueを返します。

これはPostGISにおける包含結合の中心となる機能です。この関数はOGC標準の一部であり、任意のジオメトリ型の組み合わせで使用できます。

SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
JOIN   countries co
  ON   ST_Contains(co.border, ci.location);

ST_Within：逆の視点

ST_Within(geometry A, geometry B)はST_Containsと正反対の関数です。ジオメトリAがジオメトリBの完全に内側にある場合にTRUEを返します。ST_Within(city, country)は、論理的にはST_Contains(country, city)と同一です。

この場合、どちらの関数でも同じ結果になります。どちらを使うかは読みやすさの問題です。クエリとしてより自然に読める方を選んでください。

-- These two queries return identical results:

-- Using ST_Contains (country contains city)
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co ON ST_Contains(co.border, ci.location);

-- Using ST_Within (city is within country)
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co ON ST_Within(ci.location, co.border);

一致しないポイントを見つけるLEFT JOIN

通常のJOINでは、どの国のポリゴン内にも入らない都市が除外されます。LEFT JOINとWHERE ... IS NULLによるチェックを組み合わせると、包含するポリゴンがないポイントを見つけられます。これは、データ品質の問題やカバレッジ範囲外のポイントの検出に役立ちます。

SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
LEFT JOIN countries co
  ON   ST_Contains(co.border, ci.location)
ORDER BY co.name NULLS LAST;

ポリゴンごとのポイント数の集計

空間結合は集計と組み合わせると特に効果を発揮します。都市と国を結合してから国ごとにグループ化すると、各ポリゴン内に存在するポイント数を集計できます。このパターンは地理分析で非常によく使われます。たとえば、地域ごとの店舗数、地区ごとのイベント数、ゾーンごとのセンサー数などです。

SELECT
  co.name          AS country,
  COUNT(ci.id)     AS city_count
FROM   countries co
LEFT JOIN cities  ci
  ON   ST_Contains(co.border, ci.location)
GROUP BY co.name
ORDER BY city_count DESC;

パフォーマンス向上のための空間インデックスの使用

大規模なデータセットでは、すべてのポイントをすべてのポリゴンと比較するため、空間結合が遅くなることがあります。GISTインデックス（Generalized Search Tree）を使うと、PostGISはバウンディングボックスによる事前フィルタリングを利用し、正確なST_Containsの判定を実行する前に大半の比較を省略できます。

結合やフィルタリングに使用するジオメトリ列には、必ずGISTインデックスを作成してください。クエリプランナーが自動的に使用します。

-- Create GIST indexes on both geometry columns
CREATE INDEX idx_countries_border
  ON countries USING GIST (border);

CREATE INDEX idx_cities_location
  ON cities USING GIST (location);

-- The same join now benefits from index acceleration
SELECT ci.name, co.name
FROM   cities ci
JOIN   countries co
  ON   ST_Contains(co.border, ci.location);

ST_Intersects：重なり合うジオメトリ

ST_Intersects(A, B)は、2つのジオメトリが境界上で接しているだけの場合も含め、共通する点を1つでも持つ場合にTRUEを返します。これはST_Containsよりも許容範囲が広い関数です。2つのポリゴンが重なっていれば、どちらも相手を完全に包含していなくても交差していると判定されます。

ポイントがポリゴン内にあるかを判定する場合、ST_IntersectsとST_Containsは同等です。ただし、ST_Intersectsの方がGISTインデックスを効率的に使用するため、実際にはこちらがよく選ばれます。

-- ST_Intersects is index-friendly and equivalent
-- to ST_Contains for point-in-polygon tests
SELECT
  ci.name  AS city,
  co.name  AS country
FROM   cities   ci
JOIN   countries co
  ON   ST_Intersects(co.border, ci.location);

最も近いポリゴンへのポイントの結合

ポイントが境界上に正確にある場合や、複数のポリゴンの非常に近くにある場合は、すべての一致ではなく最も近いポリゴンを取得したいことがあります。ST_Distanceは2つのジオメトリ間の距離を測定するため、ORDER BY + LIMITのパターンや、ORDER BY ... LIMIT 1を使用する専用のLATERAL結合が可能になります。

これは最近傍結合と呼ばれ、GPSの軌跡を道路セグメントにスナップしたり、ポイントを最も近い地域に割り当てたりする場合によく使われます。

-- For each city, find the single closest country centroid
SELECT DISTINCT ON (ci.name)
  ci.name                              AS city,
  co.name                              AS nearest_country,
  ST_Distance(ci.location, ST_Centroid(co.border)) AS dist
FROM   cities   ci
CROSS JOIN countries co
ORDER BY ci.name, dist;

実践例：地区内のレストラン

現実的な一連の例として、各レストランがどの都市地区に属するかを見つけ、その後、地区ごとのレストラン数を集計します。このパターンは、あらゆるポイント・イン・ポリゴンのシナリオに適用できます。たとえば、近隣地域ごとのATM、管轄区域ごとの事故、配送ゾーンごとの注文などです。

このクエリでは、空間結合の中でST_Withinを使用し、結果をグループ化して概要レポートを作成します。

-- Assume tables: districts(id, name, boundary GEOMETRY)
--                restaurants(id, name, location GEOMETRY)

SELECT
  d.name                 AS district,
  COUNT(r.id)            AS restaurant_count,
  STRING_AGG(r.name, ', ' ORDER BY r.name) AS names
FROM   districts    d
LEFT JOIN restaurants r
  ON   ST_Within(r.location, d.boundary)
GROUP BY d.name
ORDER BY restaurant_count DESC;

理解度チェック

PostGISにおける空間結合と包含についての理解度を確認しましょう。

まとめ：空間結合と包含

このレッスンでは、PostGISの空間結合を使って「どのポイントがどのエリアに入るか」という問いに答える方法を学びました。

重要なポイント：

ST_Contains(polygon, point) — ポリゴンがポイントを完全に包含している場合にtrueを返します。
ST_Within(point, polygon) — 逆の関数です。論理的にはST_Containsと同等です。
ST_Intersects — より一般的な重なりの判定です。ポイント・イン・ポリゴンではインデックスを効率的に使用できます。
ジオメトリ列へのGISTインデックスは、大規模なデータで高いパフォーマンスを得るために不可欠です。
空間結合をGROUP BYと組み合わせると、地域ごとのポイント数を集計できます。
LEFT JOINを使うと、すべてのポリゴンの外側にあるポイントを検出できます。

空間結合を使えば、外部のGISツールを必要とせず、SQLだけで地理分析を実行できます。

よくある質問

「空間結合と包含関係」レッスンは無料ですか？

はい。「空間結合と包含関係」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。

「空間結合と包含関係」で何を学びますか？

どのポイントがどの領域に含まれるかを調べますブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。

SQL Academyを始めるのに経験は必要ですか？

事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。これはレッスン3/4です。

「空間結合と包含関係」レッスンにはどのくらい時間がかかりますか？

ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。

このSQL Academyレッスンでコードを書いて実行できますか？

はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。

このコースのすべてのレッスン

← SQL Academyに戻る