在程序表 : SQL Server 2008 中的空间数据支持

项目
10/14/2015

在程序表

SQL Server 2008 中的空间数据支持

Bob Beauchemin

代码下载可从 MSDN 代码库
浏览代码联机

内容

使用地理位置的数据类型
空间索引
可视化工具
Geometry 数据类型
空间的可视化效果
SQL Server 空间
向上覆盖

SQL Server 2008 提供了的许多，包括新的数据类型、新的 T-SQL 语句和增量对 Microsoft.NET Framework 支持、 XML 支持和服务代理功能的更改的增强功能中。到目前为止，为开发人员最大和最令人兴奋的更改是空间数据 SQL Server 2008 支持 SQL Server 程序员工具箱的强大除了。在本专栏，我将显示空间数据的简短概述，并说明了一些有趣的用法。

空间数据定义为表示二维或三维图像的数据。 SQL Server 2008 支持二维的图像，并且会在将来的版本中添加了三维。空间数据可以被进一步细分成几何数据（可以使用涉及 Euclidian 几何图形的计算的数据）和地理位置的数据（标识地理位置和地球上的边界的数据）。

使用地理位置的数据类型

让我们从简单的应用程序可能会很有用最传统的联机事务处理 (OLTP) 系统涉及客户的空间数据开始。空间数据有用本例中是因为每个客户的地址。您通常将地址视为街道，城市、状态、国家 / 地区，和 ZIP 的代码但地址地球上的某个点并纬度 / 经度对可以也表示。从技术上讲，地址可以包含的可表示为一个的多边形，但让我们坚持单点在这种情况下只是为简单起见的土地的 Parcel。了解客户为纬度 / 经度居住的使您可以如回答的问题：

什么是特定客户的三个最近的银行分支？
销售人员给客户的居住 closest？
您的公司 10 的英里半径，对于是实例西雅图，Washington 中有多少客户？
多少客户 Live 超过 2 英里从您最近的分支位置？

纬度 / 经度对转换街道地址的过程称为地址 geocoding。许多的联机服务（包括 MapPoint、 Virtual Earth 和 Google 地球）提供 geocoding 作为一种服务。将在美国的街道地址点，您可以封装 MapPoint geocoding SQLCLR 函数中的 Web 服务调用。这所示，代码示例. 但在您应内 SQL Server 数据库中存储纬度 / 经度？

用于存储空间数据 SQL Server 2008 附带两种必要的数据类型：几何和地理位置。这两种数据类型是.NET 体系结构用于表示它们可以具有属性和方法的用户定义类型实现的。让我们继续使用 Geography 数据类型现在因为此更加紧密地映射到手头的问题。

可以定义与简单的 SQL 变量声明 DECLARE @g 地理或一列作为一个地理位置类型的实例表中, 和可以初始化此数据类型各种方式。地理位置类型表示某个点的一个实例最简单的方法是使用 STPointFromText 地理位置类型的静态方法。 STPointFromText 方法需要不仅打开 Geospatial 联合会的已知文本格式 (WKT) 点的文本表示，这就是 POINT(x,y)，但一个空间的引用标识符 (SRID) 还。在 SRID 标识用于循环地球或平面地球映射和，现在，它的足够知道 MapPoint geocoder Web 服务使用对应于 SRID 4326 的 GPS 坐标空间的引用系统。这表示世界上 Geodetic 系统 1984 (WGS 84)。（要查找 SQL Server 2008 支持的所有 SRIDs，只是查询元数据的表 sys.spatial_reference_systems)。

因此初始化地址地理位置的代码将类似此：

DECLARE @addr nvarchar(256) = 'Some sample address, City, State, Zip';
DECLARE @addr_as_xy nvarchar(30);
DECLARE @g geography;
SET @addr_as_xy = dbo.Geocoder(@addr);
SET @g = geography::STPointFromText(@addr_as_xy, 4326);

请注意因为地理位置实现为.NET-based 的数据类型，有一些有趣指向提到有关代码。首先，STPointFromText 是一种静态方法，因此它必须调用使用 datatype::method 语法。此外，因此，STPointFromText 必须使用完全的大小写，要区分基于.NET 的数据类型的方法名称。

