数据库基础体系 · 第 104/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

PostgreSQL PostGIS：空间类型、坐标系、空间索引和查询优化

PostGIS 是 PostgreSQL 的空间扩展。它在 PostgreSQL 的类型系统、函数系统、操作符系统和索引框架之上，增加了几何对象、地理坐标计算、空间关系判断、空间索引以及空间数据导入导出能力。

因此，理解 PostGIS 不能只记住几个函数名。一个空间查询是否正确，通常同时取决于：

1. 列使用的是 geometry 还是 geography；
2. 坐标的含义和 SRID 是否正确；
3. 距离、面积和关系判断使用的单位与数学模型；
4. 查询是否写成了空间索引可以识别的形式；
5. 索引筛选后的候选结果是否仍需精确计算；
6. 几何数据是否有效；
7. PostgreSQL 优化器是否拥有足够的统计信息。

---

一、PostGIS 在 PostgreSQL 中做了什么

安装 PostGIS 后，数据库中会增加一组扩展对象：

• geometry、geography 等空间类型；
• ST_Intersects、ST_DWithin、ST_Transform 等空间函数；
• &&、<-> 等空间操作符；
• GiST 等空间索引操作类；
• 管理 SRID、空间参考系统和几何元数据的表与函数。

PostGIS 是数据库扩展，不是 PostgreSQL 内核默认组件。需要在具体数据库中启用：

CREATE EXTENSION postgis;

这条语句的作用域是当前数据库，而不是整个 PostgreSQL 集群。连接到 gis_demo 数据库后执行，它只影响 gis_demo。

可以使用以下查询确认扩展和版本：

SELECT extname, extversion
FROM pg_extension
WHERE extname = 'postgis';

SELECT PostGIS_Full_Version();

扩展升级也属于数据库变更，应在目标版本支持的升级路径内执行，并在测试环境验证函数、索引操作类和空间参考数据是否兼容。不能把“PostgreSQL 版本升级”和“PostGIS 扩展升级”简单视为同一件事。

---

二、空间对象的基本模型

2.1 Geometry 的类型层次

PostGIS 的 geometry 表示平面或投影坐标空间中的几何对象。常见类型包括：

• POINT：点；
• LINESTRING：线；
• POLYGON：面；
• MULTIPOINT、MULTILINESTRING、MULTIPOLYGON：多部件对象；
• GEOMETRYCOLLECTION：混合几何集合。

例如：

SELECT
    ST_GeomFromText('POINT(116.397 39.908)', 4326) AS geom,
    ST_GeometryType(
        ST_GeomFromText('POINT(116.397 39.908)', 4326)
    ) AS type;

结果中的几何对象包含两类信息：

1. 坐标值，例如点的 X=116.397、Y=39.908；
2. SRID，即空间参考系统标识，例如 4326。

但是，几何对象本身并不自动知道“116.397”应该解释成米、度还是其他单位。这个含义由坐标参考系统和数据约定决定。

2.2 几何对象不等于有效几何对象

一个对象可以在类型上是 POLYGON，但拓扑上无效。例如，多边形边界自相交：

SELECT
    ST_IsValid(geom),
    ST_IsValidReason(geom)
FROM (
    SELECT ST_GeomFromText(
        'POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))',
        0
    ) AS geom
) AS s;

这个多边形形成“蝴蝶结”结构，通常会得到无效结果。空间关系和面积计算可能因此产生错误、异常或与业务预期不符。

可以使用：

SELECT ST_MakeValid(geom)
FROM invalid_geometries;

但 ST_MakeValid 不是“无损修复”按钮。修复后可能出现：

• 一个多边形变成多个多边形；
• 几何类型发生变化；
• 原本重叠或自交的部分被重新组织。

因此，修复应当是经过验证的数据处理步骤，而不是在每次查询中无条件调用。

还需要区分：

• NULL：没有值；
• GEOMETRYCOLLECTION EMPTY 等空几何：有空间对象类型，但不含坐标；
• 无效几何：有坐标，但不满足对应拓扑规则。

