SQL Server 2008 における空間データのサポート

2015-06-17

開発者によるデータベース構築

Bob Beauchemin

コードは MSDN コードギャラリーからダウンロードできます。
オンラインでのコードの参照

geography データ型を使用する
空間インデックス
ビジュアライザ
geometry データ型
空間の視覚化
SQL Server の空間データライブラリ
まとめ

SQL Server 2008 では、Microsoft .NET Framework、XML サポート、および Service Broker 機能への増分変更、新しいデータ型、新しい T-SQL ステートメントなど複数の機能拡張が提供されています。開発者にとって最も魅力的で大きな変更は、SQL Server 2008 で空間データがサポートされるようになったことです。SQL Server プログラマのツールボックスに強力な機能が追加されたということですね。このコラムでは、空間データの概要を簡単に説明し、興味深い使用法をいくつかご紹介します。

空間データは、2D 画像または 3D 画像により表現されるデータとして定義されます。SQL Server 2008 では、2D 画像をサポートしており、3D 画像も将来的にサポートする予定です。さらに、空間データは、幾何データ (ユークリッド幾何学に関連した計算を使用できるデータ) と地理データ (地球上の地理的な位置と境界を識別するデータ) に分類できます。

geography データ型を使用する

最初に、空間データの簡単なアプリケーションを見ていきましょう。顧客に関連した一般的なオンライントランザクション処理 (OLTP) システムです。この例で空間データが便利な理由は、すべての顧客に固有の住所があるということです。通常、住所は国/都道府県/市町村/番地/郵便番号という形式で表されますが、住所は地球上の特定の位置を示すものであり、緯度/経度の組み合わせで表すこともできます。技術的な見方をすると、住所はポリゴンとして表現可能な 1 区画を構成するものと見なすこともできますが、ここでは説明を簡単にするために、1 つの地点であることにこだわりましょう。顧客が住んでいる位置を緯度/経度として認識することにより、次に示す問いに答えることができます。

ある顧客から最も近い 3 つの銀行支店を挙げる。
ある顧客から最も近い場所に住んでいる営業担当者を特定する。
会社の半径 10 マイル以内に住む顧客の人数を算出する (たとえば、シアトル、ワシントンなど)。
自宅から最も近い支店から 2 マイルの範囲外に住む顧客の人数を算出する。

通常の住所表記から緯度/経度の組み合わせに変換するプロセスは、住所ジオコーディングと呼ばれます。さまざまなオンラインサービス (MapPoint、Virtual Earth、Google Earth など) が、ジオコーディングをサービスとして提供しています。合衆国の住所を特定の点に変換するために、MapPoint ジオコーディング Web サービスの呼び出しを SQLCLR 関数にカプセル化することができます。詳細については、コードサンプルを参照してください。ここで問題となるのは、SQL Server データベース内のどこに緯度/経度を格納するかということです。

SQL Server 2008 には、空間データを格納するのに必要な 2 つのデータ型が付属しています。geometry と geography です。両方とも、ユーザー定義型の .NET アーキテクチャを使用して実装されています。つまり、これらのデータ型ではプロパティおよびメソッドを使用できるということです。この例では、手近な問題により緊密に関連している地理データを扱う、geography データ型に注目しましょう。

単純な SQL 変数宣言 (DECLARE @g geography) を使用して、またはテーブル内の列として、geography 型のインスタンスを定義できます。また、このデータ型はさまざまな方法で初期化できます。点のインスタンスを表す geography 型の場合、最も簡単な方法は、geography 型の静的な STPointFromText メソッドの使用です。STPointFromText メソッドには、Open Geospatial Consortium の Well-Known Text 形式 (WKT) による点のテキスト表現 (POINT(x,y)) だけでなく、空間参照 ID (SRID) も必要です。SRID は、平面地球マッピングまたは球体地球マッピングに使用される空間参照系を識別します。ここでは、MapPoint ジオコーディング Web サービスで SRID 4326 に対応する GPS 座標が使用されるということを理解すれば十分です (これは World Geodetic System (世界測地系) 1984 (WGS 84) を表します)。SQL Server 2008 がサポートするすべての SRID を参照するには 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);

geography が .NET ベースのデータ型として実装されているため、このコードには興味深い点がいくつかあります。最初に、STPointFromText は静的メソッドであり、datatype::method 構文で呼び出す必要があります。また、.NET ベースのデータ型のメソッド名は大文字小文字を区別するため、STPointFromText の大文字小文字は正確に表記する必要があります。

