利用 PostgreSQL数据库实现对空间数据库的相关操作，包括数据库创建、关系表创建和查询等基本操作。

要求：

1. 理解空间数据库设计流程，能够完成简单空间数据库的设计；

2. 掌握空间数据的创建、数据导入（包括单个手动导入和批量数据导入），能够根据已设计空间数据信息，实施和创建空间数据库。

任务 1：根据下述说明创建美国湖泊空间数据库，并导入相关数据。

湖泊区域地物要素分布及坐标如下：

要素关系说明：

（1）图示区域为地球上通用横轴墨卡托投(UniversalTransverse
Mercator,UTM)坐标下的一个矩形。水平坐标系统32214号。注意WGS72/UTM
14带东伪偏移值为500000m，单位为m。

（2）“鹅岛”的“蓝湖”是该地区的重要要素。

（3）有一条从北到南的水系。从上面注入湖的部分叫“卡姆河”，从湖下面出来的那部分没有名字。

（4）这里有个区域叫“阿诗顿”。

（5）州属森林管理的区域包括湖和阿诗顿的一部分。形成了州属森林的边界。其中，“绿森林”等于种树森林减去湖。

（6）5号路延伸出了地图。

（7）组合的75号路高速路用粗的双黑线表示，每条线是被分离高速路的一部分。这两条路被视为多线。

（8）跨越卡姆河的桥叫“卡姆桥”，被视为点对象。

（9）与5 号路共享一段路的主街总是四车道宽的。

（10）沿着主街有两个建筑物，他们被视为点，或者被视为矩形区域。

（11）一车道的路形成了周树森林边界的一部分，如带黑边的灰色区域。

（12）这里有两个鱼池，他们不是独立的，而是一起的，故是多多边形。

各要素空间 ER 如下所示：

E-R 转关系表：

Answers:

Step1、美国湖泊空间数据库的创建

先创建常规数据库，命名为“American Lake”，然后通过SQL输入“CREATE EXTENSION
postgis;”语句，实现“空间数据库扩展”模块的加载;

Step2：创建逻辑数据表

1.完成“湖泊”表的创建

2.完成“路段”表的创建

3.完成“组合路段”表的创建

4.完成“建筑物”表的创建

5.完成“池塘”表的创建

6.完成“区域”表的创建

7.完成“森林”表的创建

8.完成“河流”表的创建

9.完成“桥”表的创建

10.完成“岛”表的创建

Step3：插入数据

1.插入“湖泊”数据

2.插入“路段”数据

3.插入“组合路段”数据

4.插入“森林”数据

5.插入“建筑物”数据

6.插入“池塘”数据

7.插入“区域”数据

8.插入“河流”的数据

9.插入“桥”数据

10.插入“岛”数据

11.通过QGIS查看创建的空间数据库图层

得到最后结果为

任务 2：基于任务 1 建立的空间数据库，完成以下查询：

1. 查看鹅岛的 WKT；

1. 列出你安装的 POSTGIS 自带空间数据参考名称；

1. 判断蓝湖的结构是否是简单的；

1. 获取鹅岛的边界和 MBR，并展示（用 QGIS）；

1. 如果卡姆河发洪水会淹没周边 1km 范围区域，判断上图两个建筑物是否会被淹；
   （UTM投影 单位为米）

任务 3：把“作业二”的相关查询，在 POSTGIS 中实现，并查看相关查询结果。

Step1：SHP文件导入PostGIS

Step2：空间查询

1、用SSQL为world数据库创建Country、River、City三个表格；

通过导入SHP文件，已建好表格。

2、找出美国（USA）的邻国，给出邻国名称；

3、给出River表中存储的所有河流流经的国家名称。

4、查收距离河流最近的城市。

5、找出圣劳伦斯河能够在300km范围内可以为哪些城市供水。

6、计算Country表中国家的人口和国土面积。

7、求出河流所流经的各国境内的长度。

8、给出各国家的GDP以及其首都到赤道的距离。

9、列出各国家邻国数量；

10、列出只有一个邻国的国家。

11、哪一个国家邻国最多。

12、列出距离华盛顿特区5000km以内的城市；

13、位于阿根廷和巴西的Rio Paranas河的长度是多少？

14、列出完全在赤道以南的国家。

15、阿根廷和巴西接壤吗？

SQL代码

--创建关系表
CREATE TABLE lakes(
fid integer PRIMARY KEY,
name character(64),
shore geometry(MultiPolygon,32214)
);

CREATE TABLE road_segments(
fid integer PRIMARY KEY,
name character(64),
alases character(64),
wide double precision,
num_lanes integer,
centerline geometry(LineString,32214)
);

CREATE TABLE divided_routes(
fid integer PRIMARY KEY,
name character(64),
num_lanes integer,
centerline geometry(MultiLineString,32214)
);

CREATE TABLE buildings(
fid integer PRIMARY KEY,
name character(64),
position geometry(point,32214),
footpront geometry(polygon,32214)
);