这三者在过滤、聚合和空间关系判断中的含义不同。

---

三、geometry 与 geography

这是 PostGIS 最重要的类型选择之一。

3.1 Geometry：在指定坐标空间中计算

geometry 的计算通常基于平面坐标。距离和面积的单位来自坐标参考系统。

例如，使用经纬度坐标：

SELECT ST_Distance(
    ST_SetSRID(ST_Point(116.397, 39.908), 4326),
    ST_SetSRID(ST_Point(116.407, 39.908), 4326)
);

结果的数值单位是“度”，不是米。直接把这个结果解释为米是错误的。

若要进行米制距离计算，通常应将数据转换到适合该区域的投影坐标系：

SELECT ST_Distance(
    ST_Transform(
        ST_SetSRID(ST_Point(116.397, 39.908), 4326),
        3857
    ),
    ST_Transform(
        ST_SetSRID(ST_Point(116.407, 39.908), 4326),
        3857
    )
);

这里的结果单位通常是米，但 Web Mercator（EPSG:3857）在距离和面积方面存在明显变形，尤其不适合把全球范围内的结果当作高精度测量值。工程上应选择适合业务区域和测量目的的投影坐标系，而不是默认使用 3857。

3.2 Geography：面向地球表面的经纬度模型

geography 用于地球表面上的经纬度对象，常见输入是 WGS 84 地理坐标。它适合：

• 全球或跨区域数据；
• “距离多少米以内”的经纬度数据；
• 不希望业务代码自行选择局部投影的场景。

例如：

SELECT ST_Distance(
    'SRID=4326;POINT(116.397 39.908)'::geography,
    'SRID=4326;POINT(116.407 39.908)'::geography
);

geography 的距离结果单位为米。计算模型基于地球椭球体或球体近似，具体函数参数和版本语义需要查看对应 PostGIS 版本文档。

但 geography 不是 geometry 的“更准确替代品”：

• 支持的函数和操作符范围有所不同；
• 某些复杂空间操作更适合先投影为 geometry；
• 地理坐标下的极点、日期变更线和大范围多边形需要特别注意；
• 对区域性、高频空间分析，合适的投影 geometry 往往更可控。

3.3 类型选择的实际判断

可以用以下原则初步判断：

场景	常见选择
城市道路、楼宇、地块分析	合适投影下的 geometry
全球门店、航线、用户位置	geography 或保留 4326 geometry 并在计算时转换
只保存经纬度、很少做空间计算	可使用 geometry(Point,4326)
需要米、平方米等测量单位	geography 或合适投影后的 geometry

不要仅因为数据输入是经纬度，就认为所有查询都必须使用 geography。存储类型、计算坐标系和展示坐标系可以不同。

---

四、SRID、坐标系与坐标转换

4.1 SRID 是标识，不是转换

SRID 是空间参考系统的标识。例如：

• 4326：常用于 WGS 84 经纬度；
• 3857：常用于 Web Mercator；
• 其他 EPSG 编号对应不同的投影、基准面和单位。

ST_SetSRID 只设置或修改元数据，不移动坐标：

SELECT ST_AsText(
    ST_SetSRID(ST_Point(116.397, 39.908), 4326)
);

坐标数值仍然是 116.397 和 39.908。

ST_Transform 才会根据源 SRID 和目标 SRID 进行坐标转换：

SELECT ST_AsText(
    ST_Transform(
        ST_SetSRID(ST_Point(116.397, 39.908), 4326),
        3857
    )
);

转换结果的数值会改变。

因此，下面的操作是错误风险很高的：

-- 错误示例：把实际为 4326 的坐标直接标记成 3857
SELECT ST_SetSRID(geom, 3857)
FROM locations;

如果原坐标仍是经纬度，这只是在“伪装”坐标系。后续距离、范围和空间关系判断都会基于错误的单位和位置。

正确流程通常是：

ST_Transform(
    ST_SetSRID(raw_geom, 4326),
    3857
)

