PostGIS 地理扩展

PostGIS 是 PostgreSQL 生态里最有名的第三方扩展，没有之一。它把 PostgreSQL 变成一个完整的空间数据库：能存经纬度、能算两点距离、能判断点在不在多边形内、能用索引快速找出「附近的 N 个门店」、还能做栅格影像与拓扑分析。

对 PHP / 后端开发者的现实意义很直接：当产品出现「附近的人」「配送范围」「门店选址」「轨迹回放」「行政区划统计」这类需求时，你有两条路 —— 一是把坐标丢给 Redis 的 GEO 或 Elasticsearch 的 geo_point，二是留在 PostgreSQL 里让 PostGIS 处理。前者要维护第二套存储、要处理双写一致性；后者能用一条 SQL 完成过滤 + 排序 + 分页，并且享受事务与备份。绝大多数业务场景下，后者更划算。

本篇按「装得上 → 算得对 → 查得快」的顺序展开：先讲清 geometry 与 geography、SRID 这些概念，再落到构造、度量、关系判断、KNN 排序四类函数，最后用索引与实战案例把性能和用法串起来。

前置知识

本篇假定你已经掌握了 CREATE EXTENSION 扩展管理里的安装、schema 归属与权限模型。如果你还没读过，建议先看那一篇，因为 PostGIS 属于非 trusted 扩展，安装需要超级用户。

基础概念

PostGIS 是什么

PostGIS 是一个为 PostgreSQL 增加空间对象、空间函数与空间索引的扩展。它由 PostGIS 项目（隶属于 OSGeo 基金会）独立维护，版本节奏与 PostgreSQL 主版本解耦 —— 也就是说，PostgreSQL 18 上可以装 PostGIS 3.4、3.5 等不同版本，版本兼容性要单独规划。

安装后你会得到：

一批空间数据类型：geometry、geography、raster、box2d、box3d 等。
数百个空间函数：ST_Distance、ST_Contains、ST_Buffer、ST_Transform……
一批空间索引操作符类：让 GiST / SP-GiST / BRIN 索引认识几何对象。
一张 spatial_ref_sys 表，存放坐标参考系（SRID）的定义。

geometry 与 geography：两种世界观

这是 PostGIS 里最重要的一个选择，也是最容易混淆的一点。

维度	`geometry`	`geography`
坐标系	平面（笛卡尔），由 SRID 决定单位	球面（默认椭球），单位固定为米
SRID 支持	任意 SRID	必须是地理坐标（如 4326）
计算方式	平面几何运算，快	球面/椭球运算，慢一些
距离单位	与 SRID 一致（4326 时是「度」）	米
函数覆盖	最全	子集（覆盖常用函数）
适用场景	投影坐标系下的精确计算、拓扑分析	经纬度数据的距离/范围查询

一句话记忆：geometry 快而全但要你自己搞对单位，geography 慢一点但「米」就是米。

SRID 与坐标系

SRID（Spatial Reference System Identifier）是一个整数编号，指向一套坐标参考系定义。

4326：WGS84 经纬度，GPS 原始数据就是它。单位是度，不是米。
3857：Web Mercator，多数地图瓦片（Google Maps、高德、腾讯）的投影。单位名义上是米，但在高纬度会明显变形。
4490：CGCS2000 经纬度，国内测绘常与 4326 混用（数值几乎相同）。
32650 / 32649 等：UTM 分区投影，单位是真正的米，适合单城市级别的精确计算。

SRID 的关键约束：两个几何对象做二元运算时 SRID 必须相同，否则报错。这也是新手最常见的报错来源。

几何对象类型一览

PostGIS 支持 OGC 定义的几何类型（后缀 ZM 表示带 Z 或 M 坐标）：

类型	说明	常见用途
`POINT`	点	门店位置、车辆当前位置
`LINESTRING`	线串	道路、轨迹、配送路线
`POLYGON`	多边形	行政区、配送范围、电子围栏
`MULTIPOINT`	多点集合	一批 POI
`MULTILINESTRING`	多线集合	路网片段
`MULTIPOLYGON`	多多边形	由多个岛组成的行政区
`GEOMETRYCOLLECTION`	混合集合	任意组合

此外 geometry 本身是一个父类型，上面这些是它的子类型；列上可以声明 geometry(Point, 4326) 做类型与 SRID 双重约束。