もう 1 つ重要なことがあります。geocoder 関数は、位置を緯度/経度ではなく "xy" として返すようにコーディングする必要があります。Open Geospatial Consortium は、WKB 形式および Well-Known Binary (WKB) 形式で座標ペアの x,y を使用するように定義しています。したがって、STPointFromText に渡すのは POINT(Latitude/Longitude) ではなく、POINT(Longitude/Latitude) である必要があります。緯度/経度の場合、SQL Server では、Latitude/Longitude を使用する XML ボキャブラリの Geographic Markup Language (GML) を使用して geography インスタンスを構築します。また、点の場合、geometry データ型で 3 つのパラメータ (latitude、longitude、および SRID) を受け取る専用の静的メソッドを使用できます。

顧客情報をジオコーディングした後で、銀行の支店の位置 (または、他の支店の情報や倉庫の情報など位置に関連する情報) や営業担当者の位置をジオコーディングします。最後に、顧客、営業担当者、および支社という名前のテーブルを作成し、各テーブルに、位置を示す geography 型の列 (単純化するために、これを geog と呼びましょう) を作成します。

この情報を使用して、先ほどの問いに答えることができます。ただし、geography データ型でいくつかのメソッドを使用する必要があります。STDistance というメソッドは、文字どおり、2 つの geography オブジェクト間の距離を計算するときに使用されます。STBuffer メソッドと STIntersects メソッドは、前述の 3 番目の問い (シアトルの半径 10 マイル以内に住む顧客の人数を算出する) に使用できます。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 マイル以内に住む顧客の人数を算出するクエリはどうなるでしょうか。シアトルの市の境目の辺りにバッファを配置して (市の境界のポリゴンとして表すか、またはシアトル市街として指定した点で表す)、そのバッファと交差するすべての点 (顧客) を選択します。コードは次のようになります。

-- 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));

これらの例を見ると、geography 型の利用方法について興味深い事柄がわかります。たとえば、geography データ型のインスタンスメソッドである STBuffer を使用する場合、STPointFromText などの静的メソッドに使用する type::method 構文ではなく、instance.method 構文を使用します。また、2 つの地点の距離を計算する場合や、1 つの geography インスタンスが別のインスタンスと交差するかどうかを調べる場合は、Instance1.method(Instance2) 構文を使用します。

住居から最も近い銀行の支店までの距離が 2 マイルを超える顧客の数を求めるには、すべての銀行の支店の地域 (それぞれ周囲 2 マイルをバッファとする) を集計して、これらの地域の集合と交わらない顧客を見つける必要があります。geography データ型には STUnion という名前のメソッドがあります。ただし、geography インスタンスのメソッドとして指定することにより、対象のインスタンスと別のインスタンスとの和集合を返すものです。

この方法は、私が思い描いたセットベースの集計ではありません。さいわい、SQL Server 2005 では .NET コードで記述できるユーザー定義集計が導入されました。ここでその集計関数を使用して、値のセットの和集合を集計しましょう。ユーザー定義の集計のコーディングについて少し詳しく (あまり深入りしない程度に) 説明します。このコーディングには、4 つのメソッドの初期化が必要です。Init (答えを初期化する)、Terminate (答えを返す)、Accumulate (各行に対して呼び出す)、および Merge (複数のスレッドから処理をマージするときに呼び出す) です。

集計では、Accumulate メソッドを呼び出すたびに、単純に現在の行の geography と answer geography を結合できます。実際は、この集計を自分で記述する必要はありません。これは、CodePlex の SQL Server Spatial Tools プロジェクトの一部です。この集計では、2 マイルのバッファに関する問いに答えるために、各行に対してバッファを渡すこともできます。クエリは次のようになります。

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

これまでの例でおわかりのように、住所をジオコーディングして郵便局仕様の住所表記と共に格納することにより、ビジネスの価値をアプリケーションに反映できます。

空間インデックス

ビジネスの規模が大きくなるにつれ、個別の顧客と各営業担当者との距離または各銀行支店との距離を計算するための時間がどんどん長くなります。SQL Server 2008 の空間データのサポートには空間インデックスが含まれています。空間インデックスは、通常の B ツリーインデックスです。SQL Server でクエリを高速化したリレーショナルインデックスと同じように、空間インデックスによって空間クエリを高速化できます。

