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

实验四 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,16 48),101);-桥insert into bridges values(110,卡姆桥,102,103,geometry:STGeomFromText (point(4431),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,6248),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 island where name=鹅岛查找本数据库中所有的空间表 Select TABLE_NAME FROM spatial.INFORMATION_SCHEMA.TABLES Where 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 island where name =鹅岛;6获得鹅岛的MBR边界select boundary.STAsText(),boundary.STEnvelope()from island where name =鹅岛;7. 获取73号路的几何类型select cneterlines.STGeometryType()from divided_routeswhere name = 路;8 获得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=102 13获得鹅岛的质心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.STNumInte