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