在 SQL Server 2005 中使用表值函数来实_sqlserver

Gyorgy Fekete 和 Alex Szalay
约翰霍普金丝大学

Jim Gray
Microsoft（联系作者）

适用于
Microsoft SQL Server 2005

摘要：本文说明了如何使用 c# 和表值函数将空间搜索函数（“邻近点的点”和“多边形内的点”）添加到 Microsoft SQL Server 2005。使用此库可以在不编写任何特殊代码的情况下向应用程序中添加空间搜索。此库实现了来自约翰霍普金丝大学的公共域 C# 分层三角网格 (HTM) 算法。该 C# 库使用一组标量值函数和表值函数连接到 SQL Server 2005。这些函数起空间索引的作用。

您可以下载本文档的 Microsoft Word 版本。

本页内容
 简介
 表值函数：主要概念

 使用表值函数添加空间索引
 数据集
 典型查询
 结束语
 附录 A：参考资料
 附录 B：基本 HTM 例程

简介
空间数据搜索常用于商业和科学应用程序。结合在针对天文领域构建 SkyServer (http://skyserver.sdss.org/)方面的努力，我们开发了一种空间搜索系统。SkyServer 是一个几千 GB 大小的数据库，其中收录了大约 3 亿个天体对象。天文学家需要对该数据库进行的许多查询都会涉及到空间搜索。典型的查询包括：“邻近此点的对象有哪些”、“此区域内包含哪些对象”以及“哪些区域与此区域有重叠”？

在本文中，我们向天文学家的赤经/赤纬 (ra/dec) 天球（天空）网格添加了纬度/经度 (lat/lon) 地球网格。这两种网格大致相同，但并非精确对应，传统顺序 lat-lon 与 dec-ra 相对应。这一顺序上的颠倒迫使我们必须明确坐标系。我们将格林尼治子午线-赤道大地坐标系称为 LatLon 坐标系。该库支持三种坐标系：

• 格林尼治纬度-经度，称为 LatLon。
 
• 天文赤经-赤纬，称为 J2000。
 
• 笛卡尔 (x, y, z)，称为笛卡尔。 
 

天文学家使用弧度分作为标准的距离度量。由于一海里为一弧度分，因此距离转换非常简单。其他许多概念也极为相似。为了说明这些问题，本文将演示如何使用此空间库针对两个 USGS 数据集构建空间索引，这两个数据集是：美国城市和美国流量计。本文使用这些索引和一些空间函数提供了若干示例，来说明如何搜索邻近某一点的城市、如何查找邻近某一城市的流量计以及如何查找某个州（多边形区域）内的流量计或城市。

我们认为此方法具有通用性。可以向几乎所有应用程序中添加空间数据核心架构和空间数据函数以便进行空间查询。这些概念也适用于其他的多维索引方案。例如，这些技术可用于搜索颜色空间或其他任何低维度度量空间。

返回页首
表值函数：主要概念
关系代数的主要概念是，每个关系运算符均使用一个或多个关系，并生成一个输出关系。SQL 是对这一概念的语法修饰，使您可以定义关系（数据定义语言）和用选择-插入-更新-删除语法来操作关系。

定义自己的标量函数使您可以对关系数据库进行扩展：您可以发送邮件，可以执行命令脚本，还可以计算非标准标量和聚合值，例如 tax() 或 median()。

但是，如果您可以创建表，则可以成为关系引擎的一部分：既是关系表的生成者，也是其使用者。这就是 OLEDB 的概念，此概念允许任何数据源生成数据流。这也是 SQL Server 2000 表值函数蕴含的概念。

在 Transact-SQL 中实现表值函数很简单：

create function t_sql_tvfPoints()
returns @points table (x float, y float)
as begin
insert @points values(1,2);
insert @points values(3,4);
return;
end

如果可以完全在 Transact-SQL 中执行函数，这样做就可以了。但是，在 SQL Server 2000 中，要在 Transact-SQL 外部实现 OLEDB 数据源或表值函数确实非常困难。

而 SQL Server 2005 集成了公共语言运行库，可以容易地创建表值函数。您可以创建列表、数组或任意 IEnumerable 对象（可以对其进行 foreach 操作的任意对象），然后将其转换为表。

[SqlFunction(TableDefinition   = “x float, y float” ,
FillRowMethodName = “FillPair”)]
public static IEnumerable csPoints( )
   {
int[,] points = { { 1, 2 }, { 3, 4 } };
return (IEnumerable) points;
   }

在 Visual Studio 中编译这段代码，然后单击 Deploy（部署）。表值函数将被安装到数据库中。

返回页首
使用表值函数添加空间索引
对于索引，人们存在许多困惑。事实上，索引非常简单：它们只是带有一些特殊属性的表：

• SQL Server 仅有一种关联（按值）索引：B 树。B 树可以具有多字段键，但最常选择的是第一个字段。
 
• 从概念上说，B 树索引是由 B 树键字段、基表键字段以及您要添加到索引的任何包含字段所组成的表。
 
• B 树索引将根据索引键（例如邮政编码或客户 ID）来排序，以便按该键进行查找或顺序扫描会很快。
 
• 索引通常比基表小，只包含最重要的属性，以便与检查整个表相比，在索引中进行查找所涉及的字节数小得多。通常情况下，索引非常小，以致能完全装在主内存中，从而省去更多的磁盘存取。
 
• 当您执行索引查找时，可能仅仅是搜索索引（基表的垂直部分），也可能是先搜索索引，再利用基表主键将符合要求的索引逐行加入基表中（书签查找）。 
 

中心思想是，空间索引可为您提供一小部分数据。索引会告诉您查找的位置，并通常附带一些有用的搜索信息（专家将其称为包含列或覆盖列）。索引的选择性表明了初始缩减的程度（图 1 所示的粗略子集）。找到相应的子集后，将仔细检查该子集的每个成员并舍弃假正值。图 1 中的菱形框指明了该过程。好的索引只含有少量的假正值。在整篇文章中，我们都将使用图 1 中的说法（粗略子集和仔细检查）。

图 1

B 树和表值函数可以如下组合，以使您可以构建自己的空间索引来生成粗略子集：

•	创建一个函数，以生成将相关数据聚集在一起的键。例如，如果项目 A 和 B 相关，则 A 和 B 的键在 B 树键空间中应该是邻近的。
•	创建一个表值函数，以便在给出了对所需子集的说明后，返回包含所有相关值的键范围（“覆盖”）列表。

您无法始终让每个键与其所有相关项邻近，因为键是在一个维度中排序的，而相关项是在二维或更高维数的空间中与其邻近。但是可以尽量这样做。假正值与正确答案的比率衡量了您所采取的方式的好坏。

标准方法是，找到某一条空间填充曲线，并使键空间沿该曲线穿过。例如，使用标准墨卡托地图，可以将西北中的每个人分配给西北键范围，将东南中的每个人分配给东南键范围。图 2 显示了第二种顺序空间填充曲线，它横穿所有这些象限，按顺序分配键。西北-西南象限中的每个人都具有键前缀 nwsw。如果您的区域与图 2 中所示的圆圈类似，就可以知道您的键范围

   key between 'nwsw' and 'nwse'

图 2

此搜索空间占整个表的八分之一，并且含有大约 75% 的假正值（由圆圈外但位于两个方框内的区域表示）。改进不大，但传达了一种概念。更好的索引要使用更精细的单元格分区。如果单元格足够精细，则聚合区域中的假正值就会非常少。要详细查看空间填充曲线和空间分区树，您可以参阅 Hanan Samet [Samet] 的书籍。

现在，我们要定义一种空间填充曲线：分层三角网格 (HTM)，它特别适用于球面。地球是圆的，天球也是圆的，因此，这种球面系统对于地理学家和天文学家来说非常方便。我们可以对任何度量空间做一些类似的事情。空间填充曲线提供了一些键来作为空间索引的基础。那么，如果某人具有所需的区域时，我们的表值函数将为其提供一组适当的键范围供查找（图 1 中的粗略筛选）。这些键范围将覆盖带有球面三角形的区域（称为 trixel），这与图 2 中覆盖圆圈的两个方框非常相似。搜索函数只需查看这些 trixel 的键范围内的所有对象，以确定它们是否符合要求（图 1 中的仔细检查）。

我们可以用一个具体的例子进行说明，假设有一个对象表

create table Object (objID bigint primary key,lat   float, -- Latitudelon   float, -- LongitudeHtmID bigint) -- The HTM key

和一个距离函数 dbo.fDistanceLatLon(lat1, lon1, lat2, lon2)，该函数可计算出两点之间的海里（弧度分）数。进一步假设以下表值函数给出了位于某个 lat-lon 点的定长半径范围内的 HtmID 点的键范围列表。

define function fHtmCoverCircleLatLon(@lat float, @lon float, @radius float)returns @TrixelTable table(HtmIdStart bigint, HtmIdEnd bigint)

然后，以下查询会查找旧金山 (lat,lon) = (37.8,-122.4) 周围 40 海里范围内的点。

select O.ObjID, dbo.fDistanceLatLon(O.lat,O.lon, 37.8, -122.4)from fHtmCoverCircleLatLon(37.8, -122.4, 40) as TrixelTable join Object O on O.HtmID between TrixelTable.HtmIdStart        -- coarse testand TrixelTable.HtmIdEnd      where dbo.fDistanceLatLon(lat,lon,37.8, -122.4) < 40   -- careful test

现在，我们必须定义 HTM 键生成函数、距离函数和 HTM 覆盖函数。下一步我们将以两组美国地质空间数据集为例执行这些操作。如果您不相信其中包含的对象达数十亿，请访问 http://skyserver.sdss.org/ 并浏览一下该网站。该网站使用相同的代码来对几千 GB 的天文数据库进行空间查找。

本文主要讲述如何使用 SQL 表值函数和像 HTM 这样的空间填充曲线来构建空间索引。同样地，我们将 HTM 代码本身当作一种在别处 [Szalay] 存档的黑色方框，我们只关注如何使其在 SQL 应用程序内适合我们的需要。

返回页首