使用SQL Server 2008中的空间数据类型-技术开发专区

使用SQL Server 2008中的空间数据类型

作者：微软编辑：胡铭娅 2008-09-23 15:54 来源：IT168�

【IT168 技术文档】

目录
准备工作 1
练习 1：创建空间数据类型的实例 2
练习 2：查询空间数据类型的实例 9
练习3：使用空间索引 12

准备工作
预计完成本实验所需的时间
60 分钟
目标
在完成本实验后，您将可以：
创建空间数据类型的实例
使用空间数据类型的方法
创建空间索引

先决条件
在完成本实验前，您必须具有：
编写Transact-SQL 脚本与使用SQL Server Management Studio的相关经验。

实验场景
Adventure Works Cycles 为遍布整个美国的许多商店提供自行车产品。您被任命开发一个应用程序，它可以管理地理信息数据，包括商店的位置和销售区域。这个应用程序必须支持空间数据的查询，从而可以回答像“哪些商店在哪个销售区域里面？”、“某个销售区域到底有多大面积？”以及“哪些商店离某个特点的地理区域比较近？”等诸如此类的问题。
虚拟机环境

从开始菜单或桌面上启用Microsoft Virtual PC 。如果Virtual PC 控制台没有启用，请查看系统托盘，然后双击系统托盘当中的Microsoft Virtual PC 。
选择Sql08 然后点击Start。

在虚拟机运行起来后，可以通过点击右Alt+Del 来向虚拟机发送一个Ctrl+Alt+Del 命令。
在登录窗口中，输入以下信息：
User name: administrator
Password: password01!

