SQL Server 2008空間數(shù)據(jù)應(yīng)用系列六：基于SQLCRL的空間數(shù)據(jù)可編程性

作者：Beniao 2011-03-22 09:17:12

基于SQL Server 2008的核心組件SQLCRL提供了友好的的可編程性支持，SQL Server數(shù)據(jù)庫(kù)引擎將使用由CLR提供的功能無(wú)縫地提供了多種功能，其中包括使用空間數(shù)據(jù)類(lèi)型goegraphy和goemetry的數(shù)據(jù)的可編程性支持。

友情提示，您閱讀本篇博文的先決條件如下：

本文示例基于Microsoft SQL Server 2008 R2調(diào)測(cè)。
具備 Transact-SQL 編程經(jīng)驗(yàn)和使用 SQL Server Management Studio 的經(jīng)驗(yàn)。
具有使用 Microsoft Visual Studio 進(jìn)行 Microsoft .NET Framework開(kāi)發(fā)的經(jīng)驗(yàn)。
熟悉或了解Microsoft SQL Server 2008中的空間數(shù)據(jù)類(lèi)型。
具備相應(yīng)（比如OGC）的GIS專(zhuān)業(yè)理論知識(shí)。

Microsoft .NET應(yīng)用通常都是宿主在操作系統(tǒng)平臺(tái)的.NET Framework之上，如果想在SQL Server 2008中使用.NET進(jìn)行托管代碼，SQL Server 2008將運(yùn)行一個(gè)屬于自己的.NET Framewrok平臺(tái)運(yùn)行環(huán)境（SQLOS），SQLOS和.NET CLR將共享數(shù)據(jù)庫(kù)引擎進(jìn)程空間，因此基于SQL Server 2008的CLR也被稱(chēng)為的SQLCLR。基于SQL Server 2008的核心組件SQLCRL提供了友好的的可編程性支持，SQL Server數(shù)據(jù)庫(kù)引擎將使用由CLR提供的功能無(wú)縫地提供了多種功能，其中包括使用空間數(shù)據(jù)類(lèi)型goegraphy和goemetry的數(shù)據(jù)的可編程性支持。

一、SQLCLR & .NET CLR互編程性

可以在SQL Server 2008的安裝目錄下找到SQLCLR與.NET CLR共享的空間數(shù)據(jù)類(lèi)型共享庫(kù)組件（Microsoft.SqlServer.Types.dll），該組件除了支持基于SQL Server 2008的空間數(shù)據(jù)類(lèi)型數(shù)據(jù)庫(kù)編程，同時(shí)也支持基于.NET Framework的面向?qū)ο缶幊蹋▽?duì)VB.NET、C#的多語(yǔ)言的支持，該組件位于如下安裝目錄中：

C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Types.dll

通過(guò)下面這個(gè)示例程序可以演示基于Microsoft.SqlServer.Types.dll在SQLCLR和.NET CLR之間的雙向互支持性。

SQLCLR：定義一個(gè)地理坐標(biāo)點(diǎn)  
declare @geom geometry;  
set @geom = geometry::Point(107.04352,28.870554,4326);  
select @geom;  
------------------------------------------------------  
.NET CLR：定義一個(gè)地理坐標(biāo)點(diǎn)  
static void Main(string[] args)  
{  
    var point = SqlGeometry.Point(107.04352, 28.870554, 4326);  
    Console.WriteLine(point.STX);  
    Console.WriteLine(point.STY);  
    Console.WriteLine(point.ToString());  
}

在.NET CLR中可以使用所有SQLCLR編程中所提供的編程接口，也就是說(shuō)在.NET CLR中變成不僅僅只是局限于再空間對(duì)象的定義上，還包括對(duì)象的相關(guān)屬性、方法的支持，如下代碼塊演示了SQLCLR和.NET CLR編程實(shí)現(xiàn)地理測(cè)距函數(shù)的對(duì)比。

