postgis

PostGIS 3.6.1 空间数据库

概述

PostGIS 3.6.1（搭配 GEOS 3.14）带来了多项重大改进：用于修复拓扑的 ST_CoverageClean、增强的 SFCGAL 3D 操作、对大型数据集的 bigint 拓扑支持，以及改进的 PostgreSQL 18 集成。本技能可确保你正确运用这些功能。

核心原则： 空间数据有其特殊性。通用数据库模式往往不适用于地理数据。

开始时请说明：“我正在应用 postgis，以确保遵循 PostGIS 3.6.1 空间最佳实践。”

适用场景

只要涉及以下任意模式，就必须使用本技能：

模式	示例
**/*geo*	models/geography.ts, geo_utils.py
**/*spatial*	lib/spatial.ts
**/*location*	services/locationService.ts
**/*coordinate*	types/coordinates.ts
**/*polygon*	db/polygons.sql
**/*geometry*	migrations/add_geometry.sql
**/*postgis*	setup/postgis.sql
**/*gis*	utils/gis.ts

或者文件中包含：

-- These patterns trigger this skill
ST_*
geography
geometry
SRID

PostGIS 3.6.1 功能

1. ST_CoverageClean（3.6.1 新增）

Coverage 清理可修复多边形集合中的拓扑错误。需要 GEOS 3.14：

-- Clean a set of polygons that should form a seamless coverage
-- Fixes: overlaps, gaps, edge inconsistencies
SELECT ST_CoverageClean(
  ARRAY[polygon1, polygon2, polygon3]::geometry[]
) AS cleaned_polygons;

-- Use case: Administrative boundaries, parcels, zones
-- Before: Manual repair with ST_MakeValid, ST_SnapToGrid
-- After: Single function handles entire coverage

-- Example: Clean municipal boundaries
WITH boundaries AS (
  SELECT geom FROM municipalities
)
SELECT ST_CoverageClean(array_agg(geom))
FROM boundaries;

适用场景：

• 导入存在拓扑错误的 GIS 数据
• 合并来自不同来源的数据集
• 确保覆盖无缝衔接（无间隙／重叠）
• 地籍／地块数据管理

2. SFCGAL 3D 函数

PostGIS 3.6.1 增强了对 SFCGAL 的支持，可执行 3D 操作：

-- Enable SFCGAL (if not already enabled)
CREATE EXTENSION IF NOT EXISTS postgis_sfcgal;

-- 3D intersection (true 3D, not projection)
SELECT ST_3DIntersection(
  ST_GeomFromText('POLYHEDRALSURFACE Z (...)'),
  ST_GeomFromText('POLYHEDRALSURFACE Z (...)')
);

-- 3D union
SELECT ST_3DUnion(geom1, geom2);

-- 3D area (actual surface area in 3D)
SELECT ST_3DArea(polyhedral_surface);

-- Minkowski sum (for buffer-like operations in 3D)
SELECT ST_MinkowskiSum(geometry1, geometry2);

-- Straight skeleton (for building roofs, etc.)
SELECT ST_StraightSkeleton(polygon);

-- Extrude 2D to 3D
SELECT ST_Extrude(polygon, 0, 0, height);

适用场景：

• 建筑／构筑物建模
• 地下基础设施
• 空域管理
• 3D 地形分析

3. Bigint 拓扑支持

PostGIS 3.6.1 支持使用 bigint 作为拓扑 ID，以处理大型数据集：

-- Create topology with bigint IDs (new in 3.6.1)
SELECT CreateTopology('massive_parcels', 4326, 0.0000001, true);
-- Last parameter: use_bigint = true

-- Supports > 2 billion features per topology
-- Previous limit: ~2 billion (int4 max)

-- Add layer
SELECT AddTopoGeometryColumn('massive_parcels', 'public', 'parcels', 'topogeom', 'POLYGON');

-- TopoGeometry operations work the same
SELECT ST_CreateTopoGeo('massive_parcels', geom);

适用场景：

• 国家／洲际尺度的数据集
• 高分辨率地块数据
• OpenStreetMap 导入
• 任何边数超过 20 亿的拓扑

