database

PostgreSQL PostGIS 地理空间

PostGIS 地理空间开发实战。涵盖 geometry 与 geography 类型的选择、SRID 坐标系统与投影、GiST 与 SP-GiST 空间索引的构建、ST_Contains 与 ST_DWithin 等关系运算、距离与面积计算、GeoJSON 与 WKT 的互转，以及空间查询的性能调优与常见踩坑。

Leeting Yan 8 分钟阅读 3572 字

PostGIS 把 PostgreSQL 变成了一套完整的地理空间数据库：它引入 geometry 与 geography 两种空间类型，提供数千个空间函数，并让 GiST 索引能够加速空间谓词。它被广泛应用于位置服务、物流路径、地理围栏、行政区划统计等场景——在这些场景里，用普通数值列存经纬度再自己算距离，几乎注定会踩到坐标系统、球面几何和索引失效三个大坑。

核心认知：空间查询的成败取决于三件事——选对类型（geometry 还是 geography）、算对坐标系（SRID）、用对索引（GiST 而非 B-tree）。

一、PostGIS 安装与几何类型

1.1 安装与启用

-- Debian/Ubuntu: apt install postgresql-16-postgis-3
-- macOS:         brew install postgis
CREATE EXTENSION postgis;

-- 验证版本
SELECT PostGIS_Version();

1.2 geometry 与 geography 的选择

这是 PostGIS 最重要的一次决策：

维度geometrygeography
坐标系平面（可投影）球面（WGS84 经纬度）
单位由 SRID 决定（米/度）始终为米
距离计算平面欧氏距离球面大圆距离
计算速度快慢（三角函数开销）
支持函数全量子集
适用城市级、已投影数据跨洲、经纬度原始数据
-- geometry：适合小范围、已投影到平面坐标系的数据
CREATE TABLE parcels (
    id    serial PRIMARY KEY,
    geom  geometry(Polygon, 3857)     -- Web Mercator
);

-- geography：适合经纬度原始数据，距离计算直接得到米
CREATE TABLE checkins (
    id     serial PRIMARY KEY,
    geog   geography(Point, 4326)     -- WGS84
);

1.3 常见几何子类型

POINT              点（POI、设备位置）
LINESTRING         线（道路、轨迹）
POLYGON            多边形（行政区、地块、围栏）
MULTIPOINT         多点集合
MULTILINESTRING    多线集合
MULTIPOLYGON       多面集合（含岛屿的行政区）
GEOMETRYCOLLECTION 混合集合

1.4 插入与构造

-- WKT 文本构造（需要显式指定 SRID）
INSERT INTO checkins (geog) VALUES (ST_GeogFromText('SRID=4326;POINT(116.404 39.915)'));

-- 用经度、纬度直接构造（注意顺序是 经度 纬度）
INSERT INTO checkins (geog) VALUES (ST_MakePoint(116.404, 39.915)::geography);

-- GeoJSON 构造
INSERT INTO parcels (geom) VALUES (
    ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[0,0],[1,0],[1,1],[0,1],[0,0]]]}')
);

关键：ST_MakePoint(x, y) 的参数顺序是先经度后纬度，与直觉相反，是最常见的错误来源。

二、坐标系统与 SRID

2.1 SRID 是什么

SRID（Spatial Reference System Identifier）标识一套坐标系统。最常见的两个：

4326  WGS84，经纬度，单位「度」——GPS 原始数据
3857  Web Mercator，单位「米」——在线地图瓦片（Google/OSM）

2.2 混用 SRID 的后果

-- 错误：两个不同 SRID 的几何直接比较，结果无意义
SELECT ST_Distance(
    ST_GeomFromText('POINT(0 0)', 4326),
    ST_GeomFromText('POINT(0 0)', 3857)
);
-- ERROR: Operation on mixed SRID geometries

即使不报错，把「度」当成「米」来算距离也是灾难：

在纬度 40° 处，1 度经度 ≈ 85km，1 度纬度 ≈ 111km
把 0.001 度当作 0.001 米，误差高达 8 万倍

2.3 投影转换

-- 从 4326 转换到 3857（在线地图用）
SELECT ST_Transform(geom, 3857) FROM parcels;

-- 查看几何的 SRID
SELECT ST_SRID(geom) FROM parcels LIMIT 1;

