PostGIS 空间数据库扩展详解

-- 创建 GiST 空间索引
CREATE INDEX idx_geom_gist ON your_table USING GIST (geom);

-- 创建 SP-GiST 空间索引（适用于点数据）
CREATE INDEX idx_points_spgist ON points_table USING SPGIST (geom);

-- 创建 BRIN 空间索引（适用于大型有序数据集）
CREATE INDEX idx_large_table_brin ON large_table USING BRIN (geom);

-- 创建包含空间数据的表
CREATE TABLE cities (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    location GEOMETRY(POINT, 4326)  -- SRID 4326 表示 WGS84 坐标系统
);

-- 插入空间数据
INSERT INTO cities (name, location) VALUES 
('北京', ST_GeomFromText('POINT(116.4074 39.9042)', 4326)),
('上海', ST_GeomFromText('POINT(121.4737 31.2304)', 4326));

-- 创建包含栅格数据的表
CREATE TABLE elevation_data (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    rast RASTER
);

-- 插入栅格数据
INSERT INTO elevation_data (name, rast) VALUES 
('北京高程数据', ST_FromGDALRaster('/path/to/beijing_elevation.tif'));

-- 空间关系查询示例
-- 查找包含在指定多边形内的所有点
SELECT * FROM points
WHERE ST_Within(geom, ST_GeomFromText('POLYGON((0 0, 10 0, 10 10, 0 10, 0 0))', 4326));

-- 空间测量示例
-- 计算两个城市之间的距离（单位：公里）
SELECT 
    c1.name AS city1,
    c2.name AS city2,
    ST_Distance(c1.location::geography, c2.location::geography) / 1000 AS distance_km
FROM cities c1, cities c2
WHERE c1.id < c2.id;

-- 空间处理示例
-- 创建河流500米缓冲区内的保护区
SELECT 
    river.name,
    ST_Buffer(river.geom, 500) AS protection_zone
FROM rivers
WHERE river.name = '长江';

-- 栅格数据处理示例
-- 计算栅格数据的统计信息
SELECT 
    (ST_SummaryStats(rast)).* 
FROM elevation_data
WHERE name = '北京高程数据';

-- 栅格裁剪示例
SELECT 
    ST_Clip(rast, geom) AS clipped_raster
FROM elevation_data, 
     ST_GeomFromText('POLYGON((116.3 39.8, 116.5 39.8, 116.5 40.0, 116.3 40.0, 116.3 39.8))', 4326) AS geom
WHERE elevation_data.name = '北京高程数据';

-- 矢量-栅格交互示例
-- 提取多边形区域内的栅格值
SELECT 
    (ST_Value(rast, geom)).* 
FROM elevation_data, 
     ST_GeomFromText('POINT(116.4074 39.9042)', 4326) AS geom
WHERE elevation_data.name = '北京高程数据';

-- 创建拓扑
SELECT topology.CreateTopology('beijing_topo', 4326);

-- 将几何数据添加到拓扑
SELECT topology.AddTopoGeometryColumn('beijing_topo', 'public', 'districts', 'topo_geom', 'POLYGON');

-- 验证拓扑
SELECT * FROM 
    topology.ValidateTopology('beijing_topo');

-- 拓扑查询示例
-- 查找与指定面相邻的所有面
SELECT d.district_name, a.district_name AS adjacent_district
FROM districts d, districts a
WHERE topology.GetAdjacentEdges('beijing_topo', d.topo_geom) && a.topo_geom
AND d.district_id != a.district_id;

-- 创建 3D 几何数据
CREATE TABLE buildings_3d (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    height FLOAT,
    geom GEOMETRY(POLYGONZ, 4326)
);

-- 插入 3D 建筑数据
INSERT INTO buildings_3d (name, height, geom) VALUES 
('国贸大厦', 330, ST_GeomFromText('POLYGONZ((116.45 39.92 0, 116.46 39.92 0, 116.46 39.93 0, 116.45 39.93 0, 116.45 39.92 0), (116.45 39.92 330, 116.46 39.92 330, 116.46 39.93 330, 116.45 39.93 330, 116.45 39.92 330))', 4326));

-- 3D 空间分析示例
-- 计算 3D 建筑之间的距离
SELECT 
    b1.name AS building1,
    b2.name AS building2,
    ST_3DDistance(b1.geom, b2.geom) AS distance_3d
FROM buildings_3d b1, buildings_3d b2
WHERE b1.id < b2.id;

