PostGIS系列课程之高级空间联合

更多更复杂的联合查询

在本节中，我们将对一般的空间查询做一些更复杂的事情。

创建一张人口普查表

我们已经在数据库中创建了一个名为nyc_census_sociodata的表。 该表包括有关纽约的有趣的社会经济数据：通勤时间，收入和受教育程度。 只有一个问题。 数据由“人口普查区域”汇总，我们没有人口普查区域空间数据！

在本节中，我们将:

• 为人口普查区创建空间表
• 将属性数据连接到空间数据
• 使用我们的新数据进行一些分析

创建一张人口普查表

通过总结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);

对空间信息和属性信息表进行联合

使用标准属性联接将区域几何表连接到区域属性表

-- 创建普查表
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;
-- 创建空间索引
CREATE INDEX nyc_census_tract_gidx 
    ON  nyc_census_tracts 
    USING GIST (geom);

回答一个有趣的问题

回答一个有趣的问题！ “按拥有研究生学位的人数的顺序排列纽约十大社区。”

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 DESCLIMIT 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”的存在感到好奇。 答案将在下一部分中讨论。

现在，让我们继续看看如何在两个不同的多边形数据集之间进行实际联接。

多边形/多边形 联合

在我们有趣的查询中，我们使用了 ST_Intersects（geometry_a，geometry_b） 函数来确定要包含在每个邻域摘要中的人口普查多边形。 这就引出了一个问题：如果一条道落在两个街区之间的边界上怎么办？ 它将相交，因此将包括在 两者 的摘要统计中。

为避免这种重复计算，有两种方法：

• 简单的方法是确保每个区域仅落在 一个统计区域 中（使用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 DESCLIMIT 10;
    
--请注意，查询现在要花更长的时间运行，
--因为必须在每个普查区域中运行ST_Centroidfunction。
------------------------------------------------
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

避免重复计算会改变结果！

Flatbush "社区"什么情况?

特别是Flatbush社区已从列表中删除。 可以通过更仔细地查看我们表中Flatbush社区的地图来了解其原因。

根据我们的数据来源的定义，Flatbush实际上并不是一个真正意义上的社区，因为它仅覆盖了Prospect Park的区域，该区域的人口普查记录的自然是零居民，但是该社区的边界确实刮擦了其中一个居民区。 与公园北侧接壤的贵族人口普查区（在gentrifiedPark Slope附近）。 当使用多边形/多边形测试时，该单段被添加到了否则为空的Flatbush中，导致该查询的得分很高。

大半径连接

大半径距离的连接

一个有趣的问题是：“地铁站附近（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正在产生大的重复计数错误。 查看缓冲地铁的图片，您可以看到问题。

解决方案是确保在将它们传递到查询的摘要部分之前，只有唯一的人口普查块。 我们可以通过将查询分解为一个查找不同块的子查询来完成此任务，该子查询包装在一个汇总查询中，该汇总查询返回我们的答案：

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

那更好！ 因此，纽约一半以上的人口位于地铁500m以内（约5-7分钟的步行路程）。