PostgreSQL PostGIS地理空间扩展

PostGIS安装

1. 安装前准备

在安装PostGIS之前，确保已安装以下依赖：

PostgreSQL数据库（建议版本10或以上）
GEOS库（几何图形引擎）
PROJ库（坐标参考系统转换）
GDAL库（地理空间数据转换）
LibXML2库（XML支持）

2. 包管理器安装

Debian/Ubuntu系统

bash

# 更新包列表
apt-get update

# 安装PostGIS扩展
apt-get install -y postgis postgresql-14-postgis-3

# 安装额外的PostGIS工具
apt-get install -y postgis-doc postgresql-14-postgis-3-scripts

RHEL/CentOS系统

bash

# 安装PostGIS扩展
yum install -y postgis31_14

# 或使用DNF（RHEL 8+）
dnf install -y postgis31_14

macOS系统

bash

# 使用Homebrew安装
brew install postgis

3. 源码编译安装

bash

# 下载PostGIS源码
wget https://download.osgeo.org/postgis/source/postgis-3.3.2.tar.gz

tar -xzf postgis-3.3.2.tar.gz
cd postgis-3.3.2

# 配置编译选项
./configure --with-pgconfig=/usr/bin/pg_config --with-geosconfig=/usr/bin/geos-config --with-projdir=/usr/

# 编译并安装
make
make install

4. 数据库内安装

sql

-- 连接到目标数据库
\c your_database

-- 安装PostGIS核心扩展
CREATE EXTENSION IF NOT EXISTS postgis;

-- 安装PostGIS拓扑扩展（可选）
CREATE EXTENSION IF NOT EXISTS postgis_topology;

-- 安装PostGIS Raster扩展（可选）
CREATE EXTENSION IF NOT EXISTS postgis_raster;

-- 安装PostGIS Tiger地理编码扩展（可选）
CREATE EXTENSION IF NOT EXISTS postgis_tiger_geocoder;
CREATE EXTENSION IF NOT EXISTS fuzzystrmatch;

-- 验证安装
SELECT postgis_version();

空间数据类型

1. 核心空间数据类型

PostGIS提供了丰富的空间数据类型，主要包括：

数据类型	描述	示例
POINT	点，存储x,y坐标	`POINT(116.4074 39.9042)`
LINESTRING	线，由多个点组成	`LINESTRING(1 1, 2 2, 3 3)`
POLYGON	多边形，封闭的区域	`POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))`
MULTIPOINT	多点集合	`MULTIPOINT(1 1, 2 2, 3 3)`
MULTILINESTRING	多线集合	`MULTILINESTRING((1 1, 2 2), (3 3, 4 4))`
MULTIPOLYGON	多多边形集合	`MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)), ((2 2, 2 3, 3 3, 3 2, 2 2)))`
GEOMETRYCOLLECTION	混合几何类型集合	`GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))`
POINTZ	带高程的点	`POINTZ(116.4074 39.9042 50)`
GEOMETRY	通用几何类型	可存储任意空间数据类型

2. 空间数据使用示例

sql

-- 创建带有空间数据类型的表
CREATE TABLE cities (
    id serial PRIMARY KEY,
    name text NOT NULL,
    location geometry(Point, 4326), -- 4326是WGS84坐标系
    population bigint,
    area numeric
);

-- 插入空间数据
INSERT INTO cities (name, location, population) VALUES
('北京', ST_GeomFromText('POINT(116.4074 39.9042)', 4326), 21540000),
('上海', ST_GeomFromText('POINT(121.4737 31.2304)', 4326), 24280000),
('广州', ST_GeomFromText('POINT(113.2644 23.1291)', 4326), 15300000),
('深圳', ST_GeomFromText('POINT(114.0579 22.5431)', 4326), 13440000);

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

空间查询与分析

1. 基础空间查询

sql

-- 查找距离北京1000公里范围内的城市
SELECT 
    name,
    ST_Distance(location, (SELECT location FROM cities WHERE name = '北京')) / 1000 AS distance_km
FROM cities
WHERE 
    name != '北京' AND
    ST_DWithin(location, (SELECT location FROM cities WHERE name = '北京'), 1000000);

