地理空间数据库:空间数据库的创建、查询及其相关基本操作

利用 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';

GISer, a novice who is learning hard
博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 ( CC 4.0 BY-SA ) 协议
本文永久链接是: https://blog.manchan.top/post/di-li-kong-jian-shu-ju-ku-er/