第 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