国土数値情報の令和2年の医療圏データの文字化けが直っていた

SQL Server

 以前の投稿(Shape2SQL でシェープファイルを SQL Server 2008 R2 にアップロードする)で国土数値情報ダウンロードサービスの医療圏データが文字化けしていると記述した.改めてダウンロードして SQL Server にアップロードしたところ,いつの間にか文字化けが直っていた.以前にも河川データの文字化けを指摘したことがあるが,こちらも修正されていた(国土数値情報の河川データが一部直っていた件).どうやら国土交通省に指摘すると修正してくれるらしい.

“国土数値情報の令和2年の医療圏データの文字化けが直っていた” の続きを読む

国土数値情報の医療機関のPointデータをダウンロードしQGIS経由でSQL Serverにアップロードする

SQL Server

 国土数値情報ダウンロードサービスには医療機関や医療圏の位置情報もある.今回は医療機関の Point データをダウンロードし,SQL Server にアップロードする方法を見つけたので備忘録として記載する.

“国土数値情報の医療機関のPointデータをダウンロードしQGIS経由でSQL Serverにアップロードする” の続きを読む

第14章 インデックス作成 (Beginning Spatial with SQL Server 2008)

SQL Server

 効率的なインデックス作成は,迅速かつ効率的に結果を探索するデータベースアプリケーションを作成するために重要である.空間データを蓄積するためにデザインされる geometry 型および geography 型に加えて,SQL Server 2008 はまた空間データを使用するための新しいインデックスの型を含んでおり,それは(驚くべきことでもないが)空間インデックスと呼ばれる.本章では空間インデックスがどのように動作し,空間インデックスを使って空間クエリの速度をどのように改善するか説明する.

付記 SQL Server の汎用クラスター化インデックスおよび非クラスター化インデックスは多くの異なるデータ型のインデックスを作成するのに使われ,そこには int 型,char 型および datetime 型を使って蓄積される値を含んでいる.しかし,空間インデックスはgeometry 型および geography 型の列の上でしか作成されず,これらのデータ型の列にのみ空間インデックスは追加できる.

“第14章 インデックス作成 (Beginning Spatial with SQL Server 2008)” の続きを読む

国土数値情報の洪水浸水想定区域データをSQL Serverにアップロードし郵便番号から検索する

SQL Server

 地球温暖化に伴い,台風の大型化や洪水被害の拡大が懸念されている.今回は国土交通省の公表している洪水浸水想定区域データを SQL Server にアップロードしたのでその経緯を報告する.

 このデータは2022年9月時点で令和3年のものであり,毎年更新されていくものと思われる.関係各位はデータ更新が大変だろうが,頑張っていただきたい.

“国土数値情報の洪水浸水想定区域データをSQL Serverにアップロードし郵便番号から検索する” の続きを読む

第 7 章 ジオコーディング (Beginning Spatial with SQL Server 2008)

SQL Server

 これまで数章にわたって,多くの異なる技術を紹介してきた.その技術により SQL Server 2008 内に空間データを追加できる.geometry 型および geography 型の静的メソッドを WKT, WKB および GML などのジオメトリ表現と共に直接使用する,Virtual Earth ウェブコントロール上にプロットポイントにより空間の特徴を視覚的にマークする,あるいは,様々な他の空間データフォーマットから既に存在する空間データをインポートする.本章では,SQL Server において空間情報のアイテムを定義するのを助けてくれる最後の一つの技術について考察しよう.ジオコーディングである.

注意 本章で議論しているジオコーディングの方法は .NET アセンブリの生成を必要とする.それにより SQLCLR の機能を拡張し,外部ウェブサービスとの連携が可能となる.このアセンブリを生成するのに使われたサンプルコードに従うには,Microsoft Visual Studio を使わなくてはならない.使用可能な Visual Studio には多くのエディションが存在する.本章でのコードは Microsoft Visual Basic 2008 Express Edition でも Microsoft Visual C# 2008 Express Edition でも動作し,どちらも無料で http://www.microsoft.com/express/download/ からダウンロードできる.

ジオコーディングとは何か?

 SQL Server 2008 で導入された新しい機能である geometry 型でも geography 型でも空間データ型が何であれ,ほぼすべての既存の SQL Server データベースはすでに空間情報の形態のいくつかを含んでいる.つまり,データのアイテムの位置を記述することを許す情報である.この空間情報は顧客やサプライヤーの住所,郵便番号,配送経路やあるいは営業部長の担当する都市や地域の名前であるかもしれない.この既存の情報に基づいて空間解析を実行できたら有用ではないだろうか?それこそまさにジオコーディングが可能にしてくれることである.

 ジオコーディングの実際は,アイテムや場所,例えば住所,ランドマークの名前,あるいは郵便番号などの位置を記述した文字情報を取得することと,空間アプリケーションで使われる情報に関連した構造的な空間表現を渡すことである.多くのジオコーディング法は,それぞれのデータのアイテムに関連する正確な位置を表現する単一の Point ジオメトリの座標を返す.例えば,ホワイトハウスの住所は合衆国大統領の公邸であるが,1600 Pennsylvania Avenue NW, Washington DC, 20500 である.仮にこの住所をジオコードしようとしたら,この住所の位置する Point に関連する緯度 38.8980°, 経度 -77.0365 ° を得るだろう.ジオコーディングの過程を Figure 7-1 に示す(略).

 ジオコーディング機能を提供するにはいくつもの異なる方法がある.あるジオコーディングツールはデスクトップベースのアプリケーションである一方,他のサービスはウェブにアクセスするものである.本章では,読者が Microsoft の MapPoint Web Service から Find service を集約し SQL Server 2008 に直接ジオコーディング機能を付加する方法を示そうと思う.

チップス 単一の住所をジオコードしたいだけなら,http://maps.live.com にある Microsoft Live Search Maps を使ってオンラインで行うことができる.トップページのサーチフィールドを使って住所を検索すると,問題の位置について地図を考えたとき,シェアをクリックする(地図の右上のコーナーに位置している).生成された URL リンク内に,&cp= 直後の2つの数字に注目する.これがその住所の緯度と経度の座標であり,その地図が考えたものである(EPSG:4326 空間参照系を使っている).