4. PostgreSQL 18 中断处理

PostGIS 3.6.1 可正确处理 PostgreSQL 18 改进后的查询取消机制：

-- Long-running spatial operations can now be cancelled cleanly
-- No more orphaned locks or corrupted state

-- Example: Cancellable heavy operation
SELECT ST_Union(geom)
FROM very_large_table
GROUP BY region;
-- ^C now works properly

-- COPY operations with PostGIS also respect cancellation
COPY (SELECT id, ST_AsGeoJSON(geom) FROM features) TO '/tmp/export.json';

数据类型

Geometry 与 Geography

-- GEOMETRY: Planar coordinates, any SRID
-- Faster computations, less accurate over large distances
CREATE TABLE places_geometry (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  location geometry(Point, 4326)  -- WGS84
);

-- GEOGRAPHY: Spherical coordinates, always WGS84
-- Accurate distances/areas, slower computations
CREATE TABLE places_geography (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  location geography(Point, 4326)  -- Always WGS84
);

-- When to use GEOMETRY:
-- - Local/city-scale applications
-- - Need complex operations (union, intersection)
-- - Performance critical
-- - Non-earth data (game maps, floor plans)

-- When to use GEOGRAPHY:
-- - Global applications
-- - Distance/area accuracy matters
-- - Simple operations (distance, contains)
-- - User-facing distance calculations

选择 SRID

-- Common SRIDs:
-- 4326: WGS84 (GPS coordinates, web maps)
-- 3857: Web Mercator (tile-based web maps, display only)
-- Local projections for accurate measurements

-- ALWAYS store in 4326 (WGS84) as source of truth
-- Transform for calculations when needed

CREATE TABLE locations (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  name text NOT NULL,
  location geography(Point, 4326),  -- Storage
  location_local geometry(Point)     -- NULL, computed as needed
);

-- Transform for local calculations
SELECT ST_Transform(
  location::geometry,
  32610  -- UTM Zone 10N (California)
) FROM locations WHERE name = 'San Francisco';

索引策略

空间索引

-- GiST index: Default for most spatial queries
CREATE INDEX idx_locations_geom ON locations USING gist(location);

-- BRIN index: For very large, naturally ordered datasets
-- (e.g., GPS tracks ordered by time)
CREATE INDEX idx_tracks_geom ON gps_tracks USING brin(location);

-- SP-GiST: For non-overlapping data (points, IP ranges)
CREATE INDEX idx_points_spgist ON points USING spgist(location);

索引最佳实践

-- Always include spatial index
CREATE TABLE features (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  geom geometry(Polygon, 4326),
  created_at timestamptz DEFAULT now()
);
CREATE INDEX idx_features_geom ON features USING gist(geom);

-- Partial spatial index for active records
CREATE INDEX idx_features_geom_active ON features USING gist(geom)
  WHERE deleted_at IS NULL;

-- Composite index for common query patterns
CREATE INDEX idx_features_type_geom ON features USING gist(geom)
  WHERE feature_type = 'building';

索引聚簇

-- Cluster table by spatial index for range query performance
CLUSTER features USING idx_features_geom;

-- For large tables, recluster periodically
-- Schedule during maintenance window

查询模式

距离查询

-- Find points within distance (geography, in meters)
SELECT * FROM locations
WHERE ST_DWithin(
  location,
  ST_MakePoint(-122.4194, 37.7749)::geography,
  1000  -- 1km radius
);

-- Find points within distance (geometry, in SRID units)
SELECT * FROM locations
WHERE ST_DWithin(
  location,
  ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326),
  0.01  -- ~1km at this latitude (degrees)
);

-- K-nearest neighbors (KNN)
SELECT *, location <-> ST_MakePoint(-122.4194, 37.7749)::geography AS distance
FROM locations
ORDER BY location <-> ST_MakePoint(-122.4194, 37.7749)::geography
LIMIT 10;
-- Uses index for efficient KNN

包含查询

-- Points within polygon
SELECT * FROM points
WHERE ST_Within(location, (
  SELECT boundary FROM regions WHERE name = 'California'
));