declare @geom geometry;  
set @geom = geometry::Point(107.04352,28.870554,4326);  
declare @end geometry;  
set @end = geometry::Point(103.84041, 29.170240,4326);  
select @geom.STDistance(@end);  
----------------------------------------------------------------  
var pointStart = SqlGeometry.Point(107.04352, 28.870554, 4326);  
var pointEnd = SqlGeometry.Point(103.84041, 29.170240, 4326);  
var result = pointStart.STDistance(pointEnd);  
Console.WriteLine("地理距離：" + result + "(米)");

二、創(chuàng)建空間對(duì)象到數(shù)據(jù)庫(kù)

Microsoft.SqlServer.Types.dll提供友好的.NET CLR編程性，同樣我們可以將在.NET CLR中創(chuàng)建的對(duì)象插入到空間數(shù)據(jù)庫(kù)中進(jìn)行查詢(xún)，以便于做空間分析、計(jì)算。下面演示通過(guò).NET CLR構(gòu)造一個(gè)多邊形空間對(duì)象并插入到SQL Server 2008數(shù)據(jù)庫(kù)中。

static void Main(string[] args)  
{  
    //定義一個(gè)多邊形  
    var polygon = SqlGeography.STGeomFromText(  
        new SqlChars(  
        new SqlString("POLYGON ((-114.01611328125 42.0003251483162, -114.0380859375 42.0003251483162," 
            + "-113.994140625 37.0200982013681, -109.05029296875 37.0200982013681, -109.09423828125 41.0130657870063, " 
            + "-111.07177734375 41.0462168145206, -111.07177734375 42.0003251483162, -114.01611328125 42.0003251483162))",  
            111)),  
            4326);  
    var sql = "insert Cities (CityName,CityLocation) values ('test','" + polygon.ToString() + "')";  
    InsertToDB(sql);  
}  
 
private static void InsertToDB(string sql)  
{  
    using (var conn = new SqlConnection(ConfigurationManager.AppSettings["SQL2008"]))  
    {  
        if (conn.State == ConnectionState.Closed) conn.Open();  
        using (var cmd = new SqlCommand(sql, conn))  
        {  
            int row = cmd.ExecuteNonQuery();  
        }  
    }  
}

三、查詢(xún)數(shù)據(jù)庫(kù)空間數(shù)據(jù)

首先來(lái)看看基于SQL Server Management Studio 查詢(xún)剛剛?cè)霂?kù)的記錄，通過(guò)空間結(jié)果可以直接預(yù)覽查詢(xún)結(jié)果。

declare @city geometry;  
select @city = CityLocation from Cities where ID=5;  
select @city;  
select @city.STArea() as 面積;--求面積 
 
0x000000000104080000000000000008815CC035B18AA70A0045400000000070825CC035B18AA70A00454000000000
A07F5CC0CECAEE93928242400000000038435BC0CECAEE93928242400000000008465BC0BCF1C323AC814440000000
0098C45BC01572BD6EEA8544400000000098C45BC035B18AA70A0045400000000008
(1 行受影響)
面積
22.6802255629445
(1 行受影響)

同樣可以使用.NET CLR對(duì)其進(jìn)行編程實(shí)現(xiàn)，這其實(shí)和查詢(xún)普通數(shù)據(jù)是沒(méi)有區(qū)別的，只是將查詢(xún)結(jié)果轉(zhuǎn)為為的是空間數(shù)據(jù)類(lèi)型。可以通過(guò)如下代碼實(shí)現(xiàn)查詢(xún)空間數(shù)據(jù)到應(yīng)用程序中。

static void Main(string[] args)  
{  
    var sql = "select CityLocation from Cities where ID = 5";  
    var result = QueryDB(sql);  
    var polygon = SqlGeography.STGeomFromText(  
        new SqlChars(  
        new SqlString(result)), 4326);  
    Console.WriteLine(polygon.ToString());   
}  
 
private static string QueryDB(string sql)  
{  
    using (var conn = new SqlConnection(ConfigurationManager.AppSettings["SQL2008"]))  
    {  
        if (conn.State == ConnectionState.Closed) conn.Open();  
        using (var cmd = new SqlCommand(sql, conn))  
        {  
            return cmd.ExecuteScalar().ToString();  
        }  
    }  
}