pgsql-15 PostGIS地理数据

2025-09-25 约 2426 字预计阅读 5 分钟

1. 概述

PostGIS 是 PostgreSQL 的空间数据库扩展，是业界事实标准（OGC 规范实现最完整），被高德、Uber、滴滴等大量 LBS（基于位置的服务）系统采用。它让数据库能存储和查询"点、线、面"等几何对象，并回答这类问题：

附近检索：距离我 3 公里内的餐厅有哪些？（外卖/打车）
围栏判断：这个坐标是否落在某个配送区域/行政区内？
路径与量算：两点间距离多远？某区域面积多大？
空间关系：两条道路是否相交？两个多边形是否重叠？

对比 MySQL：MySQL 也有 GEOMETRY 类型和基础空间函数（ST_Distance、ST_Contains），并支持 SPATIAL 索引（R-Tree）。但函数数量、投影/坐标系支持、栅格数据、拓扑等方面远不及 PostGIS。做严肃的 GIS 应用，PostgreSQL + PostGIS 几乎是唯一开源选择。

2. 安装与启用

-- 启用扩展（需先在系统层安装 postgis 包）
CREATE EXTENSION IF NOT EXISTS postgis;

-- 查看版本信息
SELECT PostGIS_Full_Version();

3. 两种核心类型：geometry vs geography

这是 PostGIS 最重要、也是面试最常问的概念区分：

维度	`geometry`（几何）	`geography`（地理）
坐标系	平面直角坐标（笛卡尔）	球面坐标（经纬度）
距离计算	平面几何，单位是坐标单位	按地球椭球面，单位是米
精度	大范围有投影误差	全球范围更精确
性能	快	慢（球面计算复杂）
函数丰富度	函数最全	部分函数不支持
适用	小范围/已投影/追求性能	全球范围、需真实距离（米）

经验法则：

只在一个城市/小区域内、且做了投影 → 用 geometry（快）。
需要跨城市/全球、直接用经纬度算真实距离 → 用 geography（准）。
常见折中：列用 geometry(Point,4326) 存，算距离时临时 ::geography 转换。

4. SRID 与坐标系

**SRID（Spatial Reference ID）**标识坐标系。最常见两个：

4326（WGS84）：GPS 经纬度坐标系，geom(经度 lng, 纬度 lat)。⚠️ 注意 经度在前、纬度在后，这是新手最易错的点。
3857（Web Mercator）：地图瓦片常用的投影坐标系（Google/高德地图底图）。

-- 坐标系转换（4326 经纬度 → 3857 米制投影）
SELECT ST_Transform(ST_SetSRID(ST_MakePoint(116.40, 39.90), 4326), 3857);

5. 建表与写入数据

CREATE TABLE places (
    id    BIGSERIAL PRIMARY KEY,
    name  VARCHAR(100),
    -- 指定几何类型为点、SRID 为 4326
    geom  geometry(Point, 4326)
);

-- 插入：ST_MakePoint(经度, 纬度) 再 ST_SetSRID 指定坐标系
INSERT INTO places (name, geom) VALUES
    ('天安门', ST_SetSRID(ST_MakePoint(116.3974, 39.9087), 4326)),
    ('故宫',   ST_SetSRID(ST_MakePoint(116.3972, 39.9163), 4326)),
    ('上海外滩', ST_SetSRID(ST_MakePoint(121.4900, 31.2400), 4326));

-- 也可用 EWKT / GeoJSON 写入
INSERT INTO places (name, geom) VALUES
    ('测试点', 'SRID=4326;POINT(120.0 30.0)'),
    ('GeoJSON点', ST_GeomFromGeoJSON('{"type":"Point","coordinates":[113.0,23.0]}'));

6. 空间索引（关键性能）

空间查询若无索引会全表逐行计算，必须建 GiST 空间索引：

CREATE INDEX idx_places_geom ON places USING GiST (geom);

-- PostGIS 也支持 SP-GiST 和 BRIN（超大范围有序空间数据）
CREATE INDEX idx_places_geom_spgist ON places USING SPGIST (geom);

空间索引加速的是"带包围盒（Bounding Box）判断"的运算符，如 &&（包围盒相交）。像 ST_DWithin、ST_Intersects 内部会先用包围盒过滤（走索引），再精确判断，因此能高效利用索引。

7. 核心函数分类

7.1 量算函数

-- 两点真实距离（米）：转 geography 才是米制
SELECT ST_Distance(a.geom::geography, b.geom::geography) AS meters
FROM places a, places b
WHERE a.name = '天安门' AND b.name = '故宫';