2 次元空間データから 1 次元の B ツリーへのマッピングは、テセレーションという手法で行うことができます。エリアを小さなサブエリアに分けて、各空間インスタンスに交差するサブエリアを記録する手法です。geography データ型の場合は、地球全体を半球というサブエリアに分けて、各半球を平面に投影するということを意味します。各 geography インスタンスによって、この平面の 1 つ以上のサブセクション (タイル) が覆われます。空間インデックスにはインスタンスに覆われる各タイルの行が含まれます。geometry 型を使用する場合は、固有の直交座標系を指定するので、空間インデックスに覆われる境界 (境界ボックス) を指定できます。

タイルに交差するインスタンスを空間インデックスの行にマッピングする方法には、いくつかルールがあります。また、空間インデックスのグリッドシステムをどのくらい細かくするかを複数のレベルで定義できます。空間インデックスの詳細については、SQL Server オンラインブックを参照してください。

顧客システムへの適用では、次のようなデータ定義言語 (DDL) を使用して顧客テーブル内の geog 列に空間インデックスを定義します。

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

各顧客の住所が 1 つの点であることから、このインデックスを粒度の高いグリッドにより定義するのが最適です。線や多角形の場合は多数のタイルに交わる可能性がありますが、点は 1 つのタイルにのみ表示されます。

空間インデックスは特定の空間メソッドにのみ使用するということに注意してください。現在では、次のような geography の述語と共に使用されます。

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

geography ではなく geometry を使用する場合、空間インデックスは STContains、STOverlaps、STTouches、および STWithin の各メソッドで使用できますが、1 (True) が返される場合のみです。空間インデックスが動作するしくみを考えると、"銀行の支店から 2 マイルの範囲外に住む顧客数" を求める空間クエリは、STIntersects が True を返す顧客をカウントしてその数を顧客の総数から減算するという手法にする必要があるでしょう。

ビジュアライザ

これまでに取り上げたいくつかの例で、空間データを行と列の形式以外の方法で表示すると非常に便利であることに注目してください。実際に、空間データ型を選択することによりバイナリ表現が返されることにお気付きでしょう。ToString メソッドまたは STAsText メソッドは WKB 形式を返します。WKB は多少は扱いやすい形式ですが、地理的な位置を直接的に示してはくれません。頭の中で緯度/経度ペアを地図上に置き換える必要があります。空間データを扱いやすくするには、ほとんどの場合、グラフィックビジュアライザが必要です。地理データが対象であるとき、ビジュアライザと言えば通常は地図の機能を備えたものを指しています。

SQL Server Management Studio 2008 には、[クエリ結果] ウィンドウの [空間結果] タブという形で、簡単なビジュアライザが付属しています。このビジュアライザは、クエリ結果の geography 列または geometry 列と連携して、空間データ型をグリッド上に表現します。クエリ結果に複数の空間列が含まれる場合、その中の 1 つを選択して視覚化することができます。表示する列は、SQL Server のバイナリ形式である必要があります。ToString() または STAsText() を使用するとビジュアライザが動作しません。

geography データ型の場合は、メルカトル図法や正距円筒図法などの地図投影を利用できますが、既定ではデータを地図に重ね合わせることはできません。地図のアウトラインデータを含むテーブルに対しては、地図への重ね合わせを行う低コストの簡単な手法があります。対象の行セットと、地図の各地域を含む行セットの間で UNION ALL を実行すればよいのです。図 1 では、SQL Server Management Studio ビジュアライザで、Mondial データベースの 700 を超える都市のセットが点によって表現されています。この生成に使用された SQL Server クエリは次のとおりです。

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

図 1 Mondial データベースの City テーブルからの点の行セット