还有的重要一点指出： geocoder 函数必须进行编码返回为"XY"位置而不是纬度 / 经度。打开 Geospatial 联合会定义其 WKB，并已知的二进制文件 (WKB) 格式设置为使用 x，y 坐标对。因此，内容的被传递到 STPointFromText 必须 POINT(longitude/Latitude)，不 POINT(Latitude/longitude)。如果您希望而不是使用纬度 / 经度，SQL Server 将构造使用地理标记语言 (GML)，使用纬度 / 经度的 XML 词汇的地理位置实例。或者在该几何图形数据类型带三个参数的如果您有一个点，没有一种特殊的静态方法： Latitude、经度和 SRID。

既然您已经 geocoded 您的客户信息还可以转提前和 geocode 银行分支位置（或其他分支办公室信息、仓库的信息或任何与位置）和在销售员位置，以及。假设在完成与名为客户、销售人员和分支的表。每个需要指示其位置的类型 Geography 一列（我们称之为 geog 为简单起见）。

可以使用此信息来回答前面提到的问题。对于此，必须使用 Geography 数据类型方法。 STDistance，是，毫无疑问，调用该方法用于计算两个地理位置的对象的距离。可以用回答第三个问题（10 英里半径的西雅图中有多少客户）方法将 STBuffer 和 STIntersects。 SQL Server 还具有用于计算更近似缓冲区名为的 BufferWithTolerance （很比计算准确的缓冲区有点快）的方法。

若要回答第一个问题，则应使用一个查询，如下所示：

SELECT TOP(3) b.name,
 c.geog.STDistance(b.geog)/1000 [Distance in km]
FROM customer c, branch b
WHERE c.customerid = '12345' -- this query looks at customer 12345
ORDER BY c.geog.STDistance(b.geog)

查询以计算最接近的销售人员将在大约相同的方式中进行。

如何在用户在 10 英里半径的西雅图 Live？此查询，您将放周围的（表示为任意多边形的城市限制或指定为 downtown 西雅图的点）的西雅图市限制缓冲区并选择相交该缓冲区的所有点（客户）。表示代码，这是：

-- or declare POINT for "downtown Seattle"
-- 1609.344 meters per mile
DECLARE @Seattle geography = 'POLYGON(....)'; SELECT c.customerid FROM
 customer c WHERE c.geog.STIntersects(@Seattle.STBuffer(10 * 1609.344));

这些示例说明了有关如何与地理位置类型的实例交互更有趣的点。使用 STBuffer，对于是实例是实例方法在地理位置数据类型上的时您可以使用 instance.method 语法而不是 type::method 语法那么可以使用类似 STPointFromText 的静态方法。此外，请注意要计算两个地域距离或确定一个地理实例是否与另一个相交时, 使用语法 Instance1.method(Instance2)。

要解答有关数量的 Live 从最近的银行分支超过两个英里的客户问题，您必须能够聚合所有银行分支的地域，与每个，周围的两个英里的缓冲区，然后查找与该集合的地域不相交的客户。 Geography 数据类型没有一个名为 STUnion，方法，但它的将其指定为方法返回该实例与另一个实例之间的地理位置实例上。