前提是 raw_geom 的坐标确实来自 4326。

4.2 轴顺序与输入约定

PostGIS 的常见使用约定是：

POINT(longitude latitude)

也就是：

POINT(X Y) = POINT(经度 纬度)

例如北京附近的点通常写作：

ST_SetSRID(ST_Point(116.397, 39.908), 4326)

而不是把纬度放在前面。

不同 GIS 标准和外部工具可能存在轴顺序约定差异。工程中应明确：

• API 输入字段的顺序；
• WKT、GeoJSON 和数据库之间的转换规则；
• 前端地图坐标是否经过加密或偏移；
• 数据供应商所说的“WGS84”是否真的代表 EPSG:4326。

4.3 在表结构中约束类型和 SRID

不要只使用宽泛的 geometry 类型，然后依赖应用代码自觉传入正确数据。可以使用 typmod 约束：

CREATE TABLE places (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     text NOT NULL,
    geom     geometry(Point, 4326) NOT NULL
);

这个定义同时约束：

• 必须是 Point；
• 必须是 SRID 4326；
• 不能为 NULL。

若要存储米制投影坐标：

CREATE TABLE projected_places (
    id       bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name     text NOT NULL,
    geom     geometry(Point, 4490) NOT NULL
);

实际 SRID 应根据业务区域选择。不能把示例中的 SRID 直接当作所有系统的推荐值。

可以在写入时显式转换：

INSERT INTO places(name, geom)
VALUES (
    '示例地点',
    ST_Transform(
        ST_SetSRID(ST_Point(116.397, 39.908), 4326),
        4326
    )
);

这里目标和源相同只是为了展示流程；真实系统不需要进行这种无意义的转换。

---

五、空间关系函数与距离函数

5.1 拓扑关系不是普通比较

空间关系函数判断的是几何对象之间的拓扑关系。例如：

• ST_Intersects(a, b)：两个对象是否有共同点；
• ST_Contains(a, b)：a 是否包含 b；
• ST_Covers(a, b)：a 是否覆盖 b，边界语义通常比 ST_Contains 更符合“落在区域内”的业务表达；
• ST_Within(a, b)：a 是否位于 b 内；
• ST_Touches(a, b)：是否接触但内部不相交；
• ST_Overlaps(a, b)：是否部分重叠且维度相同；
• ST_Crosses(a, b)：是否发生穿越关系。

边界语义非常重要。例如，一个点恰好落在多边形边界上时，ST_Contains 和 ST_Covers 的业务结果可能不同。不能因为两者都常被翻译为“包含”，就随意替换。

示例：

WITH s AS (
    SELECT
        ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 0) AS poly,
        ST_GeomFromText('POINT(0 5)', 0) AS boundary_point
)
SELECT
    ST_Contains(poly, boundary_point) AS contains_result,
    ST_Covers(poly, boundary_point) AS covers_result
FROM s;

这里点位于多边形边界上。这个结果能说明为什么需要先确认业务语义，而不是只记函数名称。

5.2 距离查询中的 ST_DWithin

“距离不超过某个阈值”应优先使用：

ST_DWithin(geom, target, radius)

而不是：

ST_Distance(geom, target) <= radius

原因有两层：

1. ST_DWithin 表达了一个直接的空间谓词；
2. 对支持空间索引的列，PostGIS 可以先进行索引候选过滤，再做精确距离判断。

例如，投影坐标单位为米时：

SELECT id, name
FROM projected_places
WHERE ST_DWithin(
    geom,
    ST_SetSRID(ST_Point(12950000, 4850000), 3857),
    1000
);

这里的 1000 表示 1000 个坐标单位；若该坐标系以米为单位，就是 1000 米。

对于 geography：

SELECT id, name
FROM places
WHERE ST_DWithin(
    geom::geography,
    'SRID=4326;POINT(116.397 39.908)'::geography,
    1000
);

这里的半径是 1000 米。