CREATE TABLE ponds(
fid integer PRIMARY KEY,
name character(64),
type character(64),
shores geometry(Multi Polygon,32214)
);

CREATE TABLE name_places(
fid integer PRIMARY KEY,
name character(64),
boundary geometry(MultiPolygon,32214)
);

CREATE TABLE forests(
fid integer PRIMARY KEY,
name character(64),
boundary_grid geometry(MultiPolygon,32214)
);


CREATE TABLE streams(
fid integer,
name character(64),
fromlakeid integer,
tolakeid integer,
centerline geometry(LineString ,32214),
PRIMARY KEY(fid),
FOREIGN KEY(fromlakeid) REFERENCES lakes(fid),
FOREIGN KEY(tolakeid) REFERENCES lakes(fid)
);

CREATE TABLE bridges(
fid integer PRIMARY KEY,
name character(64),
streamid integer,
roadseg1id integer,
roadseg2id integer,
position geometry(Point ,32214),
FOREIGN KEY(streamid) REFERENCES streams(fid),
FOREIGN KEY(roadseg1id)REFERENCES road_segments(fid),
FOREIGN KEY(roadseg2id)REFERENCES road_segments(fid)
);

CREATE TABLE islands(
fid integer,
name character(64),
lakeID integer,
footpront geometry(MultiPolygon,32214),
PRIMARY KEY(fid),
FOREIGN KEY(lakeID) REFERENCES lakes(fid)
);


--插入数据
INSERT INTO lakes(fid,name,shore) 
VALUES(101,'{BLUE LAKE}',ST_GeomFromText('MultiPolygon(((52 18,66 23,73 9,48 6,52 18),(59 18,67 18,67 13,59 19,59 18)))',32214));
--
INSERT INTO road_segments
VALUES(102,'{Route 5}',NULL,10,2,ST_GeomFromText('LineString(0 18,10 21,16 23,28 26,44 31)',32214));

INSERT INTO road_segments
VALUES(103,'{Route 5}','{Main Street}',20,4,ST_GeomFromText('LineString(44 31,56 34,70 38)',32214));

INSERT INTO road_segments
VALUES(104,'{Route 5}',NULL,10,2,ST_GeomFromText('LineString(70 38,72 48)',32214));

INSERT INTO road_segments
VALUES(105,'{Main Street}',NULL,20,4,ST_GeomFromText('LineString(70 38,84 42)',32214));

INSERT INTO road_segments
VALUES(106,'{Dirt Road by Green Forest}',NULL,5,1,ST_GeomFromText('LineString(28 26, 28 0)',32214));
--
INSERT INTO divided_routes
VALUES(119,'{Route 75}',4,ST_GeomFromText('MultiLineString((10 48,10 21,10 0),(16 0,16 23,16 48))',32214));

INSERT INTO forests
VALUES(109,'{Green Forest}',ST_GeomFromText('MultiPolygon(((28 26,28 0,84 0,84 50,62 50,56 34,44 31,28 26),(52 18,66 23,73 9,48 6,52 18)))',32214));
--
INSERT INTO buildings
VALUES(113,'{123 Main Street}',ST_GeomFromText('Point(52 30)',32214),ST_GeomFromText('Polygon((50 31,54 31,54 29,50 29,50 31))',32214));

INSERT INTO buildings
VALUES(114,'{215 Main Street}',ST_GeomFromText('Point(64 33)',32214),ST_GeomFromText('Polygon((66 34,62 34,62 32,66 32,66 34))',32214));
--
INSERT INTO ponds
VALUES(120,'{Stock Pond}',NULL,ST_GeomFromText('MultiPolygon(((24 44,22 42,24 40,24 44)),((26 44,28 42,26 40,26 44)))',32214));
--
INSERT INTO name_places
VALUES (117,'{Ashton}',ST_GeomFromText('MultiPolygon(((62 48,84 48,84 30,56 30,56 34,62 48)))',32214));

INSERT INTO name_places
VALUES (115,'{Map Neatlines}',ST_GeomFromText('MultiPolygon(((0 0,0 48,84 48,84 0,0 0)))',32214));
--
INSERT INTO streams
VALUES(111,'{Cam Stream}',101,NULL,ST_GeomFromText('LineString(38 48,44 41,41 36,44 31,52 18)',32214));

INSERT INTO streams
VALUES(112,'{Cam Stream}',101,NULL,ST_GeomFromText('LineString(76 0,78 4,73 9)',32214));
--
INSERT INTO bridges
VALUES(110,'{Cam Bridges}',111,102,103,ST_GeomFromText('Point(44 31)',32214));
--
INSERT INTO islands
VALUES(116,'{Goose island}',NULL,ST_GeomFromText('MultiPolygon(((59 13,59 18,67 18,67 13,59 13)))',32214));

