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
因此，在纽约的 800 万人口中，大约 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') 仅当“A”位于 routes 字段中时才会返回非零数字。
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 聚合函数，返回一组记录中记录的总和。
