【PostgreSQL数据分析实战：从数据清洗到可视化全流程】6.3 地理信息处理（PostGIS扩展/空间数据查询）

👉 点击关注不迷路
👉 点击关注不迷路
👉 点击关注不迷路

PostgreSQL数据分析实战：地理信息处理（PostGIS扩展/空间数据查询）

1. 地理信息处理与PostGIS概述

1.1 地理信息系统（GIS）的核心价值

地理信息系统（GIS）通过空间数据的采集、存储、分析和可视化，为城市规划、物流管理、环境监测等领域提供决策支持。

• PostgreSQL结合PostGIS扩展，实现了关系型数据库与地理信息系统的深度融合，其核心优势包括：
  • 开源生态：支持跨平台部署，与QGIS、Tableau等工具无缝集成。
  • 空间数据类型：支持点（POINT）、线（LINESTRING）、面（POLYGON）等几何类型，以及地理坐标系（GEOGRAPHY）。
  • 空间索引：通过R-Tree和GiST索引优化空间查询性能。
  • 丰富的函数库：提供空间关系判断（ST_Contains）、距离计算（ST_Distance）、缓冲区分析（ST_Buffer）等200+函数。

1.2 PostGIS的安装与配置

1.2.1 安装PostgreSQL与PostGIS

• 尽量采用方式二安装，postgresql-13-postgis-3.3，这样便于实现、体验相关函数特性。！！！

  # Ubuntu/Debian系统
  sudo apt-get install postgresql postgresql-contrib postgis
  
  # CentOS/RHEL系统
  yum install postgresql-server postgresql-contrib postgis
  
  # 方式一、通过包管理工具升级（适用于 Linux）
  yum install postgis3 
  
  # 方式二、手动升级（适用于已安装的数据库）
  # 例如 PostGIS 3.3 对应 PostgreSQL 13
  sudo apt-get install postgresql-13-postgis-3.3
  

• lsof命令：列出打开文件的工具，也可以用来查看哪些进程打开了哪些端口。

• 可以通过查看系统中是否有 PostgreSQL 的进程来间接判断服务是否运行

  • ps aux | grep postgres

• 异常解决办法： Process: 1463 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

  su postgres
  initdb -D /var/lib/pgsql/data
  
  su root
  
  systemctl status postgresql.service
  systemctl start postgresql.service
  systemctl restart postgresql.service
  

1.2.2 创建空间数据库

-- 创建模板数据库（可选）
CREATE DATABASE template_postgis WITH TEMPLATE = template0 ENCODING = 'UTF8';

-- 启用PostGIS扩展
CREATE EXTENSION postgis;

-- 创建空间数据表
CREATE TABLE restaurants (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY(POINT, 4326)
);

-- 生成 100 条测试数据并插入到 restaurants 表
DO $$
DECLARE
    i INTEGER;
    lat FLOAT;
    lon FLOAT;
BEGIN
    FOR i IN 1..100 LOOP
        -- 随机生成纬度，范围假设为 30 到 40 度
        lat := random() * (40 - 30) + 30;
        -- 随机生成经度，范围假设为 110 到 120 度
        lon := random() * (120 - 110) + 110;
        INSERT INTO restaurants (name, geom)
        VALUES (
            'Restaurant_' || i,
            ST_SetSRID(ST_MakePoint(lon, lat), 4326)
        );
    END LOOP;
END $$;

2. 空间数据类型与空间索引

2.1 空间数据类型

2.2 空间索引优化

2.2.1 创建空间索引

-- 创建GiST索引
CREATE INDEX idx_restaurants_geom ON restaurants USING GIST(geom);

-- 创建R-Tree索引（PostgreSQL 13+）
CREATE INDEX idx_restaurants_geom_rtree ON restaurants USING RTREE(geom);

2.2.2 索引维护

-- 分析表统计信息
ANALYZE restaurants;

-- 清理无效空间
VACUUM ANALYZE restaurants;

3. 空间数据查询与分析

3.1 空间关系查询

3.2 空间分析函数

-- 计算两个点之间的距离（单位：米）
SELECT ST_Distance(
    ST_GeographyFromText('POINT(116.3975 39.9085)'),
    ST_GeographyFromText('POINT(116.4075 39.9085)')
) AS distance;

-- 生成缓冲区（半径1公里）
SELECT ST_Buffer(geom, 1000) AS buffer FROM restaurants;

-- 空间聚合：统计区域内的餐厅数量
SELECT COUNT(*) AS count FROM restaurants
WHERE ST_Within(geom, ST_GeomFromText('POLYGON((116.3 39.9, 116.4 39.9, 116.4 40.0, 116.3 40.0, 116.3 39.9))'));

4. 业务场景建模实战

4.1 电子围栏分析

4.1.1 数据准备

-- 创建电子围栏表
CREATE TABLE fences (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom GEOMETRY(POLYGON, 4326)
);

