SPATIAL DATA SUPPORT IN SQL SERVER 2008

發行項
08/26/2015

本文章是由機器翻譯。

在 [資料表

在 SQL Server 2008 空間的資料支援

Bob Beauchemin

可從 MSDN 程式庫的程式碼下載
瀏覽線上的程式碼

內容

使用地理位置的資料類型
空間索引
視覺化檢視
幾何的資料型別
空間的視覺效果
SQL Server 空間的資料庫
向上換行

SQL Server 2008 會提供一些增強功能，包括新的資料型別、新的 T-SQL 陳述式和增量變更 Microsoft.NET Framework 支援、 XML 的支援和服務的仲介功能。「最大且最令人興奮的變更，為開發人員是空間的資料，SQL Server 2008 支援的最，— 一個功能強大加入 SQL Server 程式設計人員的工具箱]。在這個的資料行我將提供概觀的空間的資料來說明一些有趣的用法。

空間的資料定義為 2D 或 3D 影像所代表的資料。SQL Server 2008 支援 2D 的影像，並且可能會在未來版本中加入 3D]。空間的資料可以是進一步細分幾何資料 (可以使用涉及 Euclidian 幾何形狀的計算的資料) 和地理位置為基礎的資料 (識別地理位置和界限在地球上的資料)。

使用地理位置的資料類型

讓我們來開頭，簡單的應用程式的空間很有用，為最傳統的線上交易處理 (OLTP) 系統的客戶的資料。空間的資料很有用在這種情況下，因為每個客戶有位址。您通常是視為位址 Street，城市、狀態、國家 (地區)，和郵遞區號的程式碼的位址是在地球上的點但也可以表示為一個經緯組。技術上來說的位址可以組成在資料的土地，可以表示為多邊形，但我們堅持到單一的點在這種情況下只為了簡單起見。瞭解客戶所在為經緯，允許您回答問題，例如：

特定客戶的三個最接近銀行分支是什麼？
最近給客戶的銷售人員存留？
您的公司有一個 10 英里半徑的執行個體，Seattle，Washington，內多少客戶？
多少客戶會 Live 超過 2 英哩從您最接近的分公司位置？

在程序，經緯組轉換街道地址的稱為「位址 geocoding 」。許多線上的服務 (包括 MapPoint、 Virtual Earth 和 Google 地球) geocoding 提供服務。將在美國街道地址您可以點，封裝 MapPoint geocoding SQLCLR 中函式中的 Web 服務呼叫。這所示，程式碼範例. 但是，應該您存放在經緯在您的 SQL Server 資料庫嗎？

SQL Server 2008，並隨附兩個必要的資料型別儲存空間的資料：幾何形狀和地理位置。用於使用者定義型別表示它們可以有屬性和方法的.NET 架構實作這兩種資料型別。讓我們堅持與地理位置的資料型別現在因為這更接近對應到手邊的問題。

您可以在一個的資料表中定義一個地理位置型別，簡單 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 為基礎的資料型別中，有一些有趣指向之程式碼的相關說明。先，STPointFromText 屬於一個靜態的方法，因此它必須使用 datatype::method 語法進行呼叫。此外，.NET 為基礎的資料型別的方法名稱是區分大小寫，因此使用了，STPointFromText 必須使用確切的大小寫。

還必須一件更重要的是指向出事： geocoder 函式必須編碼成傳回為「 XY 位置而不是經緯。開啟 Geospatial 協會會定義其 WKB，並已知的二進位 (WKB) 會格式化為使用 x，y 座標配對。因此，什麼的傳入 STPointFromText 到必須 POINT(longitude/Latitude)，不 POINT(Latitude/longitude)。如果您想而使用經緯，SQL Server 就會建構使用地理標記語言 (GML)，使用經緯 XML 字彙的地理區執行個體。或幾何資料型別使用三個參數上如果您點沒有特殊的靜態方法： Latitude、緯度和 SRID。

