地理空间数据库课堂练习二

/*课堂练习二*//*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_routeswhere name='路75';/*4.获得鹅岛的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 islandwhere 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_segmentswhere 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_astext(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 islandwhere 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号路的长度*/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,zone zwhere ='路75' and ='阿诗顿';/*24.卡姆河流是否与蓝湖相接*/select st_touches(s.centerline,l.shore) /*1 l*/from streams s, lakes lwhere ='卡姆河'and b.address='主街215号';/*25.判断主街215号的建筑物是否在阿诗顿内*/select st_within(z.boundry,b.footprint)from zone z,building bwhere ='阿诗顿'and b.address='主街215号';/*26.判断102路段是否跨越75号路*/ /*s?*/select st_crosses(r.centerline,d.centerline)from road_segment r,divided_route dwhere r.fid=102 and = '路75';/*27.判断鹅岛与阿诗顿是否符合“TTTTTTTTT”的关系*/ select st_relate(z.boundary,i.boundary,'TTTTTTTTT') FROM ZONE Z,ISLAND IWHERE ='阿诗顿' and ='鹅岛';/*28.获得卡姆桥与阿诗顿的距离*/select st_distance(position,boundary)from bridge b,zone zwhere ='卡姆桥' and ='阿诗顿';/*29.获得卡姆桥与蓝湖相交部分的几何结构*/select st_astext(st_intersection(s.centerline,l.shore))from stream s,lakes lwhere ='卡姆河' and ='蓝湖';/*30.获得蓝湖和鹅岛并的几何结构*/select st_astext(st_union(l.shore,i.boundary))from lakes l,island iwhere ='蓝湖' and ='鹅岛';/*31.获得蓝湖和鹅岛对称差的几何结构*/select st_astext(st_symdifference(l.shore,i.boundary))from lake l,island iwhere = '蓝湖' and ='鹅岛';/*32.获得蓝湖的凸包*/select st_astext(st_convexhull(shore))from lakewhere name='蓝湖';/*33.找出距离Streams中河流中心线最近的一个区域*/ select ,from zone z1,streams s1where st_distance(z1.boundary,s1.centerline)<all (select st_distance(z2.boundary,s1.centerline)from zone z2where <>));/*34.找出中心线长度超过“主街”的所有路段*/select rs1,namefrom road_segments rs1where st_length(rs1.centerline)>any(select st_length(rs2.centerline)from road_segments rs2where <>'主街');/*35.列出数据中所有池塘和湖泊的名字和形状*/ select name,shoresfrom pondsunionselect name,shorefrom lakes。