这不是我必须记住基于集的聚合。幸运的是，SQL Server 2005 引入可以编写在.NET 代码中的用户定义聚合并可以使用一个此处将一组值的聚合联合。而不需要太多到编码用户定义聚合的细节，它们需要四个方法初始化：初始化 (应答初始化）终止（返回应答）、累积（调用的每一行）和合并（称为合并来自多个线程处理时）。

在 aggregate Accumulate 方法可以只合并当前行地理位置，答案地理位置的每次它的调用。实际上，您不必自己编写此聚合；的一部分， CodePlex 上 SQL Server 空间工具项目. 此聚合甚至可以将缓冲区中传递的每一行这解决了两个英里缓冲区问题。然后，该查询可以编写如下：

SELECT COUNT(*)
FROM customer c
WHERE
c.geog.STIntersects(
SELECT dbo.GeographyUnionAggregate(b.geog,2*1609.344) FROM branch b)=0

可以看到这些示例，geocoding 地址和存储以及与该地址的邮局的窗体可将应用程序的业务价值。

空间索引

一旦您的业务获取大，计算各之间每个客户和每个销售人员或每个客户以及每个银行分支可能还会太慢。 SQL Server 2008 空间数据支持包括空间索引。空间索引是为了使运行得更快的空间查询，就像关系索引 SQL Server 使关系查询运行得更快的普通 B 树索引。

将二维空间数据映射到一维的 B 树的 tessellation 完成；的是，向上划分小的子区域的区域，并且记录在相交每个空间实例的子区域。为在地理位置数据类型这意味着整个全球划分 hemispheres 和每个 Hemisphere 投影到一面。每个地理位置实例然后介绍各一个或多个部分（平铺）此平面的；空间索引将包含一个实例，每个平铺一行。对于几何图形类型因为您要指定您自己的矩形坐标系统可以指定边界（边界框），空间索引。

有许多控制如何空间实例相交麻将牌的规则映射到空间的索引中的行，您可以定义如何好网格系统将在您在多个级别的空间索引。有关具体信息的空间的索引 SQL Server 联机丛书是最好的参考。

若要回到客户系统，您可以在客户表与以下数据定义语言 (DDL) 中 geog 列上定义空间索引：

CREATE SPATIAL INDEX cust_geog_idx
ON dbo.customer(geog)
GRIDS =(LEVEL1=HIGH,LEVEL2=HIGH,LEVEL3=HIGH,LEVEL4=HIGH));

每个客户的地址是一个点将只显示相对于行或未能相交许多平铺的多边形的一个图块中，定义与高粒度网格此索引将最适合您的索引。

应注意空间索引仅用于某些空间的方法。当前，他们使用以下的地理位置谓词：

instance1.STIntersects(instance2) = 1
instance1.STEquals(instance2) = 1
instance1.STDistance(instance2) < number
instance1.STDistance(instance2) <= number

您正在处理而不是地理位置的几何图形时, 空间索引也使用了 STContains、 STOverlaps、 STTouches，和 STWithin 方法但仅当时才有效检查 1 (True)。根据空间索引的工作的方式，真正想要重新表述您的空间查询有关"客户 Live 来自银行分支超过两个英里"来计算的客户 STIntersects 返回是 True，减去客户总数的数字。

可视化工具

请注意在几个示例中我过通过为止，将非常有用能够看到行和列以外表单中空间的数据。实际上，您可能已经注意到选择该空间数据类型本身返回其二进制表示形式。使用 ToString 方法或 STAsText 方法返回 WKB 格式。这有点好但它仍然不允许您知道有关地理位置除非您可以到地图上项目纬度 / 纬度对您头脑中。空间数据几乎总是需要图形可视化工具，以使其更有用，当与您交谈有关地理位置的数据的可视化工具将通常包括映射。

SQL Server Management Studio 2008 包含在查询结果窗口中的空间的结果选项卡的形式简单的可视化工具。此可视化工具使用一个地理位置，或在查询中的几何图形列结果，并绘制网格上的空间数据类型。如果查询结果中显示多个空间列，可以选择要查看。要显示该列必须 SQL Server 二进制格式，；使用 ToString() 或 STAsText() 不使用可视化工具。

对于一个地理位置数据类型您甚至获得如墨卡托或 Equirectangular，映射预测的选择，但数据不上默认情况下重叠在地图上。是为一个 UNION ALL 行集之间包含映射地域行集的覆盖映射，如果您有一个表，其中包含映射分级显示数据的一个从简便的方式。 图 1 显示了从 SQL Server Management Studio 可视化工具中 Mondial 数据库表示多个 700 城市一点。此 SQL Server 查询生成它的是:

SELECT geog, name 
FROM Mondial.dbo.city
WHERE geog IS NOT NULL

图 1 A Mondial 数据库城市表中的点的行集合

一种更好的选择是使用映射重叠默认情况下提供，如 图 2 所示一个商业或共享可视化工具。您会注意 SQL Server Management Studio 中的空间结果选项卡显示的多个 700 点与映射贴行集。生成它的 SQL Server 查询如下所示：