INSERT INTO Store
(StoreName, StoreAddress, StorePhone, StoreLocation)
VALUES
('Aerobic Exercise Company',
  '39933 Mission Oaks Blvd, Camarillo, CA 93010',
  '244-555-0112',
  geography::GeomFromGml('<Point xmlns="http://www.opengis.net/gml">
                            <pos>34.235504 -118.988734</pos>
                          </Point>', 4326))

练习 1：创建空间数据类型的实例

在本练习中，您将创建一个数据表来存储空间数据。然后，您将创建一个geography 数据类型的实例，来表示一些点，这些点用来表示商店的位置，以及用来描述由公司销售经理所管理的销售区域的边界多边形信息。在数据库当中，您将使用不同的空间数据格
式来实例化geography实例。

在Microsoft SQL Server 数据库中使用geography 数据类型，使得您可以将现有的业务应用进行扩展，从而处理地理信息，来描述地理表面上简单或复杂的地理元素。

创建数据库和数据表来存储空间数据

点击Start | All Programs | Microsoft SQL Server 2008 | SQL Management Studio ，启动SQL Server Management Studio。
在Connect to Server对话框中输入下列信息，然后点击 Connect 按钮：

Server type: Database Engine
Server name: (local)
Authentication: Windows Authentication

点击 New Query 来创建一个新查询。如果出现提示对话框，则使用上面的连接方式连接到数据库服务器。
在查询编辑器中输入下面的Transact-SQL 代码：

CREATE DATABASE AWSales
GO
USE AWSales
GO
-- Create a table for stores
CREATE TABLE Store
(StoreID int IDENTITY PRIMARY KEY,
StoreName nvarchar(200),
StoreAddress nvarchar(200),
StorePhone nvarchar(25),
StoreLocation geography)
GO
-- Create a table for salespeople
CREATE TABLE SalesPerson
(SalesPersonID int IDENTITY PRIMARY KEY,
  FirstName nvarchar(20),
  LastName nvarchar(20),
  Email nvarchar(35),
  SalesRegionName nvarchar(25),
  SalesRegion geography)
GO

注意：在Store 表中的StoreLocation 列以及SalesPerson 表中的SalesRegion 列采用geography 数据类型。geography 数据类型适用于存储及操作涉及地球曲面的地理数据。而对于平面数据，例如点、线等，SQL Server 中引入了geometry 数据类型。
点击 Execute 运行脚本。

插入包括点值的记录

在查询编辑器中，在原有代码的下方输入下面的Transact-SQL 代码：

INSERT INTO Store
(StoreName, StoreAddress, StorePhone, StoreLocation)
VALUES
('Economic Parts Supply',
  '2532 Fairgrounds Road, West Kingston, RI 02892',
  '623-555-0153',
  geography::Parse('POINT(41.497478 -71.548531)'))

注意：这段代码利用geography 数据类型的Parse 方法，创建了一个WKT 所描述创建的点的实例。WKT 是Open Geospatial Consortium (OGC) 针对基于文本展示的地理数据所定义的标准。
在查询编辑器中，在原有代码的下方输入下面的Transact-SQL 代码：

INSERT INTO Store
(StoreName, StoreAddress, StorePhone, StoreLocation)
VALUES
('Two Bike Shops',
  '35525-9th Street SW, Puyallup, WA 98371',
  '724-555-0161',
  geography::Point('47.156622', '-122.30383', 4326))

注意：这段代码利用geography 数据类型的Point 方法来创建经纬度所构成的点的实例。geography 实例的Spatial Reference ID (SRID) 将其与特定的地球椭圆模型相关联，从而使得geography 实例同SRID 之间的对比方式与集合同文本之间的对比方式一样。geography 实例的SRID 设置为4326，这也是全世界GPS 系统遵循WGS84 标准所采用的SRID。作为geography 实例默认的SRID，该项设置在之前的记录中也遵循此数值。
在查询编辑器中，在原有代码的下方输入下面的Transact-SQL 代码：

INSERT INTO Store
(StoreName, StoreAddress, StorePhone, StoreLocation)
VALUES
('Eastside Department Store',
  '9992 Whipple Rd, Union City, CA 94587',
  '926-555-0164',
  geography::STGeomFromText('POINT(-122.084552 37.603936)', 4326))

注意：这段代码使用geography 数据类型的STGeomFromText 方法，从一个文本描述中创建一个点的实例。
在查询编辑器中，在原有代码的下方输入下面的Transact-SQL 代码：
注意：这段代码使用geography 数据类型的GeomFromGml 方法，从Geography Markup Language (GML) 描述中创建一个点的实例。GML 是OGC 针对XML 展现地理数据所定义的标准。
随便使用一种上面介绍过的技术，创建INSERT语句，从而可以将下面的商店信息添加到数据表中。

Store Data
Store name: Exotic Bikes
Store address: 6900 William Richardson Ct, South Bend, IN 46628
Store telephone number: 415-555-0147
Latitude and Longitude location: 41.733388 -86.298173
Store name: Healthy Activity Store
Store address: 4460 Newport Center Drive, Newport Beach, CA 92625
Store telephone number: 911-555-0165
Latitude and Longitude location: 33.617021 -117.87749
Store name: Metropolitan Sports Supply
Store address: 482505 Warm Springs Blvd, Fremont, CA 94539
Store telephone number: 828-555-0186
Latitude and Longitude location: 37.464038 -121.914168

选中所有您刚刚输入的INSERT语句，然后点击Execute 来插入数据。
在查询编辑器中，在原有代码的下方输入下面的Transact-SQL 代码：

SELECT StoreName, StoreLocation.ToString()
FROM Store

选中您刚刚输入的SELECT 语句，然后点击Execute 来查看商店数据。下面的结果显示了每个商店的信息。这些点表示了在下面的地图中所对应的针脚。

插入包括多边形值的记录

使用记事本来查看C:\SQLHOLs\Spatial Data\Starter\WesternUS.txt 文本文件的内容。这个文件包含了Adventure Works Western United States销售区域的WKT定义。

在查询编辑器中，在原有代码的下方输入下面的Transact-SQL 代码，并将下面的销售区域的定义粘贴到代码当中：

INSERT INTO SalesPerson
(FirstName, LastName, Email, SalesRegionName, SalesRegion)
VALUES
('Syed',
  'Abbas',
  'syed0@adventure-works.com',
  'Western US',
  geography::STGeomFromText('POLYGON((
    -123.07402559791684  37.76736709104543 ,
    -121.94097917688177  36.49093792385886 ,
     -120.37149732826121 34.045890710439785,
    -117.16155430516534  32.71568063372854 ,
     -106.47549795243778 31.733844355633792,
     -101.56221503336745 29.807382670042525,
     -100.89817721206964 29.366207091173063,
    -99.50198409288148   27.53090452533937 ,
     -102.5484260476256  49.002355502083155,
    -122.7588943299908   48.97672863231727 ,
    -123.14601579176877  48.16552414006778 ,
     -123.43134011415688 48.118720268780265,
    -124.7351822455773   48.39167853367607 ,
    -123.07402559791684  37.76736709104543 ))', 4326))

注意：这段代码使用geography 数据类型的STGeomFromText 方法，从文本描述中创建了一个多边形的实例。
使用一个XML编辑器，如Microsoft Visual Studio 2008 来查看C:\SQLHOLs\Spatial Data\Starter\EasternUS.xml文件的内容。

INSERT INTO SalesPerson
(FirstName, LastName, Email, SalesRegionName, SalesRegion)
VALUES
('Linda',
  'Mitchell',
  'linda3@adventure-works.com',
  'Eastern US',
  geography::GeomFromGml('<Polygon xmlns="http://www.opengis.net/gml">
  <exterior>
    <LinearRing>
      <posList>49.002355502083155 -102.5484260476256
               27.53090452533937 -99.501984092881486
               25.866221792519891 -97.505157798460743
               25.943457571654395 -97.149468867243158
               27.675151290420871 -97.176960378652467
               29.732981419413576 -93.894849576786243
               29.06303206938912 -90.506037559706073
               24.5583430193 -81.780293310096368
               25.792769938185575 -80.1348529277161
               26.931995972872219 -80.101951906484146
               28.079387498711611 -80.606873640877339
               28.470839300423382 -80.537540390612548
               32.18910378457182 -80.7490576888295
               35.373785353022406 -75.984925610664732
               41.189858770922925 -71.591597672350787
               41.801225588436182 -70.3056301459883
               44.812877974642831 -66.967327377820382
               47.454989805995105 -69.154285648166763
               44.99637100669522 -71.538670536472367
               45.028448990132183 -74.735724992604418
               43.995140406452222 -77.250621135570626
               43.106628418081947 -79.064275416829958
               42.885442987094834 -78.878439629892256
               41.76926713330969 -82.652583599187864
               46.518294248124974 -84.347939004022578
               48.381706457487851 -89.245498827623535
               49.002355502083155 -102.5484260476256</posList>
    </LinearRing>
  </exterior>
</Polygon>', 4326))

注意：这段代码使用geography 数据类型的GeoFromGml 方法，从GML 描述中创建了一个多边形的实例。
选中刚刚添加的INSERT语句，然后点击Execute 运行选中的代码。
在现有的代码下方，添加下面的代码以查看销售区域数据。

SELECT SalesRegionName, SalesRegion.ToString()
FROM SalesPerson

选中刚刚添加的SELECT语句，然后点击Execute 运行选中的代码。下面的结果显示了定义每个销售区域所需的点。两个销售区域都显示在下面的地图当中。

保存查询脚本文件为C:\SQLHOLs\Spatial Data\Starter\SalesData.sql。
不要关闭SQL Server Management Studio ，在下个实验中还要继续使用。

练习 2：查询空间数据类型的实例

在本练习中，您将使用geography 数据类型的方法和属性，来执行空间查询和操作。在geography实例上执行空间操作，使得您可以创建丰富的空间应用程序，其中用户可以查找和处理基于位置的信息。

查找一个geography实例的区域

在SQL Server Management Studio中，点击New Query 来创建一个新的查询。如果出现提示对话框，则使用Windows Authentication连接到(local)数据库引擎实例。

在查询编辑器中，输入下面的Transact-SQL 代码：

USE AWSales
GO
-- Find the area of the sales regions
SELECT SalesRegionName, SalesRegion.STArea()/1000000 AreaInSqKm
FROM SalesPerson

注意： geography 数据类型的STArea 方法可以返回指定geography 实例的区域。根据geography 实例的SRID 决定所使用的测量单元。这个数据库的销售区域使用SRID 4326，这意味着将采用正方形来测量区域。
点击Execute 来运行脚本，然后查看结果，它将会按照平方公里显示每个销售区域。

查找与一个geography 实例相交的geography 实例

在查询编辑器中，在原有代码的下方输入下面的Transact-SQL 代码：

-- Find which stores are in which sales region
SELECT s.StoreName,
       s.StoreAddress,
       sp.SalesRegionName,
       sp.FirstName + ' ' + sp.LastName Salesperson
FROM Store s, SalesPerson sp
WHERE sp.SalesRegion.STIntersects(s.StoreLocation) = 1

-- Create the union of the sales regions
DECLARE @westUS geography
DECLARE @eastUS geography
DECLARE @totalUS geography
SELECT @westUS = SalesRegion FROM SalesPerson WHERE SalesRegionName = 'Western US'
SELECT @eastUS = SalesRegion FROM SalesPerson WHERE SalesRegionName = 'Eastern US'
SET @totalUS = @westUS.STUnion(@eastUS)
SELECT @totalUS.ToString()

注意： STIntersects 方法用来决定geography 实例是否与另一个geography 实例进行交叉。在这个实例中，该方法用来查找与销售区域有交叉的存储数据。

选择您在上个步骤上输入的SELECT 语句，然后点击Execute 运行选中的脚本。其结果应当会显示出，那些销售区域内的商店及其销售代表的名字。

查找两个geography 实例的并集

在查询编辑器中，在原有代码的下方输入下面的Transact-SQL 代码：

注意： STUnion 方法创建了一个新的geography 实例，用来展现两个现有geography 实例的合并。在此示例中，新的geography 实例通过合并东西部的销售区域，从而展现了整个美国的完整销售区域。
选中上个步骤中刚刚输入的代码，然后点击Execute 运行选中的代码。结果应当显示出销售区域合并的多边形的一个文字描述。

查找某种地理区域附近的geography 实例
使用一个XML编辑器，例如Visual Studio 2008查看C:\SQLHOLs\Spatial Data\Starter\I5.xml文件的内容。这个文件中包含了下面图形中所示的Interstate 5 highway 的GML 定义。

关闭XML文件，然后在SQL Server Management Studio的查询编辑器中，输入下面的Transact-SQL 代码。

-- Load Interstate 5 GML from XML file
DECLARE @i5Gml xml
SET @i5Gml =(
SELECT * FROM OPENROWSET(
   BULK 'C:\SQLHOLs\Spatial Data\Starter\I5.xml',
           SINGLE_BLOB
) AS i5GML)

-- Find stores within 100 km of Interstate 5
DECLARE @i5 geography;
SET @i5 = geography::GeomFromGml(@i5Gml, 4326)
SELECT StoreName,
       StoreAddress,
       StoreLocation.STDistance(@i5)/1000 DistanceFromI5inKM
FROM Store
WHERE StoreLocation.STDistance(@i5) < 100000
ORDER BY DistanceFromI5inKM

注意：本段代码将使用OPENROWSET函数从XML文件当中，将I5的GML表示形式加载出来。然后，它使用geography数据类型的STDistance方法来找到I5的100公里附近的所有商店。

选中在上个步骤中输入的Transact-SQL 语句，然后点击Execute 运行选中的代码。结果将会显示出所有I5附近100公里以内的商店，并将按距离升序显示。

将查询脚本保存为C:\SQLHOLs\Spatial Data\Starter\QuerySpatial.sql文件。
保持SQL Server Management Studio 打开以备后面的练习使用。

练习3：使用空间索引

在本练习中，您将创建空间数据索引来提升空间查询的性能。空间索引基于一个4级网络的结构进行存储，每个级别都比上一个级别的粒度要更加细微。

使用默认设置创建一个空间索引

在SQL Server Management Studio中，点击New Query 来创建一个新的查询。如果出现提示对话框，则使用Windows Authentication 连接到(local) 数据库引擎实例。
在查询编辑器中，输入下面的Transact-SQL 代码：

USE AWSales
GO
--Create a spatial index with default settings
CREATE SPATIAL INDEX SIndx_Store_StoreLocation
ON Store(StoreLocation)
USING GEOGRAPHY_GRID
GO

点击Execute 运行脚本。

使用自定义设置创建空间索引
在查询编辑器中，在原有代码的下方输入下面的Transact-SQL 代码：

CREATE SPATIAL INDEX SIndx_Salesperson_SalesRegion
   ON SalesPerson(SalesRegion)
   USING GEOGRAPHY_GRID
   WITH (GRIDS = (LOW, MEDIUM, HIGH, HIGH),
         CELLS_PER_OBJECT = 256);
GO

注意： GRIDS 参数指定了空间索引当中每个级别的密度，同时，还决定了每个级别单元格的数量。CELLS_PER_OBJECT参数指定了单个空间对象可以占用的单元格的数量。
选择在上个步骤中创建的CREATE SPATIAL INDEX 语句，然后点击Execute 运行选中的代码。

View index usage

在查询编辑器中，在原有代码的下方输入下面的Transact-SQL 代码：

SET SHOWPLAN_TEXT ON
GO
SELECT s.StoreName
FROM Store s, SalesPerson sp
WITH (INDEX(SIndx_SalesPerson_SalesRegion))
WHERE sp.SalesRegion.STIntersects(s.StoreLocation) = 1
GO
SET SHOWPLAN_TEXT OFF
GO

注意： SHOWPLAN_TEXT选项可以通过查询优化器显示出选择的语句段的执行计划。在本演示中，数据表当中的数据量非常小，因此查询优化器将不会选择在上面的代码中使用不带查询提示的空间索引。如果空间数据的数据量非常大，那么查询优化器将可能会选择一个不带查询提示的空间索引，因为空间索引所带来的性能提升将会非常巨大。

选中上个步骤中输入的Transact-SQL 代码，然后点击Execute 运行选中的代码。

查看执行计划，然后注意空间索引的使用。

将查询脚本文件保存为C:\SQLHOLs\Spatial Data\Starter\SpatialIndexes.sql。
关闭SQL Server Management Studio。
关闭Virtual PC，不保存任何更改。

关注我们