既然您已經 geocoded 您的客戶資訊您可以移繼續進行和 geocode 位置 (或其他分支 Office 資訊、倉儲的資訊或任何與相關位置)，您的銀行分公司和您位業務員位置，以及。假設您在完成與名為客戶、業務員和分公司的資料表。每一個必須型別的地理位置，表示其位置 (讓我們來呼叫它 geog 為了簡單起見) 資料的行。

您可以使用這項資訊回答先前所述的問題。此，您需要使用地理位置的資料型別上的方法。計算兩個地理位置的物件之間的距離方法不令人驚訝的是，是，呼叫 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？對於這項查詢，您會將周圍的 Seattle (表示城市) 限制的任一個多邊形] 或 [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 語法，而不是要使用靜態方法，例如 STPointFromText type::method 語法 (英文)。此外，請注意當您要計算兩個大的距離，或判斷是否有一個地理區的執行個體交集另一個，您使用語法 Instance1.method(Instance2)。

若要回答有關 Live 接近銀行分支以上兩個英哩的客戶數問題，您必須能夠彙總的所有銀行分支，大，與每個，周圍的兩個英哩的一個緩衝區，然後再尋找與大的這個集合不交集的客戶。地理區資料型別會具有名為 STUnion，方法，但它指定它為地理區的執行個體，會傳回該執行個體和另一個執行個體之間的聯集上的方法。

這不是我必須記住 Set-以彙總。幸好，SQL Server 2005 引進.NET 的程式碼可以撰寫的使用者定義彙總而且您可以使用其中一個此處以取得一組值的彙總等位。不需太遠到細節的程式碼的使用者定義彙總，需要四個方法初始設定： Init (回應初始化) 「結束 (傳回的回應) 」、「累積 (名為每個資料列，) 和合併 (稱為合併來自多個執行緒處理時)。

在 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 的空間的資料支援，包括空間編製索引。空間的索引，是一般的 B 型樹狀目錄索引，要讓空間的查詢執行速度，就像在 SQL Server 關聯式索引使執行速度更快的關聯式查詢。

二維空間的資料對應一維的 B 型樹狀目錄的鑲嵌完成；的是，區域將設定中，分成小 subareas 錄製，subareas 交集的每個空間的執行個體。地理位置的資料型別，這表示成 hemispheres 分割整個全球各地，並將每個半球則往投影到在平面。每個地理區的執行個體再包含一或多個小節 (並排) 這個平面的索引空間會包含資料列的每個執行個體所涵蓋的並排顯示；幾何形狀類型，您要指定您自己的矩形的座標系統，因為您可以指定界限 (週框 (Bounding Rectangle) 方塊)，您的空間索引涵蓋了。

有許多規則決定如何空間交集並排顯示的執行個體，對應到一個空間的索引中資料列，而且您可以定義如何正常方格系統將會對您在多個層級的空間索引。更多細節的資訊空間的索引 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 以上兩個英哩 Bank 分公司的「空間查詢來計算的客戶的 STIntersects 傳回 true] 和 [減去該數字，從客戶的總數目。

視覺化檢視

請注意在我曾經透過為止過幾個範例，它會是真的有用能夠看到空間中的資料列和資料行以外的表單資料。事實上，您可能已經注意到選取空間的資料型別本身時，會傳回的二進位表示。使用 ToString 方法或 STAsText 方法，就會傳回 WKB 格式。稍微更好的但它仍不提供您任何有關地理位置的概念除非您可以對應到專案經緯 / Latitude 組，在您的標頭。幾乎都需要圖形視覺化檢視，讓它更有用的空間的資料，並當您在談論的地理資料的視覺化檢視通常會包含對應]。

SQL Server Management Studio 2008 的空間結果索引標籤，在 [查詢結果] 視窗中包含簡單的視覺化檢視。這個視覺化檢視使用一個地理區或幾何資料行在查詢結果繪製格線上空間的資料型別。如果查詢結果中顯示空間的多個資料行，您可以選擇要以視覺化方式檢視。如果要顯示之資料行必須是 SQL Server 二進位格式，；使用 ToString () 或 STAsText() 不會使用視覺化檢視]。