Map Point Web Service

 Microsoft ウェブサイトによると,MapPoint Service とは

Microsoft によりホストされる SOAP API を使った XML ウェブサービスであり,エンタープライズおよび独立したソフトウェア企業に使われ,位置ベースのサービスをウェブアプリケーションに集約する.http://www.microsoft.com/virtualearth/platform/mappoint.aspx

付記 MapPoint Web Service は Microsoft MapPoint と同一ではない.それは Microsoft Windows 向けのデスクトップアプリケーションであり,旅行の計画に主に使われる.

 第5章で紹介した Virtual Earth ウェブサービス同様,MapPoint Web Service は地理空間コンテンツに関連する範囲のサービスを提供し,それには関心のある地点近傍を発見し地点間の経路を発見する能力が含まれる.Virtual Earth API がウェブページのマップオブジェクトを管理するために JavaScript 経由で開発者にリクエストを発行させるのに対して,MapPoint Web Service はインターネット経由で Simple Object Access Protocol (SOAP) 法によりアクセスされる.本章でジオコーディングを実行する MapPoint は「発見」サービスであり,次のメソッドで提供されるものである.

  • Find()
  • FindAddress()
  • FindByID()
  • FindByProperty()
  • FindNearby()
  • FindPolygon()
  • FindNearRoute()
  • GetLocationInfo()
  • ParseAddress()

 これらのメソッドはすべて地球上の地物のジオコーディングに関連する様々な機能を提供する.多くの一般的なジオコーディングの応用は,例えば顧客やサプライヤーの住所から地点の座標を取得するものである.これは Find サービスの行う FindAddress() メソッドであり,本章で扱うメソッドである.

チップス FindServiceSoap クラスの完全な詳細は MSDN ライブラリ http://msdn.microsoft.com/en-us/library/aa502416.aspx で見ることができる.

SQL Server から MapPoint Web Service にアクセスする

 ジオコーディング機能を SQL Server に追加するため,.NET Base Class Libray (BCL) で提供されるメソッドを利用して,インターネット経由で MapPoint Web Service に接続する .NET メソッドを生成する.SQL Server 2008 は .NET Framework CLR をホストしているため,SQL Server から T-SQL を使って MapPoint Web Service に相互作用でき,住所の詳細を提供し,SQL Server 内で直接に CLR からジオコードされた結果を取得することができる.これは T-SQL 単独では不可能な機能を提供するために SQL Server 内で .NET がどう使われるかの別の例である.

 本章では,SQL Server 2008 データベースにジオコーディング機能を追加するために次のステップを示そう.

  1. MapPoint Find サービスに接続する .NET クラスを生成し,郵便番号を記述したパラメータのセットを送る.家屋名/家屋ナンバー,住所,市,地域,郵便番号および国名.そのクラスはサービスから反応を受け,与えられた住所を表現する Point の WKT 構文に変換する.
  2. SQL Server 2008 にインポート可能なコンパイル済みアセンブリファイル (.dll 拡張子) に .NET クラスをビルドする.
  3. SQL Server 内で,アセンブリで定義された .NET メソッドを呼び出すのに使われる新しい T-SQL 関数を定義する.
  4. ジオコードされた結果の WKT 表現を STPointFromText() メソッドに提供して,住所から新しい Point インスタンスを生成する.

 始める前に,MapPoint Web Service で使うアカウントにサインアップする必要がある.これについては次のセクションで述べる.

MapPoint Web Service にサインアップする

 MapPoint Wev Service は2つの異なる環境を提供する.

  • お試し環境は無料で使えるサービスで,お試し,デモンストレーションや概念実証目的のアプリケーション開発時に使われる.
  • 本番環境は商用環境であり,MapPoint を使う生きたアプリケーションを配備する時は必ずこれを使わなければならない.

 どちらの環境を使う前でも,Virtual Earth Platform デベロッパーアカウントをまず登録しなくてはならない(MapPoint Web Service と Virtual Earth は姉妹サービスであり,同じアカウント詳細を使ってアクセスされる).本章でのコード例では,筆者はお試し環境を使うが,読者は無料のデベロッパーアカウントを使うことができる.仮に読者が商用環境を使うために登録されたなら,要求される資格証明と適切な参照を代替する必要がある.評価開発者アカウントを要求するには,次のウェブページの指示に従うように.https://mappoint-css.live.com/MwsSignUp/Default.aspx.

 必要な登録フォームを一度完了させると,開発者アカウント ID とパスワード設定方法の指示が含まれた e-mail を受け取るだろう.一度登録プロセスが完了したら,アカウント ID とパスワードを安全な手元に保存するよう注意すること,ジオコーディング機能を実行するために後ほど SQL Server に MapPoint Web Service にアクセスさせる必要があるためである.

注意 MapPoint Web Service にアクセスするアカウントは Windows Live ID にリンクしているが,同じユーザー名とパスワードを使っているわけではない.

.NET アセンブリを生成する

 SQL Server 内部からジオコーディングを実行するため,SQLCLR により実行されるカスタムの .NET アセンブリをビルドしよう.これは SQL Server 内部で .NET CLR を使う利益の別の例である.BCL 内に含まれるいかなるメソッドをも使う SQL Server の機能を拡張させることができる..NET アセンブリを生成しコンパイルするのに最も簡単な方法は,Microsoft Visual Studio のような統合開発環境 (IDE) を使うことである.使用可能な Visual Studio のエディションはいくつかあるが,本章のコードリストは Visual Studio 2005 または Visual Studio 2008 で動作し,無料で利用可能な Visual Basic および Visual C# Express エディションに含まれ,http://www.microsoft.com/express/download/ からダウンロードできる.

 .NET アプリケーションは言語に対して中立であることを銘記されたい.つまり,.NET コードは C# や VB.NET を含む多くの共通言語仕様 (CLS) の一つで記述されている.どの CLS 言語で書かれたコードも BCL にアクセスし共通言語ランタイム (CLR) エンジンを使って実行される.必要なコードは C# および Visual Basic の両方で記述し,楽に感じる言語を選択できるようにしよう.

