# PostGIS 3.6.1 空间数据库 ## 概述 PostGIS 3.6.1（带有 GEOS 3.14）带来了重大改进：用于拓扑修复的 ST_CoverageClean、增强的 SFCGAL 3D 操作、对海量数据集的 bigint 拓扑支持以及改进的 PostgreSQL 18 集成。这项技能可确保您正确利用这些功能。 **核心原则：** 空间是特殊的。通用数据库模式通常无法处理地理数据。 **开始时宣布：**“我正在应用 postgis 来确保 PostGIS 3.6.1 空间最佳实践。” ## 当此技能适用时当触及以下任何模式时，此技能是强制性的：图案|示例 ---|--- `**/*地理*` |模型/geography.ts，geo_utils.py `**/*空间*` |库/空间.ts `**/*位置*` |服务/locationService.ts `**/*坐标*` |类型/坐标.ts `**/*多边形*` |数据库/多边形.sql `**/*几何*` |迁移/add_geometry.sql `**/*postgis*` |安装程序/postgis.sql `**/*gis*` |实用程序/gis.ts 或者当文件包含： -- 这些模式触发该技能 ST_* 地理几何学 SRID ## PostGIS 3.6.1 功能 ### 1\. ST_CoverageClean（3.6.1 中的新增功能）覆盖清理修复多边形集合中的拓扑错误。需要 GEOS 3.14： -- 清理一组应形成无缝覆盖的多边形 -- 修复：重叠、间隙、边缘不一致选择 ST_CoverageClean( ARRAY[多边形1、多边形2、多边形3]::几何[] ) AS clean_polygons; -- 使用案例：行政边界、地块、区域 -- 之前：使用 ST_MakeValid、ST_SnapToGrid 手动修复 -- 之后：单个函数处理整个覆盖范围 -- 示例：清洁城市边界边界为 AS ( 从城市中选择 geom ）选择 ST_CoverageClean(array_agg(geom)) 从边界； **何时使用：** * 导入有拓扑错误的GIS数据 * 合并不同来源的数据集 * 确保无缝覆盖（无间隙/重叠） * 地籍/地块数据管理 ### 2\. SFCGAL 3D 功能 PostGIS 3.6.1 包括对 3D 操作的增强 SFCGAL 支持： -- 启用 SFCGAL（如果尚未启用）如果不存在则创建扩展 postgis_sfcgal； -- 3D交集（真正的3D，不是投影）选择 ST_3D 交集（ ST_GeomFromText('多面体曲面 Z (...)'), ST_GeomFromText('多面体曲面 Z (...)') ）； -- 3D联盟选择 ST_3DUnion(geom1, geom2); -- 3D 面积（3D 中的实际表面积）选择 ST_3DArea(多面体表面); -- Minkowski sum（用于 3D 中类似缓冲区的操作）选择 ST_MinkowskiSum(几何图形 1, 几何图形 2); -- 直骨架（用于建筑屋顶等）选择 ST_StraightSkeleton(多边形); -- 将 2D 拉伸为 3D SELECT ST_Extrude(多边形, 0, 0, 高度); **使用案例：** * 建筑/结构建模 * 地下基础设施 * 空域管理 * 3D地形分析 ### 3\. Bigint 拓扑支持 PostGIS 3.6.1支持海量数据集的bigint拓扑ID： -- 使用 bigint ID 创建拓扑（3.6.1 中的新增功能）选择 CreateTopology('massive_parcels', 4326, 0.0000001, true); -- 最后一个参数：use_bigint = true -- 每个拓扑支持超过 20 亿个特征 -- 先前限制：~20 亿（int4 最大值） -- 添加图层 SELECT AddTopoGeometryColumn('massive_parcels', 'public', 'parcels', 'topogeom', 'POLYGON'); -- TopoGeometry 操作的工作方式相同 SELECT ST_CreateTopoGeo('massive_parcels', geom); **何时使用：** * 国家/大陆规模的数据集 * 高分辨率包裹数据 * OpenStreetMap 导入 * 任何拓扑 > 20 亿条边 ### 4\. PostgreSQL 18 中断处理 PostGIS 3.6.1 正确处理 PostgreSQL 18 改进的查询取消： -- 现在可以彻底取消长时间运行的空间操作 -- 不再有孤立锁或损坏的状态 -- 示例：可取消的重操作选择 ST_Union(geom) 来自非常大的表按地区分组； -- ^C 现在可以正常工作了 -- 使用 PostGIS 进行 COPY 操作也遵循取消操作 COPY (SELECT id, ST_AsGeoJSON(geom) FROM features) TO '/tmp/export.json'; ## 数据类型 ### 几何与地理 -- GEOMETRY：平面坐标，任何 SRID -- 计算速度更快，远距离精度较差创建表 place_geometry ( id uuid 主键默认 uuidv7(), 位置几何（点，4326）--WGS84 ）； -- 地理：球坐标，始终为 WGS84 -- 精确的距离/面积，计算速度较慢创建表places_geography ( id uuid 主键默认 uuidv7(), 位置地理（点，4326）——始终是 WGS84 ）； -- 何时使用几何： -- - 本地/城市规模的应用 -- - 需要复杂的运算（并集、交集） -- - 性能关键 -- - 非地球数据（游戏地图、平面图） -- 何时使用地理： ---全球应用 -- - 距离/面积精度很重要 -- - 简单操作（距离、包含） ---面向用户的距离计算 ### 选择 SRID -- 常见 SRID： -- 4326：WGS84（GPS 坐标、网络地图） -- 3857：Web Mercator（基于图块的网络地图，仅显示） -- 精确测量的局部投影 -- 始终存储在 4326 (WGS84) 作为事实来源 -- 需要时进行转换以进行计算创建表位置（ id uuid 主键默认 uuidv7(), 名称文本不为空，位置 geography(Point, 4326), -- 存储 location_local Geometry(Point) -- NULL，根据需要计算）； -- 转换为本地计算选择 ST_Transform( 位置::几何， 32610 -- UTM 10N 区（加利福尼亚州） ) 来自名称 = '旧金山' 的地点； ## 指数策略 ### 空间索引 -- GiST 索引：大多数空间查询的默认索引使用 gist(location) 在位置上创建索引 idx_locations_geom； -- BRIN 索引：适用于非常大的、自然排序的数据集 --（例如，按时间排序的 GPS 轨迹）使用 brin(location) 在 gps_tracks 上创建索引 idx_tracks_geom； -- SP-GiST：用于非重叠数据（点、IP 范围）使用 spgist(location) 在点上创建索引 idx_points_spgist; ### 索引最佳实践 -- 始终包含空间索引创建表功能（ id uuid 主键默认 uuidv7(), geom 几何（多边形，4326），现在创建的时间戳默认值() ）；使用 gist(geom) 在特征上创建索引 idx_features_geom; -- 活动记录的部分空间索引使用 gist(geom) 在功能上创建索引 idx_features_geom_active 其中deleted_at为NULL； -- 常见查询模式的复合索引使用 gist(geom) 在功能上创建索引 idx_features_type_geom WHERE feature_type = '建筑'; ### 索引聚类 -- 通过空间索引对表进行聚类以提高范围查询性能使用 idx_features_geom 进行聚类特征； -- 对于大表，定期重新集群 -- 维护窗口期间的时间表 ## 查询模式 ### 距离查询 -- 查找距离内的点（地理，以米为单位）从位置选择 * WHERE ST_D 内（位置, ST_MakePoint(-122.4194, 37.7749)::地理, 1000——1公里半径）； -- 查找距离内的点（几何形状，以 SRID 单位）从位置选择 * WHERE ST_D 内（位置, ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326), 0.01 -- ~1km 在这个纬度（度））； -- K-最近邻（KNN） SELECT *, 位置 <-> ST_MakePoint(-122.4194, 37.7749)::地理 AS 距离出发地点按位置排序<-> ST_MakePoint(-122.4194, 37.7749)::地理限制 10； -- 使用索引实现高效 KNN ### 遏制查询 -- 多边形内的点 SELECT * FROM 点 WHERE ST_Within（位置，（从名称 = '加利福尼亚' 的区域中选择边界））； -- 多边形包含点从区域中选择 * WHERE ST_Contains(边界, ST_MakePoint(-122.4194, 37.7749)); -- 相交（以任何方式重叠）从功能中选择 * WHERE ST_Intersects(geom, query_polygon); ### 聚合 -- 合并所有几何图形 SELECT ST_Union(geom) FROM 地块 WHERE Owner = 'City'; -- 收集而不合并（更快，保留单个几何形状） SELECT ST_Collect(geom) FROM 地块 WHERE Owner = 'City'; -- 范围（边界框）从要素中选择 ST_Extent(geom)； -- 所有点的质心从位置中选择 ST_Centroid(ST_Collect(位置))； ## GeoJSON 集成 ### 导入/导出 -- 几何到 GeoJSON 从 id = $1 的位置中选择 ST_AsGeoJSON(location)； -- 具有特征属性的几何图形选择 jsonb_build_object( '类型'，'特征'， '几何', ST_AsGeoJSON(位置)::jsonb, '属性', jsonb_build_object( 'id', id, '名字', 名字） ) 来自 id = $1 的位置； -- 特征集合选择 jsonb_build_object( '类型', '特征集合', '特征', jsonb_agg( jsonb_build_object( '类型'，'特征'， '几何', ST_AsGeoJSON(位置)::jsonb, '属性', jsonb_build_object('id', id, '名称', 名称) ）） ) 从地点； -- GeoJSON 到几何插入位置（名称、位置） VALUES ('新地点', ST_GeomFromGeoJSON($1)); -- 执行 SRID 插入位置（名称、位置） VALUES ('新地点', ST_SetSRID(ST_GeomFromGeoJSON($1), 4326)); ### API 响应模式 -- API 端点的函数创建或替换函数 get_locations_geojson( 边界几何 DEFAULT NULL ）返回 jsonb AS $$ 选择 jsonb_build_object( '类型', '特征集合', '特征', COALESCE(jsonb_agg( jsonb_build_object( '类型'，'特征'， 'id', id, 'geometry', ST_AsGeoJSON(location, 6)::jsonb, -- 6 位小数 '属性', jsonb_build_object( '名字'，名字， '创建时间', 创建时间）） ), '[]'::jsonb) ）出发地点 WHEREbounds IS NULL OR ST_Intersects(location::geometry,bounds); $$ 语言 SQL 稳定； ## 验证和修复 ### 验证几何形状 -- 检查有效性选择 ID、ST_IsValid(geom)、ST_IsValidReason(geom) 从特征哪里不是 ST_IsValid(geom); -- 常见问题： ——《自相交》 ——《环自相交》 --“几何组件中的点太少” ——“壳外有洞” ### 修复几何形状 -- 简单修复（处理大多数问题）更新功能 SET geom = ST_MakeValid(geom) 哪里不是 ST_IsValid(geom); -- 修复具体策略更新功能 SET geom = ST_MakeValid(geom, '方法=结构') 哪里不是 ST_IsValid(geom); -- 多边形集的覆盖干净 (3.6.1) 与清洁的 AS ( SELECT unnest(ST_CoverageClean(array_agg(geom ORDER BY id))) AS geom FROM 包裹）更新包裹 p SET geom = c.geom FROM 清洁后的 c WHERE ST_Intersects(p.geom, c.geom); -- 对齐网格以解决精度问题更新功能 SET geom = ST_SnapToGrid(geom, 0.000001) 其中 ST_NPoints(geom) > 1000; -- 高细节特征 ## 性能优化 ### 查询优化 -- 使用 && 进行边界框预过滤从功能中选择 * 哪里 geom && ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326) AND ST_Intersects(geom, query_polygon); -- 简化显示（减少传输大小）选择 id, ST_Simplify(geom, 0.0001) AS geom_display FROM 特征； -- 视口感知简化选择 ID，案例当缩放 < 10 时 ST_Simplify(geom, 0.01) 当缩放 < 14 时 ST_Simplify(geom, 0.001) ELSE 几何 END AS 几何从特征 WHERE geom && viewport_bounds; ### 空间表格设计 -- 将大型表的几何图形与属性分开创建表功能（ id uuid 主键默认 uuidv7(), 名称文本不为空，类别文本，元数据 jsonb DEFAULT '{}', 现在创建的时间戳默认值() ）；创建表 feature_geometry ( feature_id uuid 主键引用功能（id）删除级联，几何几何（几何，4326）， geom_simplified Geometry(Geometry, 4326) -- 预先计算的简化）；使用 gist(geom) 在 feature_geometries 上创建索引 idx_feature_geom；使用要点（geom_simplified）在feature_geometries上创建索引idx_feature_geom_simple； ### 复杂查询的物化视图 -- 预先计算的空间连接创建物化视图 feature_regions AS SELECT f.id AS feature_id、r.id ASregion_id、r.name ASregion_name FROM 特征 f JOIN 区域 r ON ST_Within(f.location, r.boundary); 在 feature_regions(feature_id) 上创建唯一索引 idx_feature_regions; -- 定期刷新同时刷新物化视图feature_regions； ## 迁移模式 ### 添加空间列 -- 第 1 步：添加列更改表位置添加列几何几何图形（点，4326）； -- 第2步：创建索引使用 gist(geom) 在位置上同时创建索引 idx_locations_geom; -- 步骤 3：从纬度/经度回填更新地点 SET geom = ST_SetSRID(ST_MakePoint(经度、纬度), 4326) WHERE geom IS NULL AND latitude IS NOT NULL; -- 步骤 4：根据需要添加约束更改表位置添加约束locations_geom_4326 检查（ST_SRID（geom）= 4326）； ### 将几何图形转换为地理图形 -- 创建新列更改表位置添加列 location_geo 地理（点，4326）； -- 迁移数据更新地点 SET location_geo = 位置::地理其中 location_geo 为 NULL； -- 在新列上创建索引使用 gist(location_geo) 在位置上同时创建索引 idx_locations_geo; -- 更新应用程序，然后删除旧列 ALTER TABLE 位置 DROP COLUMN 位置；更改表位置将列 location_geo 重命名为位置； ## PostGIS 神器实现空间功能时，发布此工件： ## PostGIS 实施总结 ### 空间列 |表|专栏 |类型 | SRID |索引 | |--------|--------|------|------|--------| |地点 |地点 |地理（点） | 4326 |要点 | |包裹 |边界|几何（多边形）| 4326 |要点 | ### PostGIS 3.6.1 使用的功能 - [ ] ST_CoverageClean 用于拓扑修复 - [ ] SFCGAL 3D 功能 - [ ] Bigint 拓扑 - [ ] PostgreSQL 18 中断处理 ### 空间查询 |查询模式 |使用索引 |性能| |----------------|------------|-------------| | KNN 距离 |是（要点）| <10毫秒| | ST_区域内 |是（要点）| <50ms | | ST_相交 |是（要点）| <100 毫秒 | ### 验证 - [ ] 所有几何图形均通过 ST_IsValid - [ ] 强制执行 SRID 约束 - [ ] 创建空间索引 - [ ] 使用 EXPLAIN ANALYZE 测试的查询模式 **PostGIS版本：** 3.6.1 **GEOS 版本：** 3.14.x **验证于：** [时间戳] ## 清单在完成 PostGIS 实施之前： * 选择正确的数据类型（几何与地理） * SRID一致（建议存储4326） * 在所有几何列上创建空间索引 * 输入几何图形已验证 (ST_IsValid) * GeoJSON 导入/导出测试 * 使用 EXPLAIN ANALYZE 验证查询性能 * 在适当的情况下利用 PostGIS 3.6.1 功能 * 工件发布到问题 ## 整合该技能与： * `database-architecture` \- 空间列遵循一般模式模式 * `postgres-rls` \- RLS 策略可以使用空间谓词 * `timescaledb` \- 具有空间维度的时间序列 ## 参考文献 * [PostGIS 3.6.1 发行说明]() * [PostGIS 文档]() * [GEOS 3.14 变更日志]() * [SFCGAL 文档]()

邮政地理信息系统

包含技能