postgis-skill

文档

• 确保每条创建语句或 CTE 前都有描述性注释 --。
• 编写足够的注释，以便将来能推断出需求是什么，避免兜圈子。
• 每项功能附近都需要有全面且最新的文档。

风格

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

索引

• 创建大型表以供临时查询使用时，为所有列创建 brin 索引。
• 如果缓存表有主键，在同一索引的 including 中添加值有助于加快查找。

调试

• 面向开发者的错误消息应比“500 Internal server error”更有帮助。
• 不要用荒唐的回退值（例如 lat/lon=0）来填补空缺；应让其余代码妥善处理数据缺失，并告知用户。
• SQL 文件应具备幂等性：先执行 drop table if exists，再执行 create table as；添加一些注释，帮助人们更快理解查询。
• 创建新的迁移时，同时创建“up”和“down/rollback”迁移，便于迭代。
• 检查 select postgis_full_version();，确认所有升级都已成功完成。
• 不要在一个 SQL 文件中运行另一个 SQL 文件——这很快就会因相对文件路径变得一团糟。

栅格

• 不要在文件系统上使用 GDAL。将数据导入数据库，并在那里处理。

SQL 注意事项

• sum(case when A then 1 else 0 end) 其实就是 count() filter (where A)
• row_number() ... = 1 很可能可以改写为 order by + limit 1（可能还会用到 distinct on 或 lateral）
• exists(select 1 from ...) 其实就是 exists(select from ...)
• tags ->> 'key' = 'value' 其实就是 tags @> '{"key": "value"}——后者对索引更快
• 不能只创建有序表，然后在扫描时不使用 order by 却依赖其保持有序

PostGIS 注意事项

• 除非有要求，否则不要使用 geometry typmod（例如 geometry(multilinestring, 4326)）——改用普通的 geometry 或 geography。这样可以避免 ST_Multi 带来的杂乱，以及通过 ST_SetSRID 产生的错误。
• ST_UnaryUnion(ST_Collect(geom)) 就是 ST_Union(geom)
• ST_Buffer(geom, 0) 应改为 ST_MakeValid(geom)
• 要启用 knn gist，应将 select min(ST_Distance(..)) 改为 select ST_Distance() ... order by a <-> b limit 1
• 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 扩展。
• 如果确定数据将以二进制形式导出、压缩为 gzip 并传输，可以在精度已知时考虑使用 ST_QuantizeCoordinates。