PostGIS 的扩展家族

从 PostGIS 3.0 起，原本打包在一起的组件被拆成了独立扩展，按需安装：

扩展名	内容	是否需要单独装
`postgis`	核心：geometry、geography、空间函数与索引	必须
`postgis_raster`	栅格（Raster）支持	3.0 起需单独安装
`postgis_topology`	拓扑（Topology）支持	3.0 起需单独安装
`postgis_sfcgal`	SFCGAL 三维/高级几何后端	需底层库支持
`postgis_tiger_geocoder`	美国地址地理编码	视需求
`address_standardizer`	地址标准化	视需求

也就是说，如果你只做经纬度距离计算，只需要装 postgis 一个。

为什么空间索引是 GiST

普通 B-tree 索引解决的是「一维有序」问题：WHERE id = 5 能二分查找。但几何对象是二维的，「找出和这个矩形相交的所有多边形」无法用一维顺序表达。

空间索引的思路是外接矩形（bounding box，简称 bbox）：给每个几何对象算一个最小的轴对齐矩形，然后用树结构组织这些矩形，快速排除掉根本不重叠的对象。PostGIS 主要提供三种：

GiST：通用、成熟，绝大多数场景的首选。
SP-GiST：PostGIS 2.5 起支持，特定点云场景更好。
BRIN：对按空间顺序物理写入的海量数据（如按区域导入的轨迹表）能以极小体积获得不错的剪枝效果。

关键认知：空间索引只加速「粗筛」（基于 bbox 的候选集），精确判断仍要逐行换算。所以函数能不能自动利用索引，取决于它内部有没有先做 bbox 比较。

语法与用法

安装与验证

sql

-- 需要超级用户；PostGIS 不是 trusted 扩展
CREATE EXTENSION postgis;

-- 推荐：装到独立 schema，避免污染 public
CREATE SCHEMA IF NOT EXISTS extensions;
CREATE EXTENSION postgis SCHEMA extensions;

验证安装结果：

sql

SELECT PostGIS_Version();
SELECT PostGIS_Full_Version();

结果示意（text）：

text

              postgis_version
--------------------------------------------
 3.5 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

查看已装的 PostGIS 家族扩展：

sql

SELECT extname, extversion, extnamespace::regnamespace AS schema
FROM pg_extension
WHERE extname LIKE 'postgis%'
   OR extname IN ('address_standardizer')
ORDER BY extname;

结果示意（text）：

text

    extname     | extversion |  schema
----------------+------------+-----------
 postgis        | 3.5.0      | extensions
 postgis_raster | 3.5.0      | extensions

构造几何对象

四种最常见的构造方式：

sql

-- 1. 从 WKT（Well-Known Text）文本构造
SELECT ST_AsText(ST_GeomFromText('POINT(116.397 39.908)', 4326));

-- 2. 直接拼 lon/lat（先建点，再补 SRID）—— 最快、最常用
SELECT ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326);

-- 3. EWKT 写法，SRID 内嵌在文本里
SELECT ST_GeomFromEWKT('SRID=4326;POINT(116.397 39.908)');

-- 4. 从 GeoJSON 构造（对接前端很方便）
SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[116.397,39.908]}');

结果示意（text）：

text

                  st_astext
----------------------------------------------
 POINT(116.397 39.908)

注意经纬度顺序：WKT 是 POINT(x y)，对应地理坐标就是 POINT(经度纬度)，即 lon 在前、lat 在后。这与很多人直觉相反，也和 GeoJSON 的 [lon, lat] 一致。

度量函数：距离、长度、面积

sql

-- geometry 上：单位由 SRID 决定。4326 时返回「度」
SELECT ST_Distance(
  ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326),
  ST_SetSRID(ST_MakePoint(116.407, 39.918), 4326)
);

-- geography 上：返回米
SELECT ST_Distance(
  ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)::geography,
  ST_SetSRID(ST_MakePoint(116.407, 39.918), 4326)::geography
);

-- 球面近似距离（把 geometry 当 4326 处理，返回米）
SELECT ST_DistanceSphere(
  ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326),
  ST_SetSRID(ST_MakePoint(116.407, 39.918), 4326)
);