SELECT geog, name 
FROM Mondial.dbo.city
WHERE geog IS NOT NULL
UNION ALL
SELECT geog, cntry_name 
FROM SpatialSamples.dbo.cntry00

图 2 A 行集用图的点的重叠

您可以使用的一个可视化工具是 Craig Dunn Geoquery 2008 程序。这是可视化一个免费工具用于 SQL Server 2008 查询结果，其中包括映射的预测，，还可以选择您的空间数据对象的颜色和线条粗细。也可以编写代码来显示 Microsoft Virtual Earth 或 Google 地球中的数据，但这不在本专栏的范围之内。

Geometry 数据类型

让我们回到在其他我涉及简要，在几何图形数据类型的空间数据类型，并讨论其中将使用几何图形相对于地理位置。几何图形数据类型表示的 x 的平面平面，y 坐标，相对于地理位置的代表经度和纬度，从中心 angles 到地球（ellipsoidal 坐标）的图面中。几何图形数据类型可以用于问题，不必考虑泥土的形状，相对较小 planar 面（如 Office cubical 布局或仓库。当您在设法在隔间土地中查找同事的矩形 Office 地球的曲度无关紧要，因此使用几何数据类型，并且线性计算就足够了。正在处理的几何图形时, 您设置您的度量单位和您的点的源（让例如是您所在的仓库的左下角的 0,0)。

虽然有时使用几何类型在地球上的空间位置的则过程会稍更为复杂。有许多不同的方式将地球映射到一个坐标系统以及所以这会影响计算的方法，如 STArea 和 STDistance，您的空间数据实例需要指定一个 SRID。

无论您正在使用的几何图形或地理位置，一个 SRID 需要。对于在 Geometry 数据类型如果您已批准坐标系统和度量单位自己（例如，当映射您的办公室布局）您需要选择 SRID 0，指定了未知的或本地空间的引用系统。SRID 0 是在 SQL Server 几何图形数据类型的默认值。有关地理位置，很重要，选择在 SRIDs SQL Server 都能够使用之一。SRID 4326 是默认值和由 GPS 系统使用的。

内置 SQL Server 地理方法可使用系统元数据表中枚举的 390 空间的引用任何的系统 sys.spatial_reference_systems。这些空间的引用系统来自在字符空间调查组的 (EPSG)geodetic 参数注册表。EPSG 不再存在，其功能已被 absorbed 欧洲空间调查组中。您可以假设油和气体制造商的关联将希望确保他们具有地球上最准确的位置，当他们浏览。

每个 SRID 不仅名称（大多数使用米）的度量单位，但还包括一个指定基准（组引用点地球上) geoid、坐标系统字符串并映射项目。有关坐标系统的详细信息，您应阅读 Isaac Kunen 白皮书"简介空间坐标系：为圆角的 Planet 平面映射."

区分大小写，用于在 SRID SQL Server Geography 数据类型计算是试图使用不具有相同的 SRID 空间类型的实例之间空间方法（是例如距离和 Intersects）将返回值为 NULL。我将讨论更多信息 SRIDs 更高版本之间转换。

空间的可视化效果

我已经介绍的某些空间数据和事实很多使用可视化工具提供数据的一个图示视图的最明显的应用程序。假设，而不是只使用世界上或状态在美国的国家 / 地区的我需要通过对县边界投影它使可视化我的数据或与 congressional Districts 或人口普查数据关联它？在这种情况下我想需要获得我称之为"空间引用数据"。空间引用数据可以从公共 Web 站点下载或如在从公司购买环境系统研究 Institute (ESRI). 请注意的可用性可用的公用空间引用数据会有所不同广泛根据本地自定义。在的美国的大部分数据我所谈论，县边界和人口普查的数据位于多免费比许多其他国家 / 地区。

使您已经消失出到您收藏的公用文件的网站，例如，美国人口普查局 Tiger Web 站点并下载地理位置的引用文件。但这些文件是不对 SQL Server 地理位置列在表中的 ESRI Shapefile 格式。