ST_DWithin 只负责过滤。若业务还需要按实际距离排序，应额外计算：

SELECT
    id,
    name,
    ST_Distance(
        geom::geography,
        'SRID=4326;POINT(116.397 39.908)'::geography
    ) AS distance_m
FROM places
WHERE ST_DWithin(
    geom::geography,
    'SRID=4326;POINT(116.397 39.908)'::geography,
    1000
)
ORDER BY distance_m;

5.3 最近邻与 <->

PostGIS 提供 KNN 距离操作符 <->，可以与 GiST 索引配合处理最近邻：

CREATE INDEX places_geom_gist
ON places
USING GIST (geom);

查询：

SELECT
    id,
    name,
    geom <-> ST_SetSRID(ST_Point(116.397, 39.908), 4326) AS distance_key
FROM places
ORDER BY geom <-> ST_SetSRID(ST_Point(116.397, 39.908), 4326)
LIMIT 10;

这里有两个要点：

• ORDER BY 中应直接使用索引列和常量或参数几何对象；
• 该距离排序使用的是对应空间类型和操作类支持的距离语义，不能未经验证地把结果当作米。

如果查询的是“半径内的最近 10 个”，应同时使用范围过滤和最近邻排序：

SELECT
    id,
    name
FROM places
WHERE ST_DWithin(
    geom::geography,
    'SRID=4326;POINT(116.397 39.908)'::geography,
    5000
)
ORDER BY geom <-> ST_SetSRID(ST_Point(116.397, 39.908), 4326)
LIMIT 10;

对于 geography、投影方式和具体索引操作类，应使用实际执行计划验证排序是否按预期使用索引。

---

六、空间索引的核心机制

6.1 为什么 B-tree 不适合二维空间

B-tree 适合一维可排序值，例如：

WHERE id = 10
WHERE created_at >= now() - interval '1 day'
ORDER BY name

二维几何对象没有一个能同时保持所有空间邻近关系的一维顺序。

例如，两个点在地图上很近，不代表它们在 WKT 文本、X 坐标或某个单一排序键上也相邻。因此，不能期待普通 B-tree 直接解决：

WHERE ST_Intersects(geom, query_polygon)

空间索引需要使用能够组织多维范围的索引结构。

6.2 GiST 是通用索引框架

GiST 是 PostgreSQL 提供的通用搜索树框架。PostGIS 使用特定的 operator class 将几何对象映射为索引可比较的空间摘要，最常见的是几何对象的包围盒。

对于一个几何对象 g，二维包围盒可以表示为：

[minX, maxX] × [minY, maxY]

查询区域也有一个包围盒。索引首先判断两个包围盒是否可能相交。

若两个包围盒不相交，则真实几何一定不相交，可以排除。

若两个包围盒相交，则只能说明“可能相交”，真实几何可能仍然不相交，必须继续执行精确几何判断。

这个过程可形式化为：

真实相交集合 ⊆ 包围盒相交候选集合

因此，空间索引通常是一个“两阶段过滤器”：

1. 索引阶段：使用包围盒或其他索引摘要快速生成候选；
2. 精确阶段：调用真正的几何算法确认关系。

这也是执行计划中可能出现：

Index Cond: (geom && ...)
Filter: st_intersects(geom, ...)
Rows Removed by Filter: ...

的原因。Rows Removed by Filter 并不一定表示索引失效，而可能表示包围盒候选中包含了真实关系不成立的对象。

6.3 建立和检查空间索引

CREATE INDEX places_geom_gist
ON places
USING GIST (geom);

ANALYZE places;

ANALYZE 为优化器收集统计信息。空间索引和空间统计信息是两件事：

• GiST 索引负责访问路径；
• 统计信息帮助优化器估计选择率和行数。

大批量导入后，应根据数据变更量执行 ANALYZE，否则优化器可能错误估计空间条件的结果规模。

生产环境在不阻塞正常写入的前提下，可考虑：

CREATE INDEX CONCURRENTLY places_geom_gist
ON places
USING GIST (geom);