-- 长度与面积：geometry 单位随 SRID，geography 一律米 / 平方米
SELECT ST_Length(geom::geography) AS len_m,
       ST_Area(geom::geography)   AS area_m2
FROM road_segments;

结果示意（text）：

text

   st_distance
------------------
 0.0141421356237
(1 row)

   st_distance
------------------
 1402.3158...

空间关系判断

布尔函数用于过滤，注意它们是精确计算：

sql

-- 相交 / 包含 / 覆盖 / 接触
SELECT ST_Intersects(a.geom, b.geom) AS is_intersects,
       ST_Contains(a.geom, b.geom)   AS a_contains_b,
       ST_Within(b.geom, a.geom)     AS b_within_a,
       ST_Touches(a.geom, b.geom)    AS is_touches
FROM districts a, shops b
WHERE a.name = '朝阳区' AND b.name = '某门店';

-- 范围查询：推荐用 ST_DWithin，而不是 ST_Distance(...) <= x
SELECT count(*) FROM shops
WHERE ST_DWithin(geom::geography, ST_MakePoint(116.397, 39.908)::geography, 1000);

ST_DWithin 与 ST_Distance(...) <= x 语义几乎等价，但性能天差地别，原因见「详细说明」。

KNN：按距离排序取最近 N 个

sql

-- <-> 是「距离」运算符，配合 ORDER BY + LIMIT 才能走索引
SELECT id, name,
       ST_Distance(geom::geography,
                   ST_MakePoint(116.397, 39.908)::geography) AS meters
FROM shops
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)
LIMIT 10;

<-> 在 geometry 上返回二维距离（单位随 SRID），在 geography 上返回米级距离。它本身不改变结果集，只影响排序，所以必须写在 ORDER BY 里。

建表与建索引

sql

CREATE TABLE shops (
    id        bigserial PRIMARY KEY,
    name      text NOT NULL,
    city      text NOT NULL,
    geom      geometry(Point, 4326) NOT NULL
);

-- GiST 空间索引：空间查询的默认选择
CREATE INDEX idx_shops_geom ON shops USING gist (geom);

-- 观察是否命中索引
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM shops
WHERE ST_DWithin(geom::geography, ST_MakePoint(116.397, 39.908)::geography, 1000);

结果示意（text）：

text

 Index Scan using idx_shops_geom on shops  (cost=0.28..8.30 rows=1 width=8)
   Index Cond: (geom && _st_expand('0101...'::geography, '1000'::double precision)::geometry)

看到 Index Cond 里出现 _st_expand(...) 或 &&，就说明索引用上了。

详细说明

SRID 必须一致：最常见的报错

二元空间函数要求两个参数 SRID 相同：

sql

-- 错误！两个 SRID 不同
SELECT ST_Distance(
  ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326),
  ST_SetSRID(ST_MakePoint(500000, 4400000), 3857)
);
-- ERROR:  Operation on mixed SRID geometries

正确做法是先统一。要么把数据都存成同一种 SRID（推荐：统一 4326），要么显式转换：

sql

-- geometry 与 geography 的转换不改变 SRID
SELECT ST_SRID(a.geom::geography), ST_SRID(b.geom);

在建表时就用 geometry(Point, 4326) 声明 SRID，能让插入时自动做 SRID 检查（初始 SRID 为 0 的几何在写入时会被赋予列 SRID），比事后排查便宜得多。

ST_SetSRID 与 ST_Transform 的区别

这两个函数名字像，作用完全不同：

sql

-- ST_SetSRID：只改「标签」，不动坐标数值
-- 把一个本来就是 4326 的点，贴上 4326 标签
SELECT ST_AsText(ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326));

-- ST_Transform：真正做坐标换算，数值会变
SELECT ST_AsText(ST_Transform(
  ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326), 3857
));

结果示意（text）：

text

-- ST_SetSRID 结果
POINT(116.397 39.908)

-- ST_Transform 结果（数值已被投影换算成米）
POINT(12958470.42 4858185.97)

记忆法：ST_SetSRID = 贴标签（数据错了会埋雷），ST_Transform = 真换算（需要 PROJ 库支持）。

常见的「埋雷」写法是把 GPS 坐标 ST_SetSRID(..., 3857) —— 数据其实是经纬度，标签却是墨卡托，后续所有距离计算全错。

