14. 空间连接练习¶

以下是一些我们已经见过的函数的提醒。提示：它们应该对练习很有用！

sum(expression): 聚合以返回一组记录的总和
count(expression): 聚合以返回一组记录的大小
ST_Area(geometry) 返回多边形的面积
ST_AsText(geometry) 返回 WKT text
ST_Contains(geometry A, geometry B) 如果几何 A 包含几何 B，则返回 true
ST_Distance(geometry A, geometry B) 返回几何 A 和几何 B 之间的最小距离
ST_DWithin(geometry A, geometry B, radius) 如果几何 A 与几何 B 的距离小于或等于半径，则返回 true
ST_GeomFromText(text) 返回 geometry
ST_Intersects(geometry A, geometry B) 如果几何 A 与几何 B 相交，则返回 true
ST_Length(linestring) 返回线字符串的长度
ST_Touches(geometry A, geometry B) 如果几何 A 的边界与几何 B 相切，则返回 true
ST_Within(geometry A, geometry B) 如果几何 A 在几何 B 内，则返回 true

还要记住我们可用的表

nyc_census_blocks
- name, popn_total, boroname, geom
nyc_streets
- name, type, geom
nyc_subway_stations
- name, routes, geom
nyc_neighborhoods
- name, boroname, geom

14.1. 练习¶

哪个地铁站在“小意大利”？它在哪个地铁线路？

SELECT s.name, s.routes
FROM nyc_subway_stations AS s
JOIN nyc_neighborhoods AS n
ON ST_Contains(n.geom, s.geom)
WHERE n.name = 'Little Italy';

   name    | routes
-----------+--------
 Spring St | 6

6 号线服务哪些街区？（提示：nyc_subway_stations 表中的 routes 列具有诸如“B,D,6,V”和“C,6”之类的值）

SELECT DISTINCT n.name, n.boroname
FROM nyc_subway_stations AS s
JOIN nyc_neighborhoods AS n
ON ST_Contains(n.geom, s.geom)
WHERE strpos(s.routes,'6') > 0;

        name        | boroname
--------------------+-----------
 Midtown            | Manhattan
 Hunts Point        | The Bronx
 Gramercy           | Manhattan
 Little Italy       | Manhattan
 Financial District | Manhattan
 South Bronx        | The Bronx
 Yorkville          | Manhattan
 Murray Hill        | Manhattan
 Mott Haven         | The Bronx
 Upper East Side    | Manhattan
 Chinatown          | Manhattan
 East Harlem        | Manhattan
 Greenwich Village  | Manhattan
 Parkchester        | The Bronx
 Soundview          | The Bronx

注意

我们使用 DISTINCT 关键字从结果集中删除重复值，其中一个街区中有多个地铁站。

9/11 事件后，“电池公园”社区被封锁了几天。有多少人被疏散了？

SELECT Sum(popn_total)
FROM nyc_neighborhoods AS n
JOIN nyc_census_blocks AS c
ON ST_Intersects(n.geom, c.geom)
WHERE n.name = 'Battery Park';

哪个社区的人口密度（人/平方公里）最高？

SELECT
  n.name,
  Sum(c.popn_total) / (ST_Area(n.geom) / 1000000.0) AS popn_per_sqkm
FROM nyc_census_blocks AS c
JOIN nyc_neighborhoods AS n
ON ST_Intersects(c.geom, n.geom)
GROUP BY n.name, n.geom
ORDER BY popn_per_sqkm DESC LIMIT 2;

      name       |  popn_per_sqkm
-------------------+------------------
 North Sutton Area | 68435.13283772678
 East Village      | 50404.48341332535

PostGIS 简介

14. 空间连接练习¶

14.1. 练习¶

导航