PostgreSQL 16 集成 PostGIS：空间数据查询实践指南

PostGIS 是 PostgreSQL 的核心空间扩展，支持地理对象存储与分析。以下为完整实践流程：

---

1. 安装与启用 PostGIS

-- 安装扩展
CREATE EXTENSION postgis;

-- 验证安装
SELECT PostGIS_Version();
/* 输出示例： 
"3.3.2"
*/

---

2. 创建空间数据表

CREATE TABLE locations (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    -- 存储地理坐标（经度,纬度）
    geom GEOMETRY(Point, 4326)  
);

---

3. 插入空间数据

-- 插入坐标点（北京天安门）
INSERT INTO locations (name, geom) 
VALUES ('天安门', ST_GeomFromText('POINT(116.3974 39.9093)', 4326));

-- 批量插入示例
INSERT INTO locations (name, geom) VALUES
('上海外滩', ST_Point(121.4905, 31.2379)),
('广州塔', ST_Point(113.3248, 23.1067));

---

4. 核心空间查询实践

4.1 距离计算

-- 计算天安门到上海外滩的距离（单位：米）
SELECT ST_Distance(
    (SELECT geom FROM locations WHERE name='天安门'),
    (SELECT geom FROM locations WHERE name='上海外滩')
) AS distance;

距离公式实现原理：
$$ d = R \cdot \arccos(\sin(\phi_1)\sin(\phi_2) + \cos(\phi_1)\cos(\phi_2)\cos(\Delta\lambda)) $$
其中 $R$ 为地球半径，$(\phi,\lambda)$ 为纬度/经度

4.2 范围搜索

-- 查找5公里范围内的地点（以天安门为中心）
SELECT name 
FROM locations
WHERE ST_DWithin(
    geom,
    (SELECT geom FROM locations WHERE name='天安门'),
    5000  -- 半径(米)
);

4.3 几何关系判断

-- 判断点是否在区域内（需先创建多边形区域）
CREATE TABLE areas (poly GEOMETRY(Polygon, 4326));
INSERT INTO areas VALUES (ST_MakePolygon(ST_GeomFromText('LINESTRING(116.39 39.91, 116.41 39.91, 116.40 39.89, 116.39 39.91)')));

SELECT name 
FROM locations 
WHERE ST_Within(geom, (SELECT poly FROM areas));

---

5. 性能优化

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

-- 索引使用分析
EXPLAIN ANALYZE
SELECT * FROM locations 
ORDER BY geom <-> ST_Point(116.40, 39.90) 
LIMIT 3;

---

6. 可视化输出

-- 生成GeoJSON地图数据
SELECT jsonb_build_object(
    'type', 'FeatureCollection',
    'features', jsonb_agg(ST_AsGeoJSON(locations.*)::jsonb)
) 
FROM locations;

输出可直接导入地图工具（如 QGIS/Leaflet）

---

应用场景

1. 实时位置服务： 用户周边POI搜索
2. 物流轨迹分析： 运输路线优化
3. 地理围栏监控： 电子围栏触发报警
4. 城市规划： 人口密度热力图生成

通过 pgRouting 扩展可实现路径规划，结合 PostGIS 形成完整GIS解决方案