design-postgis-tables

PostGIS 空间表设计

开始之前（5 个问题）

1. 地理范围是什么（单个城市/地区还是全球）？
2. 主要的查询模式是什么（半径范围内、边界框、相交、最近邻）？
3. 距离/面积需要使用什么单位（米还是 CRS 单位），精度要求有多高？
4. 预期规模如何（行数、写入速率），数据是否以追加为主？
5. 是否需要 3D（Z）或度量值（M），还是 2D 就够了？

SQL 注入提示： 将这些模式用于应用代码时，请对用户提供的值（WKT/WKB、坐标、ID、半径）使用参数化查询。避免将不可信输入与 SQL 字符串拼接；对于动态标识符，请使用安全的标识符引号处理或白名单。

核心规则

• 始终使用 PostGIS geometry/geography 类型，而不是 PostgreSQL 内置的几何类型（POINT、LINE、POLYGON、CIRCLE）。PostGIS 类型提供真正的空间功能。
• 根据使用场景选择 GEOMETRY 或 GEOGRAPHY：GEOMETRY 适用于采用笛卡尔运算的投影/本地数据；GEOGRAPHY 适用于需要精确球面计算的全球数据。
• 创建几何列时始终指定 SRID（空间参考标识符）。GPS/全球数据使用 4326（WGS84），区域数据使用适当的本地投影。
• 为所有 geometry/geography 列创建空间索引，使用 GiST（默认）。仅当非常大的 GEOMETRY 表中的行在磁盘上具有自然顺序，且可以接受较粗略的筛选时，才考虑使用 BRIN。
• 使用基于约束的类型强制机制，采用 GEOMETRY(type, SRID) 语法确保数据完整性。

Geometry 与 Geography

何时使用 GEOMETRY

• 本地/区域数据，处于单一坐标系内
• 用于精确面积/距离计算的投影坐标（米、英尺）
• 复杂空间操作（缓冲区、并集、交集）
• 对性能要求较高的查询（笛卡尔运算速度更快）
• 数据已采用投影 CRS（UTM、State Plane 等）

-- Regional data with projected coordinates (UTM Zone 10N for California)
CREATE TABLE local_parcels (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parcel_number TEXT NOT NULL,
    boundary GEOMETRY(POLYGON, 26910),  -- UTM Zone 10N (meters)
    area_sqm DOUBLE PRECISION GENERATED ALWAYS AS (ST_Area(boundary)) STORED
);

何时使用 GEOGRAPHY

• 跨越多个大陆/半球的全球数据
• GPS 坐标（十进制度数表示的纬度/经度）
• 地球表面上的精确距离计算（大圆距离）
• 简单空间操作（距离、包含）
• 来自 GPS 设备、地理编码服务或网络地图的数据

-- Global data with geodetic calculations
CREATE TABLE global_offices (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    city TEXT NOT NULL,
    location GEOGRAPHY(POINT, 4326)  -- WGS84 (lat/lon)
);

-- Distance in meters (accurate spherical calculation)
SELECT
    a.name AS office_a,
    b.name AS office_b,
    ST_Distance(a.location, b.location) / 1000 AS distance_km
FROM global_offices a
CROSS JOIN global_offices b
WHERE a.id < b.id;

对比表

方面	GEOMETRY	GEOGRAPHY
坐标系	任意 SRID（投影或大地坐标）	仅限 WGS84（SRID 4326）
距离单位	CRS 单位（度、米、英尺）	米（始终）
距离精度	取决于投影	真实椭球面距离
面积精度	在投影 CRS 中精确	在球面上精确
函数支持	完整（300 多个函数）	有限（约 40 个函数）
性能	更快（笛卡尔运算）	更慢（球面运算）
索引类型	GiST、BRIN、SP-GiST	仅 GiST
最适用场景	区域/本地数据、复杂分析	全球数据、GPS 跟踪

Geometry 类型

Point 类型

-- Single location (stores, sensors, events)
location GEOMETRY(POINT, 4326)

-- Multiple discrete locations (multi-branch business)
locations GEOMETRY(MULTIPOINT, 4326)

-- 3D point with elevation
location_3d GEOMETRY(POINTZ, 4326)

-- Point with measure value (linear referencing)
location_m GEOMETRY(POINTM, 4326)