-- 查找包含特定点的多边形
SELECT * FROM polygons WHERE ST_Contains(geom, ST_GeomFromText('POINT(116.4 39.9)', 4326));

-- 查找与特定线相交的所有线
SELECT * FROM roads WHERE ST_Intersects(geom, (SELECT geom FROM rivers WHERE name = '黄河'));

2. 空间分析函数

sql

-- 计算多边形面积
SELECT name, ST_Area(geom) AS area_sqm FROM provinces;

-- 计算线长度
SELECT name, ST_Length(geom) AS length_m FROM roads;

-- 计算两个几何对象的距离
SELECT ST_Distance(
    ST_GeomFromText('POINT(116.4074 39.9042)', 4326),
    ST_GeomFromText('POINT(121.4737 31.2304)', 4326)
) / 1000 AS beijing_shanghai_km;

-- 计算几何对象的中心点
SELECT name, ST_Centroid(geom) AS centroid FROM provinces;

坐标参考系统

1. 常用坐标系统

坐标系统代码	名称	用途
4326	WGS84	GPS全球定位系统坐标
3857	Web Mercator	网络地图（如Google Maps、OpenStreetMap）
4490	CGCS2000	中国国家大地坐标系
2416	Beijing 1954	北京54坐标系（旧）
4610	Xian 1980	西安80坐标系（旧）

2. 坐标转换示例

sql

-- 将WGS84坐标转换为Web Mercator
SELECT 
    name,
    ST_Transform(location, 3857) AS web_mercator,
    ST_Transform(location, 4490) AS cgcs2000
FROM cities;

-- 创建表时指定坐标系统
CREATE TABLE roads (
    id serial PRIMARY KEY,
    name text NOT NULL,
    geom geometry(LineString, 4490) -- 使用CGCS2000坐标系
);

PostGIS性能优化

1. 空间索引优化

sql

-- 创建GIST空间索引（推荐）
CREATE INDEX cities_location_idx ON cities USING GIST (location);

-- 为大型表创建部分索引
CREATE INDEX cities_large_location_idx ON cities USING GIST (location)
WHERE population > 1000000;

-- 重建空间索引
REINDEX INDEX cities_location_idx;

-- 查看索引使用情况
EXPLAIN ANALYZE SELECT * FROM cities WHERE ST_DWithin(location, ST_GeomFromText('POINT(116.4 39.9)', 4326), 100000);

2. 查询优化技巧

sql

-- 使用边界框查询加速
SELECT * FROM cities WHERE location && ST_MakeEnvelope(116, 39, 117, 40, 4326);

-- 先使用边界框过滤，再进行精确查询
SELECT * FROM cities 
WHERE location && ST_MakeEnvelope(116, 39, 117, 40, 4326)
AND ST_DWithin(location, ST_GeomFromText('POINT(116.4 39.9)', 4326), 100000);

-- 避免在空间列上使用函数（会导致索引失效）
-- 不推荐：
SELECT * FROM cities WHERE ST_Transform(location, 3857) && ST_MakeEnvelope(...) 
-- 推荐：
SELECT * FROM cities WHERE location && ST_Transform(ST_MakeEnvelope(...), 4326)

3. 配置参数优化

在postgresql.conf文件中添加以下PostGIS相关优化参数：

txt

-- 增加共享缓冲区大小（根据内存调整）
shared_buffers = 4GB

-- 增加工作内存（用于空间查询）
work_mem = 64MB

-- 增加维护工作内存（用于索引创建）
maintenance_work_mem = 1GB

-- 启用并行查询
max_parallel_workers_per_gather = 4

-- 调整随机页面成本（对于SSD存储）
random_page_cost = 1.1

-- 调整有效缓存大小
effective_cache_size = 12GB

PostGIS数据导入导出

1. 使用shp2pgsql导入Shapefile

bash

# 查看shp2pgsql帮助
shp2pgsql --help

# 导入Shapefile到PostgreSQL
# 格式：shp2pgsql [选项] shapefile [schema.]table | psql [连接参数] database

# 示例：导入世界地图数据
shp2pgsql -s 4326 -I -W UTF-8 world.shp public.world | psql -h localhost -p 5432 -U postgres -d geodb

