PostGIS 最佳实践指南：SQL 代码规范、空间索引与性能优化技巧

postgis-skill by postgis/postgis

99 周安装量

2,100 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/postgis/postgis --skill postgis-skill

数据库地理信息系统代码规范

🇨🇳中文介绍

文档

确保每个创建语句或 CTE 前面都有描述性注释 --。
编写足够的注释，以便未来能够推断出需求是什么，避免原地打转。
每个功能附近都需要有全面且最新的文档。

风格

PostGIS 函数遵循手册中的拼写（st_segmentize -> ST_Segmentize）。
SQL 使用小写，除非另有说明。
数据库和图层中的值应尽可能绝对：存储"生日"或"建造日期"，而不是"年龄"。
不要在代码中混合使用制表符和空格。
在逻辑块之间添加空行。
美观且一致地格式化代码。
将几何列命名为 geom；地理列命名为 geog。

索引

创建将用于临时查询的大型表时，为所有列创建 BRIN 索引。
如果缓存表有主键，在同一索引的 including 子句中添加值以加快查找速度是有意义的。

调试

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

PostGIS 常见陷阱

除非要求，否则不要使用几何类型修饰符（例如 geometry(multilinestring, 4326)）——使用普通的 geometry 或 geography 代替。这样可以避免 ST_Multi 的混乱和 ST_SetSRID 导致的错误。
ST_UnaryUnion(ST_Collect(geom)) 其实就是 ST_Union(geom)
ST_Buffer(geom, 0) 应该是 ST_MakeValid(geom)
select min(ST_Distance(..)) 应该是 select ST_Distance() ... order by a <-> b limit 1 以启用 KNN GIST 索引
order by ST_Distance(c.geog, t.geog) 应该是 order by c.geog <-> t.geog
ST_UnaryUnion 是一个信号，表明你可能做错了什么
ST_MakeValid 是一个信号，表明你在上一步可能做错了什么
调用 ST_SetSRID 时要格外注意：检查输入数据的实际投影，检查是否可以在输入时正确设置（ST_GeomFromGeoJSON、EWKT 风格的 SRID=4326;POINT(...、EWKB 允许这样做）。检查是否需要改用 ST_Transform。
在查找点与多边形的关系时，优先使用 ST_Intersects 而不是其他拓扑谓词。
通过遍历栅格或网格生成复杂几何图形时，使用 ST_Simplify(geom, 0) 可能是有意义的。
要生成可预测大小的邻域，请使用 ST_ClusterKMeans，k=2，并将 max_radius 设置为你的距离。
使用 ST_AsEWKB 进行二进制表示，而不是 ST_AsWKB，以保留 SRID。
选择投影：当输入或输出是经度和纬度，并且坐标值要显示给用户时，使用 SRID=4326（2D 经纬度）。当输出将显示在 Web 地图上、使用 ST_AsMVT 或执行短距离的 2D KNN 请求时，使用 SRID=3857（2D 球面墨卡托）。当执行内部计算、视线分析、跨越国际日期变更线的聚类和平均时，使用 SRID=4978（3D XYZ）。注意：仅使用支持 3D 的操作，在调用 ST_Transform 到该坐标系之前，对 2D CRS 数据使用 ST_Force3DZ。
不要使用 ST_Hexagon / ST_HexagonGrid，改用 h3 扩展。
当你知道数据将以二进制形式转储、压缩并四处移动时，如果精度已知，请考虑使用 ST_QuantizeCoordinates。

🇺🇸English

Documentation

Make sure every create statement or CTE has descriptive comment -- in front of it.
Write enough comments so you can deduce what was a requirement in the future and not walk in circles.
Every feature needs to have comprehensive up-to-date documentation near it.

Style

PostGIS functions follow their spelling from the manual (st_segmentize -> ST_Segmentize).
SQL is lowercase unless instructed otherwise.
Values in databases and layers should be absolute as much as possible: store "birthday" or "construction date" instead of "age".
Do not mix tabs and spaces in code.
Add empty lines between logical blocks.
Format the code nicely and consistently.
Call geometry column geom; geography column geog.

Indexing

Create brin for all columns when creating large table that will be used for ad-hoc queries.
If you have cache table that has a primary key, it makes sense to add values into including on same index for faster lookup.

Debugging

Make sure that error messages towards developer are better than just "500 Internal server error".
Don't stub stuff out with insane fallbacks (like lat/lon=0) - instead make the rest of the code work around data absence and inform user.
SQL files should to be idempotent: drop table if exists + create table as; add some comments to make people grasp queries faster.
Create both "up' and "down/rollback" migration when creating new migrations for ease of iteration.
Check select postgis_full_version(); to see if all upgrades happened successfully.
Don't run one SQL file from other SQL file - this quickly becomes a mess with relative file paths.

Raster

Do not work with GDAL on the filesystem. Import things into database and deal with data there.

SQL gotchas

sum(case when A then 1 else 0 end) is just count() filter (where A)
row_number() ... = 1 can likely be redone as order by + limit 1 (possibly with distinct on or lateral)
exists(select 1 from ...) is just exists(select from ...)
tags ->> 'key' = 'value' is just tags @> '{"key": "value"} - works faster for indexes

PostGIS gotchas

Do not use geometry typmod unless requested (things like geometry(multilinestring, 4326)) - use plain geometry or geography instead. This removes clutter of ST_Multi and errors via ST_SetSRID.
ST_UnaryUnion(ST_Collect(geom)) is just ST_Union(geom)
ST_Buffer(geom, 0) should be ST_MakeValid(geom)
select min(ST_Distance(..)) should be to enable knn gist

Weekly Installs

Repository

postgis/postgis

GitHub Stars

2.1K

First Seen

Jan 25, 2026

Security Audits

Gen Agent Trust HubPass SocketPass SnykPass

Installed on

codex77

opencode76

gemini-cli72

github-copilot72

cursor70

kimi-cli68

代码安全审查清单：最佳实践与漏洞防范指南（含密钥管理、SQL注入防护）

1,700 周安装

you can't just create ordered table and then rely on it to be ordered on scan without order by

select ST_Distance() ... order by a <-> b limit 1

order by ST_Distance(c.geog, t.geog) should be order by c.geog <-> t.geog

ST_UnaryUnion is a sign you're doing something wrong

ST_MakeValid is a sign you're doing something wrong on the previous step

be extra attintive when calling ST_SetSRID: check the actual projection of input data, check if it can be set correctly during input (ST_GeomFromGeoJSON, EWKT-style SRID=4326;POINT(..., EWKB allow that). Check if ST_Transform is needed instead.

when looking for relation between point and polygon, prefer ST_Intersects to other topology predicates

when generating complex geometry by walking raster or grid, may make sense to ST_Simplify(geom, 0)

to generate neighbourhoods of predictable size, use ST_ClusterKMeans with k=2 and max_radius set to your distance.

use ST_AsEWKB for binary representation instead of ST_AsWKB to keep SRID.

Choosing projection: SRID=4326 (2D longlat) when input or output is longitude and latitude and coordinate value is to be shown to user. SRID=3857 (2D Spherical Mercator) when output will be shown on web map, ST_AsMVT, or 2D KNN requests of short distance are to be executed. SRID=4978 (3D XYZ) when performing internal computations, line-of-sight, clustering and averaging across antimeridian. Beware: only use 3D-aware operations, ST_Force3DZ on 2D CRS data before calling ST_Transform to it.

Instead of using ST_Hexagon / ST_HexagonGrid use h3 extension.

When you know the data is going to be dumped in binary form, gzipped and moved around, consider using ST_QuantizeCoordinates if precision is known.