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

实验四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(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 island

where name='鹅岛'

查找本数据库中所有的空间表

Select TABLE_NAME

FROM https://www.360docs.net/doc/dc8948180.html,RMATION_SCHEMA.TABLES

Where TABLE_TYPE='BASE TABLE'

判断名为路5别名为“主街”的路段的几何属性是否为空

select centerline.STIsEmpty()

from road_segments

where name='路'

and aliases='主街';

4蓝湖的几何结构是否是简单的

select shore.STIsSimple()

from lakes

where 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_routes

where name='路';

8 获得102路段中点的第一个点

select centerline.STAsText(),centerline.STPointN(1) from road_segments

where fid='102'

9获得卡姆桥的x，y坐标

SELECT position.STX,position.STY

FROM 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_segments

where fid=102

13获得鹅岛的质心

select boundary.STCentroid(),boundary.STAsText()

from island

where name='鹅岛'

14判断PointOnSurface函数返回鹅岛上的点是否在其边界上select boundary.STContains(boundary.STPointOnSurface())

from island

where name='鹅岛';

15获得路段的点数目

select centerline.STNumPoints()

from road_segments

where fid='102'

16获得鹅岛的面积

select boundary.STArea()

from island

where name='鹅岛';

17--获得蓝湖内环的数目select shore.STNumInteriorRing() from lakes

where name='蓝湖';

18判断号路的几何元素的数目select cneterlines.STNumGeometries() from divided_routes

where name='路';

19获得号路的第二个几何元素select cneterlines.STAsText()

from divided_routes

where name='路';

20获得号路的长度

select cneterlines.STLength()

from divided_routes

where name='路';