-- Polygon contains point
SELECT * FROM regions
WHERE ST_Contains(boundary, ST_MakePoint(-122.4194, 37.7749));

-- Intersects (overlaps in any way)
SELECT * FROM features
WHERE ST_Intersects(geom, query_polygon);

聚合

-- Union all geometries
SELECT ST_Union(geom) FROM parcels WHERE owner = 'City';

-- Collect without merging (faster, preserves individual geometries)
SELECT ST_Collect(geom) FROM parcels WHERE owner = 'City';

-- Extent (bounding box)
SELECT ST_Extent(geom) FROM features;

-- Centroid of all points
SELECT ST_Centroid(ST_Collect(location)) FROM locations;

GeoJSON 集成

导入／导出

-- Geometry to GeoJSON
SELECT ST_AsGeoJSON(location) FROM locations WHERE id = $1;

-- Geometry with properties to Feature
SELECT jsonb_build_object(
  'type', 'Feature',
  'geometry', ST_AsGeoJSON(location)::jsonb,
  'properties', jsonb_build_object(
    'id', id,
    'name', name
  )
) FROM locations WHERE id = $1;

-- FeatureCollection
SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(location)::jsonb,
      'properties', jsonb_build_object('id', id, 'name', name)
    )
  )
) FROM locations;

-- GeoJSON to Geometry
INSERT INTO locations (name, location)
VALUES ('New Place', ST_GeomFromGeoJSON($1));

-- With SRID enforcement
INSERT INTO locations (name, location)
VALUES ('New Place', ST_SetSRID(ST_GeomFromGeoJSON($1), 4326));

API 响应模式

-- Function for API endpoints
CREATE OR REPLACE FUNCTION get_locations_geojson(
  bounds geometry DEFAULT NULL
)
RETURNS jsonb AS $$
SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', COALESCE(jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'id', id,
      'geometry', ST_AsGeoJSON(location, 6)::jsonb,  -- 6 decimal places
      'properties', jsonb_build_object(
        'name', name,
        'created_at', created_at
      )
    )
  ), '[]'::jsonb)
)
FROM locations
WHERE bounds IS NULL OR ST_Intersects(location::geometry, bounds);
$$ LANGUAGE sql STABLE;

验证与修复

验证几何对象

-- Check validity
SELECT id, ST_IsValid(geom), ST_IsValidReason(geom)
FROM features
WHERE NOT ST_IsValid(geom);

-- Common issues:
-- "Self-intersection"
-- "Ring Self-intersection"
-- "Too few points in geometry component"
-- "Hole lies outside shell"

修复几何对象

-- Simple repair (handles most issues)
UPDATE features
SET geom = ST_MakeValid(geom)
WHERE NOT ST_IsValid(geom);

-- Repair with specific strategy
UPDATE features
SET geom = ST_MakeValid(geom, 'method=structure')
WHERE NOT ST_IsValid(geom);

-- Coverage clean for polygon sets (3.6.1)
WITH cleaned AS (
  SELECT unnest(ST_CoverageClean(array_agg(geom ORDER BY id))) AS geom
  FROM parcels
)
UPDATE parcels p
SET geom = c.geom
FROM cleaned c
WHERE ST_Intersects(p.geom, c.geom);

-- Snap to grid for precision issues
UPDATE features
SET geom = ST_SnapToGrid(geom, 0.000001)
WHERE ST_NPoints(geom) > 1000;  -- High-detail features

性能优化

查询优化

-- Use && for bounding box pre-filter
SELECT * FROM features
WHERE geom && ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326)
  AND ST_Intersects(geom, query_polygon);

-- Simplify for display (reduces transfer size)
SELECT id, ST_Simplify(geom, 0.0001) AS geom_display
FROM features;

-- Viewport-aware simplification
SELECT id,
  CASE
    WHEN zoom < 10 THEN ST_Simplify(geom, 0.01)
    WHEN zoom < 14 THEN ST_Simplify(geom, 0.001)
    ELSE geom
  END AS geom
FROM features
WHERE geom && viewport_bounds;

空间数据表设计

