0Pricing
SQL Academy · 课时

空间连接与包含关系

确定哪些点位于哪些区域中。

空间连接与包含关系 是 CoddyKit 上的免费 SQL Academy 课时。 这是第 3 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 SQL Academy 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 SQL Academy 课程共包含 4 节课。

什么是空间连接

空间连接根据地理关系而不是匹配键来组合两个表。您不再询问“这个 ID 是否等于那个 ID？”，而是询问“这个点是否落在那个多边形内？”或“这两个形状是否重叠？”

PostGIS 通过几何类型和空间函数扩展 PostgreSQL，使这些连接成为可能。结果与常规 SQL JOIN 相同——组合两个表中的行——但连接条件是几何关系。

设置示例表

我们将创建两个用于操作的表：cities 用于存储点位置，countries 用于存储多边形边界。两者都使用 PostGIS 提供的 GEOMETRY 类型，并采用 SRID 4326（标准 WGS84 经纬度）。

SRID（空间参考 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)
);

插入示例数据

我们将使用 ST_GeomFromText，从熟知文本（WKT）格式插入几何值。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：核心函数

当几何体 A 完全包含几何体 B 时，ST_Contains(geometry A, geometry B) 会返回 TRUE。在当前场景中，当城市点位于国家多边形内部时，ST_Contains(country.border, city.location) 会返回真值。

这是 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 索引（广义搜索树）允许 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) 就会返回 TRUE。它比 ST_Contains 的条件更宽松：两个相交的多边形即使彼此都没有完全包含对方，也会被判定为相交。

对于点在多边形内的测试，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 用于测量两个几何体之间的距离，从而支持 ORDER BY + LIMIT 模式，或支持使用 LATERAL 连接配合 ORDER BY ... LIMIT 1。

这称为最近邻连接，常用于将 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;

实际示例：行政区中的餐厅

下面是一个完整的实际示例：找出每家餐厅所属的城市行政区，然后统计每个行政区中的餐厅数量。这种模式适用于所有点在多边形内的场景，例如社区中的自动取款机、辖区内的事故、配送区域中的订单。

该查询在空间连接中使用 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(多边形, 点)——当多边形完全包含点时返回真值。
  • ST_Within(点, 多边形)——作用相反；在逻辑上等同于 ST_Contains。
  • ST_Intersects——更通用的重叠测试；对于点在多边形内的查询更适合使用索引。
  • 在几何列上创建GIST 索引对于大规模数据下的性能至关重要。
  • 将空间连接与 GROUP BY 结合，以统计或聚合每个区域中的点。
  • 使用 LEFT JOIN 检测落在所有多边形之外的点。

空间连接让您可以直接在结构化查询语言中进行地理分析，无需外部 GIS 工具。

常见问题解答

「空间连接与包含关系」课时是免费的吗？

是的 — 「空间连接与包含关系」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 SQL Academy 课程的其余内容，请升级到 CoddyKit PRO。 SQL Academy 课程共包含 4 节课。

「空间连接与包含关系」这节课中我会学到什么？

确定哪些点位于哪些区域中。 你通过在浏览器中直接运行的动手代码来练习 SQL Academy，全天候 AI 导师会在你学习这节课的过程中回答你的问题。

学习 SQL Academy 需要有经验吗？

无需任何先前经验。CoddyKit 上的 SQL Academy 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 3 节课，共 4 节。

「空间连接与包含关系」课时需要多长时间？

大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。

我能在这节 SQL Academy 课中编写并运行代码吗？

能。每节 SQL Academy 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。

此课程中的所有课时

  1. 空间数据类型
  2. 距离与最近邻
  3. 空间连接与包含关系
  4. 空间索引（GiST）
← 返回 SQL Academy