13. 空間聯合¶
空間聯合是空間資料庫的核心功能。它們允許您使用空間關係作為聯合鍵來結合來自不同資料表的資訊。我們認為「標準 GIS 分析」的主要內容都可以表示為空間聯合。
在前一節中，我們使用兩步驟程序探討了空間關係：首先，我們為「Broad St」提取得一個地鐵站點；接著，我們使用那個點來詢問進一步的問題，例如「『Broad St』車站位於哪個街區？」
使用空間聯合，我們可以使用一個步驟回答問題，擷取有關地鐵站和包含地鐵站的街區的資訊
SELECT
subways.name AS subway_name,
neighborhoods.name AS neighborhood_name,
neighborhoods.boroname AS borough
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_subway_stations AS subways
ON ST_Contains(neighborhoods.geom, subways.geom)
WHERE subways.name = 'Broad St';
subway_name | neighborhood_name | borough
-------------+--------------------+-----------
Broad St | Financial District | Manhattan
我們可以將每個地鐵站都聯合到其包含街區，但在這種情況下，我們只想要有關其中一個地鐵站的資訊。任何在兩個資料表之間提供真/假關係的函數都可以用來驅動空間聯合，但最常使用的函數是：ST_Intersects、ST_Contains 和 ST_DWithin。
13.1. 聯合和摘要¶
JOIN 與 GROUP BY 的組合提供了通常在 GIS 系統中執行的分析類型。
例如：「曼哈頓街區的人口和種族組成是什麼？」這裡有一個問題，結合了人口普查中的有關人口的資訊和街區的邊界，並限制在曼哈頓其中一個自治區。
SELECT
neighborhoods.name AS neighborhood_name,
Sum(census.popn_total) AS population,
100.0 * Sum(census.popn_white) / Sum(census.popn_total) AS white_pct,
100.0 * Sum(census.popn_black) / Sum(census.popn_total) AS black_pct
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_census_blocks AS census
ON ST_Intersects(neighborhoods.geom, census.geom)
WHERE neighborhoods.boroname = 'Manhattan'
GROUP BY neighborhoods.name
ORDER BY white_pct DESC;
neighborhood_name | population | white_pct | black_pct
---------------------+------------+-----------+-----------
Carnegie Hill | 18763 | 90.1 | 1.4
North Sutton Area | 22460 | 87.6 | 1.6
West Village | 26718 | 87.6 | 2.2
Upper East Side | 203741 | 85.0 | 2.7
Soho | 15436 | 84.6 | 2.2
Greenwich Village | 57224 | 82.0 | 2.4
Central Park | 46600 | 79.5 | 8.0
Tribeca | 20908 | 79.1 | 3.5
Gramercy | 104876 | 75.5 | 4.7
Murray Hill | 29655 | 75.0 | 2.5
Chelsea | 61340 | 74.8 | 6.4
Upper West Side | 214761 | 74.6 | 9.2
Midtown | 76840 | 72.6 | 5.2
Battery Park | 17153 | 71.8 | 3.4
Financial District | 34807 | 69.9 | 3.8
Clinton | 32201 | 65.3 | 7.9
East Village | 82266 | 63.3 | 8.8
Garment District | 10539 | 55.2 | 7.1
Morningside Heights | 42844 | 52.7 | 19.4
Little Italy | 12568 | 49.0 | 1.8
Yorkville | 58450 | 35.6 | 29.7
Inwood | 50047 | 35.2 | 16.8
Washington Heights | 169013 | 34.9 | 16.8
Lower East Side | 96156 | 33.5 | 9.1
East Harlem | 60576 | 26.4 | 40.4
Hamilton Heights | 67432 | 23.9 | 35.8
Chinatown | 16209 | 15.2 | 3.8
Harlem | 134955 | 15.1 | 67.1
這裡發生了什麼事？概念上（資料庫在後台最佳化實際評估順序），發生下列情況
JOIN子句建立一個虛擬資料表，其中包括來自街區和人口普查資料表的欄位。WHERE子句過濾我們的虛擬資料表，只留下曼哈頓的列。其餘的列按街區名稱分組，並透過聚合函數輸入 Sum() 來加總人口值。
對最終數字進行一些算術和格式化（例如，
GROUP BY、ORDER BY）後，我們的查詢列出百分比。
備註
JOIN 子句會結合兩個 FROM 項目。預設情況下，我們會使用 INNER JOIN，但還有其他四種加入類型。有關更多資訊，請參閱 PostgreSQL 文件中的 join_type 定義。
我們也可以將距離測試用作加入關鍵字，以建立摘錄的「半徑範圍內的所有項目」查詢。讓我們使用距離查詢探討紐約的種族地理。
首先，我們來取得這座城市的基礎種族組成。
SELECT
100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
Sum(popn_total) AS popn_total
FROM nyc_census_blocks;
white_pct | black_pct | popn_total
------------------+------------------+------------
44.0039500762811 | 25.5465789002416 | 8175032
因此，在紐約的 8M 人口中，大約 44% 的人被記錄為「白人」，而 26% 的人被記錄為「黑人」。
デューク · 艾靈頓曾唱道：「你 / 必須搭乘 A 線列車 / 前往 / 哈林區的上城區。」如我們稍早所見，哈林區遠遠超過曼哈頓其他地方的非裔美國人人口（80.5%）。公爵的 A 線列車是否也一樣？
首先，請注意我們感興趣的 nyc_subway_stations 表格 routes 欄位的內容，以便找到 A 線列車。裡面的值有點複雜。
SELECT DISTINCT routes FROM nyc_subway_stations;
A,C,G
4,5
D,F,N,Q
5
E,F
E,J,Z
R,W
備註
DISTINCT 關鍵字會從結果中排除重複列。沒有 DISTINCT 關鍵字時，上面的查詢會找出 491 筆結果，而不是 73 筆結果。
因此，為了找到 A 線列車，我們將會想要 routes 中任何含有 'A' 的列。我們可以用好幾種方法做到這一點，但我們今天會利用 strpos(routes,'A') 這個事實，它只會在 routes 欄位中有 'A' 時回傳一個非零數字。
SELECT DISTINCT routes
FROM nyc_subway_stations AS subways
WHERE strpos(subways.routes,'A') > 0;
A,B,C
A,C
A
A,C,G
A,C,E,L
A,S
A,C,F
A,B,C,D
A,C,E
讓我們摘錄 A 線列車路線 200 公尺範圍內的種族組成。
SELECT
100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.geom, subways.geom, 200)
WHERE strpos(subways.routes,'A') > 0;
white_pct | black_pct | popn_total
------------------+------------------+------------
45.5901255900202 | 22.0936235670937 | 189824
因此，沿著 A 線列車的種族組成與紐約市的整體組成並沒有很大的不同。
13.2. 進階加入¶
在上一節中，我們看到 A 線列車提供的服務，與整個城市的種族組成沒有太大的差異。是否有任何火車種族組成不平均？
為了解答這個問題，我們會在查詢中新增另一個加入，以便可以同時一次計算許多地鐵路線的組成。要這麼做，我們需要建立一個新的表格，列舉出我們想要摘錄的所有路線。
CREATE TABLE subway_lines ( route char(1) );
INSERT INTO subway_lines (route) VALUES
('A'),('B'),('C'),('D'),('E'),('F'),('G'),
('J'),('L'),('M'),('N'),('Q'),('R'),('S'),
('Z'),('1'),('2'),('3'),('4'),('5'),('6'),
('7');
現在我們可以將地鐵路線的表格加入到原始查詢中。
SELECT
lines.route,
100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
Sum(popn_total) AS popn_total
FROM nyc_census_blocks AS census
JOIN nyc_subway_stations AS subways
ON ST_DWithin(census.geom, subways.geom, 200)
JOIN subway_lines AS lines
ON strpos(subways.routes, lines.route) > 0
GROUP BY lines.route
ORDER BY black_pct DESC;
route | white_pct | black_pct | popn_total
-------+-----------+-----------+------------
S | 39.8 | 46.5 | 33301
3 | 42.7 | 42.1 | 223047
5 | 33.8 | 41.4 | 218919
2 | 39.3 | 38.4 | 291661
C | 46.9 | 30.6 | 224411
4 | 37.6 | 27.4 | 174998
B | 40.0 | 26.9 | 256583
A | 45.6 | 22.1 | 189824
J | 37.6 | 21.6 | 132861
Q | 56.9 | 20.6 | 127112
Z | 38.4 | 20.2 | 87131
D | 39.5 | 19.4 | 234931
L | 57.6 | 16.8 | 110118
G | 49.6 | 16.1 | 135012
6 | 52.3 | 15.7 | 260240
1 | 59.1 | 11.3 | 327742
F | 60.9 | 7.5 | 229439
M | 56.5 | 6.4 | 174196
E | 66.8 | 4.7 | 90958
R | 58.5 | 4.0 | 196999
N | 59.7 | 3.5 | 147792
7 | 35.7 | 3.5 | 102401
與先前相同，聯結會在 `JOIN ON` 限制內產生所有可能的組合的虛擬表格，然後這些列會被提供到 `GROUP` 總結。空間魔法在於 `ST_DWithin` 函數，確保只有接近合適地鐵站的普查區塊才會被納入計算。
13.3. 函數清單¶
ST_Contains(geometry A, geometry B): 僅在 B 的點沒有任何位在 A 的外部，且 B 內部至少有一個點位在 A 的內部時傳回 true。
ST_DWithin(geometry A, geometry B, radius): 如果幾何圖形彼此相距在指定的距離內，則傳回 true。
ST_Intersects(geometry A, geometry B): 如果幾何圖形/地理「在空間上相交」（共用任何空間區段），則傳回 TRUE，如果沒有（不相交）則傳回 FALSE。
round(v numeric, s integer): PostgreSQL 數學函數，會四捨五入至 s 個小數位。
strpos(string, substring): PostgreSQL 字串函數，傳回特定子字串的整數位置。
sum(expression): PostgreSQL 總和函數，傳回一組紀錄中的紀錄總數。
