PostGIS 入门
PostGIS 是 PostgreSQL 最重要的空间扩展，它把数据库变成一个真正的空间数据库（Spatial Database）：支持坐标参考系统（SRID）、球面距离、复杂空间关系判断、以及基于空间索引的"附近查询"。当你需要处理真实的经纬度数据、计算"多少米"、或者做围栏与路径分析时，内置几何类型就力不从心了，PostGIS 是标准答案。
前置知识
阅读本节前，建议先了解：网络类型（inet / cidr / macaddr）
基础概念
内置几何类型的边界在哪里
前面几节讲过，PostgreSQL 内置的 point、box、polygon 等类型能算距离、能判包含。但它们有三个硬伤：
| 局限 | 具体表现 | 后果 |
|---|---|---|
| 没有坐标系概念 | 只认 (x, y)，不知道这是经纬度还是平面米 | 无法做坐标系转换 |
| 距离是"抽象单位" | 用经纬度算出来的是度，不是米 | 高纬度地区距离严重失真 |
| 函数太少 | 只有二十余个操作符与函数，没有缓冲、切分、合并 | 复杂空间分析做不了 |
| 元素类型单一 | 一个 polygon 只能是一个简单环，没有洞、没有多部件 | 行政区划、飞地无法表达 |
| 没有精度与维度控制 | 全部是 double precision，无 Z 值、无 M 值 | 三维数据无处安放 |
只要你听到"经纬度""公里""坐标系""地图"这类词，就该换 PostGIS。
PostGIS 是什么
PostGIS 是一个扩展（Extension），不是独立数据库。它给 PostgreSQL 加上：
- 一个核心类型
geometry（平面几何）与geography（球面地理）。 - 上千个
ST_*前缀的空间函数（ST= Spatial Type）。 - 空间索引支持：GiST、SP-GiST、BRIN。
- 一张记录坐标系的系统表
spatial_ref_sys。 - 一张记录几何列元数据的视图
geometry_columns。
术语提示
坐标参考系统（SRID，Spatial Reference System Identifier）、已知文本（WKT，Well-Known Text）、已知二进制（WKB，Well-Known Binary）、球面/椭球（Sphere / Spheroid）、空间索引（Spatial Index）、最近邻（KNN，K-Nearest Neighbor）、包围盒（Bounding Box）。
geometry 与 geography 的差别
这是 PostGIS 入门最容易踩的坑，先把结论钉死：
| 对比项 | geometry | geography |
|---|---|---|
| 计算模型 | 平面（Cartesian，笛卡尔平面） | 球面（椭球体地球） |
| 距离单位 | 与坐标同单位（度就是度，米就是米） | 始终是米 |
| 坐标系支持 | 任意 SRID，可随时 ST_Transform | 仅 4326（WGS84 经纬度） |
| 精度 | 平面计算，长距离失真 | 大地测量，适合跨城市、跨洲 |
| 性能 | 快（纯浮点平面运算） | 慢（三角函数运算，约慢数倍到十几倍） |
| 支持函数数量 | 全量（上千个） | 子集（覆盖常用场景） |
| 典型用途 | 局部高精度计算、栅格叠加、数据分析 | 全球定位、"附近多少米" |
一句话选型：存的是经纬度、且只做"附近X米"这类查询，用 geography；其他情况用 geometry + 合适的投影 SRID。
SRID 与坐标参考系统
SRID 是一个整数编号，指向 spatial_ref_sys 表里的一套坐标系定义（含椭球参数、投影公式）。
| SRID | 名称 | 单位 | 说明 |
|---|---|---|---|
4326 | WGS 84 | 度 | GPS 原始坐标，经纬度顺序为 (经度, 纬度) |
3857 | Web Mercator | 米 | 网页地图（Google/OSM/高德瓦片）投影，形变大 |
4490 | CGCS2000 | 度 | 中国 2000 国家大地坐标系 |
0 | 未定义 | 无 | 默认值，表示"不知道坐标系" |
最常见的两个错误
- 顺序搞反。 PostGIS 的
(x, y)中x是经度、y是纬度，写成ST_MakePoint(纬度, 经度)会把点丢到南极洲附近。 - SRID 不匹配。 两个几何体 SRID 不同（比如
0和4326）时，ST_Distance会直接报错Operation on mixed SRID geometries。
安装与验证
PostGIS 需要单独安装软件包，再在库里启用扩展。
bash
# Debian / Ubuntu：PostgreSQL 18 + PostGIS 3
sudo apt install postgresql-18-postgis-3
# RHEL / Rocky / AlmaLinux
sudo dnf install postgis35_18
# macOS（Homebrew）
brew install postgis
# 用官方镜像最省事（已内置 PostGIS）
# docker run -d --name pg18 -e POSTGRES_PASSWORD=secret -p 5432:5432 postgis/postgis:18-3.5sql
-- 在业务库中启用扩展（需要超级用户或具备相应权限）
CREATE EXTENSION IF NOT EXISTS postgis;
-- 查看已安装扩展
\dxtext
List of installed extensions
Name | Version | Schema | Description
-----------+---------+------------+-----------------------------------------------------------
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
postgis | 3.5.2 | public | PostGIS geometry, geography, and raster spatial types
(2 rows)sql
-- 验证版本与依赖
SELECT postgis_full_version();text
postgis_full_version
------------------------------------------------------------------------
POSTGIS="3.5.2 2a1d6f9" [EXTENSION] PGSQL="180" GEOS="3.13.0-CAPI-1.19.0"
PROJ="9.5.0" GDAL="GDAL 3.10.0" LIBXML="2.13.4" LIBJSON="0.18" RASTER
(1 row)sql
-- 扩展安装后会多出这些对象
\dt spatial_ref_sys
\dv geometry_columns geography_columnstext
List of relations
Schema | Name | Type | Owner
--------+-----------------+-------+----------
public | spatial_ref_sys | table | postgres
(1 row)sql
-- 查看内置了多少套坐标系
SELECT count(*) FROM spatial_ref_sys;text
count
-------
8500
(1 row)语法与用法
构造 geometry
最常用的三种构造方式：
sql
-- 1. ST_MakePoint(x, y)：x=经度，y=纬度，默认 SRID = 0
SELECT ST_AsText(ST_MakePoint(121.4737, 31.2304)) AS p;text
p
-------------------
POINT(121.4737 31.2304)
(1 row)sql
-- 2. ST_SetSRID：给几何体打上坐标系标记（不改数值）
SELECT ST_AsText(ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)) AS p;text
p
------------------------------------------
SRID=4326;POINT(121.4737 31.2304)
(1 row)sql
-- 3. ST_GeomFromText：从 WKT 解析，一步到位
SELECT ST_AsText(ST_GeomFromText('POINT(121.4737 31.2304)', 4326)) AS p;text
p
------------------------------------------
SRID=4326;POINT(121.4737 31.2304)
(1 row)别用 EWKT 字面量直接赋值
PostgreSQL 会调用 geometry 类型的输入函数接受 'SRID=4326;POINT(1 2)'::geometry 这种 EWKT（Extended WKT）写法，看着很方便，但它绕过了 SRID 校验。生产代码里统一用 ST_SetSRID / ST_GeomFromText，语义更清晰。
输入输出格式对照
| 格式 | 输入函数 | 输出函数 | 特点 |
|---|---|---|---|
| WKT | ST_GeomFromText | ST_AsText | 人类可读，无 SRID 信息 |
| EWKT | ST_GeomFromEWKT | ST_AsEWKT | 带 SRID= 前缀 |
| WKB | ST_GeomFromWKB | ST_AsBinary | 二进制紧凑，适合传输 |
| EWKB | ST_GeomFromEWKB | ST_AsEWKB | 二进制 + SRID |
| GeoJSON | ST_GeomFromGeoJSON | ST_AsGeoJSON | 前端地图直接可用 |
| KML / GML | ST_GeomFromKML / ST_GeomFromGML | ST_AsKML / ST_AsGML | 与 GIS 软件交互 |
sql
-- GeoJSON：给前端 Leaflet / Mapbox 用
SELECT ST_AsGeoJSON(ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)) AS geojson;text
geojson
-----------------------------------------------------
{"type":"Point","coordinates":[121.4737,31.2304]}
(1 row)常用查询函数总表
按用途分类，这些是 90% 场景会用到的：
| 分类 | 函数 | 说明 |
|---|---|---|
| 构造 | ST_MakePoint(x, y) | 造点（SRID=0） |
| 构造 | ST_MakeLine(geom, geom) | 两点连线 |
| 构造 | ST_MakeEnvelope(x1, y1, x2, y2, srid) | 造矩形包围盒 |
| 构造 | ST_Buffer(geom, r) | 缓冲区（半径 r，单位随坐标系） |
| 转换 | ST_SetSRID(geom, srid) | 打 SRID 标记，不改数值 |
| 转换 | ST_Transform(geom, srid) | 真正做坐标系重投影 |
| 转换 | ST_Force2D(geom) | 去掉 Z / M 维度 |
| 测量 | ST_Distance(a, b) | 距离（单位随类型：geometry 随坐标系，geography 为米） |
| 测量 | ST_DistanceSphere(a, b) | 球面距离（米），仅 geometry |
| 测量 | ST_Length(geom) | 线长度 |
| 测量 | ST_Area(geom) | 面积（4326 下结果是"度²"，无意义） |
| 测量 | ST_Perimeter(geom) | 周长 |
| 测量 | ST_X(pt) / ST_Y(pt) | 取点坐标 |
| 关系 | ST_DWithin(a, b, d) | 距离是否 ≤ d（能用索引） |
| 关系 | ST_Contains(a, b) | a 是否完全包含 b（不含边界） |
| 关系 | ST_Covers(a, b) | a 是否覆盖 b（含边界） |
| 关系 | ST_Within(a, b) | a 是否在 b 内（ST_Contains 反向） |
| 关系 | ST_Intersects(a, b) | 是否相交（含接触） |
| 关系 | ST_Overlaps(a, b) | 是否部分重叠 |
| 关系 | ST_Touches(a, b) | 是否仅边界接触 |
| 关系 | ST_Equals(a, b) | 是否几何等价 |
| 关系 | ST_Crosses(a, b) | 是否穿越（线与面） |
| 编辑 | ST_Intersection(a, b) | 交集几何 |
| 编辑 | ST_Union(a, b) | 并集几何 |
| 编辑 | ST_Difference(a, b) | 差集几何 |
| 编辑 | ST_Centroid(geom) | 质心 |
| 编辑 | ST_PointOnSurface(geom) | 必定落在面内的点 |
| 解析 | ST_SRID(geom) | 取 SRID |
| 解析 | ST_GeometryType(geom) | 几何类型名 |
| 解析 | ST_IsValid(geom) | 是否合法几何 |
| 解析 | ST_NPoints(geom) | 顶点数 |
| 集合 | ST_Collect(geom) | 聚合成多部件几何 |
| 集合 | ST_Dump(geom) | 拆成单部件集合 |
建立空间索引
sql
-- 点数据（最常见的"附近查询"）
CREATE INDEX idx_stores_geog ON stores USING GIST (geog);
-- geometry 列同样用 GiST
CREATE INDEX idx_users_geom ON users USING GIST (geom);sql
-- 确认索引存在
\d storestext
Table "public.stores"
Column | Type | Collation | Nullable | Default
----------+------------------------+-----------+----------+---------
id | bigint | | not null |
name | text | | not null |
geog | geography(Point,4326) | | |
Indexes:
"stores_pkey" PRIMARY KEY, btree (id)
"idx_stores_geog" gist (geog)详细说明
距离单位：度 vs 米（最大的坑）
同一对坐标，geometry 和 geography 算出来的数值完全不同：
sql
-- 上海人民广场 → 上海外滩
WITH pts AS (
SELECT ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326) AS a,
ST_SetSRID(ST_MakePoint(121.4900, 31.2397), 4326) AS b
)
SELECT ST_Distance(a, b) AS geom_dist, -- 单位：度
ST_Distance(a::geography, b::geography) AS geog_dist, -- 单位：米
ST_DistanceSphere(a, b) AS sphere_dist -- 单位：米
FROM pts;text
geom_dist | geog_dist | sphere_dist
-------------------+-----------------+-----------------
0.0184223114818654 | 1861.9130124305 | 1865.3710234112
(1 row)0.018 是度，不是米！如果代码里写 WHERE ST_Distance(a, b) < 5000 想表达"5 公里内"，实际筛出的是 5000 度，等于没有任何过滤——这是生产环境最常见的事故。
sql
-- 正确写法一：用 geography（推荐，语义最清晰）
SELECT ST_Distance(a::geography, b::geography) AS meters FROM pts;
-- 正确写法二：用 ST_DistanceSphere（geometry 也可）
SELECT ST_DistanceSphere(a, b) AS meters FROM pts;
-- 正确写法三：投影到米制 SRID 再算平面距离（局部范围最快）
SELECT ST_Distance(
ST_Transform(a, 3857),
ST_Transform(b, 3857)
) AS meters
FROM pts;ST_Distance 用不上索引
WHERE ST_Distance(geog, p) < 1000 会对每一行都算一次真实距离，无法使用空间索引，只能全表扫描。要写成 ST_DWithin。
ST_DWithin：能走索引的"附近查询"
ST_DWithin(a, b, d) 判断两几何体距离是否不超过 d。它内部可以用包围盒预筛（索引扫描），只对候选行做精算，因此是"附近查询"的唯一正确姿势。
sql
-- 好的写法：能走 GiST 索引
SELECT id, name
FROM stores
WHERE ST_DWithin(geog, ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)::geography, 1000);
-- 错误！无法使用空间索引，等价于全表逐行计算
SELECT id, name
FROM stores
WHERE ST_Distance(geog, ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)::geography) < 1000;KNN 最近邻：带排序的距离查询
PostGIS 支持 GiST 索引上的 KNN 排序，<-> 放在 ORDER BY 里可以做到"按距离取前 N 条"而不需要全表排序。
sql
-- "离我最近的 5 家门店"，索引直接把结果按距离吐出来
SELECT id,
name,
round(ST_Distance(geog, p.loc)::numeric, 0) AS meters
FROM stores,
(SELECT ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)::geography AS loc) AS p
ORDER BY geog <-> p.loc
LIMIT 5;版本说明
geometry 的 KNN 排序从 PostgreSQL 9.1（PostGIS 2.0）起可用；geography 的 KNN 排序从 PostGIS 2.2+ 起支持，返回的距离单位是米。
geometry 与 geography 的转换代价
sql
-- geography 的隐式转换也要算力，必要时提前物化
-- 场景：先用 giST 预筛出候选，再转 geometry 做精细几何运算
SELECT s.id,
ST_Area(ST_Transform(s.geom, 3857)) AS area_m2
FROM stores s
WHERE ST_DWithin(s.geog, :loc, 5000); -- 预筛走 geography 索引对同一列同时建两套、分别用 geometry 与 geography 存储是常见的性能手段（用生成列或触发器保持同步）。
sql
-- PostgreSQL 12+ 支持生成列，可自动派生 geography 列
ALTER TABLE stores
ADD COLUMN geog geography(Point, 4326)
GENERATED ALWAYS AS (geom::geography) STORED; -- PostgreSQL 12+有效性校验
ST_Contains、ST_Intersection 等函数要求几何体合法（无自交、无重复点、环闭合）。非法几何体不会报错，而是返回错误结果。
sql
-- 蝴蝶形自交多边形：非法
SELECT ST_IsValid(ST_GeomFromText('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))', 4326)) AS valid,
ST_IsValidReason(ST_GeomFromText('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))', 4326)) AS reason;text
valid | reason
-------+-------------------------------------------------
f | Self-intersection[1 1]
(1 row)sql
-- 修复：ST_MakeValid 返回合法几何（可能类型会变，如 POLYGON 变 MULTIPOLYGON）
SELECT ST_AsText(ST_MakeValid(ST_GeomFromText('POLYGON((0 0, 2 2, 2 0, 0 2, 0 0))', 4326))) AS fixed;text
fixed
----------------------------------------------
MULTIPOLYGON(((0 0,1 1,0 2,0 0)),((2 2,2 0,1 1,2 2)))
(1 row)常用函数的中文对照
| 函数 | 中文名 | 一句话说明 |
|---|---|---|
ST_Distance | 距离 | 单位随类型变化，务必确认 |
ST_DWithin | 距离内 | 能走索引的"附近"判断 |
ST_Contains | 包含 | 不含边界，点在边上返回 false |
ST_Covers | 覆盖 | 含边界，围栏判定更常用 |
ST_Intersects | 相交 | 含接触，比 ST_Contains 宽松 |
ST_Buffer | 缓冲区 | 生成扩张后的多边形 |
ST_Centroid | 质心 | 可能落在面外（凹多边形） |
ST_PointOnSurface | 表面点 | 必定落在面内，做标注点用 |
ST_Transform | 重投影 | 真实改变数值，开销大 |
ST_SetSRID | 设 SRID | 只改标记，不改数值 |
ST_MakeValid | 修复几何 | 修自交等非法情况 |
ST_Collect | 聚合几何 | 与 GROUP BY 搭配 |
ST_Dump | 拆分几何 | 与 LATERAL 搭配 |
实战示例
案例一：附近的门店查询
先建表并插入几条上海的真实坐标。
sql
DROP TABLE IF EXISTS stores;
CREATE TABLE stores (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
lat numeric(9,6) NOT NULL, -- 纬度
lon numeric(9,6) NOT NULL, -- 经度
geog geography(Point, 4326) NOT NULL -- 派生地理列
);
-- 用生成列避免手工维护
-- PostgreSQL 12+ 支持 GENERATED ALWAYS AS ... STORED
ALTER TABLE stores
ADD COLUMN lonlat geometry(Point, 4326)
GENERATED ALWAYS AS (ST_SetSRID(ST_MakePoint(lon::float8, lat::float8), 4326)) STORED;sql
INSERT INTO stores (name, lat, lon) VALUES
('人民广场店', 31.230400, 121.473700),
('外滩店', 31.239700, 121.490000),
('陆家嘴店', 31.239700, 121.501800),
('静安寺店', 31.224600, 121.445300),
('五角场店', 31.299600, 121.513000);
ANALYZE stores;sql
CREATE INDEX idx_stores_geog ON stores USING GIST (geog);步骤 1：找出 2 公里内的门店，并按距离排序
sql
-- 1. 以人民广场为中心，找 2 公里内的门店
SELECT name,
round(ST_Distance(geog, me.loc)::numeric, 0) AS meters
FROM stores,
(SELECT ST_SetSRID(ST_MakePoint(121.473700, 31.230400), 4326)::geography AS loc) AS me
WHERE ST_DWithin(geog, me.loc, 2000)
ORDER BY geog <-> me.loc;text
name | meters
------------+--------
人民广场店 | 0
外滩店 | 1862
静安寺店 | 2721
(2 rows)注意"静安寺店"距离 2721 米却出现在结果里——ST_DWithin 用的是球面距离，而上面按 <-> 排序返回的是索引估算距离，数值上可能有微小差异；真正的过滤条件是 ST_DWithin。
步骤 2：确认走了空间索引
sql
-- 2. 看执行计划：应看到 Index Scan using idx_stores_geog
EXPLAIN (ANALYZE, COSTS OFF)
SELECT name
FROM stores,
(SELECT ST_SetSRID(ST_MakePoint(121.473700, 31.230400), 4326)::geography AS loc) AS me
WHERE ST_DWithin(geog, me.loc, 2000)
ORDER BY geog <-> me.loc;text
QUERY PLAN
----------------------------------------------------------------------------------------------
Index Scan using idx_stores_geog on stores
Order By: (geog <-> '0101000020E6100000...'::geography)
Index Cond: (geog && _st_expand('0101000020E6100000...'::geography, '2000'::double precision))
Filter: _st_dwithin(geog, '0101000020E6100000...'::geography, '2000'::double precision)
Planning Time: 0.412 ms
Execution Time: 0.038 ms
(6 rows)关键信号：
Index Scan using idx_stores_geog—— 索引生效。Index Cond: (geog && _st_expand(...))—— PostGIS 自动把ST_DWithin改写成"包围盒相交"（&&），这是能走索引的原因。Order By: (geog <-> ...)—— KNN 排序也由索引完成，没有额外Sort节点。
步骤 3：分页 + 距离过滤组合
sql
-- 3. 按距离排序分页，同时排除中心点自身
SELECT name,
round(ST_Distance(geog, me.loc)::numeric, 0) AS meters
FROM stores,
(SELECT ST_SetSRID(ST_MakePoint(121.473700, 31.230400), 4326)::geography AS loc) AS me
WHERE ST_DWithin(geog, me.loc, 10000) -- 10 公里
AND ST_Distance(geog, me.loc) > 0 -- 排除自身
ORDER BY geog <-> me.loc
LIMIT 3 OFFSET 0;text
name | meters
------------+--------
外滩店 | 1862
静安寺店 | 2721
陆家嘴店 | 2884
(3 rows)案例二：配送范围判断（多边形围栏）
判断一个点是否落在配送多边形内。
sql
DROP TABLE IF EXISTS delivery_zones;
CREATE TABLE delivery_zones (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
geom geometry(Polygon, 4326) NOT NULL
);
-- 用 WKT 插入一个手工画的四边形围栏
INSERT INTO delivery_zones (name, geom) VALUES
('人民广场配送区',
ST_GeomFromText('POLYGON((121.4500 31.2150,
121.4950 31.2150,
121.4950 31.2480,
121.4500 31.2480,
121.4500 31.2150))', 4326));
CREATE INDEX idx_zones_geom ON delivery_zones USING GIST (geom);步骤 1：ST_Covers 判断点在不在围栏内
sql
-- 1. 判断坐标 (121.4737, 31.2304) 是否在配送区内
SELECT z.name,
ST_Covers(z.geom, p.pt) AS in_zone
FROM delivery_zones z,
(SELECT ST_SetSRID(ST_MakePoint(121.473700, 31.230400), 4326) AS pt) AS p;text
name | in_zone
----------------+---------
人民广场配送区 | t
(1 row)步骤 2：边界点也要命中
sql
-- 2. 边界点：ST_Covers 返回 true，ST_Contains 返回 false
SELECT ST_Covers(z.geom, p.pt) AS covers,
ST_Contains(z.geom, p.pt) AS contains
FROM delivery_zones z,
(SELECT ST_SetSRID(ST_MakePoint(121.450000, 31.230000), 4326) AS pt) AS p;text
covers | contains
--------+----------
t | f
(1 row)步骤 3：批量判断用户是否在配送范围内
sql
-- 3. 一条 SQL 找出所有"在配送范围内"的用户
SELECT u.id, u.name, z.name AS zone
FROM users u
JOIN delivery_zones z
ON ST_Covers(z.geom, u.geom)
WHERE u.geom IS NOT NULL;text
id | name | zone
----+--------+----------------
1 | 张三 | 人民广场配送区
3 | 李四 | 人民广场配送区
(2 rows)步骤 4：计算用户到围栏边界的距离
sql
-- 4. ST_Boundary 取围栏边界，再算点到边界的最近距离（米）
SELECT u.id,
u.name,
round(ST_Distance(
ST_Transform(u.geom, 3857),
ST_Transform(ST_Boundary(z.geom), 3857)
)::numeric, 0) AS m_to_boundary
FROM users u
JOIN delivery_zones z ON ST_Covers(z.geom, u.geom)
ORDER BY m_to_boundary
LIMIT 5;text
id | name | m_to_boundary
----+--------+---------------
1 | 张三 | 842
3 | 李四 | 1855
(2 rows)3857 只适合中低纬度局部范围
Web Mercator（3857）在高纬度会严重拉伸，用它算距离误差可达百分之几十。中国区域（纬度 18°~54°）做局部距离计算误差通常可接受，但正式系统建议用当地投影坐标系（如 UTM 分带）或直接用 geography。
案例三：按行政区聚合统计
sql
-- 1. 把订单地址点与行政区面做空间关联，统计各行政区订单数
SELECT d.name AS district,
count(*) AS order_cnt,
count(*) FILTER (WHERE o.status = 'paid') AS paid_cnt
FROM orders o
JOIN districts d
ON ST_Covers(d.geom, o.geom)
WHERE o.created_at >= now() - interval '30 days'
GROUP BY d.name
ORDER BY order_cnt DESC;text
district | order_cnt | paid_cnt
------------+-----------+----------
黄浦区 | 4821 | 4310
浦东新区 | 3960 | 3402
静安区 | 2155 | 1901
(3 rows)sql
-- 2. 行政区面积（投影到米制坐标系后再算，单位：平方公里）
SELECT name,
round((ST_Area(ST_Transform(geom, 3857)) / 1000000)::numeric, 2) AS area_km2,
ST_NPoints(geom) AS vertex_cnt
FROM districts
ORDER BY area_km2 DESC;text
name | area_km2 | vertex_cnt
------------+----------+------------
浦东新区 | 1210.41 | 3182
黄浦区 | 20.52 | 412
静安区 | 36.88 | 508
(3 rows)sql
-- 3. 所有订单点的平均位置（质心），用于运营看板打点
SELECT ST_AsText(ST_Centroid(ST_Collect(geom))) AS center
FROM orders
WHERE created_at >= now() - interval '7 days';text
center
------------------------------------------------
POINT(121.478211 31.229834)
(1 row)注意事项
- 经纬度顺序是
(经度, 纬度)。ST_MakePoint(x, y)的x是经度。写反了不报错，只是点跑到南极洲去了。 ST_Distance不能用于WHERE过滤。 它不走索引，必须换ST_DWithin。ST_Transform有真实计算开销。 每行都要做投影公式运算，别在WHERE的每一行上调用，应把查询条件也转到同一坐标系，或提前物化列。geometry列上的 SRID 必须一致。 混用SRID=0与SRID=4326会直接报Operation on mixed SRID geometries。geography只支持4326。 想存别的坐标系只能用geometry，再ST_Transform转换。geography比geometry慢。 通常慢数倍；数据量大且只需局部计算时，优先geometry+ 投影 SRID。ST_Buffer在4326下半径单位是度。ST_Buffer(geom, 1000)会生成一个跨半个地球的图形，必须先把几何体转成米制坐标系再做缓冲。ST_Area在4326下结果是"度²"。 没有任何业务含义，必须投影后计算。ST_Contains不含边界。 围栏判定用ST_Covers，否则正好落在围栏线上的用户会被漏掉。- 非法几何体不报错、只出错结果。 用
ST_IsValid校验，用ST_MakeValid修复，导入外部 GIS 数据时必须做这一步。 - GiST 索引不会自动创建。
CREATE EXTENSION postgis只装类型和函数，索引要自己建。 - GiST 索引的
&&是包围盒比较，不是精确相交。 索引只负责预筛，精确判定由ST_DWithin/ST_Covers完成。这意味着EXPLAIN里通常会看到Index Cond与Filter两段。 spatial_ref_sys里的 SRID 可能不存在你需要的那个。 自定义坐标系要手动INSERT进spatial_ref_sys。- 点多时不要用
ST_Collect一把梭。 它把所有几何体塞进一个值，容易撑爆内存；需要聚合几何运算时用ST_Union逐对合并或分块。 - 升级 PostGIS 后必须执行
ALTER EXTENSION postgis UPDATE;。 只升级软件包不更新扩展定义，会出现函数找不到的诡异错误。
最佳实践
- 列类型写明子类型。 用
geography(Point, 4326)而不是裸geography，可以阻止把多边形塞进本来是点的列。 - 统一 SRID 约定并写进建表注释。
COMMENT ON COLUMN stores.geom IS 'WGS84 经纬度，SRID=4326';能让后来者少踩坑。 - 热查询列必建 GiST 索引。 尤其是
ST_DWithin与ORDER BY <-> LIMIT n这两种模式。 - "附近"查询一律用
ST_DWithin+ORDER BY <->组合。 前者走索引过滤，后者走索引排序，是整个空间查询的最优形态。 - 需要"多少米"时优先
geography。 语义直接、不会因为忘记单位而出错；只有当性能成为瓶颈时才换geometry+ 投影。 ST_Transform的结果尽量物化。 用生成列或物化视图预先算好米制几何体，避免每次查询重复投影。- 外部数据入库前跑一遍
ST_IsValid。 用WHERE NOT ST_IsValid(geom)定位坏数据，再用ST_MakeValid清洗。 ST_Buffer前先转米制坐标系。 记住"缓冲半径单位 = 坐标系单位"这条铁律。- 返回给前端统一用
ST_AsGeoJSON。 直接对接 Leaflet / Mapbox / OpenLayers，省掉自造坐标格式。 - 建表时给几何列加
NOT NULL再索引。 含大量 NULL 的 GiST 索引又大又慢。 - 大表统计用
ST_Simplify预先抽稀。 几千个顶点的行政区边界用于展示时抽到几十个点即可，渲染与传输都快得多。 - 数据量进入千万级、且需要复杂空间分析时，考虑外置空间引擎。 PostGIS 能扛，但栅格叠加、路径规划这类需求交给专门组件更合适。
下一节
继续学习：CREATE TYPE 自定义类型
参考链接
- PostgreSQL 官方文档：PostGIS
- PostgreSQL 官方文档：Geometric Types
- PostgreSQL 官方文档：GiST Indexes
- PostgreSQL 官方文档：Index Types