CREATE INDEX CONCURRENTLY 会经历多个阶段，耗时更长，并且不能放在普通事务块中。失败时可能留下需要检查和清理的无效索引对象。应根据 PostgreSQL 的并发建索引规则安排操作，不能把它当作普通 CREATE INDEX 的无锁替代品。

6.4 SP-GiST、BRIN 与 GiST 的边界

PostGIS 某些几何类型和版本提供 SP-GiST 或 BRIN 相关操作类，但它们不是“所有空间数据的默认替代品”。

• GiST 通常是通用空间查询的首选；
• SP-GiST 适合某些具有特定空间分区特征的数据和操作类；
• BRIN 依赖物理存储顺序与页级摘要，适合数据按空间或时间相关顺序批量写入、且表规模很大的场景；
• 不同 PostGIS 版本、数据类型和操作类支持范围可能不同。

应先检查当前环境实际支持的操作类：

SELECT
    opcname,
    amname
FROM pg_opclass oc
JOIN pg_am am ON am.oid = oc.opcmethod
WHERE opcname ILIKE '%gist%'
   OR opcname ILIKE '%spgist%'
   OR opcname ILIKE '%brin%';

不能仅根据索引名称猜测其能力。最终仍应通过 EXPLAIN (ANALYZE, BUFFERS) 验证。

---

七、从建表到查询的完整示例

下面使用一个经纬度点表，展示数据约束、索引、半径查询和执行计划。

7.1 初始化数据

CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE city_places (
    id      bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name    text NOT NULL,
    geom    geometry(Point, 4326) NOT NULL
);

INSERT INTO city_places(name, geom)
VALUES
    ('天安门附近', ST_SetSRID(ST_Point(116.397, 39.908), 4326)),
    ('故宫附近',   ST_SetSRID(ST_Point(116.397, 39.916), 4326)),
    ('西单附近',   ST_SetSRID(ST_Point(116.374, 39.907), 4326));

CREATE INDEX city_places_geom_gist
ON city_places
USING GIST (geom);

ANALYZE city_places;

这些语句可以放在一个事务中执行，但大表上的索引创建、批量导入和在线变更需要结合锁与事务持续时间单独规划。CREATE EXTENSION 还需要足够的数据库权限，生产环境通常由迁移系统或数据库管理员执行。

7.2 查询 2 千米内的地点

SELECT
    id,
    name,
    ST_Distance(
        geom::geography,
        'SRID=4326;POINT(116.397 39.908)'::geography
    ) AS distance_m
FROM city_places
WHERE ST_DWithin(
    geom::geography,
    'SRID=4326;POINT(116.397 39.908)'::geography,
    2000
)
ORDER BY distance_m;

这里的逻辑是：

1. geom 中保存经度和纬度；
2. 过滤阶段将几何转换为 geography，使半径单位为米；
3. ST_DWithin 先判断是否在 2000 米内；
4. 只有通过过滤的行才计算精确距离；
5. 最后按实际距离排序。

如果这是高频查询，反复在查询中写 geom::geography 可能使索引匹配和计算成本变得复杂。可以建立表达式索引：

CREATE INDEX city_places_geog_gist
ON city_places
USING GIST ((geom::geography));

查询表达式应与索引表达式保持一致：

SELECT id, name
FROM city_places
WHERE ST_DWithin(
    geom::geography,
    'SRID=4326;POINT(116.397 39.908)'::geography,
    2000
);

生产使用前必须通过实际计划验证表达式索引是否被采用，因为是否使用索引还取决于表规模、选择率、统计信息和优化器成本判断。

---

八、查询优化的关键原则

8.1 优先使用索引感知的空间谓词

以下形式通常更容易触发 PostGIS 的空间索引优化：

ST_Intersects(geom, :query_geom)
ST_Within(geom, :query_geom)
ST_Contains(:query_geom, geom)
ST_DWithin(geom, :query_point, :radius)
geom && :query_geom
ORDER BY geom <-> :query_point

