Class 9 空间关系

1 St_Equals

判断两个geometry是否完全相等

2 St_Intersects

如果两个图形有相同的部分，即他们的边界或者内部相交，即返回true

以宽街地铁站（Broad Street subway station）为例，使用ST_Intersects()函数确定其所在社区。(也就是说求一个点，是否是在某一个面上)

（1）首先获取Broad Street subway station的地理信息：

SELECT name,st_astext(geom) from nyc_subway_stations where name = 'Broad St';
-- Broad St
-- POINT(583571.905921312 4506714.34119218)
-- 0101000020266900000EEBD4CF27CF2141BC17D69516315141

（2）

SELECT name,boroname from nyc_neighborhoods where st_intersects(geom, '0101000020266900000EEBD4CF27CF2141BC17D69516315141');

3 St_Disjoint

这个方法与intersect相反，即两个图片没有重合，就返回true；

ST_Crosses 以两个 ST_Geometry 对象作为输入，如果这两个对象的交集生成的几何对象的维度小于两个源对象中的最大维度，则返回 1（Oracle 和 SQLite）或 t (PostgreSQL)。**交集对象所包含的点必须在两个源几何的内部，并且不等于其中任何一个源对象。**否则，返回 0（Oracle 和 SQLite）或 f (PostgreSQL)。

4 St_Overlaps

**ST_Overlaps(geometry A, geometry B)**比较两个相同维度的几何图形，如果它们的结果集与两个源几何图形都不同但具有相同维度，则返回TRUE。

5 St_Touches

判断两个几何图形是否在边界上相接触，但是内部没有相交。

6 ST_Within和ST_Contains

如果第一个图形完全位于第二个图形的内部，则Within返回true；

如果第二个图形完全位于第一个图形的内部，那么Contains返回true；

7 ST_Distance和ST_DWithin

（1）distance用于计算两个geom之间的最短距离；

（2）使用我们的宽街地铁站（Broad Street subway station），我们可以找到地铁站附近（10米内）的街道：

SELECT name from nyc_streets where 
st_dwithin(geom, '0101000020266900000EEBD4CF27CF2141BC17D69516315141',10);

Class 10 空间连接

简单的说，就是利用外键来进行空间上的连接。这一章很重要。

（1）判断Broad St地铁站所在的neighbor和boroname

SELECT
	subway. NAME AS subway_name,
	neighbor. NAME AS neighbor_name,
	neighbor.boroname AS boroname
FROM
	nyc_neighborhoods AS neighbor
JOIN nyc_subway_stations AS subway ON st_intersects (subway.geom, neighbor.geom)
WHERE
	subway. NAME = 'Broad St' 
--- 这里要注意的是 on的执行时间要比where早，也就是说where是在on中的结果再筛选

1 连接和汇总

问：曼哈顿行政区的各个社区的人口和种族构成是什么？

SELECT
	neighbor. NAME AS neighbor_name,
	SUM (blocks.popn_total) AS total,
	100.0 * SUM (blocks.popn_white) / SUM (blocks.popn_total) AS white_pct,
	100.0 * SUM (blocks.popn_black) / SUM (blocks.popn_total) AS black_pct
FROM
	nyc_neighborhoods AS neighbor
JOIN nyc_census_blocks AS blocks ON st_intersects (neighbor.geom, blocks.geom)
WHERE
	neighbor.boroname = 'Manhattan'
GROUP BY
	neighbor. NAME
ORDER BY
	white_pct DESC;

2 Example 1

"小意大利（Little Italy）社区"有什么地铁站？它在哪些地铁线路上？

SELECT
	neighbor. NAME AS neighbor_name,
	subway. NAME AS subway,
	subway.routes AS routes
FROM
	nyc_neighborhoods AS neighbor
JOIN nyc_subway_stations AS subway ON st_intersects (neighbor.geom, subway.geom)
WHERE
	neighbor. NAME = 'Little Italy'

3 Example 2

"9/11事件后，'Battery Park'社区附近将禁止入内几天，那么要疏散多少人呢？"

SELECT
	SUM (blocks.popn_total) AS total
FROM
	nyc_census_blocks AS blocks
JOIN nyc_neighborhoods AS neighborhoods ON st_intersects (
	blocks.geom,
	neighborhoods.geom
)
WHERE
	neighborhoods. NAME = 'Battery Park';

4 Example 3

"Upper West Side'和'Upper East Side'的人口密度是多少"

SELECT
	n. NAME,
	SUM (C .popn_total) / (
		st_area ((n.geom)) / 1000000.0
	) AS popu
FROM
	nyc_census_blocks AS C
JOIN nyc_neighborhoods AS n 
ON st_intersects (C .geom, n.geom)
WHERE
	n. NAME = 'Upper West Side'
OR n. NAME = 'Upper East Side'
GROUP BY
	n. NAME,
	n.geom