实验四SQL Server空间数据库应用案例一、实验学时4学时二、实验目的1. 了解SQL Server 2008 r2的空间参考系统表；2. 掌握SQL Server 2008r2的空间数据类型的使用；3. 掌握空间数据库与数据表的设计与实现4.掌握用SQL实现空间查询与分析功能的方法三、预习内容1.SQL Server 有关空间操作的教程2.教材中有关SQL语言的关于空间查询与分析的语法章节四、实验设备及数据1.安装了SQL Server 2008 r2的电脑2.教材第五章的空间数据库实例数据五、实验内容1. SQL Server 2008的空间参考系统表的介绍2空间数据类型的介绍与使用3. 空间数据库与数据表的创建与修改4. 使用SQL语句添加、删除、修改空间数据记录5. 用SQL实现空间查询和分析六、实验步骤建蓝湖数据库create table lakes(fid integer not null primary key,name varchar(64),shore geometry);create table road_segments(fid integer not null primary key,name varchar(64),alises varchar(64),num_lanes integer,centerline geometry);create table divided_routes(fid integer not null primary key,name varchar(64),roadseg1id integer references road_segments,roadseg2id integer references road_segments,position geometry);create table bridges(fid integer not null primary key,name varchar(64),roadseg1id integer references road_segments, roadseg2id integer references road_segments, position geometry);create table streams(fid integer not null primary key,name varchar(64),fromlakeid integer references lakes,tolakeid integer references lakes,centerline geometry);create table buildings(fid integer not null primary key,address varchar(64),position geometry,footprint geometry);create table poods(fid integer not null primary key,name varchar(64),type varchar(64),shores geometry);create table island(fid integer not null primary key,name varchar(64),lakeid integer references lakes,boundary geometry);create table zone(fid integer not null primary key,name varchar(64),boundary geometry);录入数据insert into lakes values(101,'蓝湖',geometry::STGeomFromText('multipolygon(((52 18,66 23,73 9,48 6,52 18),(59 18,67 18,67 13,59 13,59 18)))',101));insert into lakes values (0,'图片外其他湖泊',geometry::STGeomFromText('multipolygon(((62 28,76 33,83 19,58 16,62 28),(69 28,77 28,77 23,69 23,69 28)))',101));--路段insert into road_segments values(102,'路',null,2,geometry::STGeomFromText('linestring(0 18,10 21,16 23,28 26,44 31)',101));insert into road_segments values(103,'路','主街',4,geometry::STGeomFromText('linestring(44 31,56 34,70 38)',101));insert into road_segments values(104,'路',null,2,geometry::STGeomFromText('linestring(70 38,72 48 )',101));insert into road_segments values(105,'主街',null,4,geometry::STGeomFromText('linestring(70 38,84 42 )',101));insert into road_segments values(106,'绿森林边路',null,1,geometry::STGeomFromText('linestring(28 26,28 0 )',101));--组合路insert into divided_routes values(119,'路',null,4,geometry::STGeomFromText('multilinestring((10 48,10 21,10 0),(16 0,16 23,1648))',101));--桥insert into bridges values(110,'卡姆桥',102,103,geometry::STGeomFromText('point(44 31)',101));insert into streams values (111,'卡姆河',0,101,geometry::STGeomFromText('linestring(38 48,44 41,41 36,44 31, 52 18)',101)); insert into streams values (112,null,101,0,geometry::STGeomFromText('linestring(76 0,78 4, 73 9)',101));insert into buildings values (113,'主街号',geometry::STGeomFromText('point(52 30)',101),geometry::STGeomFromText('polygon((50 31, 54 31, 54 29,50 29,50 31))',101));insert into buildings values (114,'主街号',geometry::STGeomFromText('point(64 33)',101),geometry::STGeomFromText('polygon((66 34, 62 34, 62 32,66 32,66 34))',101));insert into poods values (120,null,'思道哥池塘',geometry::STGeomFromText('multipolygon(((24 44,22 42,24 40,24 44)),((26 44,26 40,28 42,26 44)))',101));insert into island values (109,'鹅岛',101,geometry::STGeomFromText('multipolygon(((67 13,67 18,59 18,59 13,67 13)))',101));--区域insert into zone values (117 ,'阿诗顿',geometry::STGeomFromText('multipolygon(((62 48,84 48,84 30,56 30,56 34,62 48)))',101));insert into zone values (118 ,'绿森林',geometry::STGeomFromText('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)))',101));查询获得鹅岛的wkt表示select boundary.STAsText()from islandwhere name='鹅岛'查找本数据库中所有的空间表Select TABLE_NAMEFROM RMATION_SCHEMA.TABLESWhere TABLE_TYPE='BASE TABLE'判断名为路5别名为“主街”的路段的几何属性是否为空select centerline.STIsEmpty()from road_segmentswhere name='路'and aliases='主街';4蓝湖的几何结构是否是简单的select shore.STIsSimple()from lakeswhere name='蓝湖';5.获得鹅岛的边界select boundary.STAsText(),boundary.STBoundary() from island6获得鹅岛的MBR边界select boundary.STAsText(),boundary.STEnvelope() from islandwhere name='鹅岛';7.获取73号路的几何类型select cneterlines.STGeometryType()from divided_routes8 获得102路段中点的第一个点select centerline.STAsText(),centerline.STPointN(1) from road_segmentswhere fid='102'9获得卡姆桥的x，y坐标SELECT position.STX,position.STYFROM bridges WHERE name='卡姆桥';10获得路段的长度SELECT centerline.STLength()FROM road_segments WHERE fid=106;11判断鹅岛的MBR边界是否闭合SELECT boundary.STIsClosed(),boundary.STBoundary() FROM island WHERE name='鹅岛';12获得路段的起点和终点select centerline.STAsText(),centerline.STStartPoint(),centerline.STEndPoint() from road_segmentswhere fid=10213获得鹅岛的质心select boundary.STCentroid(),boundary.STAsText()from islandwhere name='鹅岛'14判断PointOnSurface函数返回鹅岛上的点是否在其边界上select boundary.STContains(boundary.STPointOnSurface())from islandwhere name='鹅岛';15获得路段的点数目select centerline.STNumPoints()from road_segmentswhere fid='102'16获得鹅岛的面积select boundary.STArea() from islandwhere name='鹅岛';17--获得蓝湖内环的数目select shore.STNumInteriorRing() from lakeswhere name='蓝湖';18判断号路的几何元素的数目select cneterlines.STNumGeometries() from divided_routeswhere name='路';19获得号路的第二个几何元素select cneterlines.STAsText()from divided_routeswhere name='路';20获得号路的长度select cneterlines.STLength() from divided_routes where name='路';。