DAT 331: 实例演示：用SQL Server 2008开发一个地理信息处理系统
-----------------------------------------------------------------
-- 空间数据的处理
-- 点
declare @point Geometry
set @point = Geometry::Parse('POINT(0 0)')
select @point.ToString(), @point.STBuffer(10)
go
-- 多点
declare @mpoint Geometry
set @mpoint = Geometry::Parse('MULTIPOINT(0 0, 0 80, 80 80, 0 80)')
select @mpoint.ToString(), @mpoint.STBuffer(2)
-- 多边型
-- 1) 至少四个不同点
-- 2) 起点和终点一致
declare @polygon Geometry
set @polygon = Geometry::Parse('POLYGON((0 0, 0 40, 40 40, 40 0, 0 0))')
select @polygon.ToString(), @polygon
DECLARE @g geography;
SET @g = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);
select @g.STBuffer(0.01)
SELECT @g.BufferWithTolerance(1, .5, 0).ToString();
-- 多个多边型
declare @mpolygon Geometry
set @mpolygon = Geometry::Parse('MULTIPOLYGON(((0 0, 0 80, 80 80, 0 80, 0 0), (-1 -1, -2 -10, -10 -2, -1 -1)))')
select @mpolygon.ToString(), @mpolygon
-- 线: 至少两个不同点
declare @line Geometry
set @line = Geometry::Parse('LINESTRING(0 0, 0 0, 0 0)')
select @line.ToString()
select @line.STNumPoints()
-- 多线
-- Note: Polygon is LingString too
declare @mline Geometry
set @mline = Geometry::Parse('MULTILINESTRING((0 0, 5 5, 0 0), (0 0, 0 80, 80 80, 0 80, 0 0))')
select @mline.ToString()
-- 空间集GeometryCollection
declare @gc Geometry
set @gc = Geometry::Parse('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0, 5 5), POLYGON((0 0, 0 80, 80 80, 0 80, 0 0)))')
select @gc.ToString()
-- 一个使用实例
DECLARE @g geometry = geometry::STGeomFromText('POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))', 0);
DECLARE @h geometry = geometry::STGeomFromText('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))', 0);
--DECLARE @h geometry = geometry::STGeomFromText('POLYGON((1 1, 3 1, 3 3, 1 3, 1 1))', 0);
SELECT @g.STContains(@h);
SELECT @h.STContains(@g)
-- 有关日期、时间的新数据类型
if exists( select * from sys.tables where name = 't1' )
drop table t1
CREATE TABLE t1 (
c1 DATE,
c2 TIME(3),
c3 DATETIME2(7) NOT NULL DEFAULT GETDATE(),
c4 DATETIMEOFFSET CHECK (c4 < CAST(GETDATE() AS DATETIMEOFFSET(0)))
)
INSERT INTO t1 VALUES ('0001-01-01', '23:59:59',
'0001-12-21 23:59:59.1234567',
'2008-10-10 23:59:59.1234567 -07:00')
SELECT c4 cDateTimeOffset,
DATEPART(TZOFFSET, c4) cTimeZone,
DATEPART(ISO_WEEK, c4) cWeek,
DATEPART(MICROSECOND, c4) cMicrosecond
FROM t1
-- 通过改变本机的时区, 观察SYSDATETIMEOFFSET不同的结果
SELECT SYSDATETIME(), SYSUTCDATETIME(), SYSDATETIMEOFFSET()
-- 文件流(Filestream). 注意: 需要先激活这一功能。
use master
-- enable filestream
sp_configure 'filestream_access_level', 2
reconfigure with override
if exists( select * from sys.databases where name = 'MyFSDB' )
drop database MyFSDB
-- 注意: 改变下面文件路径以适应你的系统
create database MyFSDB
on primary
( name = MyFSDB_dat,
filename = 'c:\sqldata\FS\MyFSDB_dat.mdf' ),
filegroup filestreamgroup contains filestream
( name = MyFSDB_fsGroup,
filename = 'c:\sqldata\FS\MyFSDB_fsGroup' )
log on
( name = MyFSDB_log,
filename = 'c:\sqldata\FS\MyFSDB_log.ldf' )
use MyFSDB
-- create table with column on filestream
CREATE TABLE demoFilestream (
fileID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL primary key,
fileName NVARCHAR(256),
officeFile VARBINARY(MAX) FILESTREAM)
filestream_on filestreamgroup
-- insert NULL value
insert demoFilestream values(newid(), NULL, NULL)
-- insert empty value
insert demoFilestream values(newid(), 'EmptyFile', cast('' as varbinary(max)))
-- insert some value
insert demoFilestream values(newid(), 'HelloWorld.doc', cast(N'Hello World! 世界,你好!' as varbinary(max)))
-- take a look the data
SELECT *, datalength(officeFile)
, cast(officeFile as nvarchar(max))
, officeFile.PathName()
from demoFilestream
-- 稀疏列Sparse Column
drop table tblTestBig
-- create table with a set of sparse column, and a column set
create table tblTestBig
( c1 int,
c2 int sparse,
c3 varchar(8000) sparse,
c4 varchar(50) sparse,
cs xml column_set for all_sparse_columns
-- insert a row
insert tblTestBig (c1, c2, c3, c4) values(
120, null,
'12345678901234567890123456789012345678901234567890',
'abcdefghijklmnopqrstuvwxyz')
-- select from it
select * from tblTestBig
-- add another column
alter table tblTestbig add c5 int sparse
-- select again and see the difference
-- insert another row
insert tblTestBig (c1, c2, c3, c4, c5) values(
'abcdefghijklmnopqrstuvwxyz',
'100')
-- 全文本搜索的演示将在下一帖中展示。