ST_DWithin 为什么快，ST_Distance 为什么慢

这是 PostGIS 性能问题的核心。

官方文档对 ST_DWithin 有一句关键说明（原文）：

This function call includes a bounding box comparison that makes use of any indexes that are available on the geometries.

翻译过来：ST_DWithin 内部先做边界框（bbox）比较，把几何对象的外接矩形与「目标点扩张 1000 米后的矩形」比较，凡是 bbox 不相交的直接排除，因此可以用上 GiST 索引。

而 ST_Distance(a, b) <= 1000 是逐行精确计算：每行都要求出真实距离再比大小，索引无从下手（除非表达式索引且距离函数不可索引，一般不行）。所以：

sql

-- 不推荐：全表扫描 + 逐行精确距离
SELECT id FROM shops
WHERE ST_Distance(geom::geography, ST_MakePoint(116.397, 39.908)::geography) <= 1000;

-- 推荐：bbox 粗筛走索引，甚至不需要精筛
SELECT id FROM shops
WHERE ST_DWithin(geom::geography, ST_MakePoint(116.397, 39.908)::geography, 1000);

注意 ST_DWithin 的结果是精确的（不是近似），bbox 只是它内部用于剪枝的手段，不会产生误判。

粗筛 + 精排：官方推荐的两段式

ST_DWithin 解决了「半径过滤」，但如果你想「按距离排序取最近的 N 个」，ST_DWithin 给的顺序没有意义。这时用 KNN：

sql

-- 索引给出近似的 bbox 距离排序，SELECT 列里的 ST_Distance 给出真实距离
SELECT id, name,
       ST_Distance(geom::geography, p.center::geography) AS meters
FROM shops, (SELECT ST_MakePoint(116.397, 39.908)::geography AS center) p
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)
LIMIT 10;

<-> 在 GiST 索引里可以用 bbox 距离做 KNN 排序，取到最近的 10 条候补；SELECT 里再用 ST_Distance 算出精确米数展示给用户。代价是前 10 条的排序可能与精确距离略有出入（bbox 距离 ≠ 真实距离），业务上通常可接受；要严格精确可以取 50 条再在外层排序。

索引技巧：ST_Expand 预扩张

官方文档在 ST_DWithin 页给出了一个很有用的技巧：如果你经常对同一个中心点做固定半径的查询，可以直接对「扩张后的几何」建索引。

sql

-- 场景：常年查询「距离配送站 5 公里内的订单」
CREATE TABLE delivery_stations (
    id    serial PRIMARY KEY,
    name  text,
    geom  geometry(Point, 4326)
);

ALTER TABLE delivery_stations
ADD COLUMN service_area geometry(Polygon, 4326);

UPDATE delivery_stations
SET service_area = ST_Buffer(geom::geography, 5000)::geometry;

CREATE INDEX idx_station_area ON delivery_stations USING gist (service_area);

这样 ST_Intersects(order.geom, station.service_area) 可以走纯 bbox + 精确判断，且省掉了每次查询都要构造缓冲区（ST_Buffer 是相对昂贵的操作）。

注意

ST_Buffer 对 geography 返回的是米为单位的近似多边形；半径很大时（如几十公里以上）多边形顶点数会暴涨，需要配合 ST_Simplify 或分段处理。以项目文档为准。

geography 的选择与代价

什么时候必须用 geography？

数据是原生 GPS 经纬度（SRID 4326），且不想做投影。
需要「米」为单位的距离、缓冲、范围查询。
覆盖范围很大（跨省、跨国），用投影会有明显变形。

代价是：

函数覆盖不如 geometry 全（如部分栅格、拓扑函数不适用）。
计算慢（球面/椭球三角运算 vs 平面运算）。
默认使用椭球（spheroid）计算，可通过 use_spheroid = false 切换到球面近似换取速度：

sql

-- 默认椭球（更准，稍慢）
SELECT ST_DWithin(a::geography, b::geography, 1000);

-- 显式用球面近似（更快，千米级误差通常可忽略）
SELECT ST_DWithin(a::geography, b::geography, 1000, false);

投影坐标系下做精确计算

单城市范围内的精确距离（如配送计费），投影到 UTM 是更稳的做法：

sql

