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, and 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 <http://www.postgresql.org/docs/9.1/interactive/sql-select.html#SQL-FROM>`_ 定义。

我们还可以使用距离测试作为连接键，来创建汇总的“半径内的所有项目”查询。让我们使用距离查询来探索纽约的种族地理。

首先，让我们了解一下该城市的基本种族构成。

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 table routes 字段的内容是我们感兴趣的查找 A-train 的内容。里面的值有点复杂。

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”的任何行。我们可以通过多种方式做到这一点，但今天我们将使用这样一个事实：仅当“A”位于 ``routes``字段中时，:command:`strpos(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): 四舍五入到 s 小数位的 PostgreSQL 数学函数

strpos(string, substring):PostgreSQL 字符串函数，返回指定子字符串的整数位置。

sum(expression): PostgreSQL 聚合函数，返回一组记录中的记录总和。

Introduction to PostGIS

13. 空间连接¶

13.1. 连接并总结¶

13.2. 高级连接¶

13.3. 函数列表¶

导航