但“函数名称看起来正确”不等于“一定走索引”。必须检查计划：

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, name
FROM city_places
WHERE ST_DWithin(
    geom::geography,
    'SRID=4326;POINT(116.397 39.908)'::geography,
    2000
);

关注：

• 是否出现 Index Scan 或 Bitmap Index Scan；
• Index Cond 是否包含空间操作；
• 实际行数与估算行数是否相差很大；
• Rows Removed by Filter 是否异常高；
• 是否因为表很小而选择顺序扫描。

小表选择 Seq Scan 并不代表索引坏了。若扫描整张表的成本低于访问索引和回表，顺序扫描可能是正确选择。

8.2 不要把空间列包在无法匹配的函数中

例如：

WHERE ST_Transform(geom, 3857) && :query_geom

如果只有 geom 上的 GiST 索引，索引未必能直接用于这个表达式。可选方案有两种。

第一种：将查询对象转换到表中几何的 SRID：

WHERE geom && ST_Transform(:query_geom, 4326)

第二种：建立表达式索引：

CREATE INDEX city_places_geom_3857_gist
ON city_places
USING GIST (ST_Transform(geom, 3857));

然后查询同一表达式：

WHERE ST_Transform(geom, 3857) && :query_geom_3857

表达式索引会增加写入成本和存储空间。若查询模式固定、转换成本高且访问频繁，它才有明确价值。

8.3 不要手工用 ST_Expand 替代 ST_DWithin

旧式空间查询常见写法是：

geom && ST_Expand(:point, :radius)
AND ST_Distance(geom, :point) <= :radius

这种写法有时仍有用途，但容易出现单位错误和重复逻辑。现代 PostGIS 中，优先使用：

ST_DWithin(geom, :point, :radius)

它同时表达范围条件，并提供更直接的索引优化路径。

8.4 空间连接的代价

空间连接通常写成：

SELECT p.id, a.id
FROM points p
JOIN areas a
  ON ST_Intersects(p.geom, a.geom);

执行过程可能是：

1. 选择一个表作为外层；
2. 对每个外层几何使用另一表的空间索引生成候选；
3. 对候选执行精确相交判断；
4. 输出匹配结果。

如果两个表都很大，连接代价可能非常高。优化依赖：

• 两边是否都有空间索引；
• 连接条件是否直接使用索引列；
• 区域几何是否极度复杂；
• 数据分布是否均匀；
• 统计信息是否新鲜；
• 是否可以先用业务条件缩小任一侧数据集。

例如：

SELECT p.id, a.id
FROM points p
JOIN areas a
  ON ST_Intersects(p.geom, a.geom)
WHERE p.created_at >= DATE '2025-01-01'
  AND a.region_code = 'north';

普通条件应尽量提前参与过滤，但最终是否改变执行计划，要看优化器估算结果，而不是依赖 SQL 文本中的书写顺序。

---

九、空间查询的正确性边界

9.1 SRID 不匹配

不同 SRID 的 geometry 不能被当作同一坐标空间直接比较。常见失败表现包括：

• 抛出 SRID 不一致错误；
• 结果为空或完全不符合地图位置；
• 距离数值看似合理但单位错误。

处理顺序应是：

1. 确认每个输入坐标的真实来源；
2. 使用 ST_SetSRID 正确标记原坐标；
3. 使用 ST_Transform 转换到统一计算坐标系；
4. 再执行空间关系或距离函数。

9.2 经纬度下直接计算距离

错误示例：

WHERE ST_Distance(
    geom,
    ST_SetSRID(ST_Point(116.397, 39.908), 4326)
) < 1000

这里的 1000 不是 1000 米，而是 1000 个角度单位，查询结果没有业务意义。

修正方式可以是：

WHERE ST_DWithin(
    geom::geography,
    'SRID=4326;POINT(116.397 39.908)'::geography,
    1000
)

或者把两者都转换到合适的米制投影坐标系后使用 geometry。

9.3 ST_Contains 的边界误解

