PostgreSQL数据库中​​几何类型​功能介绍及使用演示

以下是对 PostgreSQL 中几何类型功能的完整介绍及详细使用演示，包含从基础操作到高级分析的完整示例，所有代码均可直接执行：

​​一、PostGIS 几何类型深度解析​​

1. ​​几何类型全览​​

2. ​​核心功能矩阵​​

​​二、完整使用演示​​

1. ​​环境准备​​

sql

-- 启用PostGIS扩展
CREATE EXTENSION IF NOT EXISTS postgis;

-- 创建测试数据库（可选）
CREATE DATABASE gis_demo WITH TEMPLATE template_postgis;
\c gis_demo  -- 切换到新数据库

2. ​​创建完整示例表​​

sql

-- 城市地标表（包含点、线、面混合数据）
CREATE TABLE landmarks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(20) CHECK (type IN ('monument', 'river', 'park')),
    geom GEOMETRY,  -- 通用几何字段
    area_sqkm NUMERIC,  -- 面积（自动计算）
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 空间索引
CREATE INDEX landmarks_geom_idx ON landmarks USING GIST(geom);

3. ​​插入多类型数据​​

sql

-- 插入点数据（天安门）
INSERT INTO landmarks (name, type, geom)
VALUES (
    'Tiananmen Square',
    'monument',
    ST_SetSRID(ST_Point(116.404, 39.915), 4326)
);

-- 插入线数据（长江主干）
INSERT INTO landmarks (name, type, geom)
VALUES (
    'Yangtze River',
    'river',
    ST_SetSRID(
        ST_GeomFromText('LINESTRING(121.5 31.2, 117.2 30.3, 114.3 30.6, 111.0 30.7)'),
        4326
    )
);

-- 插入多边形数据（颐和园）
INSERT INTO landmarks (name, type, geom, area_sqkm)
VALUES (
    'Summer Palace',
    'park',
    ST_SetSRID(
        ST_GeomFromText('POLYGON((116.26 39.99, 116.27 39.99, 116.27 40.00, 116.26 40.00, 116.26 39.99))'),
        4326
    ),
    ST_Area(
        ST_Transform(
            ST_SetSRID(
                ST_GeomFromText('POLYGON((116.26 39.99, 116.27 39.99, 116.27 40.00, 116.26 40.00, 116.26 39.99))'),
                4326
            ),
            3857  -- Web墨卡托投影（单位：平方米）
        )
    ) / 1000000  -- 转换为平方公里
);

4. ​​空间查询实战​​

​​基础查询​​

sql

-- 查询所有公园的几何类型和面积
SELECT 
    name,
    GeometryType(geom) AS geom_type,
    area_sqkm
FROM landmarks 
WHERE type = 'park';

​​空间关系查询​​

sql

-- 查找距离天安门5公里内的地标（使用GEOGRAPHY类型精确计算）
SELECT 
    l.name,
    ST_Distance(
        ST_SetSRID(ST_Point(116.404, 39.915), 4326)::geography,
        l.geom::geography
    ) AS distance_meters
FROM landmarks l
WHERE 
    ST_DWithin(
        ST_SetSRID(ST_Point(116.404, 39.915), 4326)::geography,
        l.geom::geography,
        5000  -- 5公里
    )
    AND l.id != 1;  -- 排除天安门本身

​​几何操作​​

sql

-- 为长江创建1公里缓冲区
SELECT 
    ST_AsText(
        ST_Buffer(
            (SELECT geom FROM landmarks WHERE name = 'Yangtze River')::geography,
            1000  -- 1公里
        )::geometry
    ) AS river_buffer;

-- 计算颐和园与假设开发区域的重叠面积
WITH dev_zone AS (
    SELECT ST_SetSRID(
        ST_GeomFromText('POLYGON((116.25 39.98, 116.28 39.98, 116.28 40.01, 116.25 40.01, 116.25 39.98))'),
        4326
    ) AS geom
)
SELECT 
    ST_Area(
        ST_Intersection(
            (SELECT geom FROM landmarks WHERE name = 'Summer Palace'),
            (SELECT geom FROM dev_zone)
        )::geography
    ) AS overlap_area_sqmeters;

5. ​​高级分析示例​​

​​动态生成几何网格​​

sql

-- 在北京范围内生成1km×1km的网格
WITH bj_bounds AS (
    SELECT ST_SetSRID(
        ST_GeomFromText('POLYGON((115.7 39.4, 117.4 39.4, 117.4 41.1, 115.7 41.1, 115.7 39.4))'),
        4326
    ) AS geom
)
SELECT 
    (ST_PixelAsPolygons(
        ST_AsRaster(
            (SELECT geom FROM bj_bounds),
            1000,  -- 像素宽度（米）
            1000,  -- 像素高度
            '8BUI',  -- 像素类型
            1,      -- 初始值
            0       -- 背景值
        )
    )).geom AS grid_cell
FROM bj_bounds
LIMIT 10;  -- 显示前10个网格

​​空间聚合分析​​

sql

-- 统计各类型地标的空间分布密度
SELECT 
    type,
    COUNT(*) AS count,
    SUM(ST_Area(geom::geography)) / 1000000 AS total_area_sqkm,
    COUNT(*) / NULLIF(SUM(ST_Area(geom::geography)), 0) * 1000000 AS density_per_sqkm
FROM landmarks
GROUP BY type;

​​三、可视化与导出​​

1. ​​生成GeoJSON​​

sql

-- 导出所有地标的GeoJSON
SELECT 
    jsonb_build_object(
        'type', 'FeatureCollection',
        'features', jsonb_agg(
            jsonb_build_object(
                'type', 'Feature',
                'geometry', ST_AsGeoJSON(geom)::jsonb,
                'properties', jsonb_build_object(
                    'name', name,
                    'type', type
                )
            )
        )
    ) AS geojson
FROM landmarks;

2. ​​QGIS连接步骤​​

1. 在QGIS中添加PostgreSQL连接
2. 选择 landmarks 表
3. 设置几何字段为 geom
4. 设置SRID为4326（WGS84）

​​四、性能优化技巧​​

1. ​​使用覆盖索引​​：

   sql

   CREATE INDEX landmarks_geom_covering_idx ON landmarks 
   USING GIST(geom) INCLUDE (name, type);

2. ​​分区表​​：

   sql

   CREATE TABLE landmarks_partitioned (
       LIKE landmarks INCLUDING INDEXES
   ) PARTITION BY RANGE (created_at);

3. ​​空间聚类​​：

   sql

   CLUSTER landmarks USING landmarks_geom_idx;

​​总结​​

通过本示例，您已经掌握：

1. 多类型几何数据的存储方法
2. 空间关系判断与度量计算
3. 复杂几何操作（缓冲区、交集等）
4. 空间聚合分析与可视化输出

实际应用中，可结合：

• 实时GPS轨迹分析（LINESTRING动态更新）
• 地理围栏报警（ST_Contains）
• 城市规划中的叠加分析（ST_Union + ST_Area）