# 选项说明：
# -s 4326：指定坐标系为WGS84
# -I：创建空间索引
# -W UTF-8：指定编码为UTF-8

2. 使用pgsql2shp导出Shapefile

bash

# 导出表为Shapefile
pgsql2shp -f output.shp -h localhost -p 5432 -U postgres -d geodb public.cities

# 导出查询结果为Shapefile
pgsql2shp -f large_cities.shp -h localhost -p 5432 -U postgres -d geodb "SELECT * FROM cities WHERE population > 1000000"

3. 使用ogr2ogr导入导出

bash

# 从GeoJSON导入到PostgreSQL
ogr2ogr -f "PostgreSQL" PG:"dbname=geodb user=postgres host=localhost password=123456" cities.geojson -nln cities -nlt POINT -s_srs EPSG:4326 -t_srs EPSG:4326

# 从PostgreSQL导出到GeoJSON
ogr2ogr -f GeoJSON cities.geojson PG:"dbname=geodb user=postgres host=localhost password=123456" -sql "SELECT name, location FROM cities"

PostGIS常用管理命令

sql

-- 查看PostGIS版本
SELECT postgis_version();
SELECT version();

-- 查看空间表列表
SELECT f_table_name, f_geometry_column, type, srid 
FROM geometry_columns;

-- 查看空间索引
SELECT indexname, indexdef 
FROM pg_indexes 
WHERE indexdef LIKE '%gist%' OR indexdef LIKE '%spgist%';

-- 检查空间数据完整性
SELECT * FROM cities WHERE NOT ST_IsValid(location);

-- 修复无效的空间数据
UPDATE cities SET location = ST_MakeValid(location) WHERE NOT ST_IsValid(location);

-- 计算表中空间数据的总大小
SELECT pg_size_pretty(pg_total_relation_size('cities'));

PostGIS备份与恢复

1. 空间数据库备份

bash

# 使用pg_dump备份包含PostGIS的数据库
pg_dump -h localhost -p 5432 -U postgres -d geodb -f geodb_backup.sql

# 压缩备份
pg_dump -h localhost -p 5432 -U postgres -d geodb | gzip > geodb_backup.sql.gz

2. 空间数据库恢复

bash

# 恢复备份到新数据库
createdb -h localhost -p 5432 -U postgres new_geodb
psql -h localhost -p 5432 -U postgres -d new_geodb -f geodb_backup.sql

# 从压缩备份恢复
gunzip -c geodb_backup.sql.gz | psql -h localhost -p 5432 -U postgres -d new_geodb

3. 备份注意事项

确保备份包含PostGIS扩展元数据
恢复时先创建数据库，再恢复数据
对于大型空间数据库，考虑使用pg_dumpall或增量备份
定期验证备份的完整性

常见问题（FAQ）

Q1：如何解决"type "geometry" does not exist"错误？

A1：这是因为PostGIS扩展未正确安装。请确保：

已安装PostGIS扩展包
在目标数据库中执行了CREATE EXTENSION postgis;
检查扩展是否安装成功：SELECT postgis_version();

Q2：如何提高空间查询性能？

A2：

为空间列创建GIST索引
使用边界框查询（&&操作符）加速
调整work_mem和shared_buffers参数
对于大型表，考虑分区表
避免在空间列上使用函数
使用适当的坐标系统

Q3：如何处理不同坐标系的数据？

A3：

确定数据的原始坐标系
使用ST_Transform()函数进行坐标转换
在应用程序中统一使用同一坐标系
建议在存储时使用WGS84（4326），查询时根据需要转换

Q4：如何修复无效的空间数据？

A4：

sql

-- 查找无效数据
SELECT * FROM table_name WHERE NOT ST_IsValid(geom_column);

-- 修复无效数据
UPDATE table_name SET geom_column = ST_MakeValid(geom_column) WHERE NOT ST_IsValid(geom_column);

-- 检查修复结果
SELECT * FROM table_name WHERE NOT ST_IsValid(geom_column);

Q5：如何优化PostGIS存储空间？

A5：