使用 POINT 存储： 地点、传感器位置、事件坐标、地址、POI
使用 MULTIPOINT 存储： 作为单个要素存储的多个相关地点

Line 类型

-- Single path (road segment, river, route)
path GEOMETRY(LINESTRING, 4326)

-- Multiple paths (road network, transit lines)
network GEOMETRY(MULTILINESTRING, 4326)

-- 3D line with elevation profile
trail_3d GEOMETRY(LINESTRINGZ, 4326)

使用 LINESTRING 存储： 道路、河流、管道、GPS 轨迹、路线
使用 MULTILINESTRING 存储： 不连续的道路路段、河流系统

Polygon 类型

-- Single area (parcel, building footprint, zone)
boundary GEOMETRY(POLYGON, 4326)

-- Multiple areas (archipelago, fragmented habitat)
territories GEOMETRY(MULTIPOLYGON, 4326)

-- 3D polygon (building with height)
footprint_3d GEOMETRY(POLYGONZ, 4326)

使用 POLYGON 存储： 地块边界、行政区域、服务区
使用 MULTIPOLYGON 存储： 包含岛屿的国家、碎片化区域

通用类型

-- Any geometry type (flexible schema)
geom GEOMETRY(GEOMETRY, 4326)

-- Collection of mixed types
features GEOMETRY(GEOMETRYCOLLECTION, 4326)

适合使用 GEOMETRY 的场景： 接受多种类型的灵活模式 避免使用 GEOMETRYCOLLECTION： 优先使用同质类型，以便获得更好的索引效果

坐标系（SRID）

常见 SRID

SRID	名称	使用场景	单位
4326	WGS84	GPS、全球数据、网络地图	度
3857	Web Mercator	网络地图瓦片（仅用于显示）	米
26910-26919	UTM Zones (US)	区域分析	米
32601-32660	UTM Zones (North)	区域分析	米
32701-32760	UTM Zones (South)	区域分析	米

SRID 最佳实践

• 使用 WGS84（4326）存储，以确保互操作性并支持 GPS 数据
• 转换为投影坐标系，以便精确测量
• 空间操作中绝不混用 SRID，除非进行显式转换
• 面积／距离计算需要高精度时，使用适当的本地坐标系

-- Store in WGS84, calculate in UTM
CREATE TABLE survey_points (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    location GEOMETRY(POINT, 4326),  -- Storage: WGS84
    CONSTRAINT valid_location CHECK (ST_IsValid(location))
);

-- Calculate distance in meters using UTM projection
SELECT
    a.id AS point_a,
    b.id AS point_b,
    ST_Distance(
        ST_Transform(a.location, 26910),  -- Transform to UTM
        ST_Transform(b.location, 26910)
    ) AS distance_meters
FROM survey_points a
CROSS JOIN survey_points b
WHERE a.id < b.id;

空间索引

GiST 索引（默认）

用途最广泛的空间索引。适用于所有 geometry／geography 列。

-- Geometry (most common)
CREATE INDEX idx_your_table_geom_gist ON your_table_name USING GIST (geom);

-- Geography (GiST is the supported option)
CREATE INDEX idx_your_table_geog_gist ON your_table_name USING GIST (geog);

-- Analyze after index creation
VACUUM ANALYZE your_table_name;

支持： 所有空间运算符（&&、@>、<@、~=、<->） 最适合： 通用空间查询、混合查询模式

BRIN 索引

适用于非常大型且自然有序数据集的块范围索引。

-- BRIN for very large, append-only GEOMETRY tables (geography uses GiST)
CREATE INDEX idx_your_table_geom_brin
    ON your_table_name
    USING BRIN (geom)
    WITH (pages_per_range = 128);

支持： 边界框运算符（&&、@>、<@） 最适合： 仅追加表、时序空间数据、超大型数据集（>100M 行） 权衡： 比 GiST 小得多，但过滤精度较低

SP-GiST 索引

适用于具有特定分布的点数据的空间划分 GiST。

-- SP-GiST for GEOMETRY(POINT, ...) only
CREATE INDEX idx_sensors_location_spgist
    ON sensors
    USING SPGIST (location);

最适合： 仅包含点的数据、适合四叉树的分布 不适合： 复杂几何体、混合类型

索引选择指南