地理區的資料型別請您即使取得對應的預測，例如 Mercator 或 Equirectangular 的選擇，但資料未在地圖上 overlaid 預設]。一個便宜且容易重疊對應，如果您有包含對應的大綱資料在資料表就是要在 UNION ALL 您的資料列集和資料列集，包含在對應的大之間。 [圖 1 ] 顯示表示一組超過 700 個城市的 SQL Server Management Studio 的視覺化檢視中的 Mondial 資料庫的點。這是 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 點與地圖的一個資料列集 Overlay

您可以使用一個視覺化檢視會是 Craig Dunn Geoquery 2008 的程式。這是視覺化可用檢視 SQL Server 2008 的查詢結果包含對應的預測，也可讓您選擇您的空間的資料物件的色彩和行寬度。您可能也撰寫程式碼在 Microsoft Virtual Earth] 或 [Google 地球中, 顯示您的資料，但這超過此資料行的範圍]。

幾何的資料型別

讓我們回到其他空間資料型別，我接觸簡短，幾何資料型別上，並討論的地理位置相對於會使用幾何形狀。幾何資料型別會表示 x 在一般的平面，而 Y 而不是以地理位置座標，表示經度和緯度、直角從中心 (至地球 (ellipsoidal 座標) 的介面。幾何資料型別無法用於問題，不需要考慮地的圖形，相對較小的平面介面，例如 Office cubical 版面配置] 或 [倉儲。當您嘗試在 [cubicle 土地] 中找出同事的矩形的 Office 會沒關係，道路地球的使用幾何資料型別，因此，並線性的計算就夠了。您正在處理幾何時, 您設定您的測量的單位] 和 [您的原始的點 (讓是您的倉儲的左下角落，例如 0，0)。

雖然有時候您也可以使用幾何型別在地球上的空間位置，處理程序都將是有點複雜。因為有多種不同的方式，要對應一個的座標系統在地球和因為這些影響計算的方法，例如 STArea 和 STDistance 時，您的空間的資料執行個體必須指定一個 SRID。

不論您使用幾何形狀或地理位置的 SRID 是必要的。幾何資料型別，如果您已指定座標系統和測量單位自己 (例如時對應您的辦公室配置),，則可以將選擇 SRID 0，指定的未知或本機的空間參考的系統。SRID 0 會是幾何資料型別在 SQL Server 中預設值。地理位置，很重要您選擇一個 SQL Server 使用的 [SRIDs。SRID 4326 是 GPS 系統所使用的預設值。

地理位置為基礎 SQL Server 內建的方法可以使用任何在 390 的空間的參考系統的系統中繼資料表，中的列舉 sys.spatial_reference_systems。這些空間的參考系統來自，歐洲石油調查 Group 的 (EPSG)geodetic 參數的登錄。EPSG 已經不存在，其功能已經被 absorbed 到歐洲石油調查群組。您可以假設的油及產生者的關聯會要確定它們有最精確的位置，在地球上時它們進行探索。

每個 SRID 會不只是名稱的量值 (大部分使用公尺) 單位，但也包含字串，指定一個個資材 (地球上的參考點組)、 geoid、座標系統，並對應投影。座標系統相關的更多資訊，您應該參閱 Isaac Kunen 白皮書」空間的座標系統的簡介：一般的導覽的圓角的！."

因為 SQL Server 地理區的資料型別的計算是在 SRID 所使用的敏感，嘗試使用空間方法 (例如，距離和 Intersects) 空間的型，相同的 SRID 的執行個體之間將會傳回 NULL 的值。我將討論詳細資訊稍後 SRIDs 之間轉換。

空間的視覺效果

我已涵蓋某些最明顯的應用程式空間的資料和事實上，許多這些使用視覺化檢視，以提供檢視資料的圖示。假設，而非只使用對應的世界的狀態，在美國國家，我要視覺化我的資料，藉由將它往投影對郡的界限，或相互關聯與 congressional 學區或 census 資料嗎？在這種情況下我就必須取得我呼叫空間的參考資料。空間的參考資料可以從公用網站下載或購買例如從公司環境系統研究局 (ESRI). 請注意可用公用空間的參考資料的可用性有所廣泛本機的自訂。在的美國大部分的資料，我已經有關，曾等郡界限 census 的資料位於更自由地使用比許多其他國家 / 地區。

讓您已消失出至您的我的最愛公用的網站，舉例來說，U.S.census Bureau Tiger 網站和地理位置為基礎的參考檔案的下載。但檔案 ESRI Shapefile 格式，不是SQL Server 資料地理區行資料表中。

您如何匯入到 SQL Server 的這些檔案？並公開可用的許多檔案使用 SRID 4269，不 SRID 4326 您 geocoder 發出。如何您之間轉換不同 SRIDs，讓所有您的空間方法不會傳回 NULL？需要以下解決問題可以是多個比簡單的轉換問題的發生解壓縮轉換載入 (ETL) 問題。您要使以及非空間的資料，如 census 追蹤號碼或擴展的數字。而且，雖然您無法 (在理論上) 轉換之間 SRIDs 即時資料，這會降低您的查詢明顯。這是絕對的 ETL 的問題。

SQL Server 2008 真的不會發行任何在協助您在此方塊中。有程式 (例如 SQL Server 資料表中載入 Shapefiles 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 空間的資料庫

在您的組織的特定需求] 和 [也上特定的應用程式的需要將視而定，您會選擇使用來移動資料進出 SQL Server 之格式。SQL Server 資料型別本質上支援，WKB 和 WKT 的格式和地理位置為基礎的標記語言 (GML) 格式。SQL Server 會公開幾何形狀和地理位置的型別] 和 [在名為 Microsoft.SqlServer.types.dll.NET 程式庫中關聯的方法。不僅會與 SQL Server，隨附此程式庫，但是因為它根據.NET，您可以下載它做為 SQL Server 2008 功能套件的一部分，並移至中介層或用戶端，必要的轉換與計算。記住伺服器端提供空間以最佳化您的空間的資料查詢的索引。

SQL Server 真的儲存在.NET 類別 sqlgeometry 的執行個體，並 SqlGeography ；在類別和方法都是內建至媒體櫃]。您可以使用 SQL Server 查詢中的空間的方法，或封裝您的作業在 T-SQL 或 SQLCLR 預存程序、使用者定義的函式和觸發程序。您可以使用 SqlGeometry] 和 [SqlGeography，為在.NET 使用者定義型別和使用者定義彙總像我在稍早所使用的 [GeographyUnionAggregate 成員，以擴充基底功能。

[空間] 資料庫也會包含一個可用來最佳化載入和自訂轉換的彙總 (Aggregation) 的產生器 API。這會組成一個 SqlGeometryBuilder SqlGeographyBuilder 和接收器介面 (IGeometrySink 和 IGeographySink) 您可以用來最佳化的執行空間的資料執行個體建構。您實作其中一個接收介面，可讓您開始或結束圖形、加入行，以及設定適當的 SRID。一旦您擁有資料，在位置，只需呼叫 populate) 方法，來建立，您已設定的執行個體。

向上換行

我已涵蓋在此處的項目，表示功能的已加入至 SQL Server 的空間的資料型別與空間業界標準的程式庫的只是一些愛好。您透過 geocoding，公用參考的資料收集，或您自己空間資料的倉儲和辦公室可以儲存在 SQL Server，管理使用相同的系統管理工具使用其他種類的商務的資料的資料，「索引」、「查詢，和分析將整個企業新維度。

若要可協助您自動，請記住 [任何種類的資料，您將能夠擷取 GPS 系統可以匯入，並可搭配您的資料庫資料的其餘部分]。GPS 功能變得更 commonplace 在電話、數位相機、 automobiles，及其他裝置和裝置，與這麼多的資訊系統現在包含位址資料，潛在的 GPS 資料整合到有趣的功能 — 那些確實符合使用者的需求 — 是真正的無限制。因此密切的一些令人興奮的發展。

Bob Beauchemin 使用資料庫中心應用程式 practitioner 和架構設計人員，在課程的作者和講師、寫入器和開發人員的技術合作夥伴SQLskills. 在 SQL Server、資料存取和整合的技術和資料庫安全性，他的寫入書籍與文章。您可以將他在到達bobb@sqlskills.com.

其他資源