使用合适的数据类型，避免过度存储精度
对于大型数据集，考虑使用PostGIS的压缩功能
定期运行VACUUM ANALYZE清理碎片
对于历史数据，考虑归档或分区存储
使用DROP TABLE或TRUNCATE清理不再需要的数据

Q6：如何在应用程序中使用PostGIS？

A6：

使用ORM框架的空间扩展（如Django的GeoDjango、Ruby on Rails的rgeo）
直接在SQL查询中使用空间函数
将空间数据转换为GeoJSON或其他格式返回给前端
使用地图库（如Leaflet、OpenLayers）在前端展示空间数据

Q7：如何升级PostGIS扩展？

A7：

sql

-- 查看当前PostGIS版本
SELECT postgis_version();

-- 升级PostGIS扩展
ALTER EXTENSION postgis UPDATE;

-- 升级所有相关扩展
ALTER EXTENSION postgis_topology UPDATE;
ALTER EXTENSION postgis_raster UPDATE;

-- 验证升级结果
SELECT postgis_version();

Q8：如何监控PostGIS性能？

A8：

使用pg_stat_statements监控慢查询
查看PostgreSQL日志中的慢查询
使用EXPLAIN ANALYZE分析查询计划
监控索引使用情况
查看共享缓冲区命中率
使用pgBadger生成性能报告

PostgreSQL PostGIS地理空间扩展 ​

PostGIS安装 ​

1. 安装前准备 ​

2. 包管理器安装 ​

Debian/Ubuntu系统 ​

RHEL/CentOS系统 ​

macOS系统 ​

3. 源码编译安装 ​

4. 数据库内安装 ​

空间数据类型 ​

1. 核心空间数据类型 ​

2. 空间数据使用示例 ​

空间查询与分析 ​

1. 基础空间查询 ​

2. 空间分析函数 ​

坐标参考系统 ​

1. 常用坐标系统 ​

2. 坐标转换示例 ​

PostGIS性能优化 ​

1. 空间索引优化 ​

2. 查询优化技巧 ​

3. 配置参数优化 ​

PostGIS数据导入导出 ​

1. 使用shp2pgsql导入Shapefile ​

2. 使用pgsql2shp导出Shapefile ​

3. 使用ogr2ogr导入导出 ​

PostGIS常用管理命令 ​

PostGIS备份与恢复 ​

1. 空间数据库备份 ​

2. 空间数据库恢复 ​

3. 备份注意事项 ​

常见问题（FAQ） ​

Q1：如何解决"type "geometry" does not exist"错误？ ​

Q2：如何提高空间查询性能？ ​

Q3：如何处理不同坐标系的数据？ ​

Q4：如何修复无效的空间数据？ ​

Q5：如何优化PostGIS存储空间？ ​

Q6：如何在应用程序中使用PostGIS？ ​

Q7：如何升级PostGIS扩展？ ​

Q8：如何监控PostGIS性能？ ​

PostgreSQL PostGIS地理空间扩展

PostGIS安装

1. 安装前准备

2. 包管理器安装

Debian/Ubuntu系统

RHEL/CentOS系统

macOS系统

3. 源码编译安装

4. 数据库内安装

空间数据类型

1. 核心空间数据类型

2. 空间数据使用示例

空间查询与分析

1. 基础空间查询

2. 空间分析函数

坐标参考系统

1. 常用坐标系统

2. 坐标转换示例

PostGIS性能优化

1. 空间索引优化

2. 查询优化技巧

3. 配置参数优化

PostGIS数据导入导出

1. 使用shp2pgsql导入Shapefile

2. 使用pgsql2shp导出Shapefile

3. 使用ogr2ogr导入导出

PostGIS常用管理命令

PostGIS备份与恢复

1. 空间数据库备份

2. 空间数据库恢复

3. 备份注意事项

常见问题（FAQ）

Q1：如何解决"type "geometry" does not exist"错误？

Q2：如何提高空间查询性能？

Q3：如何处理不同坐标系的数据？

Q4：如何修复无效的空间数据？

Q5：如何优化PostGIS存储空间？

Q6：如何在应用程序中使用PostGIS？

Q7：如何升级PostGIS扩展？

Q8：如何监控PostGIS性能？