-- 面积、周长、长度
SELECT ST_Area(geom::geography)   AS 面积平方米,
       ST_Perimeter(geom::geography) AS 周长米
FROM regions;

7.2 空间关系判断（返回布尔）

ST_Contains(A, B)    -- A 是否完全包含 B
ST_Within(A, B)      -- A 是否在 B 内部
ST_Intersects(A, B)  -- 是否相交（有公共点）
ST_Overlaps(A, B)    -- 是否部分重叠
ST_Touches(A, B)     -- 是否仅边界接触
ST_DWithin(A, B, d)  -- A、B 距离是否在 d 之内（最常用于"附近"）

7.3 几何构造/处理

ST_Buffer(geom, 1000)     -- 生成缓冲区（如以点为中心 1km 的圆）
ST_Centroid(geom)         -- 求质心
ST_Union(geom1, geom2)    -- 合并
ST_Intersection(a, b)     -- 求交集几何
ST_ConvexHull(geom)       -- 凸包

7.4 格式转换

ST_AsText(geom)      -- 转 WKT 文本：POINT(116.4 39.9)
ST_AsGeoJSON(geom)   -- 转 GeoJSON（前端地图直接用）
ST_X(geom), ST_Y(geom) -- 取经度、纬度

8. 实战：附近的地点（LBS 核心查询）

“查找我附近 N 公里内的地点，并按距离排序"是外卖/打车/社交的核心场景。

8.1 距离过滤 + 排序（正确写法）

-- 我的位置
WITH me AS (SELECT ST_SetSRID(ST_MakePoint(116.40, 39.91), 4326)::geography AS g)
SELECT
    p.name,
    ST_Distance(p.geom::geography, me.g) AS distance_m
FROM places p, me
WHERE ST_DWithin(p.geom::geography, me.g, 3000)   -- ★3km内，能用空间索引
ORDER BY p.geom::geography <-> me.g               -- ★KNN 距离排序，也能用索引
LIMIT 20;

为什么这样写高效？

ST_DWithin(..., 3000) 而不是 ST_Distance(...) < 3000：前者内部先用包围盒走 GiST 索引过滤，后者会对全表算距离（无法用索引）。这是 PostGIS 最经典的性能陷阱。
<-> 是 KNN 距离运算符，配合 GiST 索引可高效返回"最近的 N 个”，无需算完所有距离再排序。

8.2 电子围栏：判断点是否在区域内

CREATE TABLE delivery_zones (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(50),
    area geometry(Polygon, 4326)
);

-- 判断某坐标落在哪个配送区
SELECT z.name
FROM delivery_zones z
WHERE ST_Contains(z.area, ST_SetSRID(ST_MakePoint(116.40, 39.91), 4326));

9. 常见性能陷阱

用 ST_Distance < d 而非 ST_DWithin → 无法用索引，改用 ST_DWithin。
忘记建 GiST 索引 → 空间查询全表扫描。
geometry 与 geography 混用 → 距离单位不对（度 vs 米）。
SRID 不一致 → 函数报错或结果错误，入库前统一 SRID。
经纬度写反（把纬度当经度）→ 位置飞到国外，牢记"经度在前"。

10. 面试高频问答

Q1：geometry 和 geography 有什么区别？ geometry 是平面笛卡尔坐标，计算快但大范围有投影误差，单位是坐标单位；geography 是球面坐标，按地球椭球面计算、单位是米、全球更精确但更慢。小范围用 geometry，跨区域/需真实距离用 geography。

Q2：为什么查"附近 3 公里"要用 ST_DWithin 而不是 ST_Distance < 3000？ ST_DWithin 内部先用包围盒判断，可命中 GiST 空间索引；ST_Distance(...) < 3000 需对每一行都精确算距离，无法用索引，大表会很慢。

Q3：PostGIS 用什么索引？原理是什么？ 主要用 GiST 索引，基于 R-Tree 思想，用几何对象的最小包围盒（MBR）构建平衡树，查询时先按包围盒快速过滤候选，再精确判断。

Q4：SRID 4326 和 3857 分别是什么？ 4326 是 WGS84 GPS 经纬度坐标系；3857 是 Web Mercator 投影坐标系，地图瓦片底图常用。存储常用 4326，可视化/量算时按需 ST_Transform 转换。

Q5：PostGIS 相比 MySQL 空间能力强在哪？ PostGIS 函数最全、支持完整 OGC 规范、多坐标系投影、栅格与拓扑数据、KNN 检索等，是开源 GIS 事实标准；MySQL 空间功能较基础。

11. 下一步

学习 pgvector向量数据库，进入 AI 时代的相似度检索。

目录