-- 北京大致落在 UTM 50N（EPSG:32650）
ALTER TABLE shops
ADD COLUMN geom_utm geometry(Point, 32650);

UPDATE shops
SET geom_utm = ST_Transform(geom, 32650);

CREATE INDEX idx_shops_utm ON shops USING gist (geom_utm);

-- 现在 ST_Distance 直接返回米，且是平面计算，快且准
SELECT id, round(ST_Distance(
  geom_utm,
  ST_Transform(ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326), 32650)
)::numeric, 1) AS meters
FROM shops
ORDER BY geom_utm <-> ST_Transform(ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326), 32650)
LIMIT 10;

代价是多存一列、写入要算一次 ST_Transform，换来的是查询期零换算。

GeoJSON：与前后端交换数据

sql

-- 输出 GeoJSON（maxdecimaldigits 默认 9，建议降到 6 减小体积）
SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', jsonb_agg(ST_AsGeoJSON(t.*, 'geom', 6)::jsonb)
) AS geojson
FROM (
  SELECT id, name, geom FROM shops WHERE city = '北京' LIMIT 100
) t;

sql

-- 从 GeoJSON 写入
INSERT INTO districts (name, geom)
VALUES (
  '示例区',
  ST_SetSRID(
    ST_GeomFromGeoJSON('{"type":"Polygon","coordinates":[[[116.3,39.9],[116.4,39.9],[116.4,40.0],[116.3,40.0],[116.3,39.9]]]}'),
    4326
  )
);

注意 ST_GeomFromGeoJSON 不会自动设置 SRID，需要外层 ST_SetSRID 补上。另外多边形环必须闭合（首尾坐标相同）且遵循右手法则，否则可能报错或产生无效几何。

几何有效性：ST_IsValid 与 ST_MakeValid

从 GIS 系统导入的数据经常是「无效几何」（自相交的多边形、重复点等）：

sql

-- 找出无效几何
SELECT id, ST_IsValidReason(geom)
FROM districts
WHERE NOT ST_IsValid(geom);

-- 修复
UPDATE districts
SET geom = ST_MakeValid(geom)
WHERE NOT ST_IsValid(geom);

结果示意（text）：

text

 id |        st_isvalidreason
----+---------------------------------
  7 | Self-intersection[116.4 39.95]

无效几何会让 ST_Contains、ST_Area、甚至索引构建出现不可预期结果，入库前校验是必须的一步。

性能与体积：ST_Subdivide 与简化

一个省界多边形可能有几十万个顶点，巨大的 bbox 会让空间索引失去剪枝能力（「一坨覆盖整张表」）。两个对策：

sql

-- 1. ST_Subdivide：把大多边形切成小块，每块顶点数受限
SELECT ST_Subdivide(geom, 256) AS piece
FROM provinces
WHERE name = '某省';

-- 2. ST_Simplify：按容差抽稀顶点（去细节保形状）
SELECT ST_Simplify(geom, 0.001) FROM provinces WHERE name = '某省';

ST_Simplify 可能产生无效几何；需要保拓扑时用 ST_SimplifyPreserveTopology。可视化用途（前端地图展示）可以大胆简化，做空间判断的原始列则不要简化。

空间聚合函数

除了逐行函数，PostGIS 也提供聚合：

sql

-- 统计每个城市门店的外接矩形与凸包
SELECT city,
       count(*) AS n,
       ST_Extent(geom)         AS bbox,        -- box2d 类型，覆盖所有点
       ST_AsText(ST_ConvexHull(ST_Collect(geom))) AS hull
FROM shops
GROUP BY city;

-- 把轨迹点按时间串成一条线
SELECT device_id,
       ST_MakeLine(geom ORDER BY ts) AS track
FROM gps_points
WHERE ts >= now() - interval '1 day'
GROUP BY device_id;

几个常用聚合：

聚合	作用
`ST_Collect`	把几何收集成 MULTI* 或 GEOMETRYCOLLECTION，不合并
`ST_Union`	做真正的几何合并（会消耗较多 CPU）
`ST_Extent`	返回所有几何的 bbox（`box2d`）
`ST_3DExtent`	三维 bbox
`ST_MakeLine`	按顺序串成线，支持 `ORDER BY`

postgis_raster 与 postgis_topology 简介