さらにお勧めの方法としては、既定で地図の重ね合わせ機能を備えている商用またはシェアウェアのビジュアライザを使用することです (図 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 地図の重ね合わせによる点の行セット

使用できるビジュアライザとして、Craig Dunn 氏の Geoquery 2008 プログラムがあります。これは、地図投影を含む、SQL Server 2008 クエリ結果用の無料のビジュアライザです。空間データオブジェクトの色や線の太さを選択できます。また、このコラムでは取り上げませんが、Microsoft Virtual Earth または Google Earth でデータを表示するコードを記述することもできます。

geometry データ型

先ほど簡単に紹介しましたが、もう 1 つの空間データ型、geometry データ型に話を戻しましょう。ここでは、どのような場合に geography ではなく geometry を使用するかについて説明します。geography が緯度と経度、つまり地球の中心から地表面に対する角度 (楕円体座標) を表すのに対して、geometry データ型は x 座標と y 座標によって平面を表します。geometry データ型は、地球の形を考慮する必要のない問題に適しています。たとえば、オフィスの立方体レイアウトや倉庫などの比較的小さな 2 次元サーフェスに使用できます。パーティションで四角形に区切られた同僚の席の場所を示す場合、地球の曲率は関連していないため、geometry データ型および線形の計算で十分です。geometry の場合、測定単位と起点を設定します (たとえば、倉庫の左下角に 0,0 を設定します)。

geometry 型を地球上の空間位置に使用する場合もありますが、その場合はプロセスが少し複雑になります。地球を座標系にマッピングするさまざまな方法があり、それぞれが STArea や STDistance などの計算メソッドに影響を与えるため、空間データインスタンスで SRID を指定する必要があります。

geometry または geography のいずれを使用する場合も、SRID が必要です。geometry データ型の場合、自分で座標システムおよび測定単位を決めるのであれば (たとえば、オフィスのレイアウトをマッピングするときなど)、未知またはローカルの空間参照システムを指定する SRID 0 を選択します。SRID 0 は、SQL Server の geometry データ型の既定値です。geography の場合、SQL Server で使用可能な SRID の中から 1 つを選択することが重要です。SRID 4326 は既定値であり、GPS システムで使用されるものです。

SQL Server に組み込まれている地理的なメソッドでは、sys.spatial_reference_systems というシステムメタデータテーブルに列挙された 390 の空間参照系の中から任意のものを使用できます。これらの空間参照系は、EPSG (European Petroleum Survey Group) の測地パラメータレジストリに基づいています。なお、EPSG の機能は Oil and Gas Producers (OGP) Surveying and Positioning Committee に吸収されており、EPSG は現在は存在していません。石油およびガスの生産にかかわる組織の調査においては、最も正確な地球上の位置の把握が必須であることは言うまでもありませんね。

個別の SRID には、測定単位 (大半はメートル) の名前だけでなく、ジオイド、座標系、地図投影、およびデータム (地球上の参照点のセット) を示す文字列が含まれています。座標系の詳細については、Isaac Kunen によるホワイトペーパーの「空間座標系の概要 : 球形である惑星の平面地図」を参照してください。

SQL Server の geography データ型の計算は使用される SRID の影響を受けるため、SRID が異なる空間型のインスタンス間で空間メソッド (Distance や Intersects など) を使用すると NULL が返されます。SRID 間の変換の詳細については後で説明します。

空間の視覚化

空間データの最もわかりやすいアプリケーションをいくつか紹介し、その多数がビジュアライザを使用して視覚的にデータを表示している事実についても説明しました。では、次のようなケースではどうでしょう。世界の国々または合衆国の州を示した地図を使用するのではなく、データを群の境界に投影することにより、または下院選挙区や国勢調査のデータに関連付けることにより、データを視覚化するとします。この場合、"空間参照データ" というものを取得する必要があります。空間参照データは、公開 Web サイトからダウンロードするか、または ESRI (Environmental Systems Research Institute) などの企業から購入することができます。公開空間参照データを無料で入手できるかどうかは、それぞれの地域の慣習によって異なります。米国では、上記のようなデータ (群の境界や国勢調査のデータなど) は他の国に比べると無料で使用できる割合が高いようです。

お気に入りの公開 Web サイト (たとえば、米国勢調査局の Tiger Web サイト) にアクセスし、地理的な参照ファイルをダウンロードしたとします。そして、これらのファイルが SQL Server テーブル内の geography 型の列ではなく、ESRI のシェープファイル形式であるとします。

これらのファイルをどのようにして SQL Server にインポートすればよいでしょうか。公開されている多数のファイルで SRID 4269 が使用されていますが、自分の使用しているジオコーダによる出力が SRID 4326 である場合はどうでしょうか。あらゆる空間メソッドで NULL が返されないようにするには、さまざまな SRID をどのように変換すればよいでしょうか。ここで解決する必要があるのは、単純な変換に関する問題ではなく、ETL (抽出、変換、読み込み) の問題です。たとえば、国勢調査単位数や人口統計などの非空間データを使用するとします。(理論上は) クエリ実行中に SRID 間でデータを変換できますが、その結果、クエリの速度が大幅に低下するでしょう。これはまさに ETL 問題です。

SQL Server 2008 には、このような問題に対処するための機能はありません。Morten Nielsen 氏の Shape2SQL ツール (シェープファイルを SQL Server テーブルに読み込む) のようなプログラムを使用します。サードパーティの空間ファイルには、シェープファイル以外の種類もあります。また、場合によっては SRID 間の変換や、より特化されたその他の変換も必要になるでしょう。各種の空間データ変換や一括読み込みに特化された、各種のサードパーティ商用製品を利用できます。たとえば、SAFE Software の FME for SQL Server、Manifold の Spatial Extender for SQL Server、そして言うまでもなく ESRI の一連の地理情報システム (GIS) 製品などです。これらを使用して、データを SQL Server に移動したり、SQL Server から本格的な GIS に移動したりできます。SAFE Software の FME for SQL Server という製品を問題なく使用できました。この製品では、変換のライブラリや、ほとんどすべての空間データ形式のサポートが提供されます。また、SQL Server ETL システム (SQL Server Integration Services) の一連のコンポーネントも提供されます。

SQL Server の空間データライブラリ

SQL Server へのデータの移動または SQL Server からのデータの移動に使用する形式は、組織に固有の要件および特定のアプリケーション固有の要件に基づいて選択します。SQL Server のデータ型は、基本的に WKB 形式と WKT 形式をサポートし、GML (Geographic Markup Language) 形式もサポートします。SQL Server は geometry 型と geography 型、および .NET ベースのライブラリ (Microsoft.SqlServer.Types.dll) 内の関連付けられたメソッドを公開します。このライブラリは SQL Server に付属しているというだけでなく、.NET に基づいているため、SQL Server 2008 Feature Pack の一部としてダウンロードし、必要に応じて計算と変換を中間層またはクライアントに移動することができます。空間データのクエリを最適化するための空間インデックスを提供できるのは、サーバー側のみであることに注意してください。

実際、SQL Server は .NET クラス (SqlGeometry および SqlGeography) のインスタンスを格納します。これらのクラスおよびメソッドはライブラリに固有のものです。SQL Server クエリで空間メソッドを使用したり、ストアドプロシージャの T-SQL または SQLCLR、ユーザー定義関数、およびトリガに操作をカプセル化したりできます。私が使用した GeographyUnionAggregate のように、SqlGeometry や SqlGeography を .NET ユーザー定義型およびユーザー定義集計のメンバとして使用することにより、基本的な機能を拡張することができます。

空間データライブラリには、読み込みの最適化やカスタム変換および集計に使用できるビルダ API も含まれています。これは、SqlGeometryBuilder および SqlGeographyBuilder と、空間データインスタンスの最適な構築に使用できるシンクインターフェイス (IGeometrySink および IGeographySink) で構成されています。シンクインターフェイスの 1 つを実装することにより、図の開始または終了、線の追加、適切な SRID の設定などを行うことができます。データを所定の場所に配置したら、後は設定済みのインスタンスを作成する Populate メソッドを呼び出すだけです。

まとめ

ここで説明した内容 (空間データ型および業界標準の空間ライブラリなど) は、SQL Server に追加された機能のほんの一部です。ジオコーディングで収集したデータ、公開参照データ、または倉庫やオフィス用の固有の空間データを SQL Server に格納できます。さらに、他の種類のビジネスデータと同じ管理ツールでの管理、インデックス指定、クエリ実行、および分析を行うことにより、ビジネスに新しい要素を導入することができます。

始めるにあたり、GPS システムで取得できる種類のデータは、インポートしてデータベースの残りのデータと共に使用できることを思い出してください。電話、カメラ、自動車、およびその他のデバイスやアプリケーションで GPS 機能がますます普及しており、現在は多数の情報システム内に住所データが格納されています。将来的には、ユーザーのニーズを十分に反映した形で、GPS データが興味深い機能に統合されるでしょう。その可能性は無限大です。魅力的な開発に常に注目していてください。

Bob Beauchemin は、データベース指向アプリケーションの専門家およびアーキテクト、学習コースの作成者および教官、ライター、および SQLskills のデベロッパースキルパートナーです。これまで、SQL Server、データアクセスおよび統合のテクノロジ、およびデータベースセキュリティについてさまざまな書籍や記事を執筆しています。連絡先は bobb@sqlskills.com です。

次の方法で共有

その他のリソース