PostGIS 地理空间查询

sql -- 创建数据库后，连接并执行 CREATE EXTENSION postgis; -- 如果需要额外的栅格、拓扑等高级功能，可以再加 CREATE EXTENSION postgis_raster; CREATE EXTENSION postgis_topology;

确认扩展已启用：

```sql
SELECT PostGIS_full_version();

理解核心空间数据类型

PostGIS 引入两种最重要的空间数据类型，它们几乎会出现在你所有的查询中。

几何类型 (geometry)

用于存储平面地图上的点、线、面等要素。它基于直角坐标系，适合在小范围（如城市、园区）进行精确的量算，比如距离、面积计算。

地理类型 (geography)

用于存储地球球面上的地理坐标（经度/纬度），基于球体或椭球体模型。适合全球范围的距离和面积计算，单位通常是米。如果不需要复杂的大地测量运算，可以先从 geometry 入门。

快速体验：创建带空间列的表

CREATE TABLE parks (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  geom geometry(Point, 4326)  -- 点类型，SRID 4326 表示 WGS84 坐标系
);

插入空间数据：手动造点

先亲手插入几条数据，感受几何对象的构造方法。

INSERT INTO parks (name, geom)
VALUES
  ('中央公园', ST_SetSRID(ST_MakePoint(116.397, 39.908), 4326)),
  ('人民广场', ST_SetSRID(ST_MakePoint(121.473, 31.230), 4326));

ST_MakePoint 创建几何点，ST_SetSRID 为其设置空间参考标识符（SRID），4326 是常用的 WGS84 经纬度坐标系。永远不要省略 SRID，否则后续的空间运算会出错。

创建空间索引：为查询加速

空间查询的数据量通常很大，必须创建索引才能保证性能。

CREATE INDEX parks_geom_idx ON parks USING GIST (geom);

GIST（通用搜索树）是 PostGIS 最常用的空间索引。之后你在 WHERE 子句中使用的任何空间操作符（如 &&、ST_Intersects 等）都会自动利用索引大幅提速。

基础空间查询实战

以下示例假设你已经拥有一份名为 cities（点状城市）、roads（线状道路）和 regions（面状区域）的数据，它们都带有 geometry 列。

空间测量：距离与面积

计算两个城市之间的直线距离（平面单位：度，若用 geography 类型则是米）。

SELECT
  a.name AS city_a,
  b.name AS city_b,
  ST_Distance(a.geom, b.geom) AS distance_degrees
FROM cities a, cities b
WHERE a.name = '北京' AND b.name = '上海';

对于面积计算：

SELECT name, ST_Area(geom) AS area_sq_degrees
FROM regions;

如果使用 geography 类型或转换为合适的投影坐标系，可以得到米制结果。

空间关系判断：相交、包含、相邻

PostGIS 提供一系列空间关系函数，对数据分析极为重要。

判断点是否在面内

查找某公园所在的城市：

SELECT parks.name AS park, cities.name AS city
FROM parks, cities
WHERE ST_Within(parks.geom, cities.geom);

ST_Within(A, B) 表示 A 完全在 B 内部。反过来也可以用 ST_Contains(B, A)。

查找相交的道路

找出与特定区域相交的所有道路：

SELECT roads.name, roads.type
FROM roads, regions
WHERE regions.name = '海淀区'
AND ST_Intersects(roads.geom, regions.geom);

ST_Intersects 只要空间上有任何重叠或接触即可返回 true，是最常用的空间连接条件。

查找相邻区域

找与目标区域共享边界的区域：

SELECT b.name AS neighbor
FROM regions a, regions b
WHERE a.name = '朝阳区'
AND ST_Touches(a.geom, b.geom);

ST_Touches 要求边界接触但内部不重叠。

缓冲区分析：查找周边设施

想找到某条地铁线周围 500 米范围内的所有便利店，可以先生成缓冲区：

SELECT shop.name, shop.address
FROM subway_lines s, shops
WHERE s.line_name = '1号线'
AND ST_DWithin(shops.geom, s.geom, 500);

ST_DWithin 直接判断两个几何体之间的距离是否在指定值内，内部会自动利用索引，比先构造缓冲区再判断相交效率高得多。如果几何是 geography 类型，单位就是米；如果是 geometry，单位取决于 SRID（如 4326 下单位为度，此时应避免直接使用 ST_DWithin，最好先转换为合适的投影坐标系）。

最近邻查询：找最近的超市

PostGIS 提供了强大的 KNN（K 最近邻）运算符 <->，可以极快地查找距离排序。

SELECT name, geom <-> ST_SetSRID(ST_MakePoint(121.48, 31.23), 4326) AS dist
FROM shops
WHERE type = '超市'
ORDER BY geom <-> ST_SetSRID(ST_MakePoint(121.48, 31.23), 4326)
LIMIT 5;

<-> 计算两个几何中心点之间的二维距离，结合 ORDER BY 和 LIMIT 让索引精确排序，性能远超普通函数。

空间聚合与数据转换

合并相邻区域

想要得到一个大区的轮廓，可以使用 ST_Union 聚合函数：

SELECT ST_Union(geom) AS combined_geom
FROM districts
WHERE city = '上海' AND district_name IN ('黄浦区', '静安区', '徐汇区');

通常 ST_Union 很消耗资源，如果只是需要包含所有要素的外包框，可以用 ST_Collect，最后再计算外包框。

计算点到线的距离并汇总

统计每条公交线路到城市绿地的最短距离：

SELECT routes.line_id, MIN(ST_Distance(routes.geom, green.geom)) AS min_dist
FROM bus_routes routes, urban_greens green
GROUP BY routes.line_id;

这类查询配合索引可以处理海量数据，但条件允许的话尽量先做空间过滤（如先用 ST_DWithin 限定范围）以减少参与 MIN 计算的数据量。

坐标系转换：解决单位不一致问题

如果你的数据是经纬度（SRID 4326），但你需要用米来计算面积或距离，就可以将几何转换到合适的投影坐标系。

-- 将经纬度转为适合测量中国的投影坐标系（如 Albers 等积投影 SRID 3857 或 4549）
SELECT name,
  ST_Area(ST_Transform(geom, 3857)) AS area_sqm
FROM regions;