-- Separate geometry from attributes for large tables
CREATE TABLE features (
  id uuid PRIMARY KEY DEFAULT uuidv7(),
  name text NOT NULL,
  category text,
  metadata jsonb DEFAULT '{}',
  created_at timestamptz DEFAULT now()
);

CREATE TABLE feature_geometries (
  feature_id uuid PRIMARY KEY REFERENCES features(id) ON DELETE CASCADE,
  geom geometry(Geometry, 4326),
  geom_simplified geometry(Geometry, 4326)  -- Pre-computed simplification
);

CREATE INDEX idx_feature_geom ON feature_geometries USING gist(geom);
CREATE INDEX idx_feature_geom_simple ON feature_geometries USING gist(geom_simplified);

复杂查询的物化视图

-- Pre-computed spatial joins
CREATE MATERIALIZED VIEW feature_regions AS
SELECT f.id AS feature_id, r.id AS region_id, r.name AS region_name
FROM features f
JOIN regions r ON ST_Within(f.location, r.boundary);

CREATE UNIQUE INDEX idx_feature_regions ON feature_regions(feature_id);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY feature_regions;

迁移模式

添加空间列

-- Step 1: Add column
ALTER TABLE locations ADD COLUMN geom geometry(Point, 4326);

-- Step 2: Create index
CREATE INDEX CONCURRENTLY idx_locations_geom ON locations USING gist(geom);

-- Step 3: Backfill from lat/lng
UPDATE locations
SET geom = ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
WHERE geom IS NULL AND latitude IS NOT NULL;

-- Step 4: Add constraint if needed
ALTER TABLE locations ADD CONSTRAINT locations_geom_4326
  CHECK (ST_SRID(geom) = 4326);

将 Geometry 转换为 Geography

-- Create new column
ALTER TABLE locations ADD COLUMN location_geo geography(Point, 4326);

-- Migrate data
UPDATE locations
SET location_geo = location::geography
WHERE location_geo IS NULL;

-- Create index on new column
CREATE INDEX CONCURRENTLY idx_locations_geo ON locations USING gist(location_geo);

-- Update application, then drop old column
ALTER TABLE locations DROP COLUMN location;
ALTER TABLE locations RENAME COLUMN location_geo TO location;

PostGIS 产物

实现空间功能时，请发布此产物：

## PostGIS Implementation Summary

### Spatial Columns

| Table | Column | Type | SRID | Index |
|-------|--------|------|------|-------|
| locations | location | geography(Point) | 4326 | gist |
| parcels | boundary | geometry(Polygon) | 4326 | gist |

### PostGIS 3.6.1 Features Used

- [ ] ST_CoverageClean for topology repair
- [ ] SFCGAL 3D functions
- [ ] Bigint topology
- [ ] PostgreSQL 18 interrupt handling

### Spatial Queries

| Query Pattern | Index Used | Performance |
|---------------|------------|-------------|
| KNN distance | Yes (gist) | <10ms |
| ST_Within region | Yes (gist) | <50ms |
| ST_Intersects | Yes (gist) | <100ms |

### Validation

- [ ] All geometries pass ST_IsValid
- [ ] SRID constraints enforced
- [ ] Spatial indexes created
- [ ] Query patterns tested with EXPLAIN ANALYZE

**PostGIS Version:** 3.6.1
**GEOS Version:** 3.14.x
**Verified At:** [timestamp]

检查清单

完成 PostGIS 实现前，请确认：

• 已选择正确的数据类型（geometry 或 geography）
• SRID 保持一致（建议存储时使用 4326）
• 所有几何列均已创建空间索引
• 已验证输入几何对象（ST_IsValid）
• 已测试 GeoJSON 导入／导出
• 已使用 EXPLAIN ANALYZE 验证查询性能
• 已在适当场景中运用 PostGIS 3.6.1 功能
• 已向 issue 发布产物

集成

本技能与以下内容集成：

• database-architecture - 空间列遵循通用架构模式
• postgres-rls - RLS 策略可使用空间谓词
• timescaledb - 带空间维度的时间序列

参考资料

• PostGIS 3.6.1 发行说明
• PostGIS 文档
• GEOS 3.14 更新日志
• SFCGAL 文档