利用 PostgreSQL/PostGIS扩展模块对空间网络数据（如河流网、铁路网、道路网等）连通关系进行管理，包括网络数据的存储、遍历、查询等。

要求：

（1）理解空间网络数据在空间数据库中的实现方法，包括存储机制、连通关系管理等；

（2）掌握 PostgresSQL 递归操作实现网络图的遍历或查询；

1 基于美国密西西比河流网络数据连通关系查询（递归）

注：上述查询是关注河流的流入流出关系，并不是河流的精确几何
形状；因此，之前所学 SQL 查询及空间查询是无法满足要求的。

Qs1: 如何用数据库管理这类空间网络数据？ 存储：有向图

查询：递归

Qs2：递归查询如何实现？

1. Connect By PRIOR 语句

基于语法规则：

CONNECT BY PRIOR <连通节点，及方向> START WITH <遍历起始节点>

注：Connect by 语句在 Oracle 支持，PostgreSQL 和 SQL Server

不支持；

查询结果如下：

P1 表示：Missouri 河流。

1. WITH RECURSIVE 语句

语法规则：

注：Oracle、PostgreSQL、SQL Server 均支持；

2 任务 1：请在 PostgreSQL 下用 WITH RECURSIVE 语句实现以下两题。

1、首先将美国湖泊网络数据表存储至 PostgreSQL

数据库，然后实现以下查询，并给出查询结果

P1 表示：Missouri 河流。

建立Falls_Into关系表

导入数据

（1）列出密西西比河在明尼苏达州境内的所有直接或间接支流的名称。

（2）列出科罗拉多河的所有直接支流。

（3）如果河流Pl溢洪，哪些河流会受到影响。Pl表示：Missouri 河流。

2、利用北美+南美行政区划图，完成以下查询，并给出查询结果。

查询各个国家的邻国

导出查询结果在Excel中进行去重处理，删除双向数据，仅留下单向数据，避免使用递归函数时产生死循环的状况。

创建美洲出发地目的地关系表，并导入处理好后的数据

（1）若从加拿大到阿根廷，最少要经过几个国家；

将加拿大的位深定为1，由下图查询可知从加拿大到阿根廷的路线中，阿根廷的最小位深为11。

从位深1到最小位深11，中间间隔有9个位深，故最少经过9个国家。也可通过下图查询直接得到。

（2）找出从陆路就能抵达美国的所有国家。

-- 创建关系表
CREATE TABLE Falls_Into
	(
		Source char(20),
		Dest   char(20)
	)
;

--列出密西西比河在明尼苏达州境内的所有直接或间接支流的名称。
WITH RECURSIVE X(source,depth) AS (
VALUES ('Mississippi',1)
UNION
	SELECT Y.source,X.depth+1
	FROM X,falls_into Y
	WHERE Y.dest=X.source)
SELECT * FROM X WHERE depth!=1;

WITH RECURSIVE X(Destination) AS (
VALUES ('B')
UNION
	SELECT Y.Destination
	FROM X,EDGE Y
	WHERE Y.Origin=X.Destination AND X.Destination<>'I')
)
SELECT * FROM X

--列出科罗拉多河的所有直接支流。
WITH RECURSIVE X(source,depth) AS (
VALUES ('Colorado',1)
UNION
	SELECT Y.source,X.depth+1
	FROM X,falls_into Y
	WHERE Y.dest=X.source)
SELECT * FROM X WHERE depth<=2 AND depth!=1;

--如果河流Pl溢洪，哪些河流会受到影响。Pl表示：Missouri 河流。
WITH RECURSIVE X(dest,depth) AS (
VALUES ('Missouri',1)
UNION
	SELECT Y.dest,X.depth+1
	FROM X,falls_into Y
	WHERE Y.source=X.dest)
SELECT * FROM X WHERE depth!=1;

--查询相邻关系
SELECT C1.cntry_name AS cntry_From,C2.cntry_name AS cntry_TO
FROM northamerica C1,northamerica C2
WHERE ST_Touches(C1.geom,C2.geom)=true;
--查询结果导入新表
SELECT C1.cntry_name AS cntry_From,C2.cntry_name AS cntry_TO
INTO America_FromTO
FROM northamerica C1,northamerica C2
WHERE ST_Touches(C1.geom,C2.geom)=true;
--创建关系表
CREATE TABLE America_FromTO
	(
		cntry_From character varying,
		cntry_TO character varying
	)
;

--到阿根廷经过的国家
WITH RECURSIVE X(cntry_From,depth) AS (
VALUES ('Argentina',1)
UNION
	SELECT Y.cntry_From,X.depth+1
	FROM X,America_FromTO Y
	WHERE Y.cntry_TO=X.cntry_From)
SELECT * FROM X

--从加拿大出发经过的国家；
WITH RECURSIVE X(cntry_To,depth) AS (
VALUES ('Canada',1)
UNION
	SELECT Y.cntry_To,X.depth+1
	FROM X,America_FromTO Y
	WHERE Y.cntry_From=X.cntry_To)
SELECT * FROM X

--若从加拿大到阿根廷，最少要经过几个国家；
WITH RECURSIVE X(cntry_To,depth) AS (
VALUES ('Canada',1)
UNION
	SELECT Y.cntry_To,X.depth+1
	FROM X,America_FromTO Y
	WHERE Y.cntry_From=X.cntry_To)
SELECT X.cntry_to,MIN(x.depth)-1 AS cntry_MIN
FROM X 
WHERE cntry_to='Argentina' 
GROUP BY X.cntry_to;

--找出从陆路就能抵达美国的所有国家。
WITH RECURSIVE X(cntry_To,depth) AS (
VALUES ('United States',1)
UNION
	SELECT Y.cntry_To,X.depth+1
	FROM X,America_FromTO Y
	WHERE Y.cntry_From=X.cntry_To)
SELECT X.cntry_to,MIN(x.depth) AS cntry_MIN
FROM X 
WHERE x.depth>1
GROUP BY X.cntry_to
ORDER BY cntry_MIN,X.cntry_to;