SQL-Server空间数据库应用案例报告

实验四SQLServer空间数据库应用案例一、实验学时4学时二、实验目的1.了解SQLServer2008r2的空间参考系统表；2.掌握SQLServer2008r2的空间数据类型的使用；3.掌握空间数据库与数据表的设计与实现4.掌握用SQL实现空间查询与分析功能的方法三、预习内容1.SQLServer有关空间操作的教程2.教材中有关SQL语言的关于空间查询与分析的语法章节四、实验设备及数据1.安装了SQLServer2008r2的电脑2.教材第五章的空间数据库实例数据五、实验内容1.SQLServer2008的空间参考系统表的介绍2空间数据类型的介绍与使用3.空间数据库与数据表的创建与修改4.使用SQL语句添加、删除、修改空间数据记录5.用SQL实现空间查询和分析六、实验步骤建蓝湖数据库createtablelakes(fidintegernotnullprimarykey,namevarchar(64),shoregeometry);createtableroad_segments(fidintegernotnullprimarykey,namevarchar(64),alisesvarchar(64),num_lanesinteger,centerlinegeometry);createtabledivided_routes(fidintegernotnullprimarykey,namevarchar(64),roadseg1idintegerreferencesroad_segments,roadseg2idintegerreferencesroad_segments,positiongeometry);createtablebridges(fidintegernotnullprimarykey,namevarchar(64),roadseg1idintegerreferencesroad_segments,roadseg2idintegerreferencesroad_segments,positiongeometry);createtablestreams(fidintegernotnullprimarykey,namevarchar(64),fromlakeidintegerreferenceslakes,tolakeidintegerreferenceslakes,centerlinegeometry);createtablebuildings(fidintegernotnullprimarykey,addressvarchar(64),positiongeometry,footprintgeometry);createtablepoods(fidintegernotnullprimarykey,namevarchar(64),typevarchar(64),shoresgeometry);createtableisland(fidintegernotnullprimarykey,namevarchar(64),lakeidintegerreferenceslakes,boundarygeometry);createtablezone(fidintegernotnullprimarykey,namevarchar(64),boundarygeometry);录入数据insertintolakesvalues(101,'蓝湖',geometry::STGeomFromText('multipolygon(((5218,6623,739,486,5218),(5918,6718,6713,5913,5918)))',101));insertintolakesvalues(0,'图片外其他湖泊',geometry::STGeomFromText('multipolygon(((6228,7633,8319,5816,6228),(6928,7728,7723,6923,6928)))',101));--路段insertintoroad_segmentsvalues(102,'路',null,2,geometry::STGeomFromText('linestring(018,1021,1623,2826,4431)',101));insertintoroad_segmentsvalues(103,'路','主街',4,geometry::STGeomFromText('linestring(4431,5634,7038)',101));insertintoroad_segmentsvalues(104,'路',null,2,geometry::STGeomFromText('linestring(7038,7248)',101));insertintoroad_segmentsvalues(105,'主街',null,4,geometry::STGeomFromText('linestring(7038,8442)',101));insertintoroad_segmentsvalues(106,'绿森林边路',null,1,geometry::STGeomFromText('linestring(2826,280)',101));--组合路insertintodivided_routesvalues(119,'路',null,4,geometry::STGeomFromText('multilinestring((1048,1021,100),(160,1623,1648))',101));--桥insertintobridgesvalues(110,'卡姆桥',102,103,geometry::STGeomFromText('point(4431)',101));insertintostreamsvalues(111,'卡姆河',0,101,geometry::STGeomFromText('linestring(3848,4441,4136,4431,5218)',101));insertintostreamsvalues(112,null,101,0,geometry::STGeomFromText('linestring(760,784,739)',101));insertintobuildingsvalues(113,'主街号',geometry::STGeomFromText('point(5230)',101),geometry::STGeomFromText('polygon((5031,5431,5429,5029,5031))',101));insertintobuildingsvalues(114,'主街号',geometry::STGeomFromText('point(6433)',101),geometry::STGeomFromText('polygon((6634,6234,6232,6632,6634))',101));insertintopoodsvalues(120,null,'思道哥池塘',geometry::STGeomFromText('multipolygon(((2444,2242,2440,2444)),((2644,2640,2842,2644)))',101));insertintoislandvalues(109,'鹅岛',101,geometry::STGeomFromText('multipolygon(((6713,6718,5918,5913,6713)))',101));--区域insertintozonevalues(117,'阿诗顿',geometry::STGeomFromText('multipolygon(((6248,8448,8430,5630,5634,6248)))',101));insertintozonevalues(118,'绿森林',geometry::STGeomFromText('multipolygon(((2826,280,840,8442,2826),(5218,6623,739,486,5218)),((5918,6718,6713,5913,5918)))',101));查询获得鹅岛的wkt表示selectboundary.STAsText()fromislandwherename='鹅岛'查找本数据库中所有的空间表SelectTABLE_NAMEFROMspatial.INFORMATION_SCHEMA.TABLESWhereTABLE_TYPE='BASETABLE'判断名为路5别名为“主街”的路段的几何属性是否为空selectcenterline.STIsEmpty()fromroad_segmentswherename='路'andaliases='主街';4蓝湖的几何结构是否是简单的selectshore.STIsSimple()fromlakeswherename='蓝湖';5.获得鹅岛的边界selectboundary.STAsText(),boundary.STBoundary()fromislandwherename='鹅岛';6获得鹅岛的MBR边界selectboundary.STAsText(),boundary.STEnvelope()fromislandwherename='鹅岛';7.获取73号路的几何类型selectcneterlines.STGeometryType()fromdivided_routeswherename='路';8获得102路段中点的第一个点selectcenterline.STAsText(),centerline.STPointN(1)fromroad_segmentswherefid='102'9获得卡姆桥的x，y坐标SELECTposition.STX,position.STYFROMbridgesWHEREname='卡姆桥';10获得路段的长度SELECTcenterline.STLength()FROMroad_segmentsWHEREfid=106;11判断鹅岛的MBR边界是否闭合SELECTboundary.STIsClosed(),boundary.STBoundary()FROMislandWHEREname='鹅岛';12获得路段的起点和终点selectcenterline.STAsText(),centerline.STStartPoint(),centerline.STEndPoint()fromroad_segmentswherefid=10213获得鹅岛的质心selectboundary.STCentroid(),boundary.STAsText()fromislandwherename='鹅岛'14判断PointOnSurface函数返回鹅岛上的点是否在其边界上selectboundary.STContains(boundary.STPointOnSurface())fromislandwherename='鹅岛';15获得路段的点数目selectcenterline.STNumPoints()fromroad_segmentswherefid='102'16获得鹅岛的面积selectboundary.STArea()fromislandwherename='鹅岛';17--获得蓝湖内环的数目selectshore.STNumInteriorRing()fromlakeswherename='蓝湖';18判断号路的几何元素的数目selectcneterlines.STNumGeometries()fromdivided_routeswherename='路';19获得号路的第二个几何元素selectcneterlines.STAsText()fromdivided_routeswherename='