“点在行政区域内”并不总是等于 ST_Contains(area, point)。如果边界点也应该归属于区域，通常应认真评估 ST_Covers(area, point)。

边界语义还会受到：

• 几何有效性；
• 浮点计算；
• 数据采集精度；
• 多边形重叠和缝隙；

等因素影响。行政区划等业务通常还需要定义边界归属规则，而不能只依赖一个函数名。

9.4 多边形面积与投影选择

在 4326 geometry 上执行：

SELECT ST_Area(geom)
FROM regions;

结果单位是平方度，通常不是业务需要的面积单位。

常见做法是转换到适合区域的投影：

SELECT ST_Area(ST_Transform(geom, :local_srid))
FROM regions;

其中 :local_srid 必须根据区域和精度要求选择。对跨洲、跨极区或全球多边形，单一局部投影可能不适用，应评估 geography 或分区计算方案。

---

十、诊断空间查询为什么慢

10.1 先看执行计划，而不是只看 SQL

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...

常见诊断路径如下：

情况一：顺序扫描

Seq Scan on city_places

可能原因：

• 表很小；
• 查询选择率很高，绝大多数行都会匹配；
• GiST 索引成本高于顺序扫描；
• 查询表达式无法匹配索引；
• 统计信息过期；
• 强制转换或函数破坏了索引条件。

情况二：索引候选很多，精确过滤很慢

如果执行计划显示大量：

Rows Removed by Filter

通常说明包围盒较粗，候选对象很多。典型原因包括：

• 多边形包围盒远大于其真实形状；
• 几何跨度很大；
• MULTIPOLYGON 包含相距很远的多个部分；
• 查询区域过大；
• 数据存在大量重叠。

此时可以考虑拆分多部件对象、规范化数据、缩小查询范围或优化数据模型，而不是盲目增加索引数量。

情况三：估算行数严重错误

若计划估算行数为几十，而实际为数百万，优化器可能选择错误的连接顺序或扫描方式。可以执行：

ANALYZE city_places;

并检查表的自动分析配置、数据分布和查询参数化方式。空间数据高度倾斜时，默认统计信息可能不足以准确描述所有区域的选择率。

10.2 检查索引和列定义

\d+ city_places

检查：

• 空间列是否为预期类型；
• SRID 是否被 typmod 约束；
• 是否存在 GiST 索引；
• 索引表达式是否与查询表达式一致。

还可以查询索引定义：

SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'city_places';

10.3 先过滤再转换

如果表中保存的是 4326 geometry，查询又需要较复杂的投影转换，常见策略是：

• 使用原始几何索引做粗过滤；
• 对候选结果再 ST_Transform 做精确计算；
• 或为稳定、高频的目标坐标系建立表达式索引。

例如：

SELECT id, name
FROM city_places
WHERE geom && ST_Expand(
    ST_SetSRID(ST_Point(116.397, 39.908), 4326),
    0.02
)
AND ST_DWithin(
    ST_Transform(geom, 3857),
    ST_Transform(
        ST_SetSRID(ST_Point(116.397, 39.908), 4326),
        3857
    ),
    2000
);

这段 SQL 的正确性依赖于 ST_Expand 的单位。对 4326 来说，0.02 是度，不是米，只适合做粗略候选框，不能替代最终的米制距离判断。若直接手写这种双阶段逻辑，必须明确其近似边界。

---

十一、数据建模与生产边界

11.1 原始坐标与计算坐标可以分离

一种常见设计是：

CREATE TABLE assets (
    id            bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name          text NOT NULL,
    geom_wgs84    geometry(Point, 4326) NOT NULL,
    geom_local    geometry(Point, 4490)
);

geom_wgs84 用于交换和展示，geom_local 用于区域性距离、面积和高频分析。这样可以避免每次查询都重复坐标转换，但需要保证两个字段一致。

可以在应用写入事务中同时生成，也可以使用触发器或生成列；具体可行性取决于函数稳定性、数据库版本和部署规范。无论采用哪种方式，都应测试坐标转换依赖的 PROJ 数据和扩展升级行为。