场景	索引类型	原因
通用空间查询	GiST	用途最广，支持所有运算符
超大型、仅追加数据	BRIN	占用空间极小，适合按时间排序的数据
仅包含点、均匀分布	SP-GiST	点查找效率高
geography 列	GiST	唯一支持的选项
空间与属性组合	GiST + B-tree	使用独立索引或表达式索引

表设计示例

兴趣点（POI）

CREATE TABLE pois (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name TEXT NOT NULL,
    category TEXT NOT NULL,
    location GEOGRAPHY(POINT, 4326) NOT NULL,
    address TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    CONSTRAINT valid_category CHECK (category IN (
        'restaurant', 'hotel', 'gas_station', 'hospital', 'school'
    ))
);

-- Spatial index
CREATE INDEX idx_pois_location ON pois USING GIST (location);

-- Category + location for filtered spatial queries
CREATE INDEX idx_pois_category ON pois (category);

-- Find restaurants within 1km
SELECT name, address,
       ST_Distance(
         location,
         ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::GEOGRAPHY
       ) AS distance_m
FROM pois
WHERE category = 'restaurant'
  AND ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326)::GEOGRAPHY,
    1000
  )
ORDER BY distance_m;

地块

CREATE TABLE parcels (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    parcel_id TEXT NOT NULL UNIQUE,
    owner_name TEXT,
    boundary GEOMETRY(MULTIPOLYGON, 4326) NOT NULL,
    centroid GEOMETRY(POINT, 4326) GENERATED ALWAYS AS (ST_Centroid(boundary)) STORED,
    area_sqm DOUBLE PRECISION GENERATED ALWAYS AS (
        ST_Area(boundary::GEOGRAPHY)
    ) STORED,
    perimeter_m DOUBLE PRECISION GENERATED ALWAYS AS (
        ST_Perimeter(boundary::GEOGRAPHY)
    ) STORED,
    CONSTRAINT valid_boundary CHECK (ST_IsValid(boundary)),
    CONSTRAINT closed_boundary CHECK (ST_IsClosed(ST_ExteriorRing(ST_GeometryN(boundary, 1))))
);

CREATE INDEX idx_parcels_boundary ON parcels USING GIST (boundary);
CREATE INDEX idx_parcels_centroid ON parcels USING GIST (centroid);

-- Find parcels intersecting a search area
SELECT parcel_id, owner_name, area_sqm
FROM parcels
WHERE ST_Intersects(boundary, ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326));

GPS 轨迹追踪

CREATE TABLE gps_tracks (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    device_id TEXT NOT NULL,
    recorded_at TIMESTAMPTZ NOT NULL,
    location GEOGRAPHY(POINT, 4326) NOT NULL,
    speed_kmh DOUBLE PRECISION,
    heading DOUBLE PRECISION,
    accuracy_m DOUBLE PRECISION
);

-- Composite index for device + time queries
CREATE INDEX idx_gps_device_time ON gps_tracks (device_id, recorded_at DESC);

-- Spatial index for location queries
CREATE INDEX idx_gps_location ON gps_tracks USING GIST (location);

-- Note: GEOGRAPHY supports GiST; BRIN is for GEOMETRY (when appropriate).

-- Create linestring from track points
SELECT
    device_id,
    ST_MakeLine(location::GEOMETRY ORDER BY recorded_at) AS track_line,
    MIN(recorded_at) AS start_time,
    MAX(recorded_at) AS end_time
FROM gps_tracks
WHERE device_id = 'device_001'
  AND recorded_at >= '2024-01-01'
GROUP BY device_id;

服务区域／覆盖区

CREATE TABLE service_zones (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    zone_name TEXT NOT NULL,
    zone_type TEXT NOT NULL,
    boundary GEOMETRY(POLYGON, 4326) NOT NULL,
    population INTEGER,
    active BOOLEAN NOT NULL DEFAULT true,
    CONSTRAINT valid_zone_type CHECK (zone_type IN ('delivery', 'service', 'coverage')),
    CONSTRAINT valid_boundary CHECK (ST_IsValid(boundary))
);

CREATE INDEX idx_zones_boundary ON service_zones USING GIST (boundary);
CREATE INDEX idx_zones_active ON service_zones (active) WHERE active = true;