付記 次のステップでは Visual Basic 2008 Express エディションを使って .NET アセンブリの生成を記述する.Visual Studio の異なるバージョンを使ってアセンブリを生成するときは,いくつかのメニューアイテムは異なるヘッダーで見えているかもしれない.

新しいプロジェクトを作成する

 最初のタスクは新しいクラスファイブラリプロジェクトを生成することであり,次のこれらのステップを参照のこと.

  1. Visual Studio メニューバーから,「ファイル」→「新しいプロジェクト」を選ぶ(Ctrl+Nを押下する).
  2. 「新しいプロジェクト」ダイアログボックス内で,Figure 7-2 に示す(略)が,Class Library テンプレートを選択し,新しいプロジェクト名をタイプする.この例では,プロジェクトに Geocoder と名付けた.
  3. OKをクリックする.

チップス Visual Studio Professional エディションや Team System を使っているなら,ジェネリック Class Library テンプレートよりも SQL Server Project テンプレートを使うことができる.それは Visual Studio から SQL Server にアセンブリを自動でデプロイすることができ,ついでにデバッグ訂正機能を提供してくれる.

アプリケーションウィンドウ下部にあるステータスバーには次のメッセージが表示されている.

Creating project ‘Geocoder’… project creation successful.

 一度プロジェクトが生成されると,プロジェクトのワークスペースが出現し,プロジェクト内部に Class1.vb ファイルがメインウィンドウに見えるようになる(あるいは Visual C# を使うなら Class1.cs).

プロジェクトを設定する

 ジオコーディング機能を追加する前に,そのプロジェクトの設定にいくつもの変更を加える必要がある.これらの変更を行うため,まずメインメニューバーから「プロジェクト」→「Geocoder プロパティ」を選んでプロジェクトプロパティページをまず開く.

出力ディレクトリを設定する

 Visual Studio がコンパイル済みアセンブリ (DLL) ファイルを生成するための出力ディレクトリを指定する必要がある.メモできて簡単にアクセスできる位置を選ぶことが重要である.なぜなら後でコンパイル済みアセンブリを SQL Server にインポートするときは手動でファイルパスを指定しなければならないからである.コンパイル済みアセンブリを置く位置を設定するには

  1. プロジェクトプロパティページの左側のタブのリストから「コンパイル」を選ぶ.Figure 7-3 に示すように(略)(Visual C# 2008 Express を使うなら,このタブは代わりに「ビルド」と呼ばれる).
  2. 「ビルド出力パス」フィールドに生成したコンパイル済みアセンブリを置きたい位置を入力する.この例では,パスを C:\Spatial\ と設定した(Visual C# 2008 Express エディションではそのフィールドは単に「出力パス」と呼ばれる).
シリアル化アセンブリを生成する

 我々のジオコーディング機能はインターネット接続経由で MapPoint Web Service にデータを送受信することに頼っている.この方法で .NET アセンブリが Web 資源にアクセスするときはいつでも,すべてのデータはまずシリアル化,つまりネットワーク経由で伝達するためバイトの系列に変換されなければならない.Visual Studio は我々のコードに必要なシリアル化を含む付加的なアセンブリを生成することができ,この付加的なアセンブリを生成することを確立するため,次のステップを実行する.

  1. プロジェクトプロパティページの「コンパイル」(VB.NET)「ビルド」(C#)タブを確認すること.
  2. ページ下部の Advanced Compile Options ボタンをクリックする.Advanced Compiler Setting ダイアログボックスが出現するだろう.Visual C# を使っているなら,代わりにビルドタブの下部にスクロールするだけである.
  3. Generate Serialization Assemblies ドロップダウンリストで On を選ぶ.
  4. OKをクリックして Advanced Compiler Setting ウィンドウを閉じる(Visual Basic 2008 Express エディションのみ).
ウェブ参照を追加する

 我々のジオコーディング内で Find サービスメソッドを使う前に,.NET プロジェクトに MapPoint Web Service およびその提供するメソッドを提供するための方法を必要とする.これは web reference を追加することでなされる.web reference は Web Services Description Language (WSDL) ファイルの URL を与える.これは XML ファイルの一種で,ウェブサービスおよびその提供するメソッドを記述したものである.次のステップで MapPoint Web Serveice に web reference を追加できる.

  1. メインメニューバーから「Project」→「Add Service Reference」を選ぶ.
  2. Add Serveice Reference ダイアログボックスで,左下コーナーにある Advanced ボタンをクリックする.
  3. Service Reference Setting ダイアログボックスで,ウィンドウ下部にある Add Web Reference ボタンをクリックすると Add Web Reference ダイアログボックスが開き,これらのステップを通じて参照のための Figure 7-4 に示したように(略)例を完了させる.
  4. URL ボックスに,MapPoint Web Serivce お試し環境の URL を入力する.それは http://staging.mappoint.net/standard-30/mappoint.wsdl である.商用環境を使いたいなら,次の URL を指定すべきである.http://service.mappoint.net/standard-30/mappoint.wsdl
  5. URL ボックス右側の Go ボタンをクリックする.しばらくすると,一つのサービス mappoint がこの URL で見つかったとのメッセージを受け取るだろう.メインウィンドウには Find サービスの次の記述を含めてそのサービスに関する記述が表示される.「Find サービスは住所,位置,緯度経度座標および関心のある地点を見つけ出します(MapPoint Web Serveice データ,あるいはあなたの用途のため私どものホストするあなたのデータいずれからでも)」
  6. Web reference Name フィールドは我々のプロジェクトでのサービスを参照するのに使う名前を与える.それは実際に net.mappoint.staging と書かれているが,便利ではないため,代わりになにか簡便なものに変更する.例えば MapPoint など.
  7. Add Reference ボタンをクリックする.

 新しく生成した MapPoint reference は Solution Explorer ペインに出現し,Geocoder プロジェクトの Web Reference フォルダー内部に含まれる.これがプロジェクト設定のためのものであり,やっと我々は我々のジオコーディング関数を書いて付き合うことができるようになった.

付記 Solution Explorer ペインは普通ならワークスペースの右側に見える.もし見えないなら,「View」→「Solution Explorer」を選ぶか,Ctrl+Alt+L を押下する.

ジオコーディング関数を追加する

 今やそのプロジェクトはセットアップされ,ジオコーディング自体を実行するユーザー定義関数を追加する時が来た.最初にプロジェクトを生成した時に追加した Class1.vb/Class1.cs ファイル内に必要なコードを追加しよう.そして Solution Explorer ペイン内の関連するファイル名をダブルクリックして Visual Studio ウィンドウ内でこのファイルをロードしよう.コードを VB .NET を使って書いているなら(Visual Studio 2005/2008 または Visual Basic 2008 Express エディションのどちらかを使っているなら),Class1.vb ファイルを編集して Listing 7-1. に示したように読む.代わりにコードを C# を使って開発しているなら(Visual Studio 2005/2008 または Visual C# 2008 Express エディションを使っているなら),Class1.cs ファイルを編集して Listing 7-1. に示したように読む.

 どちらの場合でも,アカウント ID とパスワードを ENTER YOUR ACCOUNT ID HERE および ENTER YOUR PASSWORD HERE の場所に挿入する必要がある.(このユーザー名は Virtual Earth Platform 開発者アカウント ID であり MapPoint Web Service 登録後に送られてくるもので,そのパスワードはアカウント確定時に選んだパスワードである.)findAddressSpec.DataSourceName プロパティの値は変更する必要があるかも知れないが,それは読者がジオコードしたい住所の含まれる国による(詳細は次のコードで説明する).

Listing 7-1. Class1.vb ファイル(VB .NET Project Types のためのもの)

'Import references for generic .NET functionality
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Net
'Import the web reference to the MapPoint Web Service
Imports Geocoder.MapPoint
Partial Public Class UserDefinedFunctions
  <microsoft.sqlserver.server.sqlfunction()>
  Public Shared Function geocode(
    ByVal AddressLine As SqlString, _
    ByVal PrimaryCity As SqlString, _
    ByVal Subdivision As SqlString, _
    ByVal PostalCode As SqlString, _
    ByVal CountryRegion As SqlString) As SqlString
    'Initialize the MapPoint Find service
    Dim findService As New FindServiceSoap
    'Provide the logon credentials
    Dim UserName As String = "ENTER YOUR ACCOUNT ID HERE"
    Dim Password As String = "ENTER YOUR PASSWORD HERE"
    findService.Credentials = New Net.NetworkCredential(Username, Password)
    'FindAddressSpecification contains the details passed to the function
    Dim findAddressSpec As New FindAddressSpecification
    'Build a new address object from the parameters provided to the function
    Dim address As New Address
    address.AddressLine = AddressLine.ToString
    address.PrimaryCity = PrimaryCity.ToString
    address.Subdivision = Subdivision.ToString
    address.PostalCode = PostalCode.ToString
    address.CountryRegion = CountryRegion.ToString
    findAddressSpec.InputAddress = address
    'Specify the data source in which to search for the address
    findAddressSpec.DataSourceName = "MapPoint.NA"
    'Create the options to limit the result set
    Dim findOptions As New FindOptions
    'Filter the result to only show LatLong information
    findOptions.ResultMask = FindResultMask.LatLongFlag
    'Only return the first matching result
    Dim findRange As New FindRange
    findRange.StartIndex = 0
    findRange.Count = 1
    findOptions.Range = findRange
    'Apply the options to the specification
    findAddressSpec.Options = findOptions
    'Call the MapPoint Web Service and retrieve the results
    Dim findResults As FindResults
    findResults = findService.FindAddress(findAddressSpec)
    'Create the WKT representation of the geocoded result
    Dim WKT As New SqlString
    If findResults.Results.Length > 0 Then
      WKT = "POINT" & _
        findResults.Results(0).FoundLocation.LatLong.Longitude & " " & _
        findResults.Results(0).FoundLocation.LatLong.Ltitude & ")"
    ELSE
      WKT = "POINT EMPTY"
    End If
    'Return the result to SQL Server
    Return WKT
  End Function
End Class</microsoft.sqlserver.server.sqlfunction()>

Listing 7-2. Class1.cs ファイル(C# Project Types のためのもの)

 コードを Visual Basic や C# のいずれに基づいて生成しても,両者のコードセットは同じ機能を実行する.

  • FindServiceSoap クラスの新しいインスタンスを生成する.
  • findService.Credential プロパティを使って,接続に使う資格証明を設定する.先述したように,Username の値 (ENTER YOUR ACCOUNT ID HERE) および Passeord (ENTER YOUR PASSWORD HERE) を変更して読者が MapPoint Web Service にサインアップした時に取得したアカウント情報を反映させなくてはならない.
  • メソッドに提供される文字列パラメータに基づいて InputAddress (ジオコードされる住所)を設定する.
  • findAddress.DataSourceName プロパティを使って,住所を検索するデータソースを選択する.MapPoint Web Service に使われる情報は,いくつかのデータソースに分割されており,異なる国の情報を保持している.読者のジオコードしたい住所の位置する国のデータを含むデータソース名を指定しなくてはならない.Table 7-1 に MapPoint Web Service で利用可能なデータソースを列挙しており,それらの含む住所の詳細の国である.Listing 7-1. および 7-2. に示したコード例では MapPoint.NA データソースを指定しており,合衆国,カナダおよびプエルトリコで使われる.代わりに英国の住所をジオコードしたいなら,例えば,コードを変更して findAddressSpec.DataSourceName = “MapPoint.EU” と読む.
Table 7-1. 住所のジオコードをサポートする MapPoint Web Serive
DataSourceName Description
MapPoint.AP アジア太平洋データソースでオーストラリア,ニュージーランド,香港,台湾の住所レベルのジオコードをサポートする.
MapPoint.BR ブラジルのデータソースでブラジルの住所を発見するのに使われる.
MapPoint.EU ヨーロッパのデータソースでオーストリア,ベルギー,デンマーク,フィンランド(ヘルシンキのみ),フランス,ドイツ,イタリア,ルクセンブルク,オランダ,ノルウェー(オスロのみ),ポルトガル,スペイン,スウェーデン,スイス,ギリシャ(アテネのみ),英国の住所レベルのジオコードをサポートする.
MapPoint.NA 北アメリカのデータソースで合衆国,カナダ,プエルトリコの住所レベルのジオコードをサポートする.
  • Find サービスにより返る結果集合を制限するオプションを指定する.これらは反応の大きさを削減し,それにより帯域幅を節約して関数の効率を向上させる.まず,結果の中の経度と緯度情報のみを返すようフィルターを適用し,Find サービスにより提供される追加の関連情報を一切返さないようにする.次に,範囲オプションを単一の結果のみ返すように指定する.デフォルトでは,Find サービスからの SOAP 反応は提供された住所にマッチする近似をすべて含む配列であるが,これはジオコーディングの目的ではなく,トップに返ってほしいのは正確なマッチである.
  • ジオコードすべきアイテムを全指定して FindAddress() メソッドを呼び出し,反応を取得する.
  • ジオコードされた反応により表現される Point ジオメトリの WKT 表現を構築し,その結果を SQL Server に返す.マッチした住所が見つからない場合,空の Point が返る.

 これらのメソッドのさらなる詳細については,MapPoint Web Service SDK を参照のこと.http://msdn.microsoft.com/en-us/library/bb507684.aspx

アセンブリをコンパイルする

 今度は「Build」→「Build Geocoder」を選ぶか Ctrl+Shift+B を押下することでアセンブリをコンパイルできる(Visual C# 2008 Express エディションなら「Build」→「Build Solution」).次のメッセージが出力ウィンドウに現れるのを見るだろう(もし出力ウィンドウが見えないなら,View メニューからそれを選ぶか Ctrl+Alt+O を押下すること).

------ Build started: Project: Geocoder, Configuration: Release Any CPU ------
Geocoder -> C:\Spatial/Geocoder.dll
======== Build: 1 succeded or up-to-date, 0 failed, 0 skipped ========

 Visual Studio で必要なのは以上である.では今度は SQL Server に戻ろう.

データベースを設定する

 ジオコーディング関数を使えるようになる前に,SQL Server データベースエンジンが新しい Geocoder アセンブリを正しく使えるようにいくつかの設定変更を行う必要がある.

CLR サポートを可能にする

 SQL Server が .NET CLR プロセスを走らせることはわかっている.つまり,geometry 型と geography 型のデータ型がどう動作するかである.しかし,システム定義された CLR が追加の CLR 設定は要さないにも関わらず,SQL Server にユーザー定義 CLR 関数を正常にインポートし走らせることはできない.これはこの機能がデフォルトでは使用不可となっているためである.これは,データベース管理者がこれらの強力な(しかし潜在的に危険な)機能の使用許可を確認するための,意図的な安全機構である.カスタムの .NET ジオコーディング関数を使用するため,まずデータベースが CLR をサポートできるように構成する必要がある.クエリエディタウィンドウで次の T-SQL コードを実行することでこれを行うことができる.

EXEC sp_configure 'clr enabled' , '1'
GO

 その構成変更が成功すれば,次の結果を得る.

Configuration option 'clr enabled' changed from 0 to 1.
Run the RECONFIGURE statement to install.

 変更を完了するため,サーバーを再構成して変更した値を反映する必要があり,次のように RECONFIGURE ステートメントで T-SQL クエリを実行する.

FECONFIGURE
GO

 SQL Server 構成設定が更新され,ユーザー定義 CLR コードを実行できるようになり,次のメッセージを受け取る.

Command(s) completed successfully.

セキュリティパーミッションを設定する

 次に,ウェブサービスからの情報にアクセスする .NET 管理されたコードを使いたいので,データベースの適切なセキュリティパーミッションを設定して外部データにアクセスできるようにする必要がある.これは次の T-SQL コードを実行することで行われる(Spatial と言う名前を読者のデータベース名に変更すべきことを銘記されたい).

ALTER DATABASE Spatial SET TRUSWORTHY ON
GO

 次のメッセージを受け取る.

Command(s) completed successfully.

 データベースは今や構成され,ジオコーディングアセンブリをインポートする準備が整った.

アセンブリをインポートする

 我々の .NET アセンブリを生成してコンパイルし,我々のサーバーに必要な構成変更を行ったので,やっとデータベースにアセンブリをインポートすることができる.次の T-SQL スクリプトを実行することでこれを行う.

CREATE ASSEMBLY Geocoder
FROM 'C:\Spatial\Geocoder.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS;
GO

 これは Visual Studio でコンパイルされた Geocoder.dll 出力ファイルから Geocoder と呼ばれるアセンブリをデータベース内に生成する.C:\Spatial\Geocoder.dll から,先に Visual Studio で設定したビルド出力位置に一致するところへファイルパスを変更する必要がある.PERMISSION_SET 引数はこのアセンブリに許可されたパーミッションレベルを指定する.デフォルトでは,新しい SQL Server アセンブリは SAFE にマークされており,それは制限されたローカル資源のみにアクセスできるという意味である.これは許可されていない外部の(潜在的に危険な)資源にどんなコードでもアクセスできないことを確保するためのセキュリティ機能である.我々のジオコーディング関数に働いてもらうために,我々は明示的に我々のコードを外部資源にアクセスすることを許可する必要があり,PERMISSION_SET = EXTERNAL_ACCESS と指定する.

 我々の Visual Studio プロジェクトを構成した時,XML シリアル化アセンブリもまた自動的に生成されるよう指定し,ウェブサービスへのデータ転送がシリアル化されることを銘記されたい.これは2つ目の DLL ファイルを生成し,必要なシリアル化機能を含んでおり,次のようにインポートされなくてはならない.

CREATE ASSEMBLY [Geocoder.XmlSerializers]
FROM 'C:\Spatial\Geomder.XmlSerializers.dll'
WITH PERMISSION_SET = SAFE;
GO

 繰り返しになるが,Visual Studio で設定した出力位置に一致するのに必要なファイルパスを指定すべきである.Geocoder.XmlSerializers アセンブリは外部データにはアクセスせず,ゆえにデフォルトの PERMISSION_SET = SAFE で付与は十分である.

 アセンブリが生成された時,SQL Server Management Studio のオブジェクトエクスプローラー内でそれらが見えるようになり,それらのインポートされたデータベースのプログラミングノード内のアセンブリの下に列挙されており,Figure 7-5 に示す(アセンブリが見えるようになる前に,オブジェクトエクスプローラービューを更新する必要があるかもしれない.アセンブリノードを右クリックして「最新の情報に更新」を選ぶ).

チップス これらのアセンブリを Model データベース内で生成するコードを実行すると,その SQL Server インスタンスで将来作成されるデータベース全てでそれらが利用可能となる.

関数を生成する

 我々のアセンブリをインポートした今,T-SQL コード内でジオコーディングメソッドを使えるようにするため,関数を定義する必要がある.その関数は住所の異なる記述フィールドを表現する入力パラメータの数を指定し, その住所を表現する Point ジオメトリの WKT 表現を含む出力文字列を返し,それは MapPoint Find サービスから返る座標から構成されている.関数を生成するため,次の T-SQL コードを実行する.

CREATE FUNCTION dbo.Geocode(
  @AddressLine nvarchar(255),
  @PrimaryCity nvarchar(32),
  @Subdivision nvarchar(32),
  @Postcode nvarchar(10),
  @CountryRegion nvarchar(20))
RETURNS nvarchar(255)
AS EXTERNAL NAME
Geocoder.[Geocoder.UserDefinedFunctions].geocode
GO

 このコードは dbo.Geocode と呼ばれる T-SQL 関数を生成し,それは Geocoder アセンブリ内部に含まれるジオコードメソッドにインターフェースを提供する.Geocode 関数を使う時に必ず提供しなければならないパラメータは住所,都市名,細分化(例,国/州),郵便コード(例,郵便番号)および国や地域であることを指定するものである.これらのパラメータは .NET メソッドにより MapPoint Web Service に渡されるパラメータに正確に対応している.その関数の戻り値はその住所に関連する Point の WKT 表現を含む nvarchar(255) 文字列である.

付記 .NET アセンブリから関数を生成する時,EXTERNAL NAME 句のための構文は次の通りである.AssemblyName.[Namespace.ClassName].FunctionName

 これで終わりである!おめでとう..NET を使って SQL Server に新しい関数を追加し,既存の空間機能を拡張して住所データをジオコードできるようになった.

ジオコード関数を使う

 最後に,我々のジオコーディング関数を使い始める.それを試すため,Apress 社のオフィスのある Point の緯度と経度を取得してみよう.次の住所に位置している.

Apress, Inc.
2855 Telegraph Avenue, Suite 600
Berkeley,
CA 94705

 dbo.Geocode 関数の構文は次の通り.

dbo.Geocode('@Address', '@PrimaryCity', '@Subdivision', '@Postcode', '@CountryRegion')

 Apress 社のオフィス住所に基づく Point の WKT 表現を取得するため,SQL Server Management Studio で次のクエリを実行することができる.

SELECT dbo.Geocode('2855 Telegraph Avenue', 'Berkeley', 'CA', '94705', 'USA')

 結果は次の通り.

POINT(-122.259388935609 37.8584886447626)

付記 お試し環境だと MapPoint Web Service から結果を取得するのに数秒かかる.商用環境だともっと速い.

 今や住所を表現する Point の WKT 表現を取得できるのを欲すれば,いかなる状況でも SQL Server データベース内部から Geocode 関数を使うことができる.MapPoint Web Service により返る座標値は EPSG:4326 空間参照系を使うため,geometry 型や geomgraphy 型の STPointFromTect() メソッドで結果の表現を次のように使うことができる.

SELECT geography::STPointFromText(
  dbo.Geocode('2855 Telegraph Avenue', 'Berkeley', 'CA', '94705', 'USA'),4326).STAsText()

 結果は次の通り.

POINT(-122.259388935609 37.8584886447626)

 この例をさらに拡張するため,住所,街,国および郵便コードの列を含む住所データの典型的なテーブルについて考えてみよう.次のコード例はそのようなテーブルを生成し,合衆国内の Barnes and Noble 書店の5つのアウトレットを含んでいる.

CREATE TABLE #BarnesAndNoble (
  Address varchar(255),
  City varchar(255),
  State char(2),
  ZIP int)
GO
INSERT INTO #BarnesAndNoble (Address, City, State, ZIP) VALUES
('100 Quinn Drive', 'Pittsburgh', 'PA', '15275'),
('10180 South State Street', '', 'UT', '84070'),
('2800 South Rochester Road', 'Rochester Hills', 'MI', ''),
('3909 Sth Cooper ST', 'Arlington', 'TX', '76015'),
('555 Fifth Avenue', 'New York', 'NY', '')
GO

付記 不完全なデータしかなくても,例えば先述した例に示すように郵便番号や住所の一部の欠損があっても,MapPoint Web Service は提供された情報に最も一致するものを返す.

 今度はこのテーブルに geography 型の列を追加し,dbo.Geocode 関数を使って,データの各行を表現する Point を移入しよう.

ALTER TABLE #BarnesAndNoble
  ADD Location geography
GO
UPDATE #BarnesAndNoble
  SET Location = geography::STPointFromText(dbo.Geocode(Address, City, State, ZIP, 'USA'), 4326)
GO

付記

 SQL Server Management Studio はジオコードされた結果の数を示すメッセージを表示する.

(5 row(s) affected)

 テーブルを検査して各蓄積のジオコードされた住所に基づく Point インスタンスを含むか見ることができ,それは他の空間データアイテムと同様に扱うことができる.次のクエリのように.

SELECT
  Address,
  City,
  Location.STAsText() AS WKT
FROM
  #BarnesAndNoble

 これが結果である.

Address                    City             WKT
100 Quiin Drive            Pittsburgh       POINT (-80.178599 40.44776)
10180 South State Street                    POINT (-111.890829 40.566146)
2800 South Rochester Road  Rochester Hills  POINT (-83.131891 42.639297)
3909 South Cooper Street   Arlington        POINT (-97.133609 32.682357)
555 Fifth Avenue           New York         POINT (-73.979266 40.755704)

チップス テーブルに入力された時に自動的にジオコードするためには,新しい行が挿入された時全てで dbo.Geocode 関数をテーブルが発火させるトリガを生成することができる.トリガについての更に詳細な情報については次を参照のこと.http://msdn.microsoft.com/en-us/library/ms187834(SQL.90).aspx

要約

 本章では,ジオコードされたデータから空間情報を生成する可能性を含め,SQL Server の機能を拡張する方法を学んだ.特に,次の点について学んだ.

  • ジオコーディングは,その特徴についての記述から,地球上の地物の構造化された空間情報を派生させるのに使われる.
  • MapPoint Web Service は,ジオコードデータに使われる多くの方法を提供し,ウェブ上の SOAP インターフェース経由でアクセスすることを可能にする.
  • .NET アセンブリを生成して SQL Server にインポートすることで新しい関数を定義でき,MapPoint Web Service に接続し,ジオコードされた住所の WKT 表現を構築する.
  • 本章で作成したようなジオコーディング関数は,T-SQL の UPDATE ステートメントと組み合わせて,住所テーブルのデータの各行を表現する Points 表現などの geography 型の列に移入するのに使われる.

 ジオコーディングは多くのアプリケーションで有用であり,dbo.Geocode 関数はこの機能を SQL Server 2008 で提供される空間性能に追加する一つの方法である.仮にジオコーディングを MapPoint Web Service を使って商用環境で実行したい場合には,まず,登録したアカウントを商用サービスにアップグレードしなくてはならないことを覚えておこう.

第13章 空間の関係性を検査する(Begining Spatial with SQL Server 2008)

SQL Server

 空間情報を解析するとき,しばしば地球上の2つ以上の地物の関係を理解したくなる.例えば,a と b はどれだけ離れているのか?x と y との間の経路に z はあるのか?p と q は共通の境界を共有しているのか?本章では,これらの疑問に答えるためのメソッドを紹介し,空間データの2つのアイテム間の関係の異なる点から比較しよう.これらのメソッドのすべての構文は,比較のなされるインスタンスがパラメータとしてメソッドに提供され,最初のインスタンス上で次のように動作する.

Instance1.Method(Instance2)

 geometry 型および geography 型の両者が実装しているメソッドのコアセットは空間関係を比較するのに使われ,STEquals(), STIntersects(), STDistance() を含んでいる一方,geometry 型は特異的な関係を検査するのに使うもっと拡張した範囲のメソッドを実装しており,STOverlaps(), STCrosses(), および STContains() のようなものである.本章で紹介する各メソッドについて,どちらのデータ型で使われるか示そう.注意すべき点として,geometry 型および geography 型両者に実装されていても,異なるデータ型のインスタンスをそのメソッドで比較することはできないことである.例えば,STDistance() メソッドは2つの geography 型インスタンス間または2つの geometry 型インスタンス間の距離を計算することはできるが,geometry 型インスタンスと geography 型インスタンス間の距離を決定するのに使うことはできない.

“第13章 空間の関係性を検査する(Begining Spatial with SQL Server 2008)” の続きを読む

第12章 空間オブジェクトを修飾する (Begining Spatial with SQL Server 2008)

SQL Server

 本章においては,他の既存のインスタンスの一部を修飾したり,結合したり,選択したりして geography 型や geometry 型のデータの新しいアイテムを生成するのに使われる多くのメソッドを紹介しよう.これらのメソッドは静的メソッドとは異なり,ジオメトリの座標点の表現から新しいデータのアイテムを代わりに生成することを了承いただきたい.

 本章で論ずるメソッドのいくつか,例えば STBuffer(), STConvexHull() および Reduse() は,単一のインスタンス上で動作する.他のメソッド,例えば STIntersection() および STDifference() は一つ以上のジオメトリ上で動作する.SQL Server 2008 におけるすべての空間機能性の場合のように,geometry 型および geography 型は利用可能なメソッドや,同じ方法でそれらを実装する同じセットをもつ必要はない.本章で紹介する各メソッドについて,いつどのようにそれが使われるかを見せよう.

付記 本章で論ずるメソッドは,それが呼び出された元のインスタンスにいかなる変化をも与えない.むしろ,それらはそれらのインスタンスで表現されるジオメトリの修飾により新しいインスタンスを生成する.

“第12章 空間オブジェクトを修飾する (Begining Spatial with SQL Server 2008)” の続きを読む

第 11 章 空間オブジェクトのプロパティを検査する(Begining Spatial with SQL Server 2008)

SQL Server

第 4 部 空間データの解析

 本書のこの部では,geography 型および geometry 型でデータをフィルターし解析できるメソッドを論ずる.それはアプリケーションにおいて空間データの力を開発するのに使うのに必要な主要な機能を提供してくれる.
 そのメソッドは3つのカテゴリに分類される.章ごとに一つ.第11章では個別の空間オブジェクトのプロパティについての情報を解析して返すメソッドを扱う.第12章では既存のオブジェクト間の組み合わせまたは修飾の間の新しいオブジェクトを定義するメソッドをカバーする.第13章ではオブジェクト間の関係をテストするメソッドを紹介する.例えば同一性,近接性,交差などである.

“第 11 章 空間オブジェクトのプロパティを検査する(Begining Spatial with SQL Server 2008)” の続きを読む

第 4 章 空間データオブジェクトを生成する (Beginning Spatial with SQL Server 2008)

SQL Server

第 2 部 空間データを追加する

 本書のこの部では空間データを SQL Server 2008 データベースに追加する様々な方法を説明する.第 4 章では,既知の座標からいかなるアイテムをも生成するのに使われる存在する静的メソッドのそれぞれを紹介する.第 5 章では,外部資源をかぶせてそれらの座標を派生させるのを助けるための技術を解説する.Microsoft の Virtual Earth Map Control を使う.第 6 章では,空間データの蓄積される他の一般的なデータフォーマットについて議論し,これらの蓄積されたデータを SQL Server 2008 にインポートする方法についての例を提供する.最後に,第 7 章で SQL Server を拡張してジオコーディング機能を提供する方法を示す.つまり,.NET 経由で Microsoft MapPoint Web Services にアクセスして自動的に住所から座標を取得することである.

 前章では,静的メソッドの概念について紹介し,geography 型および geometry 型のオブジェクトをインスタンス化する方法について説明した.本章では各データ型で利用できる異なる静的メソッドを検査し,空間データの新しいアイテムを生成するのに使われる方法について比較する.

注記 本章でのほとんどのコードサンプルは,@Point などのローカル変数を宣言し,その結果静的メソッドにより生成されたインスタンスを蓄積する.しかし,INSERT ステートメントを呼び出すことで静的メソッドの結果をテーブルの geometry 型および geography 型の列に挿入することもできる.

適切な静的メソッドを選択する

 geography 型および geometry 型は共に多くの異なる静的メソッドを提供し,空間データオブジェクトを生成する.特定の状況で適切なメソッドを使用するのは次に述べる因子による.

生成しようとするジオメトリの種類

 いくつかのメソッドは単に特定のジオメトリを生成するためだけに使われる.例えば,STLineFromWKB() メソッドおよび STLineFromText() メソッドは単に LineString ジオメトリインスタンスを生成するだけである.

このジオメトリの属性を記述する方法

 全てのメソッドはジオメトリの空間参照の提供を必要とし,三つの異なる標準フォーマットのうち一つを使う.つまり,Well-Known Text (WKT), Well-Known Binary (WKB) および Geometry Markup Language (GML) である.ジオメトリを記述するためにいずれのメソッドを選んだかにより,適切な表現フォーマットを使わなければならない.例えば,任意のジオメトリインスタンスを GML 表現から生成するには GeomFromGML() メソッドを使わなければならない.

geometry 型または geography 型で生成したいアイテムは一体何か

 geometry 型および geography 型共に,それら自身の各静的メソッドを実装している.静的メソッドはメソッド自身と同一のデータ型のオブジェクトのインスタンスを生成できるだけであるため,生成したいオブジェクトのデータ型によって適切なデータ型のメソッドを選択しなければならない.例えば,geometry 型の Point オブジェクトを WKT 表現から生成するには geometry::STPointFromText() メソッドを使うべきである.geography 型の Point オブジェクトを WKT 表現から生成するには代わりに同等の geography 型のメソッドである geography::STPointFromText メソッドを使うべきである.

 Table 4-1 に異なるジオメトリインスタンスを生成するのに使用されるメソッドを示す.これら全てのメソッドは geography 型にも geometry 型にも共に実装されている.

Table 4-1. 異なる表現から空間オブジェクトをインスタンス化するのにサポートされる方法
オブジェクトの種類 Well-Known Text Well-Known Binary Geography Markup Laguage
Point STPointFromText() STPointFromWKB() GeoFromGml()
LineString STLineFromText() STLineFromWKB() GeoFormGml()
Polygon STPolyFromText() STPolyFromWKB() GeoFromGml()
MultiPoint STMPointFromText() STMPointFromWKB() GeoFromGml()
MultiLineString STMLineFromText() STMLineFromWKB() GeoFromGml()
MultiPolygon STMPolyFromText() STMPolyFromWKB() GeoFromGml()
Geometry Collection STGeomCollFromText() STGeomCollFromWKB() GeoFromGml()
Any supported type STGeomFromText() Parse() STGeomFromWKB() GeoFromGml()

 空間オブジェクトを表現するのにいずれの言語を使うにせよ,また,いずれの型のオブジェクトを生成するにせよ,本章で議論する静的メソッドは全て同一の構文を共有する.この汎用構文は次に示す通りである.

datatype::method(geometryrepresentation, srid)

 この構文は次に示すとおり 4 つの構成要素からなる.

  • datatype は静的メソッドが geography 型か geometry 型のいずれに属するかを指定し,ゆえにメソッドにより生成されたインスタンスのデータ型を定義する.
  • method はジオメトリを生成するメソッド名である.これは必ず Table 4-1 に列挙したメソッドの一つでなければならない.
  • geometryrepresentation は生成すべきジオメトリの有効な表現である.この表現は選択されたメソッドにより生成された適切なフォーマットにおいて表現されなければならない.その表現とは(WKT および GML フォーマットの)文字列,あるいは問題のジオメトリを定義するのに必要な全情報を含んだバイナリーストリーム (WKB) である.
  • srid は空間参照系の識別子を表現した整数値であり,メソッドを通して geometryrepresentation パラメーター内で座標を定義するのに使われる.

 その中でジオメトリが表現される各フォーマットおよび,オブジェクトがそれらの表現から生成される各メソッドは,それぞれ利点と欠点を有する.しかし,geography 型データか geometry 型データか,いずれのメソッドを使ってアイテムを生成するかの決定は,生成の時点でのみ重要であり,アイテムの生成後は無関係になっていく.特定のメソッドを使って空間データのアイテムを生成してしまえば,そのデータのアイテムは,他の利用可能だったメソッドを使って生成されたものであっても,全く同一のものとなる.

レイトバインディング

 任意の静的メソッドにより指定された geography 型あるいは geometry 型オブジェクトへの参照は,実行中にのみ解決され,それはメソッドを呼び出すクエリが実行される時である.これはレイトバインディングとして知られている.レイトバインディングの一つの効果として,クエリが実行されるまでは SQLCLR はどんな種類のオブジェクトが静的メソッドにより生成されるかを知らない.結果として,SQL Server は表現を解析して有効なデータであるかチェックすることができない.例えば,次のクエリはいかなる解析エラーも発生させずに SQL Server Management Studio において正しくコンパイルされる.

SELECT  geometry::STGeomFromText('This is not a real geometry')

 しかし,実行の際に,メソッドに渡された表現が有効な WKT ではないため,このクエリは失敗する.有効な WKT とは,STGeomFromText() メソッドに必要な,という意味である.いかなる静的メソッドでも表現を指定して渡す時には注意されたい.というのは,これらのクエリを実行するまでエラーが表示されないかもしれないからである.

“第 4 章 空間データオブジェクトを生成する (Beginning Spatial with SQL Server 2008)” の続きを読む