Raster 用于栅格数据（卫星影像、DEM 高程、气象格网）。它把影像切成瓦片存进表，并支持按经纬度反查像元值：

sql

CREATE EXTENSION postgis_raster;

-- 按经纬度取值（ST_Value 需要配合 ST_WorldToRasterCoord 等）
SELECT rid, ST_Value(rast, 1,
         ST_Transform(ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326), ST_SRID(rast))
       ) AS elevation
FROM dem_tiles
WHERE ST_Intersects(rast, ST_Transform(ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326), ST_SRID(rast)));

Topology 用于拓扑数据模型（共享边界的行政区块、路网），能让相邻多边形的公共边只存一份，修改时自动传播：

sql

CREATE EXTENSION postgis_topology;

-- 创建拓扑并添加面
SELECT CreateTopology('city_topo', 4326, 0.00001);
SELECT TopoGeo_AddPolygon('city_topo', geom) FROM districts_raw;

这两个扩展都属于「上了一定规模才会用到」的类型，且都要求核心 postgis 已安装。

实战示例

场景一：附近门店（距离过滤 + 排序 + 分页）

最常见的一个需求，正确写法是「ST_DWithin 过滤 + <-> 排序」：

sql

SELECT s.id, s.name, s.city,
       round(ST_Distance(s.geom::geography, p.pt::geography)::numeric, 0) AS meters
FROM shops s
CROSS JOIN (
    SELECT ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326) AS pt
) p
WHERE ST_DWithin(s.geom::geography, p.pt::geography, 3000)
ORDER BY s.geom <-> p.pt
LIMIT 20;

关键点：ST_DWithin 先走索引把候选集缩小到 3 公里内，<-> 再排序取 20 条，ST_Distance 只在这 20 行上算精确米数（只用于展示）。

场景二：电子围栏（点是否在多边形内）

sql

SELECT o.id AS order_id, d.name AS district
FROM orders o
JOIN districts d
  ON ST_Contains(d.geom, o.geom)
WHERE o.id = 12345;

若 districts 表很大，把条件写进 WHERE 让 GiST 先按 bbox 剪枝：

sql

SELECT o.id, d.name
FROM orders o, districts d
WHERE o.id = 12345
  AND d.geom && o.geom          -- bbox 快速排除（走索引）
  AND ST_Contains(d.geom, o.geom);  -- 精确判断

&& 是 bbox 相交运算符，可被 GiST 索引直接使用，显式写出能让计划更稳定。

场景三：多级围栏优先级（由小到大判定）

业务上常有「先判门店专属围栏，再判城市围栏」的层级需求，可以用窗口函数一次判定：

sql

SELECT f.fence_id, f.level, f.name
FROM fences f
WHERE ST_Contains(f.geom, ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326))
ORDER BY f.level DESC          -- 越小越精细，优先取
LIMIT 1;

或者用 DISTINCT ON 取每个层级里唯一命中的那条：

sql

SELECT DISTINCT ON (f.level) f.level, f.fence_id
FROM fences f
WHERE ST_Contains(f.geom, ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326))
ORDER BY f.level, f.priority DESC;

场景四：地理编码到行政区（按 polygon 归属统计）

sql

-- 每个行政区内的门店数与营收
SELECT d.name AS district,
       count(s.id) AS shop_cnt,
       coalesce(sum(s.revenue), 0) AS revenue
FROM districts d
LEFT JOIN shops s
  ON s.geom && d.geom
 AND ST_Within(s.geom, d.geom)
WHERE d.city = '北京'
GROUP BY d.name
ORDER BY shop_cnt DESC;

注意 LEFT JOIN + s.geom && d.geom 的组合：&& 让索引生效，ST_Within 做精确归属。

场景五：轨迹去重与抽稀

sql

-- 同一设备 10 秒内的重复上报点，只保留每个时间桶的第一个
WITH bucketed AS (
    SELECT device_id, ts, geom,
           date_trunc('second', ts) -
           (extract(second FROM ts)::int % 10) * interval '1 second' AS bucket
    FROM gps_points
    WHERE device_id = 42
      AND ts >= now() - interval '1 hour'
)
SELECT DISTINCT ON (bucket) device_id, bucket, ts, ST_AsText(geom) AS pt
FROM bucketed
ORDER BY bucket, ts;