-- 土地利用分析示例
-- 计算不同土地利用类型的面积
SELECT 
    land_use_type,
    SUM(ST_Area(geom::geography)) / 1000000 AS area_sq_km
FROM land_use
GROUP BY land_use_type
ORDER BY area_sq_km DESC;

-- 城市规划示例
-- 查找规划区域内的影响建筑
SELECT 
    b.name,
    b.address,
    ST_Distance(b.geom, p.geom::geography) AS distance_m
FROM buildings b, planning_areas p
WHERE p.name = '中央商务区'
AND ST_DWithin(b.geom, p.geom, 500)  -- 500米范围内
ORDER BY distance_m;

-- 位置搜索示例
-- 查找指定位置附近的餐厅
SELECT 
    name,
    address,
    ST_Distance(location, ST_GeomFromText('POINT(116.4074 39.9042)', 4326)::geography) AS distance_m
FROM restaurants
WHERE ST_DWithin(location, ST_GeomFromText('POINT(116.4074 39.9042)', 4326), 0.01)  -- 约1公里范围内
ORDER BY distance_m
LIMIT 10;

-- 地理围栏示例
-- 检测车辆是否进入指定区域
SELECT 
    v.vehicle_id,
    v.plate_number,
    CASE 
        WHEN ST_Contains(f.geom, v.location) THEN '在围栏内'
        ELSE '在围栏外'
    END AS status
FROM vehicles v, fences f
WHERE f.fence_id = 1
AND v.vehicle_id = 12345;

-- 配送路线优化示例
-- 计算配送中心到各客户的距离
SELECT 
    c.customer_id,
    c.name,
    c.address,
    ST_Distance(d.location, c.location::geography) / 1000 AS distance_km
FROM distribution_centers d, customers c
WHERE d.center_id = 1
ORDER BY distance_km;

-- 服务区域分析示例
-- 创建服务区域（30分钟车程范围）
SELECT 
    d.center_id,
    d.name,
    ST_Buffer(d.location, 30000) AS service_area  -- 约30分钟车程范围
FROM distribution_centers d
WHERE d.center_id = 1;

-- 气象数据分析示例
-- 查找指定区域内的气象站
SELECT 
    s.station_id,
    s.name,
    s.elevation,
    m.temperature,
    m.humidity,
    m.record_time
FROM weather_stations s, weather_measurements m, regions r
WHERE s.station_id = m.station_id
AND ST_Contains(r.geom, s.location)
AND r.name = '北京市'
AND m.record_time >= CURRENT_DATE - INTERVAL '7 days'
ORDER BY m.record_time DESC;

-- 污染扩散模拟示例
-- 计算污染源影响范围
SELECT 
    p.source_id,
    p.name,
    p.pollutant_type,
    ST_Buffer(p.location, p.influence_radius) AS influence_area
FROM pollution_sources p
WHERE p.pollutant_type = 'PM2.5';

-- 矢量切片服务示例
-- 生成矢量切片
SELECT 
    ST_AsMVT(tile, 'buildings', 4096, 'geom') AS mvt_tile
FROM (
    SELECT 
        id, 
        name, 
        ST_AsMVTGeom(geom, ST_Transform(ST_MakeEnvelope(%s, %s, %s, %s, 4326), 3857), 4096, 256, true) AS geom
    FROM buildings
    WHERE geom && ST_Transform(ST_MakeEnvelope(%s, %s, %s, %s, 4326), 3857)
) AS tile
WHERE ST_IsValid(geom);

-- 空间数据 API 示例
-- 按区域查询 POI 数据
SELECT 
    id,
    name,
    category,
    address,
    ST_AsGeoJSON(geom) AS geojson
FROM pois
WHERE ST_Within(geom, ST_GeomFromText(%s, 4326))
AND category = %s
LIMIT 100;

# Ubuntu/Debian 系统安装
sudo apt-get update
sudo apt-get install postgresql postgis postgresql-16-postgis-3

# CentOS/RHEL 系统安装
sudo yum install postgresql-server postgis

# macOS 系统安装（使用 Homebrew）
brew install postgres
brew install postgis

# Windows 系统安装
# 下载并运行 PostgreSQL 安装程序，选择包含 PostGIS 的安装包

-- 连接到 PostgreSQL
psql -U postgres -d your_database

-- 启用 PostGIS 扩展
CREATE EXTENSION postgis;

