🤖PostGIS 入门笔记

1. PostGIS 简介

PostGIS 是 PostgreSQL 数据库的空间扩展，支持地理对象存储和空间数据分析。

主要特性

• 支持多种空间数据类型（点、线、面等）
• 丰富的空间函数和操作符
• 空间索引支持（GiST、SP-GiST）
• 坐标系统转换
• 拓扑数据处理
• 三维几何支持
• 栅格数据处理

2. 安装和配置

安装 PostGIS

-- 创建数据库后添加 PostGIS 扩展
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

验证安装

SELECT PostGIS_Version();
SELECT PostGIS_Full_Version();

3. 空间数据类型

基本几何类型

-- 点 (Point)
SELECT ST_GeomFromText('POINT(0 0)');

-- 线 (LineString)
SELECT ST_GeomFromText('LINESTRING(0 0, 1 1, 2 1)');

-- 多边形 (Polygon)
SELECT ST_GeomFromText('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))');

-- 多点 (MultiPoint)
SELECT ST_GeomFromText('MULTIPOINT(0 0, 1 1)');

-- 多线 (MultiLineString)
SELECT ST_GeomFromText('MULTILINESTRING((0 0, 1 1), (2 2, 3 3))');

-- 多多边形 (MultiPolygon)
SELECT ST_GeomFromText('MULTIPOLYGON(((0 0, 1 0, 1 1, 0 1, 0 0)))');

地理类型 (Geography)

-- 使用地理坐标（WGS84）
SELECT ST_GeogFromText('POINT(-122.334 47.609)');

4. 创建空间表

基本示例

-- 创建包含空间列的表
CREATE TABLE spatial_table (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    geom GEOMETRY(Point, 4326),
    geog GEOGRAPHY(Point)
);

-- 或者单独添加空间列
SELECT AddGeometryColumn('spatial_table', 'geom', 4326, 'POINT', 2);

带空间索引的表

CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    population INTEGER,
    geom GEOMETRY(Point, 4326)
);

-- 创建空间索引
CREATE INDEX cities_geom_idx ON cities USING GIST (geom);

5. 常用空间函数

几何创建函数

-- 从 WKT 创建几何
SELECT ST_GeomFromText('POINT(1 1)');

-- 从 WKB 创建几何
SELECT ST_GeomFromWKB('\x0101000000000000000000f03f000000000000f03f');

-- 从 GeoJSON 创建几何
SELECT ST_GeomFromGeoJSON('{"type":"Point","coordinates":[1,1]}');

-- 创建点
SELECT ST_MakePoint(1, 2);
SELECT ST_SetSRID(ST_MakePoint(-122.33, 47.61), 4326);

几何访问函数

-- 获取坐标
SELECT ST_X(geom), ST_Y(geom) FROM points_table;

-- 获取边界框
SELECT ST_Envelope(geom);

-- 获取几何类型
SELECT ST_GeometryType(geom);

-- 获取 SRID
SELECT ST_SRID(geom);

空间关系函数

-- 包含关系
SELECT ST_Contains(polygon, point);

-- 相交关系
SELECT ST_Intersects(geom1, geom2);

-- 距离计算
SELECT ST_Distance(
    ST_GeomFromText('POINT(0 0)'),
    ST_GeomFromText('POINT(1 1)')
);

-- 缓冲区
SELECT ST_Buffer(geom, 0.1);

-- 交集
SELECT ST_Intersection(geom1, geom2);

6. 空间查询示例

基础查询

-- 查找所有点
SELECT * FROM cities WHERE ST_GeometryType(geom) = 'ST_Point';

-- 计算几何面积
SELECT name, ST_Area(geom) FROM polygons_table;

距离查询

-- 查找距离某点10公里内的城市
SELECT name, 
       ST_Distance(
           geog, 
           ST_GeogFromText('POINT(-122.33 47.61)')
       ) as distance
FROM cities 
WHERE ST_DWithin(
    geog, 
    ST_GeogFromText('POINT(-122.33 47.61)'), 
    10000  -- 10公里
)
ORDER BY distance;

空间连接

-- 查找每个城市所在的州
SELECT c.name as city_name, s.name as state_name
FROM cities c
JOIN states s ON ST_Within(c.geom, s.geom);

7. 坐标系统

SRID 管理

-- 设置 SRID
SELECT ST_SetSRID(ST_MakePoint(-122.33, 47.61), 4326);

-- 转换坐标系统
SELECT ST_Transform(geom, 3857) FROM spatial_table;

-- 查找可用的 SRID
SELECT * FROM spatial_ref_sys WHERE auth_name = 'EPSG';

8. 空间索引

创建和维护索引

-- 创建 GiST 索引
CREATE INDEX idx_spatial_geom ON spatial_table USING GIST (geom);

-- 更新统计信息
ANALYZE spatial_table;

-- 检查索引使用
EXPLAIN ANALYZE 
SELECT * FROM spatial_table 
WHERE ST_Intersects(geom, ST_MakeEnvelope(0, 0, 1, 1, 4326));

9. 性能优化技巧

查询优化

-- 使用边界框预过滤
SELECT * FROM spatial_table
WHERE geom && ST_MakeEnvelope(min_x, min_y, max_x, max_y, 4326)
AND ST_Contains(geom, point);

-- 限制返回结果数量
SELECT * FROM spatial_table 
WHERE ST_DWithin(geog, target_geog, 1000)
LIMIT 100;

表设计优化

-- 使用分区表
CREATE TABLE spatial_data_2023 PARTITION OF spatial_table 
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- 添加条件索引
CREATE INDEX idx_active_points ON points_table 
USING GIST (geom) WHERE active = true;

10. 高级功能

栅格数据处理

-- 创建栅格表
CREATE TABLE raster_data (
    id SERIAL PRIMARY KEY,
    rast RASTER
);

-- 栅格分析
SELECT ST_Union(rast) FROM raster_data;
SELECT ST_Clip(rast, geometry) FROM raster_data;

三维几何

-- 3D 点
SELECT ST_MakePoint(1, 2, 3);
SELECT ST_3DDistance(geom1, geom2);

拓扑处理

-- 创建拓扑
SELECT CreateTopology('city_topo', 4326);

-- 拓扑分析
SELECT ST_GetFaceGeometry('city_topo', 1);

11. 实用工具函数

数据导入导出

-- 导出为 GeoJSON
SELECT ST_AsGeoJSON(geom) FROM spatial_table;

-- 导出为 KML
SELECT ST_AsKML(geom) FROM spatial_table;

-- 导出为 WKT
SELECT ST_AsText(geom) FROM spatial_table;

几何验证和修复

-- 验证几何
SELECT ST_IsValid(geom), ST_IsValidReason(geom) FROM spatial_table;

-- 修复无效几何
SELECT ST_MakeValid(geom) FROM spatial_table 
WHERE NOT ST_IsValid(geom);

12. 最佳实践

1. 始终指定 SRID
2. 为空间列创建索引
3. 使用适当的数据类型（Geometry vs Geography）
4. 定期维护空间索引
5. 使用空间约束确保数据完整性
6. 考虑使用分区提高大表性能

13. 常见问题解决

性能问题

-- 检查索引使用
EXPLAIN ANALYZE YOUR_QUERY;

-- 更新表统计信息
VACUUM ANALYZE spatial_table;

数据质量问题

-- 查找无效几何
SELECT id, ST_IsValidReason(geom) 
FROM spatial_table 
WHERE NOT ST_IsValid(geom);

-- 简化复杂几何
SELECT ST_Simplify(geom, tolerance) FROM spatial_table;