PostGIS系列课程之空间联合

空间联接是空间数据库的基础。 它们允许通过使用空间关系作为JOIN键来组合来自不同表的信息。 我们认为的大多数“标准GIS分析”都可以表示为空间JOIN。

在上一节中，我们使用两步过程探索了空间关系：首先，我们提取了Broad St的地铁站点； 然后，我们用这一点来问其他问题，例如“Broad St车站在哪个社区？”

使用空间JOIN，我们可以一步一步地回答问题，检索有关地铁站及其附近的信息：

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

我们本来可以将每个地铁站都加入到其附近的社区，但是在这种情况下，我们只需要有关一个的信息。 任何在两个表之间提供真假关系的函数都可以用来驱动空间JOIN，但是最常用的函数是：ST_Intersects，ST_Contains和ST_DWithin。

联合 和 统计

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
  ...

这里发生了什么？
名义上（实际评估ORDER由数据库进行了优化）是这样的：

1.JOIN子句创建一个虚拟表，其中包含来自邻里和人口普查表的列。
2.WHERE子句将虚拟表过滤为曼哈顿中的行。
3.其余行按邻居名称分组，并通过聚合函数馈入总体值Sum（）。
4.对最终数字进行一些算术运算和格式化（例如，GROUP BY，ORDER BY）后，我们的查询会吐出百分比。

注意

JOIN子句结合了两个FROM项。 默认情况下，我们使用的是INNER JOIN，但是还有其他四种类型的JOIN。 有关更多信息，请参见PostgreSQL文档中的[JOIN_type]
（https://www.postgresql.org/docs/9.1/interactive/sql-SELECT.html\SQL-FROM） 定义。

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

首先，让我们获取城市的基本种族构成。

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火车/到/在哈林（Harlem）到达糖山（Sugar Hill）。 正如我们前面所看到的，哈林区（Harlem）远远超过了曼哈顿的非裔美国人人口（80.5％）。 杜克大学的A火车也是如此吗？

首先，请注意，nyc_subway_stations表routes字段的内容是我们寻找A火车的兴趣所在。 其中的值有点复杂。

SELECT  DISTINCT routes  FROM nyc_subway_stations;
-------------------------------------
A,C,G4,5D,F,N,Q5E,FE,J,ZR,W

注意

DISTINCT关键字从结果中消除重复的行。 如果没有DISTINCT关键字，则上面的查询会标识491个结果，而不是73个结果。

因此，要查找A列火车，我们将需要“路线”中任何包含A的行。 我们可以通过多种方法来做到这一点，但是今天我们将使用以下事实：只有在routes字段中使用A时，strpos（routes，'A'）才会返回非零数字。

SELECT  DISTINCT routes 
    FROM nyc_subway_stations AS subways 
    WHERE  strpos(subways.routes,'A') > 0;
-------------------------------
A,B,CA,CAA,C,GA,C,E,LA,SA,C,FA,B,C,DA,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火车进行的种族构成与整个纽约市的构成并没有根本不同。

高级联合

在上一节中，我们看到了A列火车没有为与该城市其他地区的种族构成有很大差异的人群提供服务。 有没有种族构成不平均的火车？

为了回答这个问题，我们将在查询中添加另一个JOIN，以便我们可以一次同时计算许多地铁线路的组成。 为此，我们需要创建一个新表，该表枚举我们要总结的所有行。

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) > 
    0GROUP 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 |  229439M |      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创建一个虚拟表，其中包含在JOIN ON约束内的所有可能组合，然后将这些行馈入GROUP摘要中。 空间算法的起名之处在ST_DWithin函数中，该函数可确保在计算中仅包括靠近相应地铁站的人口普查区。