11.2 分区不是空间索引的替代品

按时间分区、租户分区或区域分区可以减少需要扫描的分区数量，但每个分区仍可能需要自己的空间索引。

例如轨迹数据可以按月份分区：

轨迹查询条件 = 时间范围 + 空间范围

先通过分区裁剪排除无关月份，再在保留分区内使用 GiST。若查询没有时间条件，分区可能无法带来预期收益，甚至增加计划和维护复杂度。

11.3 FDW 和扩展边界

PostGIS 空间类型和索引属于数据库端能力。通过 FDW 访问外部数据时，空间谓词是否下推、坐标系是否一致、远端是否安装 PostGIS，都需要单独验证。

不能因为本地表支持：

ST_Intersects(geom, :query_geom)

就假设外部数据源也会执行同样的空间索引过滤。常见风险包括：

• 外部表整批拉回本地；
• 远端没有相同 SRID 定义；
• 远端函数语义不同；
• 本地与远端扩展版本不一致。

因此，FDW 查询应通过 EXPLAIN 检查是否发生条件下推，并根据数据量决定是否先在远端物化或预过滤。

11.4 JSONB 与空间列的职责分离

空间位置不应仅作为 JSONB 中的经纬度字段保存：

{"longitude": 116.397, "latitude": 39.908}

JSONB 适合保存结构变化大的属性，但无法自然替代 PostGIS 的：

• SRID 约束；
• 空间关系；
• 米制距离；
• GiST 空间索引；
• 几何有效性检查。

更合理的模型通常是：

CREATE TABLE devices (
    id          bigint PRIMARY KEY,
    attributes  jsonb NOT NULL,
    position    geometry(Point, 4326) NOT NULL
);

JSONB GIN 索引和空间 GiST 索引分别服务于属性过滤和空间过滤。两者可以并存，但优化器会根据选择率和成本决定使用方式，不应假设两个索引一定会同时被高效使用。

---

十二、一个可复用的检查顺序

遇到空间查询结果错误或性能异常时，可以按以下因果顺序排查：

1. 类型：列是 geometry 还是 geography？
2. 真实坐标：输入坐标的来源和轴顺序是什么？
3. SRID：是否正确标记，而不是错误地用 ST_SetSRID 冒充转换？
4. 单位：当前距离、面积和半径的单位是什么？
5. 有效性：ST_IsValid 是否通过？
6. 语义：边界点应该算 Contains 还是 Covers？
7. 索引：是否存在匹配空间列或表达式的 GiST 索引？
8. 谓词：是否使用了 ST_DWithin、空间关系函数或 <-> 等索引感知形式？
9. 统计信息：是否在批量导入或大规模更新后执行了 ANALYZE？
10. 计划：EXPLAIN (ANALYZE, BUFFERS) 中实际扫描和过滤发生在哪里？
11. 数据分布：包围盒是否过大、几何是否过于复杂或严重重叠？
12. 部署边界：查询是否跨分区、FDW、不同数据库或不同 PostGIS 版本？

PostGIS 的正确使用，本质上是把“空间数据的数学含义”和“PostgreSQL 的执行机制”连接起来：先确保坐标和拓扑语义正确，再让 GiST 等索引缩小候选集合，最后通过精确几何计算得到结果。任何一个环节被忽略，都会出现“查询很快但结果错误”或“结果正确但无法扩展”的问题。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：PostgreSQL 参数与性能：内存、WAL、Planner、连接和 Autovacuum
• 下一篇：Oracle Schema 与数据类型：NUMBER、字符、日期、LOB 和对象
• 延伸：PostgreSQL JSONB 与扩展生态：全文检索、分区、FDW 和扩展边界
• 延伸：PostgreSQL 索引与优化器：B-tree、GIN、GiST、BRIN 和执行计划

官方资料

• PostgreSQL Current Documentation
• PostgreSQL Internals

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。