Postgis

项目地址： https://github.com/postgis/postgis

官方文档： https://postgis.net/docs/

最新版本： 3.6.4（截至 2026-10，Releases）；3.7 系列已到 3.7.0rc3，尚未正式发布

许可证： GPL-2.0+

概述

PostGIS 是基于 PostgreSQL 的开源空间数据库扩展，遵循 OGC Simple Features for SQL 规范。核心能力：

• 空间数据类型：geometry（平面）、geography（球面）、raster、topology、3DZ/4DZM
• 空间索引：基于 R-Tree-over-GiST，亿级要素仍保持毫秒级查询
• 1000+ 空间函数：覆盖度量、关系、构造、聚合、栅格代数
• 坐标参考系：内置 6000+ EPSG 投影，自动 ST_Transform
• 数据导入导出：与 GDAL/ogr2ogr/shp2pgsql 无缝集成

版本要求： PostgreSQL 12–18 配合 PostGIS 3.x（最新稳定版 3.6.4；3.7 仍处 RC 阶段，候选版为 3.7.0rc3，截至 2026-10）；GEOS ≥ 3.8（部分新算子需 GEOS ≥ 3.14）

环境准备

安装

bashCopy code
# Debian/Ubuntu
sudo apt install postgresql-17-postgis-3 postgresql-17-postgis-3-scripts

# CentOS/RHEL
sudo yum install postgis36_17

# macOS
brew install postgis

# Docker（推荐，标签格式 <PG 主版本>-<PostGIS 主版本>）
docker run --name pg -e POSTGRES_PASSWORD=pg -p 5432:5432 -d postgis/postgis:17-3.6

启用扩展

sqlCopy code
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;     -- 拓扑（可选）
CREATE EXTENSION postgis_raster;       -- 栅格（可选）
CREATE EXTENSION postgis_sfcgal;       -- 3D 高级运算（可选）
SELECT PostGIS_Full_Version();         -- 验证

核心 API：数据类型与函数

建表与索引

sqlCopy code
CREATE TABLE poi (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom geometry(Point, 4326)
);

CREATE INDEX poi_geom_idx ON poi USING GIST (geom);

INSERT INTO poi(name, geom) VALUES
    ('天安门', ST_GeomFromText('POINT(116.397 39.908)', 4326)),
    ('外滩',   ST_SetSRID(ST_MakePoint(121.490, 31.241), 4326));

ANALYZE poi;

常用空间 SQL

1. 空间关系

sqlCopy code
-- 范围查询：&& 走 GiST 索引
SELECT id, name FROM poi
WHERE geom && ST_MakeEnvelope(116, 39, 117, 40, 4326)
  AND ST_Within(geom, ST_MakeEnvelope(116, 39, 117, 40, 4326));

-- KNN：<-> 走索引
SELECT id, name, geom <-> ST_MakePoint(116.4, 39.9)::geometry AS d
FROM poi ORDER BY d LIMIT 10;

-- 5 公里缓冲区内
SELECT * FROM poi
WHERE ST_DWithin(geom::geography, ST_MakePoint(116.4, 39.9)::geography, 5000);

2. 几何变换

sqlCopy code
SELECT ST_Buffer(geom::geography, 100)::geometry FROM poi;       -- 100 米
SELECT ST_Transform(geom, 3857) FROM poi;                        -- WGS84→Mercator
SELECT ST_SimplifyPreserveTopology(geom, 0.001) FROM province;
SELECT ST_Centroid(geom), ST_ConvexHull(geom), ST_Envelope(geom) FROM province;

3. 集合运算

sqlCopy code
SELECT ST_Union(geom)        FROM city WHERE province_id = 31;
SELECT ST_Intersection(a.geom, b.geom) FROM a, b WHERE a.id=1 AND b.id=2;
SELECT ST_Difference(a.geom, b.geom)   FROM a, b;

4. 度量

sqlCopy code
SELECT ST_Area(geom::geography), ST_Length(geom::geography),
       ST_Distance(a.geom::geography, b.geom::geography);

5. 输出格式

sqlCopy code
SELECT ST_AsText(geom), ST_AsGeoJSON(geom), ST_AsBinary(geom) FROM poi;

数据导入导出

bashCopy code
# Shapefile → PostGIS
shp2pgsql -s 4326 -I -W UTF-8 china.shp public.china | psql -d gisdb

# 反向
pgsql2shp -f out.shp -h localhost -u postgres gisdb "SELECT * FROM poi"

# 通用（GDAL）
ogr2ogr -f PostgreSQL "PG:host=localhost user=postgres dbname=gisdb password=pg" \
        -nln poi -nlt PROMOTE_TO_MULTI -lco GEOMETRY_NAME=geom \
        -t_srs EPSG:4326 input.gpkg

矢量瓦片（ST_AsMVT）

sqlCopy code
WITH mvtgeom AS (
    SELECT ST_AsMVTGeom(
             ST_Transform(geom, 3857),
             ST_TileEnvelope(:z, :x, :y)) AS geom,
           id, name
    FROM poi
    WHERE geom && ST_Transform(ST_TileEnvelope(:z, :x, :y), 4326)
)
SELECT ST_AsMVT(mvtgeom.*, 'poi', 4096, 'geom') FROM mvtgeom;

栅格

sqlCopy code
-- 导入（命令行）
raster2pgsql -s 4326 -I -C -M dem.tif -F -t 256x256 public.dem | psql -d gisdb