-- 转换后再计算距离（3857 单位为米）
SELECT ST_Distance(
    ST_Transform(a.geom, 3857),
    ST_Transform(b.geom, 3857)
) AS meters
FROM points a, points b WHERE a.id = 1 AND b.id = 2;

2.4 选 SRID 的实用建议

- 数据来自 GPS、跨区域 → 存 4326，用 geography 或按需投影
- 单一城市/国家、追求性能 → 投影到当地平面坐标系（如 UTM）
- 与在线地图对接 → 3857 或运行时转换
- 中国境内高精度 → 考虑 GCJ-02 偏移问题（PostGIS 不内置，需自行纠偏）

三、空间索引

3.1 GiST 索引

空间查询没有空间索引时会全表扫描，GiST 是 PostGIS 的主力索引：

-- geometry 列建 GiST
CREATE INDEX idx_parcels_geom ON parcels USING gist (geom);

-- geography 列同样
CREATE INDEX idx_checkins_geog ON checkins USING gist (geog);

3.2 GiST 为什么能加速

GiST 把每个几何对象的外包矩形（bounding box）组织成树，查询时先比较包围盒：

查询点 P → 树节点包围盒 → 命中叶子 → 精确几何判断
        ↑ 快速排除绝大部分对象      ↑ 只对少量候选做精确计算

因此空间谓词必须写成能用索引的形式（&&、ST_DWithin、ST_Intersects 等），而 ST_Distance(a, b) < 100 这种写法不会用索引。

3.3 索引使用验证

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM parcels
WHERE ST_Contains(geom, ST_MakePoint(116.404, 39.915));

期望看到 Index Scan using idx_parcels_geom。如果出现 Seq Scan，说明谓词没走索引。

3.4 索引与查询的对齐

-- 走索引：ST_DWithin 是索引感知的
SELECT id FROM checkins
WHERE ST_DWithin(geog, ST_MakePoint(116.404, 39.915)::geography, 1000);

-- 不走索引：先算距离再过滤，无法利用包围盒
SELECT id FROM checkins
WHERE ST_Distance(geog, ST_MakePoint(116.404, 39.915)::geography) < 1000;

ST_DWithin 与 ST_Distance < x 语义相近，但前者能用索引，性能差距可达数百倍。

3.5 SP-GiST 与覆盖索引

-- 点数据可用 SP-GiST（空间分区树）
CREATE INDEX idx_points_spgist ON points USING spgist (geom);

-- PostgreSQL 12+ 支持 INCLUDE 覆盖索引，避免回表
CREATE INDEX idx_parcels_geom_inc ON parcels USING gist (geom) INCLUDE (name);

四、空间关系与查询

4.1 空间谓词一览

函数含义是否用索引
&&包围盒相交是
ST_Intersects几何相交是
ST_ContainsA 包含 B是
ST_WithinA 在 B 内是
ST_DWithin距离小于阈值是
ST_CoversA 覆盖 B（含边界）是
ST_Distance精确距离否

4.2 附近搜索与方圆查询

-- 找出 1 公里内的所有签到点
SELECT id, ST_Distance(geog, ST_MakePoint(116.404, 39.915)::geography) AS meters
FROM checkins
WHERE ST_DWithin(geog, ST_MakePoint(116.404, 39.915)::geography, 1000)
ORDER BY meters
LIMIT 20;

4.3 地理围栏判断

-- 判断某点落在哪个行政区（面）内
SELECT p.name AS district
FROM districts d
JOIN points p ON true
WHERE ST_Contains(d.geom, p.geom)
LIMIT 1;

-- 更好的写法：先在点表上过滤，再关联
SELECT p.id, d.name
FROM points p
LEFT JOIN LATERAL (
    SELECT name FROM districts d
    WHERE ST_Contains(d.geom, p.geom)
    LIMIT 1
) d ON true;

4.4 距离与面积计算

-- geography 直接得到米
SELECT ST_Distance(
    ST_MakePoint(116.404, 39.915)::geography,
    ST_MakePoint(121.473, 31.230)::geography
) AS meters;   -- 北京到上海约 1067000 米

-- 面积：geography 返回平方米
SELECT ST_Area(geog) AS sq_meters FROM regions;

-- geometry 面积取决于 SRID 单位
SELECT ST_Area(ST_Transform(geom, 3857)) AS approx_sq_meters FROM regions;

注意：在 3857（Web Mercator）上算面积会随纬度产生显著畸变，高精度面积应使用等面积投影（如 Albers）或 geography。

4.5 格式化输出