如何入这些文件 SQL Server？和许多公开文件使用 SRID 4269，不 SRID 4326 您 geocoder 发出的。您如何转换不同 SRIDs 之间，以便所有您空间方法不会返回 NULL？需要此解决的问题是多个简单的转换问题比一个提取转换加载 (ETL) 问题。想要和人口普查跟踪号码或填充图形的非空间数据显示。和，尽管您可以（在理论上）转换之间动态 SRIDs 数据，这将降低您的查询很多。这绝对是一个 ETL 问题。

SQL Server 2008 实际上不会提供以下帮助您在框中的任何内容。有如 Shapefiles 加载到 SQL Server 表的 Morten Nielsen Shape2SQL 工具的程序。但 Shapefiles 不只的第三空间文件，并且可能想要执行 SRIDs 或其他更为专业的转换之间的转换。有各种要负责空间数据转换和大容量加载中的第三方商业产品。它们包括 SQL Server 的 SAFE 软件 FME、 Manifold 的空间扩展 SQL Server，和地理信息系统（GIS) 产品 ESRI 的线，当然。这些可以将数据移动到 SQL Server 从或移动数据 SQL Server 到成熟的 GIS。我已经成功地使用 SAFE 软件的产品包括的转换，库的 SQL Server 支持几乎所有的空间的数据格式，甚至 SQL Server ETL 系统提供一系列的组件调用 FME： SQL Server Integration Services。

SQL Server 空间

将选择使用移动 in and out of SQL Server 的数据的格式取决于您的组织的特定需求以及特定应用程序的需要。SQL Server 数据类型本质上支持 WKB 和 WKT 格式，以及地理位置的标记语言 (GML) 格式。SQL Server 公开 Geometry 和地理位置类型和在基于.NET 的库中名为 Microsoft.SqlServer.types.dll 相关的方法。执行此库都将随 SQL Server，不仅因为它基于.NET，您可以下载其作为 SQL Server 2008 功能包的一部分并移动到中间层或客户端，如有必要的计算和转换。请记住仅在服务器端提供空间索引，以便优化您的空间数据查询。

SQL Server 实际上存储的.NET 类 sqlgeometry 的实例和 SqlGeography ；类和方法对库的内部。您可以在 SQL Server 查询中使用空间的方法或封装 T-SQL 或 SQLCLR 存储过程、用户定义的函数和触发器中操作。通过 SqlGeometry 和 SqlGeography 作为.NET 用户定义的类型和用户定义聚合像我之前使用的该 GeographyUnionAggregate 中的成员，您可以扩展基本的功能。

空间数据库还包括一个生成器 API，用于优化加载和自定义转换和聚合的。此配置一个 SqlGeometryBuilder SqlGeographyBuilder 和接收器接口（IGeometrySink 和 IGeographySink）可用于执行优化构造空间数据实例。您实现接收器接口允许您开始或结束图表，添加行，并设置适当的 SRID 之一。一旦拥有数据位置，您只需调用填充方法创建您设置的实例。

向上覆盖

此处我已经介绍的元素表示只是一些喜欢使用空间的数据类型和行业标准空间库包含已添加到 SQL Server 的功能。通过 geocoding，公用引用数据，收集或您自己的空间数据仓库和分支机构可以存储在 SQL Server 的管理与用于其他类型的业务数据在相同的管理工具的数据编制索引查询，分析将整个新维度以您的业务。

若要帮助您开始，请记住任何类型的能够使用 GPS 系统捕获的数据可以导入的而且可与您的数据库数据的其余部分一起使用。GPS 功能变得更 commonplace 电话、照相机、单位，和其他设备和设备中, 和这么多的信息系统现在包含地址数据，GPS 数据集成到有趣的功能真正满足用户的需要的那些）是确实没有限制。因此，保持优化的一些令人兴奋的发展。

Bob Beauchemin是一个数据库中心应用程序的 Practitioner 和架构师，在合作伙伴课程的作者和教师、编写器和开发人员技能SQLskills. 他的写入书籍和文章 SQL Server、数据访问和集成技术和数据库安全性。您可以 brian.noyesbobb@sqlskills.com.

其他资源