-- 导入Shapefile数据（使用shp2pgsql工具）
shp2pgsql -s 4326 fences.shp public.fences | psql -U postgres -d postgres

4.1.2 实时监控

-- 查询进入围栏的车辆
SELECT vehicles.* FROM vehicles
JOIN fences 
ON ST_Intersects(vehicles.geom, fences.geom)
WHERE fences.name = '学校区域';

• ST_Intersects
  • PostGIS（PostgreSQL 的一个扩展，用于支持地理空间数据处理）中的一个非常重要的空间函数，用于判断两个几何对象是否相交。
  • 函数在处理地理空间数据时非常有用，例如查找位于某个区域内的所有点、线或者面，或者判断两条路径是否相交等。

4.2 物流路径优化

4.2.1 最短路径计算

-- 计算从A到B的最短路径
SELECT * FROM pgr_dijkstra(
    'SELECT id, source, target, length FROM roads',
    1, 100,
    directed := false
);

• pgr_dijkstra
  • pgRouting 扩展为 PostgreSQL 数据库提供的一个函数，用于在图中计算最短路径。
  • pgRouting 基于 PostGIS，能够为地理空间数据提供强大的路由功能
  • pgr_dijkstra 函数实现了 Dijkstra 算法，这是一种经典的用于计算加权图中单个源节点到所有其他节点最短路径的算法。

5. 性能优化与最佳实践

5.1 硬件与配置优化

5.2 查询优化策略

• 1. 使用空间索引：避免全表扫描，提升查询速度。
• 2. 限制返回结果：使用LIMIT和OFFSET减少数据量。
• 3. 预处理数据：将常用查询结果物化到临时表。
• 4. 并行查询：设置max_parallel_workers_per_gather参数启用并行扫描。

6. 可视化与工具集成

6.1 与QGIS集成

• 1. 连接PostGIS数据库：在QGIS中选择“PostGIS”连接，输入数据库信息。
• 2. 加载空间图层：将表中的几何字段拖放到地图窗口。
• 3. 空间分析：使用QGIS的空间分析插件进行缓冲区、叠加分析。

6.2 在线地图服务

• 检查 PostGIS 版本

  SELECT postgis_full_version();
  

  • 如果版本 < 3.0：需要升级 PostGIS 到 3.0+。
  • 如果版本 ≥ 3.0：检查是否正确启用了 PostGIS 扩展（确保执行了 CREATE EXTENSION postgis;）。
    

  # 方式一、通过包管理工具升级（适用于 Linux）
  yum install postgis3 
  
  # 方式二、手动升级（适用于已安装的数据库）
  # 例如 PostGIS 3.3 对应 PostgreSQL 13
  sudo apt-get install postgresql-13-postgis-3.3
  

# 使用PG_Tileserv生成地图瓦片
from flask import Flask, Response
import psycopg2

app = Flask(__name__)

@app.route('/map/<int:z>/<int:x>/<int:y>')
def map_tile(z, x, y):
    conn = psycopg2.connect("dbname=gis user=postgres password=secret")
    cur = conn.cursor()
    cur.execute(f"SELECT ST_AsMVT(tile) FROM (SELECT * FROM restaurants WHERE ST_Intersects(geom, ST_TileEnvelope({z}, {x}, {y}))) AS tile;")
    tile = cur.fetchone()[0]
    conn.close()
    return Response(tile, mimetype='application/vnd.mapbox-vector-tile')

if __name__ == '__main__':
    app.run()

• ST_AsMVT
  • 用于将 PostgreSQL 中的地理数据转换为 Mapbox 矢量瓦片（MVT）格式
  • Mapbox 矢量瓦片是一种轻量级、可缩放的地理数据格式，适合在 Web 地图应用中高效传输和显示地理数据。该函数可以将查询结果中的几何对象和属性数据打包成一个或多个 MVT 瓦片。
• ST_TileEnvelope
  • 用于根据给定的瓦片坐标（z、x、y）生成对应瓦片的地理范围（即边界框）。
  • 在基于瓦片的地图系统中，每个瓦片都有一个唯一的坐标，通过这个函数可以确定该瓦片覆盖的地理区域。
• ST_Intersects
  • 用于判断两个几何对象在空间上是否相交。
  • 如果两个几何对象有至少一个公共点，函数返回 true；否则返回 false。
  • 该函数在地理空间分析中非常常用，例如查找位于某个区域内的所有地理要素。
    
    

7. 总结与扩展

PostGIS为PostgreSQL赋予了强大的地理信息处理能力，通过空间数据类型、索引和函数库，实现了从数据存储到复杂分析的全流程支持。

• 在实际应用中，需结合业务场景选择合适的空间数据类型和索引策略，并通过硬件优化和查询调优提升性能。
• 未来可进一步探索时空数据处理（如PostGIS时态扩展）和高级分析（如空间统计、机器学习集成）。