-- 转 GeoJSON 给前端地图
SELECT id, ST_AsGeoJSON(geom)::jsonb AS geojson FROM parcels LIMIT 5;

-- 转 WKT
SELECT ST_AsText(geom) FROM parcels LIMIT 1;

-- 转 KML / SVG（可视化）
SELECT ST_AsKML(geom) FROM parcels LIMIT 1;

五、地理处理与分析

5.1 缓冲区

-- 生成 500 米缓冲区（geography 单位为米）
SELECT ST_Buffer(geog, 500) FROM checkins WHERE id = 1;

-- geometry 缓冲需先投影到米制坐标系
SELECT ST_Buffer(ST_Transform(geom, 3857), 500) FROM parcels;

5.2 相交与合并

-- 两个面的交集
SELECT ST_Intersection(a.geom, b.geom) FROM regions a, regions b
WHERE a.id = 1 AND b.id = 2;

-- 合并相邻地块
SELECT ST_Union(geom) FROM parcels WHERE owner = 'acme';

-- 快速合并（只做包围盒，比 ST_Union 快很多）
SELECT ST_Collect(geom) FROM parcels WHERE owner = 'acme';

5.3 最近邻查询 KNN

-- 借助 <-> 距离操作符，GiST 索引支持 KNN，无需先算距离
SELECT id, name
FROM pois
ORDER BY geom <-> ST_MakePoint(116.404, 39.915)::geometry
LIMIT 10;

KNN 是「找最近 N 个」的最优写法，它让 GiST 索引按距离顺序返回结果，避免全表计算距离再排序。

5.4 抽稀与简化

-- 简化几何（Douglas-Peucker），用于前端渲染
SELECT ST_Simplify(geom, 0.001) FROM parcels;
SELECT ST_SimplifyPreserveTopology(geom, 0.001) FROM parcels;

5.5 坐标与索引的常见组合

-- 为「按距离排序 + 距离过滤」组合建立索引
CREATE INDEX idx_pois_geom ON pois USING gist (geom);

SELECT id, geom <-> ST_MakePoint(116.404, 39.915)::geometry AS dist
FROM pois
WHERE ST_DWithin(geom::geography, ST_MakePoint(116.404, 39.915)::geography, 5000)
ORDER BY dist
LIMIT 10;

六、性能调优与踩坑

6.1 用 EXPLAIN 确认索引生效

EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM parcels WHERE ST_Contains(geom, ST_MakePoint(1, 1));

-- 好：Index Scan，Rows Removed by Index Recheck 少
Index Scan using idx_parcels_geom on parcels
  Index Cond: (geom ~ st_makepoint(1, 1))

-- 坏：Seq Scan，全表逐行判断
Seq Scan on parcels
  Filter: st_contains(geom, st_makepoint(1, 1))

6.2 geography 的性能代价

geography 的距离运算涉及球面三角函数，比 geometry 慢数倍。混合策略是：存储用 geography，高频查询前先投影到 geometry。

-- 高频查询：预先把 geography 转成投影后的 geometry
ALTER TABLE checkins ADD COLUMN geom3857 geometry(Point, 3857);
UPDATE checkins SET geom3857 = ST_Transform(geog::geometry, 3857);
CREATE INDEX ON checkins USING gist (geom3857);

6.3 常见踩坑清单

1. 经纬度写反：ST_MakePoint(经度, 纬度)，不是 (纬度, 经度)
2. 混用 SRID：4326 与 3857 直接运算 → 报错或结果无意义
3. 用 ST_Distance < x 代替 ST_DWithin → 索引失效
4. 把「度」当「米」：4326 上 0.001 不是 1 米
5. 在 3857 上算面积：高纬度严重畸变
6. 忘记建 GiST 索引：全表扫描
7. 缓冲区半径单位错：geometry 的 ST_Buffer 用 SRID 单位

6.4 索引维护

-- GiST 索引随更新会膨胀，定期检查
SELECT indexrelname, pg_size_pretty(pg_relation_size(indexrelid)) AS size,
       idx_scan
FROM pg_stat_user_indexes
WHERE relname IN ('parcels', 'checkins');

-- 必要时重建
REINDEX INDEX CONCURRENTLY idx_parcels_geom;

常见问题（FAQ）

geometry 还是 geography 的选择

数据是经纬度、需要跨区域精确距离且量级不大，用 geography。数据已投影到米制平面、或追求极致性能、或数据集中在小范围，用 geometry。很多系统两者并存：geography 保真存储，geometry 供高频查询。

