22. 更多空间连接

在上一节中，我们看到了 ST_Centroid(geometry) 和 ST_Union([geometry]) 函数，以及一些简单的示例。在本节中，我们将使用它们做一些更复杂的事情。

22.1. 创建人口普查区表

在研讨会 \data\ 目录中，有一个包含属性数据但没有几何图形的文件，nyc_census_sociodata.sql。该表包含有关纽约的有趣社会经济数据：通勤时间、收入和教育程度。只有一个问题。数据按“人口普查区”汇总，我们没有人口普查区空间数据！

在本节中，我们将

  • 加载 nyc_census_sociodata.sql 表

  • 为人口普查区创建一个空间表

  • 将属性数据加入到空间数据

  • 使用我们的新数据进行一些分析

22.1.1. 加载 nyc_census_sociodata.sql

  1. 在 PgAdmin 中打开 SQL 查询窗口

  2. 从菜单中选择 **文件->打开**，并浏览到 nyc_census_sociodata.sql 文件

  3. 按“运行查询”按钮

  4. 如果您按 PgAdmin 中的“刷新”按钮，表列表现在应该包括 nyc_census_sociodata 表

22.1.2. 创建人口普查区表

正如我们在上一节中看到的，我们可以通过对 blkid 键的子字符串进行汇总来从人口普查区块构建更高层次的几何图形。为了获得人口普查区，我们需要对 blkid 的前 11 个字符进行分组汇总。

360610001001001 = 36 061 000100 1 001

36     = State of New York
061    = New York County (Manhattan)
000100 = Census Tract
1      = Census Block Group
001    = Census Block

使用 ST_Union 聚合函数创建新表

-- Make the tracts table
CREATE TABLE nyc_census_tract_geoms AS
SELECT
  ST_Union(geom) AS geom,
  SubStr(blkid,1,11) AS tractid
FROM nyc_census_blocks
GROUP BY tractid;

-- Index the tractid
CREATE INDEX nyc_census_tract_geoms_tractid_idx
  ON nyc_census_tract_geoms (tractid);

22.1.3. 将属性加入空间数据

使用标准属性连接将地块几何图形表与地块属性表连接起来。

-- Make the tracts table
CREATE TABLE nyc_census_tracts AS
SELECT
  g.geom,
  a.*
FROM nyc_census_tract_geoms g
JOIN nyc_census_sociodata a
ON g.tractid = a.tractid;

-- Index the geometries
CREATE INDEX nyc_census_tract_gidx
  ON nyc_census_tracts USING GIST (geom);

22.1.4. 回答一个有趣的问题

回答一个有趣的问题！“列出纽约前 10 个按拥有研究生学历的人口比例排序的社区。”

SELECT
  100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total) AS graduate_pct,
  n.name, n.boroname
FROM nyc_neighborhoods n
JOIN nyc_census_tracts t
ON ST_Intersects(n.geom, t.geom)
WHERE t.edu_total > 0
GROUP BY n.name, n.boroname
ORDER BY graduate_pct DESC
LIMIT 10;

我们对感兴趣的统计数据进行汇总，然后在最后将它们除以。为了避免除以零错误，我们不费心引入人口数为零的地块。

 graduate_pct |       name        | boroname
--------------+-------------------+-----------
         47.6 | Carnegie Hill     | Manhattan
         42.2 | Upper West Side   | Manhattan
         41.1 | Battery Park      | Manhattan
         39.6 | Flatbush          | Brooklyn
         39.3 | Tribeca           | Manhattan
         39.2 | North Sutton Area | Manhattan
         38.7 | Greenwich Village | Manhattan
         38.6 | Upper East Side   | Manhattan
         37.9 | Murray Hill       | Manhattan
         37.4 | Central Park      | Manhattan

注意

纽约地理学家会对这个受过高等教育的社区名单中出现“Flatbush”感到奇怪。答案将在下一节中讨论。

22.2. 多边形/多边形连接

