PostgreSQL实战指南：多领域数据库应用与实践

第四部分：PostgreSQL与时空数据库

借助业界领先的 PostGIS 扩展，PostgreSQL 摇身一变，成为一个功能强大的地理空间信息系统（GIS）。本部分将带领读者深入探索 PostgreSQL 在时空数据领域的应用，从基础的地理数据处理到复杂的空间分析与路径规划，无所不包。

第17章：地理空间查询与空间关系分析

在上一章中，我们学习了如何存储地理空间数据。现在，是时候利用这些数据进行有意义的查询和分析了。本章将聚焦于 PostGIS 的核心功能：空间查询。我们将学习如何判断几何对象之间的空间关系、如何进行距离计算和范围查询，以及如何执行更复杂的空间操作。

17.1 空间索引的重要性

在我们开始之前，必须再次强调空间索引的重要性。几乎所有的空间查询函数都能从 GiST 索引中受益。如果没有索引，数据库将不得不对表中的每一行进行精确的几何计算，这将非常缓慢。有了索引，PostGIS 可以首先使用对象的边界框（bounding box）进行快速的、近似的筛选，只对少数可能匹配的候选对象进行精确计算，从而极大地提升查询性能。

1
2
-- 确保你的几何列上有 GiST 索引
CREATE INDEX idx_gist_poi_geom ON points_of_interest USING GIST (geom);

17.2 空间关系查询

PostGIS 提供了一整套函数来判断两个几何对象之间的拓扑关系，这些函数都返回布尔值 (true 或 false)。

ST_Intersects(geomA, geomB): A 和 B 是否有任何共同之处（相交）？这是最常用的空间关系函数。
ST_Contains(geomA, geomB): A 是否完全包含 B？
ST_Within(geomA, geomB): A 是否完全在 B 内部？（ST_Within(A,B) 等同于 ST_Contains(B,A)）
ST_Covers(geomA, geomB): A 是否覆盖 B？（比 ST_Contains 更通用，允许边界接触）
ST_Touches(geomA, geomB): A 和 B 是否在边界上接触，但内部不相交？
ST_Overlaps(geomA, geomB): A 和 B 是否有重叠部分，但互不完全包含？

17.3 空间测量与操作

除了关系判断，PostGIS 还能进行各种测量和操作。

ST_Distance(geomA, geomB): 计算两个几何对象之间的最短距离。
ST_DWithin(geomA, geomB, distance): A 和 B 之间的距离是否在指定的距离之内？这个函数非常高效，因为它能直接利用空间索引。
ST_Area(geom): 计算多边形的面积。
ST_Length(linestring): 计算线的长度。
ST_Buffer(geom, distance): 创建一个围绕几何对象的缓冲区（一个多边形）。
ST_Union(geomA, geomB): 合并两个几何对象。

重要提示: ST_Distance, ST_Area, ST_Length 等函数的计算结果单位取决于坐标系。

对于地理坐标系 (如 SRID 4326)，单位是度，这通常不是我们想要的。
对于投影坐标系 (如 SRID 3857)，单位是米或英尺。因此，在进行测量时，通常需要先使用 ST_Transform 将几何对象转换到合适的投影坐标系，或者直接使用 GEOGRAPHY 类型。

17.4 `GEOMETRY` vs `GEOGRAPHY`

PostGIS 提供了两种主要的类型来存储空间数据：

GEOMETRY: 在笛卡尔平面坐标系（平面地图）上进行计算。计算速度快，函数丰富。但对于全球范围的数据，距离和面积计算会因地球曲率而失真。
GEOGRAPHY: 在椭球体（地球模型）上进行计算。计算结果非常精确（单位总是米），但计算速度稍慢，支持的函数也比 GEOMETRY 少。

选择原则:

如果你的数据局限在一个小范围（如一个城市或省份），并且使用了合适的投影坐标系，使用 GEOMETRY 是最佳选择。
如果你的数据是全球范围的，或者你主要关心的是精确的距离测量（如经纬度点之间的距离），使用 GEOGRAPHY 更简单、更准确。

你可以通过类型转换 ::geography 来进行计算。

1
2
3
4
5
-- 使用 geography 类型计算两个经纬度点之间的精确距离 (米)
SELECT ST_Distance(
    'SRID=4326;POINT(-73.9855 40.7580)'::geography, -- Times Square
    'SRID=4326;POINT(-73.9654 40.7829)'::geography  -- Central Park
);

17.5 场景实战：查找附近的咖啡馆

业务场景描述:

一个移动应用需要一个功能：“查找我附近1公里内的所有咖啡馆”。

数据建模与查询:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- 1. 准备数据表
CREATE TABLE coffee_shops (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    -- 使用 geography 类型，因为距离计算是核心需求
    geog GEOGRAPHY(POINT, 4326)
);

-- 创建空间索引
CREATE INDEX idx_gist_coffee_shops_geog ON coffee_shops USING GIST (geog);

-- 插入数据
INSERT INTO coffee_shops (name, geog) VALUES
('Brew & Co.', 'SRID=4326;POINT(-73.9879 40.7527)'),
('The Daily Grind', 'SRID=4326;POINT(-73.9910 40.7500)'),
('Espresso Yourself', 'SRID=4326;POINT(-73.9821 40.7598)'),
('Distant Cafe', 'SRID=4326;POINT(-74.0000 40.7400)');

-- 2. 定义当前位置并执行查询
-- 假设我的当前位置是 Penn Station (-73.9936 40.7506)
WITH my_location AS (
    SELECT 'SRID=4326;POINT(-73.9936 40.7506)'::geography AS geog
)
SELECT
    cs.name,
    -- 计算并显示距离 (米)
    ST_Distance(cs.geog, my_loc.geog) AS distance_meters
FROM
    coffee_shops cs, my_location my_loc
WHERE
    -- 使用 ST_DWithin 进行高效的范围查询
    -- 查找距离在 1000 米内的咖啡馆
    ST_DWithin(cs.geog, my_loc.geog, 1000)
ORDER BY
    distance_meters;

代码解释与思考:

ST_DWithin: 这是实现“附近搜索”功能的关键。相比 ST_Distance(A, B) < 1000，ST_DWithin(A, B, 1000) 的性能要高得多，因为它被设计为可以直接利用 GiST 空间索引。它会先通过索引快速筛选出可能在范围内的候选项，然后再进行精确的距离计算。
GEOGRAPHY 类型: 在这个场景中，使用 geography 类型非常方便。我们无需关心坐标系转换，ST_Distance 和 ST_DWithin 的单位自然就是米，代码直观且结果准确。
WITH 子句 (CTE): 使用通用表表达式（Common Table Expression）来定义 my_location 使得查询更加清晰和易于维护。如果需要改变当前位置，只需修改 CTE 即可。

17.6 总结

本章我们深入学习了 PostGIS 的查询和分析能力。我们掌握了如何判断空间关系、如何进行精确的距离和面积测量，并理解了 GEOMETRY 和 GEOGRAPHY 类型之间的重要区别。通过“查找附近咖啡馆”的实战案例，我们学会了如何使用 ST_DWithin 来高效地实现基于位置的范围查询。

PostgreSQL实战指南：多领域数据库应用与实践 - 第17章：地理空间查询与空间关系分析

文章目录