--（1）查看鹅岛的 WKT；
SELECT ST_astext(footpront)
FROM islands
WHERE name='{Goose island}';
--（2）列出你安装的 POSTGIS 自带空间数据参考名称；
SELECT Srtext
FROM spatial_ref_sys;
--（3）判断蓝湖的结构是否是简单的； 
SELECT ST_ISSIMPLE(Shore)
FROM Lakes
WHERE name='{BLUE LAKE}';
--（4）获取鹅岛的边界和 MBR，并展示（用 QGIS）； 
SELECT ST_boundary(footpront)
FROM islands
WHERE name='{Goose island}';

SELECT ST_OrientedEnvelope(footpront)
FROM islands
WHERE name='{Goose island}';
--（5）如果卡姆河发洪水会淹没周边 1km 范围区域，判断上图两个建筑物是否会被淹； 
SELECT ST_Overlaps(buildings.footpront,ST_Buffer(Streams.centerline,1000)))
FROM streams,buildings
WHERE Streams.name='{Cam Stream}';


--找出美国（USA）的邻国，给出邻国名称；
SELECT C1.cntry_name AS "Neighbors of USA"
FROM northamerica C1,northamerica C2
WHERE ST_Touches(C1.geom,C2.geom)= true AND C2.cntry_name='United States';

--给出River表中存储的所有河流流经的国家名称。
SELECT R.name,C.cntry_name
FROM northamerica_rivers R,northamerica C
WHERE ST_Crosses(R.geom,C.geom);

--查收距离河流最近的城市。
SELECT C1.city_name,R1.name
FROM northamerica_cities C1,northamerica_rivers R1
WHERE ST_distance(C1.geom,R1.geom) <
ALL(SELECT ST_distance(C2.geom,R1.geom)
FROM northamerica_cities C2
WHERE C1.city_name<>C2.city_name);

--找出圣劳伦斯河能够在300km范围内可以为哪些城市供水。
SELECT Ci.city_name
FROM northamerica_cities Ci,northamerica_rivers R
WHERE ST_Overlaps(Ci.geom,ST_Buffer(R.geom,300000/(6371004*PI()/180)))=true AND R.name='St. Lawrence'; 

SELECT Ci.city_name
FROM northamerica_cities Ci,northamerica_rivers R
WHERE ST_Contains(ST_Buffer(R.geom,300000/(6371004*PI()/180)),Ci.geom)
AND R.name='St. Lawrence';

--计算Country表中国家的人口和国土面积。
SELECT C.cntry_name,C.Pop_cntry,C.SQKM AS "Area"
FROM northamerica C;

--列出各国家邻国数量；
SELECT Co.cntry_name,COUNT (Co1.cntry_name)
FROM northamerica Co,northamerica Co1
WHERE ST_touches (Co.geom,Co1.geom)
GROUP BY Co.cntry_name;

--列出只有一个邻国的国家。
SELECT Co.cntry_name
FROM northamerica CO
WHERE Co.cntry_name in
(SELECT Co.cntry_name
FROM northamerica CO,northamerica Co1
WHERE ST_touches (Co.geom,Co1.geom)
group by Co.cntry_name
HAVING Count(*)=1);

--哪一个国家邻国最多。
CREATE VIEW neighbor AS
SELECT C1.cntry_ name, COUNT(C2.cntry_ name) AS num_ nei ghbor
FROM northamerica C1，northamerica C2
where ST_touches(C1.geom,C2.geom)
GROUP BY C1.cntry_ name;
SELECT cntry_name,num_neighbor
FROM neighbor
WHERE num_neighbor = (
		SELECT MAX(num_neighbor)
		FROM neighbor
		);

--列出距离华盛顿特区5000km以内的城市；
SELECT C2.city_name
FROM northamerica_cities C1,northamerica_cities C2
WHERE ST_Distance (C2.geom,C1.geom) <(5000/(6371004*PI()/180))
AND C1.city_name='Washington D.C.';

--位于阿根廷和巴西的Rio Paranas河的长度是多少？
CREATE VIEW rivercountrylength AS
SELECT C1.cntry_name,ST_Length(ST_intersection (C1.geom,C2.geom)) AS length
FROM northamerica C1, northamerica_rivers C2
WHERE C2.Name='Rio Paronas';
SELECT C1.length + C2.length
FROM rivercountrylength C1,rivercountrylength C2
WHERE C1.cntry_name='Argentina'AND C2.cntry_name='Brazil';

--求出河流所流经的各国境内的长度。
SELECT R.name,C.cntry_name,ST_Length(ST_Intersection(R.geom,C.geom))
AS"Length"
FROM northamerica_rivers R,northamerica C
WHERE ST_Crosses(R.geom,C.geom)=true;

--列出完全在赤道以南的国家
SELECT C.cntry_name
FROM northamerica C
WHERE ST_Within(C.geom,ST_GeomFromText('POLYGON((00 00,-360 00,-360 -90,00 -90,00 00))',4326))=true;

--阿根廷和巴西接壤吗？
SELECT ST_Touches(C1.geom,C2.geom)
FROM northamerica C1,northamerica C2
WHERE C1.cntry_name='Argentina' AND C2.cntry_name='Brazil';