第 5 章。空间查询

您可以使用一段非常简单的 SQL 来回答这个问题

SELECT sum(ST_Length(geom))/1000 AS km_roads FROM bc_roads;

km_roads
------------------
70842.1243039643

此查询将属性条件（在城市名称上）与空间计算（多边形面积）相结合

SELECT
  ST_Area(geom)/10000 AS hectares
FROM bc_municipality
WHERE name = 'PRINCE GEORGE';

hectares
------------------
32657.9103824927

此查询使用空间测量值作为排序值。有几种方法可以解决这个问题，但最有效的方法如下

SELECT
  name,
  ST_Area(geom)/10000 AS hectares
FROM bc_municipality
ORDER BY hectares DESC
LIMIT 1;

name           | hectares
---------------+-----------------
TUMBLER RIDGE  | 155020.02556131

请注意，为了回答此查询，我们必须计算每个多边形的面积。如果我们经常这样做，则向表中添加一个面积列以便为性能建立索引是有意义的。通过以降序排列结果，然后使用 PostgreSQL 的“LIMIT”命令，我们可以轻松地选择最大值，而无需使用 MAX() 之类的聚合函数。

这是一个“空间连接”的示例，它将来自两个表的数据（使用连接）通过空间交互（“包含”）作为连接条件（而不是通常的在公共键上连接的关系方法）结合在一起

SELECT
  m.name,
  sum(ST_Length(r.geom))/1000 as roads_km
FROM bc_roads AS r
JOIN bc_municipality AS m
  ON ST_Contains(m.geom, r.geom)
GROUP BY m.name
ORDER BY roads_km;

name                        | roads_km
----------------------------+------------------
SURREY                      | 1539.47553551242
VANCOUVER                   | 1450.33093486576
LANGLEY DISTRICT            | 833.793392535662
BURNABY                     | 773.769091404338
PRINCE GEORGE               | 694.37554369147
...

这个查询需要一段时间，因为表中每条道路都被汇总到最终结果中（例如表约 25 万条道路）。对于较小的数据集（几千条记录和几百条记录），响应可能非常快。

这是一个“叠加”的示例，它接收两个表，并输出一个新表，该表包含空间裁剪或切割的结果。与上面演示的“空间连接”不同，此查询创建了新的几何图形。叠加就像一个涡轮增压的空间连接，对于更精确的分析工作很有用

CREATE TABLE pg_roads as
SELECT
  ST_Intersection(r.geom, m.geom) AS intersection_geom,
  ST_Length(r.geom) AS rd_orig_length,
  r.*
FROM bc_roads AS r
JOIN bc_municipality AS m
  ON ST_Intersects(r.geom, m.geom)
WHERE
  m.name = 'PRINCE GEORGE';

SELECT
  sum(ST_Length(r.geom))/1000 AS kilometers
FROM bc_roads r
JOIN bc_municipality m
  ON ST_Intersects(m.geom, r.geom
WHERE
  r.name = 'Douglas St'
  AND m.name = 'VICTORIA';

kilometers
------------------
4.89151904172838

SELECT gid, name, ST_Area(geom) AS area
FROM bc_municipality
WHERE ST_NRings(geom) > 1
ORDER BY area DESC LIMIT 1;

gid  | name         | area
-----+--------------+------------------
12   | SPALLUMCHEEN | 257374619.430216