ST_DWithin 与 ST_Distance 的差异

语义上 ST_DWithin(a, b, d) 等价于 ST_Distance(a, b) <= d，但前者能被 GiST 索引加速（先做包围盒扩张），后者必须对每行算精确距离。所有「距离小于阈值」的查询都应改写成 ST_DWithin。

空间查询仍然全表扫描的原因

常见原因有三：谓词写成了 ST_Distance(...) < x 而非 ST_DWithin；几何列没有 GiST 索引；或者查询里对几何列做了函数包装（如 ST_Transform(geom, 3857) 后再比较），导致索引无法使用。

经纬度顺序容易搞错怎么办

记住 PostGIS 遵循 x, y 顺序，即 POINT(经度 纬度)。可以用 ST_X（经度）与 ST_Y（纬度）验证，也可以用 ST_MakePoint(longitude, latitude) 显式命名来避免混淆。

空间索引对 geography 是否有效

有效。geography 列同样支持 GiST 索引，且 ST_DWithin、ST_Intersects 等谓词能利用它。只是 geography 的精确计算更慢，索引能过滤掉大部分候选，剩下的精确判断仍比 geometry 慢。

相关阅读

延伸阅读

完整示例（一键复制）

-- ========== 1. 启用扩展 ==========
CREATE EXTENSION IF NOT EXISTS postgis;

-- ========== 2. 建表 ==========
CREATE TABLE pois (
    id   serial PRIMARY KEY,
    name text,
    geom geometry(Point, 4326),
    geog geography(Point, 4326)
);

CREATE TABLE districts (
    id   serial PRIMARY KEY,
    name text,
    geom geometry(Polygon, 4326)
);

-- ========== 3. 空间索引 ==========
CREATE INDEX idx_pois_geom    ON pois      USING gist (geom);
CREATE INDEX idx_pois_geog    ON pois      USING gist (geog);
CREATE INDEX idx_districts_geom ON districts USING gist (geom);

-- ========== 4. 插入数据（注意经度在前） ==========
INSERT INTO pois (name, geom, geog) VALUES
    ('天安门', ST_SetSRID(ST_MakePoint(116.397, 39.909), 4326),
              ST_SetSRID(ST_MakePoint(116.397, 39.909), 4326)::geography),
    ('王府井', ST_SetSRID(ST_MakePoint(116.410, 39.915), 4326),
              ST_SetSRID(ST_MakePoint(116.410, 39.915), 4326)::geography);

-- ========== 5. 附近搜索：1 公里内，按距离排序 ==========
SELECT id, name,
       ST_Distance(geog, ST_SetSRID(ST_MakePoint(116.404, 39.915), 4326)::geography) AS meters
FROM pois
WHERE ST_DWithin(geog, ST_SetSRID(ST_MakePoint(116.404, 39.915), 4326)::geography, 1000)
ORDER BY meters
LIMIT 20;

-- ========== 6. KNN 最近邻 ==========
SELECT id, name, geom <-> ST_SetSRID(ST_MakePoint(116.404, 39.915), 4326) AS dist
FROM pois
ORDER BY dist
LIMIT 10;

-- ========== 7. 围栏判断 ==========
SELECT p.name, d.name AS district
FROM pois p
LEFT JOIN LATERAL (
    SELECT name FROM districts d WHERE ST_Contains(d.geom, p.geom) LIMIT 1
) d ON true;

-- ========== 8. GeoJSON 输出 ==========
SELECT id, name, ST_AsGeoJSON(geom)::jsonb AS geojson FROM pois;

-- ========== 9. 距离与面积 ==========
SELECT ST_Distance(
    ST_SetSRID(ST_MakePoint(116.404, 39.915), 4326)::geography,
    ST_SetSRID(ST_MakePoint(121.473, 31.230), 4326)::geography
) AS beijing_to_shanghai_meters;

-- ========== 10. 计划验证 ==========
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM pois
WHERE ST_DWithin(geog, ST_SetSRID(ST_MakePoint(116.404, 39.915), 4326)::geography, 1000);

继续阅读

探索更多技术文章

浏览归档，发现更多关于系统设计、工具链和工程实践的内容。

全部文章 返回首页

「database」更多文章

  1. PostgreSQL 时序数据工作负载
  2. PostgreSQL 数据类型深入
  3. PostgreSQL 大版本升级