-- 可选：启用其他相关扩展
CREATE EXTENSION postgis_topology;    -- 拓扑支持
CREATE EXTENSION postgis_raster;      -- 栅格数据支持
CREATE EXTENSION fuzzystrmatch;       -- 模糊字符串匹配（用于地理编码）
CREATE EXTENSION address_standardizer; -- 地址标准化（用于地理编码）
CREATE EXTENSION postgis_tiger_geocoder; -- TIGER 地理编码器（美国地区）

-- 验证 PostGIS 安装
SELECT PostGIS_Version();
SELECT PostGIS_Full_Version();

-- 创建包含空间数据的表
CREATE TABLE parks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    area FLOAT,
    geom GEOMETRY(POLYGON, 4326)
);

-- 添加空间字段约束
ALTER TABLE parks ADD CONSTRAINT enforce_geom_type CHECK (ST_GeometryType(geom) = 'ST_POLYGON'::text OR geom IS NULL);
ALTER TABLE parks ADD CONSTRAINT enforce_geom_srid CHECK (ST_SRID(geom) = 4326);

-- 插入空间数据
INSERT INTO parks (name, area, geom) VALUES 
('朝阳公园', 288.5, ST_GeomFromText('POLYGON((116.47 39.94, 116.49 39.94, 116.49 39.96, 116.47 39.96, 116.47 39.94))', 4326)),
('颐和园', 290.0, ST_GeomFromText('POLYGON((116.26 39.99, 116.29 39.99, 116.29 40.01, 116.26 40.01, 116.26 39.99))', 4326));

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

-- 空间查询示例
-- 查找包含指定点的公园
SELECT name, area
FROM parks
WHERE ST_Contains(geom, ST_GeomFromText('POINT(116.48 39.95)', 4326));

-- 查找与指定多边形相交的公园
SELECT name, area
FROM parks
WHERE ST_Intersects(geom, ST_GeomFromText('POLYGON((116.45 39.93, 116.50 39.93, 116.50 39.97, 116.45 39.97, 116.45 39.93))', 4326));

-- 计算两个公园之间的距离
SELECT 
    p1.name AS park1,
    p2.name AS park2,
    ST_Distance(p1.geom::geography, p2.geom::geography) / 1000 AS distance_km
FROM parks p1, parks p2
WHERE p1.id < p2.id;

-- 空间连接示例
-- 查找每个区内的公园数量
SELECT 
    d.name AS district,
    COUNT(p.id) AS park_count,
    SUM(ST_Area(p.geom::geography)) / 1000000 AS total_park_area_sq_km
FROM districts d
LEFT JOIN parks p ON ST_Contains(d.geom, p.geom)
GROUP BY d.id, d.name
ORDER BY park_count DESC;

-- 缓冲区分析示例
-- 创建地铁站500米范围内的服务区
SELECT 
    s.name AS station_name,
    s.line,
    ST_Buffer(s.geom, 500) AS service_area
FROM subway_stations s
WHERE s.line = '1号线';

-- 空间聚合示例
-- 按区域聚合 POI 数据
SELECT 
    d.name AS district,
    p.category,
    COUNT(p.id) AS poi_count
FROM districts d
JOIN pois p ON ST_Contains(d.geom, p.geom)
GROUP BY d.id, d.name, p.category
ORDER BY d.name, poi_count DESC;

-- 复杂空间分析示例
-- 查找最佳新店选址（在商业区内，距离现有店至少1000米，人口密度高）
WITH commercial_areas AS (
    SELECT id, name, geom
    FROM land_use
    WHERE land_use_type = '商业用地'
),
existing_stores AS (
    SELECT id, name, geom
    FROM stores
),
high_density_areas AS (
    SELECT id, name, geom
    FROM population_density
    WHERE density > 10000  -- 每平方公里人口超过1万人
),
candidate_areas AS (
    SELECT 
        ca.id,
        ca.name,
        ca.geom,
        ST_Area(ca.geom::geography) / 1000000 AS area_sq_km
    FROM commercial_areas ca
    WHERE NOT EXISTS (
        SELECT 1 FROM existing_stores es 
        WHERE ST_DWithin(ca.geom, es.geom, 1000)
    )
    AND EXISTS (
        SELECT 1 FROM high_density_areas hda 
        WHERE ST_Intersects(ca.geom, hda.geom)
    )
)
SELECT 
    id,
    name,
    area_sq_km
FROM candidate_areas
ORDER BY area_sq_km DESC
LIMIT 10;