更多更复杂的联合查询
在本节中,我们将对一般的空间查询做一些更复杂的事情。
创建一张人口普查表
我们已经在数据库中创建了一个名为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分钟的步行路程)。