Chapter 01_Create table and insert data

最新推荐文章于 2022-10-24 14:29:13 发布

小呆呆同学

最新推荐文章于 2022-10-24 14:29:13 发布

阅读量460

点赞数 2

分类专栏： postgis 文章标签： postgresql

本文链接：https://blog.csdn.net/populartutu/article/details/116953391

版权

postgis 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

写在文章前面：以下操作均在Postgis里实现；
一、创建名为DavidB.Smith的数据库
二、创建以下表格，并插入数据
1.lakes

create table lakes(fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),shore geometry);
insert into lakes values(101,'蓝湖',st_geomfromtext('multipolygon(((52 18,66 23,73 9,48 6,52 18),(59 18,67 18,67 13,59 13,59 18)))',26918));
insert into lakes values(0,'图外其他湖泊');

2.road_segments

create table road_segments(fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),aliases VARCHAR(64),num_lanes INTEGER,centerline GEOMETRY);
insert into road_segments values(102,'路5',null,2,st_geomfromtext('linestring(0 18,10 21,16 23,28 26,44 31)',26918));
insert into road_segments values(103,'路5','主街',4,st_geomfromtext('linestring(44 31,56 34,70 38)',26918));
insert into road_segments values(104,'路5',null,2,st_geomfromtext('linestring(70 38,72 48)',26918));																
insert into road_segments values(105,'主街',null,4,st_geomfromtext('linestring(70 38,84 42)',26918));
insert into road_segments values(106,'绿森林边路',null,1,st_geomfromtext('linestring(28 26,28 0)',26918));

3.divided_routes

create table divided_routes(fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),num_lanes INTEGER,centerline GEOMETRY);
insert into divided_routes values(119,'路75',4,st_geomfromtext('multilinestring((10 48,10 21,10 0),(16 0,16 23,16 48))',26918))；

4.bridges

create table bridges(fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),roadseg1id INTEGER REFERENCES road_segments,roadseg2id INTEGER REFERENCES road_segments,position GEOMETRY);
insert into bridges values(110,'卡姆桥',102,103,st_geomfromtext('point(44 31)',26918));

5.streams

create table streams(fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),fromlakeid INTEGER REFERENCES lakes,tolakeid INTEGER REFERENCES lakes,centerline GEOMETRY);
insert into streams values(111,'卡姆河',0,101,st_geomfromtext('linestring(38 48,44 41,41 36,44 31,52 18)',26918));
insert into streams values(112,null,101,0,st_geomfromtext('linestring(76 0,78 4,73 9)',26918));

6.buildings

create table buildings(fid INTEGER NOT NULL PRIMARY KEY,Address VARCHAR(64),position GEOMETRY,footprint GEOMETRY);
insert into buildings values(113,'主街123号',st_geomfromtext('point(52 30)'),st_geomfromtext('polygon((50 31,54 31,54 29,50 29,50 31))',26918));
insert into buildings values(114,'主街215号',st_geomfromtext('point(64 33)'),st_geomfromtext('polygon((66 34,62 34,62 32,66 32,66 34))',26918));

7.ponds

create table ponds(fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),type VARCHAR(64),shores GEOMETRY);
insert into ponds values(120,null,'思道哥池塘',st_geomfromtext('multipolygon(((24 44,22 42,24 40,24 44),(26 44,26 40,28 42,26 44)))',26918));

8.island

create table island(fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),lakeid INTEGER REFERENCES lakes,boundary GEOMETRY);
insert into island values(109,'鹅岛',101,st_geomfromtext('polygon((67 13,67 18,59 18,59 13,67 13))',26918));

9.zone

create table zone(fid INTEGER NOT NULL PRIMARY KEY,name VARCHAR(64),boundary GEOMETRY);
insert into zone values(117,'阿诗顿',st_geomfromtext('multipolygon(((62 48,84 48,84 30,56 30,56 34,62 48)))',26918));
insert into zone values(118,'绿森林',st_geomfromtext('multipolygon(((28 26,28 0,84 0,84 42,28 26),(52 18,66 23,73 9,48 6,52 18),(59 18,67 18,67 13,59 13,59 18)))',26918));