在我们有趣的问题中（在 回答一个有趣的问题）我们使用 ST_Intersects(geometry_a, geometry_b) 函数来确定每个社区摘要中要包含哪些人口普查地块多边形。这引出了一个问题：如果一个地块落在两个社区的边界上怎么办？它将与两者相交，因此将被包含在两个社区的汇总统计数据中。

_images/centroid_neighborhood.png

为了避免这种双重计数，有两种方法

  • 简单的方法是确保每个地块只落在一个汇总区域中（使用 ST_Centroid(geometry)）

  • 复杂的方法是在边界处划分交叉地块（使用 ST_Intersection(geometry,geometry)）

以下是如何使用简单方法来避免在我们的研究生教育查询中进行双重计数的示例

SELECT
  100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total) AS graduate_pct,
  n.name, n.boroname
FROM nyc_neighborhoods n
JOIN nyc_census_tracts t
ON ST_Contains(n.geom, ST_Centroid(t.geom))
WHERE t.edu_total > 0
GROUP BY n.name, n.boroname
ORDER BY graduate_pct DESC
LIMIT 10;

请注意，查询现在需要更长的时间才能运行，因为 ST_Centroid 函数必须在每个人口普查地块上运行。

 graduate_pct |        name         | boroname
--------------+---------------------+-----------
         48.0 | Carnegie Hill       | Manhattan
         44.2 | Morningside Heights | Manhattan
         42.1 | Greenwich Village   | Manhattan
         42.0 | Upper West Side     | Manhattan
         41.4 | Tribeca             | Manhattan
         40.7 | Battery Park        | Manhattan
         39.5 | Upper East Side     | Manhattan
         39.3 | North Sutton Area   | Manhattan
         37.4 | Cobble Hill         | Brooklyn
         37.4 | Murray Hill         | Manhattan

避免双重计数会改变结果！

22.2.1. Flatbush 怎么样？

特别是，Flatbush 社区已经从列表中消失了。原因可以通过更仔细地查看我们表格中 Flatbush 社区的地图来了解。

_images/nyc_tracts_flatbush.jpg

根据我们的数据来源，Flatbush 并不是传统意义上的社区，因为它只覆盖了展望公园的区域。该区域的人口普查地块自然记录了零居民。但是，社区边界确实擦过公园北侧的一个昂贵的人口普查地块（在经过改造的公园坡社区）。当使用多边形/多边形测试时，这个单一地块被添加到原本空旷的 Flatbush 中，导致该查询得分非常高。

22.3. 大圆距离连接

一个有趣的问题是：“靠近（500 米以内）地铁站的人的通勤时间与远离地铁站的人的通勤时间有何不同？”

然而，这个问题会遇到一些重复计数的问题：许多人会位于多个地铁站的 500 米范围内。比较纽约的人口

SELECT Sum(popn_total)
FROM nyc_census_blocks;

8175032

与纽约 500 米范围内地铁站的人口

SELECT Sum(popn_total)
FROM nyc_census_blocks census
JOIN nyc_subway_stations subway
ON ST_DWithin(census.geom, subway.geom, 500);

10855873

靠近地铁的人比总人口还多！显然，我们简单的 SQL 语句造成了很大的重复计数错误。从缓冲地铁的图片中可以看出问题所在。

_images/subways_buffered.png

解决方案是确保在将人口普查区块传递到查询的汇总部分之前，我们只有不同的区块。我们可以通过将查询分解为一个子查询来找到不同的区块，然后将其包装在一个汇总查询中来返回我们的答案

WITH distinct_blocks AS (
  SELECT DISTINCT ON (blkid) popn_total
  FROM nyc_census_blocks census
  JOIN nyc_subway_stations subway
  ON ST_DWithin(census.geom, subway.geom, 500)
)
SELECT Sum(popn_total)
FROM distinct_blocks;

5005743

这样好多了！因此，纽约人口的一半以上位于地铁 500 米（大约 5-7 分钟步行）范围内。