《地理空间数据库课堂练习二》由会员分享，可在线阅读，更多相关《地理空间数据库课堂练习二（5页珍藏版）》请在金锄头文库上搜索。
1、/*课堂练习二*/*1.查找本数据库中所有的空间表*/select f_table_namefrom geometry_columns;/*2.查找 streams 表的空间参考系的授权 */select s.auth-sridfrom spatial_ref_sys s,geometry_columns gwhere g.f_table_name=streams and s.srid=g.srid;/*3.获得 75 号路的几何类型*/select st_geometrytype(centerlines)from divided_routes where name=路 75;/*4.获得鹅岛
2、的 WKT 表示*/select st_astext(boundary)from islandwhere name=鹅岛;/*5.判断名为路 5，别名为“ 主街”的路段的几何属性是否为空 */select st_isempty(centerline)from road_segmentswhere name=路 5 AND aliases = 主街;/*6.蓝湖的几何结构是否简单的*/select st_issimple(shore)from lakewhere name=蓝湖;/*7.获得鹅岛的边界*/select st_astext(st_boundary(boundary)from isl
3、andwhere name = 鹅岛;/*8.获得鹅岛的 MBR 边界*/select st_astext(st_envelope(boundary)from islandwhere name=鹅岛;/*9.获得卡姆桥的 x，y 坐标*/select st_x(position),st_Y(position)from bridgeswhere name=卡姆桥;/*10.获得 102 路段的起点和终点*/select st_astext(st_startpoint(centerline),st_astext(st_endpoint(centerline)from road_segmentswh
4、ere fid=102;/*11.判断鹅岛的 MBR 边界是否闭合*/select st_isclosed(st_boundary(boundary)from islandwhere name=鹅岛;/*12.获得 106 路段的长度*/select st_length(centerline)from road_segmentswhere fid=106;/*13.获得 102 路段中点的数目*/select st_numpoints(centerline)from road_segmentswhere fid=102;/*14.获得 102 路段中点的第一个点*/select st_aste
5、xt(st_pointn(centerline,1)from road_segmentswhere fid=102;/*15.获得鹅岛的质心*/select st_astext(st_centroid(boundary)from islandwhere name=鹅岛;/*16.判断 PointOnSurface 函数返回鹅岛上的点是否在其边界内*/select st_contains(boundary,st_pointonsurface(boundary)from islandwhere name=鹅岛;/*17.获得鹅岛的面积*/select st_area(boundary)from i
6、slandwhere name=鹅岛;/*18.获得蓝湖内环的数目*/select st_numinteriorring(shore)from lakeswhere name=蓝湖;/*19.判断 75 号路的几何元素的数目 */select st_numgeometries(centerlines)from divided_routeswhere name=路 75;/*20.获得 75 号路的第 2 个几何元素*/select st_astext(st_geometryN(centerlines,2)from divided_routeswhere name=路 75;/*21.获得 75
7、 号路的长度*/select st_length(centerline)from divided_routewhere name=路 75;/*22.判断鹅岛是否与其 WKT 表示构造的同样几何结构是否相等*/select st_equals(boundary,st_polyfromtext(polygon(67 13,67 18,59 18,59 13,67 13),101)from islandwhere name=鹅岛;/*23.判断 75 号路是否与阿诗顿相离 */select st_disjoint(centerlines,boundary)from divided_routes d
8、,zone zwhere d.name=路 75 and z.name=阿诗顿;/*24.卡姆河流是否与蓝湖相接*/select st_touches(s.centerline,l.shore) /*1 l*/from streams s, lakes lwhere s.name=卡姆河and b.address=主街 215 号;/*25.判断主街 215 号的建筑物是否在阿诗顿内*/select st_within(z.boundry,b.footprint)from zone z,building bwhere z.name=阿诗顿and b.address=主街 215 号;/*26.
9、判断 102 路段是否跨越 75 号路*/ /*s?*/select st_crosses(r.centerline,d.centerline)from road_segment r,divided_route dwhere r.fid=102 and d.name = 路 75;/*27.判断鹅岛与阿诗顿是否符合“TTTTTTTTT”的关系*/select st_relate(z.boundary,i.boundary,TTTTTTTTT)FROM ZONE Z,ISLAND IWHERE Z.NAME=阿诗顿 and i.name=鹅岛;/*28.获得卡姆桥与阿诗顿的距离*/select
10、st_distance(position,boundary)from bridge b,zone zwhere b.name=卡姆桥 and z.name=阿诗顿;/*29.获得卡姆桥与蓝湖相交部分的几何结构*/select st_astext(st_intersection(s.centerline,l.shore)from stream s,lakes lwhere s.name=卡姆河 and l.name=蓝湖;/*30.获得蓝湖和鹅岛并的几何结构*/select st_astext(st_union(l.shore,i.boundary)from lakes l,island iwh
11、ere l.name=蓝湖 and i.name=鹅岛;/*31.获得蓝湖和鹅岛对称差的几何结构*/select st_astext(st_symdifference(l.shore,i.boundary)from lake l,island iwhere l.name = 蓝湖 and i.name=鹅岛;/*32.获得蓝湖的凸包*/select st_astext(st_convexhull(shore)from lakewhere name=蓝湖;/*33.找出距离 Streams 中河流中心线最近的一个区域 */select z1.name,s1.namefrom zone z1,st
12、reams s1where st_distance(z1.boundary,s1.centerline)z2.name);/*34.找出中心线长度超过“主街 ”的所有路段*/select rs1,namefrom road_segments rs1where st_length(rs1.centerline)any(select st_length(rs2.centerline)from road_segments rs2where rs2.name主街);/*35.列出数据中所有池塘和湖泊的名字和形状*/select name,shoresfrom pondsunionselect name,shorefrom lakes