-- Check if location is within any active service zone
SELECT zone_name, zone_type
FROM service_zones
WHERE active = true
  AND ST_Contains(boundary, ST_SetSRID(ST_MakePoint(-122.4194, 37.7749), 4326));

性能模式

使用 ST_DWithin 替代 ST_Distance

-- SLOW: calculates distance for all rows
SELECT * FROM pois
WHERE ST_Distance(location, ref_point) < 1000;

-- FAST: uses spatial index
SELECT * FROM pois
WHERE ST_DWithin(location, ref_point, 1000);

使用 && 进行边界框预筛选

-- Bounding box operator leverages spatial index
SELECT * FROM parcels
WHERE boundary && ST_MakeEnvelope(-122.5, 37.7, -122.4, 37.8, 4326)
  AND ST_Intersects(boundary, search_polygon);

避免在索引列上使用函数

-- SLOW: function prevents index usage
SELECT * FROM parcels WHERE ST_Area(boundary) > 10000;

-- FAST: use generated column with regular index
ALTER TABLE parcels ADD COLUMN area_sqm DOUBLE PRECISION
    GENERATED ALWAYS AS (ST_Area(boundary::GEOGRAPHY)) STORED;
CREATE INDEX idx_parcels_area ON parcels (area_sqm);
SELECT * FROM parcels WHERE area_sqm > 10000;

简化用于显示的几何体

-- Reduce complexity for web display (tolerance in CRS units)
SELECT
    id,
    name,
    ST_AsGeoJSON(ST_Simplify(boundary, 0.0001)) AS geojson
FROM parcels;

使用适当的精度

-- Reduce coordinate precision for storage efficiency
UPDATE locations SET geom = ST_ReducePrecision(geom, 0.000001);

-- GeoJSON with limited decimal places
SELECT ST_AsGeoJSON(location, 6) AS geojson FROM pois;

数据验证

几何有效性检查

-- Add validity constraint
ALTER TABLE parcels ADD CONSTRAINT valid_geom CHECK (ST_IsValid(boundary));

-- Find and fix invalid geometries
SELECT id, ST_IsValidReason(boundary) AS reason
FROM parcels
WHERE NOT ST_IsValid(boundary);

-- Attempt to fix invalid geometries
UPDATE parcels
SET boundary = ST_MakeValid(boundary)
WHERE NOT ST_IsValid(boundary);

SRID 一致性

-- Verify SRID consistency
SELECT DISTINCT ST_SRID(geom) FROM spatial_table;

-- Enforce SRID with constraint
ALTER TABLE locations ADD CONSTRAINT enforce_srid
    CHECK (ST_SRID(location) = 4326);

坐标范围验证

-- Ensure coordinates are within valid WGS84 bounds
ALTER TABLE global_locations ADD CONSTRAINT valid_coords CHECK (
    ST_X(location::GEOMETRY) BETWEEN -180 AND 180 AND
    ST_Y(location::GEOMETRY) BETWEEN -90 AND 90
);

请勿使用

• PostgreSQL 内置类型（POINT、LINE、POLYGON、CIRCLE）——改用 PostGIS 类型
• SRID 0（未定义）——始终指定正确的 SRID
• 使用 ST_Distance 进行筛选——使用 ST_DWithin 执行受索引支持的距离查询
• 在操作中混用 SRID——务必先转换为通用 SRID
• 使用 GEOGRAPHY 进行复杂分析——使用带有适当投影的 GEOMETRY
• 坐标精度过高——GPS 精度约为 3-5 m，6 位小数（0.1 m）已足够

常见陷阱

1. 经度/纬度顺序：PostGIS 使用 (经度, 纬度) = (X, Y)，而不是 (纬度, 经度)
2. GEOGRAPHY 距离单位：始终以米为单位，与显示方式无关
3. 未使用索引：运行 EXPLAIN ANALYZE，验证是否使用了空间索引
4. 转换性能：对于重复查询，请缓存转换后的几何图形
5. 大型几何图形：对于非常复杂的多边形，可考虑使用 ST_Subdivide
6. SQL 注入 / 不安全的动态 SQL：不要将不可信输入拼接到 SQL 中。对值使用参数化查询；对于动态标识符，使用安全引用（quote_ident、format('%I', ...)）或严格的允许列表。