进一步抽稀（去掉与其他点过近的点），可以用窗口函数配合 ST_DWithin 的近似替代：

sql

-- 按距离抽稀：只保留与上一个保留点距离 > 50 米的点
SELECT device_id, ts, geom
FROM (
    SELECT device_id, ts, geom,
           lag(geom) OVER w AS prev_geom
    FROM gps_points
    WHERE device_id = 42
    WINDOW w AS (ORDER BY ts)
) t
WHERE prev_geom IS NULL
   OR ST_Distance(geom::geography, prev_geom::geography) > 50;

场景六：索引优化前后对比

先看没有索引的情况：

sql

DROP INDEX IF EXISTS idx_shops_geom;
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM shops
WHERE ST_DWithin(geom::geography, ST_MakePoint(116.397, 39.908)::geography, 1000);

结果示意（text）：

text

 Seq Scan on shops  (cost=0.00..1943.00 rows=5 width=8) (actual time=0.021..31.204 rows=12 loops=1)
   Filter: st_dwithin((geom)::geography, '...'::geography, '1000'::double precision)
   Rows Removed by Filter: 99988
 Planning Time: 0.152 ms
 Execution Time: 31.402 ms

建索引后：

sql

CREATE INDEX idx_shops_geom ON shops USING gist (geom);
ANALYZE shops;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM shops
WHERE ST_DWithin(geom::geography, ST_MakePoint(116.397, 39.908)::geography, 1000);

结果示意（text）：

text

 Index Scan using idx_shops_geom on shops  (cost=0.28..8.30 rows=1 width=8) (actual time=0.048..0.213 rows=12 loops=1)
   Index Cond: (geom && _st_expand('...'::geography, '1000'::double precision)::geometry)
   Filter: st_dwithin((geom)::geography, '...'::geography, '1000'::double precision)
 Planning Time: 0.298 ms
 Execution Time: 0.284 ms

从 31ms 降到 0.28ms，且随表增大差距会进一步拉开 —— 索引把复杂度从 O(n) 降到了 O(log n + 命中数)。

注意事项

注意一：4326 的 ST_Distance 返回「度」，不是米。

sql

-- 错误理解：以为结果是米
SELECT ST_Distance(
  ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326),
  ST_SetSRID(ST_MakePoint(116.407, 39.918), 4326)
);  -- 返回 0.0141，单位是「度」

注意二：两个几何 SRID 不同会直接报错。

sql

-- 错误！
SELECT ST_Distance(
  ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326),
  ST_SetSRID(ST_MakePoint(116.397, 39.908), 3857)
);
-- ERROR:  Operation on mixed SRID geometries

注意三：ST_Distance(...) <= x 用不上索引。

sql

-- 不推荐：全表扫描
SELECT id FROM shops
WHERE ST_Distance(geom::geography, ST_MakePoint(116.397, 39.908)::geography) <= 1000;

改用 ST_DWithin。

注意四：ST_SetSRID 不换算坐标。

sql

-- 错误！把经纬度贴上 3857 标签，后续所有距离全错
SELECT ST_SetSRID(ST_MakePoint(116.397, 39.908), 3857);

注意五：WKT 是「经度在前」。

sql

-- 错误！把 lat/lon 写反，点跑到南极去了
SELECT ST_GeomFromText('POINT(39.908 116.397)', 4326);

注意六：GIN 索引不支持空间 KNN。

sql

-- 错误！geometry 操作符类只有 GiST / SP-GiST
CREATE INDEX idx_bad ON shops USING gin (geom);

注意七：<-> 必须写在 ORDER BY 里才有效。

sql

-- 错误：把 <-> 当布尔条件用
SELECT id FROM shops WHERE geom <-> ST_MakePoint(116.397, 39.908) < 1000;

注意八：无效几何会让结果不可预期。

sql

-- 不推荐：不校验直接入库
INSERT INTO districts (geom) VALUES (ST_GeomFromText('POLYGON((0 0,1 1,1 0,0 1,0 0))'));
-- 自相交多边形，ST_IsValid 返回 false

入库前用 ST_IsValid 校验、ST_MakeValid 修复。

注意九：多边形的环必须闭合且方向正确。

sql

