14. 空间连接练习¶

这里提醒一下我们见过的一些功能。提示：它们应该对练习有用！

:command:`sum(expression)`聚合函数用于对一组记录进行求和并返回总和
count(expression): 聚合函数用于返回一组记录的大小
ST_Area(geometry) 返回多边形的面积
ST_AsText(geometry) 返回 WKT 格式的``text``
ST_Contains(geometry A, geometry B) 如果几何体A包含几何体B，则返回true
:command:`ST_Distance(geometry A, geometry B)`返回几何图形A和几何图形B之间的最小距离
:command:`ST_DWithin(geometry A, geometry B, radius)`如果几何图形 A 与几何图形 B 的距离为‘radius’或更近，则返回 true
ST_GeomFromText(text)`返回 ``geometry`
:command:`ST_Intersects(geometry A, geometry B)`如果几何图形A与几何图形B相交，则返回true
ST_Length(linestring) 返回线串的长度
:command:`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. 练习¶

“Little Italy”有哪个地铁站？它在哪条地铁路线上？

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 事件后，“Battery Park”社区连续几天禁止进入。有多少人需要疏散？

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

Introduction to PostGIS

14. 空间连接练习¶

14.1. 练习¶

导航