SELECT ST_Value(rast, ST_MakePoint(116.4, 39.9)) FROM dem;
SELECT (ST_SummaryStats(rast)).* FROM dem;
SELECT ST_Clip(rast, geom, true) FROM dem, region WHERE region.id=1;

典型工作流：Shapefile 入 PostGIS → 空间连接 → 导出 GeoJSON

输入：data/poi.shp（点，EPSG:4326）、data/districts.shp（面，EPSG:4326）。

步骤 1 —— 入库并建索引（shp2pgsql -I 自动建 GiST 索引，-s 4326 指定 SRID）：

bashCopy code
shp2pgsql -s 4326 -I -W UTF-8 data/poi.shp public.poi \
  | psql -d gisdb -v ON_ERROR_STOP=1
shp2pgsql -s 4326 -I -W UTF-8 data/districts.shp public.districts \
  | psql -d gisdb -v ON_ERROR_STOP=1
psql -d gisdb -c "VACUUM ANALYZE poi, districts;"

步骤 2 —— 空间连接（点落在哪个区；先用 && 走索引再 ST_Within 精筛）：

sqlCopy code
CREATE TABLE poi_with_district AS
SELECT p.id, p.name, d.name AS district, p.geom
FROM poi AS p
JOIN districts AS d
  ON p.geom && d.geom           -- 包围盒粗筛，走 GiST
 AND ST_Within(p.geom, d.geom); -- 精确判断

CREATE INDEX ON poi_with_district USING GIST (geom);

步骤 3 —— 导出 GeoJSON（-t_srs EPSG:4326 保证输出为经纬度）：

bashCopy code
ogr2ogr -f GeoJSON out/poi_with_district.geojson \
  "PG:host=localhost dbname=gisdb user=postgres" poi_with_district \
  -t_srs EPSG:4326

输出与验收：

sqlCopy code
-- 落在区内的点数（INNER JOIN 会过滤掉区外的点，因此 <= POI 总数）
SELECT count(*) FROM poi_with_district;

-- 未匹配数 = POI 总数 - 已匹配数（差值即落在所有区之外的点）
SELECT (SELECT count(*) FROM poi)
     - (SELECT count(*) FROM poi_with_district) AS unmatched;

-- SRID 应为 4326
SELECT ST_SRID(geom) FROM poi_with_district LIMIT 1;

-- 确认连接走 GiST：计划里应出现 poi_geom_idx 上的 Index/Bitmap Index Scan
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM poi AS p JOIN districts AS d
  ON p.geom && d.geom AND ST_Within(p.geom, d.geom);

若两边 SRID 不一致，&& 会退化为逐行比较且可能报 Operation on mixed SRID；先 ST_Transform 统一到 4326 再入库。

最后用 ogrinfo -al -so out/poi_with_district.geojson 核对要素数与字段，不要只凭 psql 的 INSERT 0 n 判定成功。

性能优化

1. 必建空间索引：USING GIST(geom)
2. 用 && 让查询走索引，再用 ST_Within/Intersects 精筛
3. 大批量导入后 VACUUM ANALYZE
4. SRID 必须一致，跨 SRID 必须 ST_Transform
5. 距离排序用 KNN <-> 替代 ST_Distance
6. 几何简化：前端展示用 ST_SimplifyPreserveTopology
7. 大表按行政区分区
8. ST_Subdivide 切分大几何加速空间连接

常见问题

AI 使用建议

• 执行环境：命令在终端 / shell 中执行（Windows 用 psql.exe，或 docker exec -i pg psql -U postgres）；psql 元命令（\d、\c）用 psql -c "\d poi" 单条传入，别依赖交互式会话。
• 先探测版本：psql -c "SELECT version(), PostGIS_Full_Version();" 核对 PostgreSQL、PostGIS 3.6.4 与 GEOS/PROJ；扩展缺失时先看 SELECT * FROM pg_available_extensions WHERE name LIKE 'postgis%';。
• 验证产物：导入后别只看 INSERT 0 n，跑 SELECT count(*), ST_SRID(geom), ST_IsValid(geom) FROM t GROUP BY 2,3; 核对要素数、SRID 与无效几何；性能结论必须用 EXPLAIN (ANALYZE, BUFFERS) 证明索引被使用。
• 写操作回读：CREATE/ALTER、UpdateGeometrySRID 或批量 UPDATE 后回读 \d+ t 与 geometry_columns，确认列类型与 SRID 真的落库。
• 安全与权限：密码不要写进命令行或报告（用 PGPASSWORD/.pgpass/连接串环境变量）；DROP TABLE/SCHEMA、整表改写 geom、VACUUM FULL、重启 PostgreSQL 前必须先向用户确认并备份。
• 大表 DDL：UPDATE/CREATE INDEX 尽量放在可控事务里避免长时间锁表；CREATE INDEX CONCURRENTLY 不能包在事务块中执行。

相关技能

• geoserver — 地图服务发布（PostGIS 是最常见数据源）：../geoserver/SKILL.md
• geotools — Java GIS 工具库：../geotools/SKILL.md
• geopandas — Python 矢量数据处理：../geopandas/SKILL.md
• gdal — 命令行数据导入导出：../gdal/SKILL.md

参考资源

• 文档：https://postgis.net/docs/
• 函数速查：https://postgis.net/docs/reference.html
• Workshop：https://postgis.net/workshops/postgis-intro/
• 中文教程（znlgis）：https://znlgis.github.io/gis/tutorial/postgis/