-- 错误！首尾坐标不同，环未闭合
SELECT ST_GeomFromText('POLYGON((0 0,1 0,1 1,0 1))');  -- 某些路径下报错或产生无效几何

-- 正确：首尾一致
SELECT ST_GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))');

注意十：PostGIS 不是 trusted 扩展，普通用户装不了。

sql

-- 以普通用户身份
CREATE EXTENSION postgis;
-- ERROR:  permission denied to create extension "postgis"
-- HINT:  Must have superuser privilege to create this extension.

由 DBA 预装，业务账号只授 USAGE 与 EXECUTE。

最佳实践

存储统一用 geometry(..., 4326)，需要米制精度时另存一列投影坐标（UTM / 地方坐标系），不要混用多种 SRID。
建列时声明子类型与 SRID：geometry(Point, 4326) 比裸 geometry 多一层写入校验，且让迁移脚本自文档化。
把 PostGIS 装进独立 schema（如 extensions），并把它加入 search_path，避免污染 public 与 pg_dump --schema=public。
入库前校验几何有效性，用 CHECK (ST_IsValid(geom)) 或写入前显式调用 ST_IsValid，把坏数据拦在门外。
半径过滤一律用 ST_DWithin，永远不要用 ST_Distance(...) <= x，这是空间查询性能的第一原则。
「最近 N 个」用 <-> + ORDER BY + LIMIT，让 GiST 做 KNN 排序；展示用的精确距离放在 SELECT 里单独算。
显式写出 && 让索引剪枝更稳定，尤其是在 JOIN 条件里：ON a.geom && b.geom AND ST_Contains(...)。
空间索引用 GiST 起步，只有在明确的点数据或物理有序数据场景才考虑 SP-GiST / BRIN，并且一定要用 EXPLAIN (ANALYZE, BUFFERS) 验证。
大数据量的多边形做 ST_Subdivide，避免单个几何的 bbox 覆盖全表导致索引失效；展示用途的几何做 ST_Simplify，判定用途的原始列不要简化。
GeoJSON 输出时把 maxdecimaldigits 调到 6 左右，默认 9 位在小数点后浪费大量带宽。
一次性大更新后记得 ANALYZE，PostGIS 会维护 geometry_columns 统计与 ST_EstimatedExtent 所需的信息，统计过期会让计划变差。
跨大版本升级 PostGIS 前先在副本上演练，尤其是 postgis_raster / postgis_topology 从捆绑拆分的 3.0 边界，以及底层 GEOS / PROJ 版本变化带来的行为差异。

下一篇 TimescaleDB 时序扩展会把视角从「空间」转向「时间」：hypertable 如何自动分区、time_bucket() 如何做降采样、连续聚合与压缩策略如何让海量时序数据既有写入吞吐又有查询速度。

PostGIS 地理扩展 ​

基础概念 ​

PostGIS 是什么 ​

geometry 与 geography：两种世界观 ​

SRID 与坐标系 ​

几何对象类型一览 ​

PostGIS 的扩展家族 ​

为什么空间索引是 GiST ​

语法与用法 ​

安装与验证 ​

构造几何对象 ​

度量函数：距离、长度、面积 ​

空间关系判断 ​

KNN：按距离排序取最近 N 个 ​

建表与建索引 ​

详细说明 ​

SRID 必须一致：最常见的报错 ​

ST_SetSRID 与 ST_Transform 的区别 ​

ST_DWithin 为什么快，ST_Distance 为什么慢 ​

粗筛 + 精排：官方推荐的两段式 ​

索引技巧：ST_Expand 预扩张 ​

geography 的选择与代价 ​

投影坐标系下做精确计算 ​

PostGIS 地理扩展

基础概念

PostGIS 是什么

geometry 与 geography：两种世界观

SRID 与坐标系

几何对象类型一览

PostGIS 的扩展家族

为什么空间索引是 GiST

语法与用法

安装与验证

构造几何对象

度量函数：距离、长度、面积

空间关系判断

KNN：按距离排序取最近 N 个

建表与建索引

详细说明

SRID 必须一致：最常见的报错

ST_SetSRID 与 ST_Transform 的区别

ST_DWithin 为什么快，ST_Distance 为什么慢

粗筛 + 精排：官方推荐的两段式

索引技巧：ST_Expand 预扩张

geography 的选择与代价

投影坐标系下做精确计算