MapWinGIS－エクセルでGISその3

前回は、エクセルシートにMapWinGISの地図コントロールを貼り付けてOSM地図を表示し、その上にシェープファイルを重ね合わせ表示しました。
今回は、オラクルデータベースと接続し、逆ジオコーディングとオラクル上のgeometryカラムから図形を取得して重ね合わせ表示を行います。

今回作成するプログラム

今回作成するプログラムの仕様を以下に列挙します。

• Mapコントロール上にOSM地図を表示
• 以前オラクル化した2015年版国勢調査の小地域（町丁・字等別）データを使用して逆ジオコーディングを実施
• 該当住所名をエクセルのセルに表示しOSM地図上にはその範囲を表示

完成イメージは、以下となります。

地図上をダブルクリックして逆ジオコーディングを実施。結果の住所名をセル”C1”に表示し、その範囲をOSM地図上に表示しています。

オラクル接続の環境設定

エクセルVBAの環境からオラクルに接続する環境構築は、以下の手順で行います。
1.オラクル社からODAC（Oracle Data Access Compornents）をダウンロードする
2.ODACからOLEDBプロバイダーをインストールする
3.VBAの環境でADODBに参照設定を追加する
早速インストールしていきましょう。

1.オラクル社からODACをダウンロードする
オラクル社が無償で提供するODAC（Oracle Data Access Compornents）をダウンロードします。ダウンロードには、アカウント登録（無償）が必要です。
ODACは、オラクルDBクライアント開発用の接続ドライバー群と考えるとよいと思います。ODACの中には、InstantClientや今回使用するOLEDB、.NET環境からの接続ドライバーなどが同梱されています。
今回は、下記URLにて提供される32ビット版のODAC 12c Rel4を使用しました。
https://www.oracle.com/technetwork/jp/database/windows/downloads/utilsoft-087491-ja.html

2.ODACからOLEDBプロバイダーをインストールする
ダウンロードしたODACは約70MBのzipファイルです。
これを一時ディレクトリ（例：c:\temp\odac）に解凍し、これとは別にインストール先ディレクトリ（例：c:\tool\oracle\oledb）を作成します。
次に、コマンドプロンプトを管理者で起動し一時ディレクトリへ移動します。
この状態で、下記コマンドを実行しoledbをインストールします。第二引数にインストール先ディレクトリを、第三引数にORACLE_HOMEを指定します。第一引数はインストールモジュール、第四引数は依存バイナリの導入指定です。

>.\install.bat oledb C:\tool\oracle\oledb D:\tool\oracle\instantclient_12_2 true

コマンド実行後は、下記にPATHを設定しておきます。
・oledbインストールディレクトリ（例：C:\tool\oracle\oledb）
・oledbインストールディレクトリの下のbin（例：C:\tool\oracle\oledb\bin）
念のためにOS再起動しておいた方がよいと思います。
また、一時ディレクトリは不要ですのでファイルごと削除しておいてください。

3.VBAの環境でADODBに参照設定を追加する
ここまでで、オラクル接続の環境は整いましたので、実施にVBA環境にて接続を行います。
まず、VBAの編集画面を開きMicrosoft ActiveX Data Objects（ADO）に参照設定を行います。バージョンはいくつかあると思いますが、最も数字の大きいバージョン（画面例では6.1）がよいと思います。

これでオラクルとの接続が可能となりましたので、以下のテストプログラム（テーブル名一覧の取得）を実行すると、シートにテーブル一覧が作成されるはずです。
なお、”hoge”の部分（DB接続文字列、ユーザー名、パスワード）は環境にあわせて変更してください。

Sub sample()
  'オブジェクトの作成
  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  
  '接続文字列の設定
  cn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=hoge;User ID=hoge;Password=hoge"
  cn.Open
  'SQLの発行
  rs.Open "select table_name from user_tables", cn
 
  ActiveSheet.Range("A1").CopyFromRecordset rs
 
  'オブジェクトのクローズ
  rs.Close
  cn.Close
        
  'オブジェクトの解放
  Set rs = Nothing
  Set cn = Nothing
  
End Sub

プログラム作成上のポイント

次に、今回作成するプログラム上のポイントは下記3つとなります。

1. 動的にシェープファイルオブジェクトを生成
2. オラクルDBを使った逆ジオコーディングと小地域ポリゴンの取得
3. 図形オブジェクト（Shape）の生成とWKTテキストのインポート

1.メモリ上にシェープファイルオブジェクトを生成
MapWinGISの地図コントロール上に任意の図形を表示する場合は、以下のようなイメージでメモリ上にシェープファイルを新規作成し、地図コントロールにレイヤとして追加します。

　Set Shapefile = New MapWinGIS.Shapefile　// シェープファイルオブジェクトの作成
　Shapefile.CreateNew(“”, SHP_POLYGON)　　// メモリ上にシェープファイルを作成
　Map1.AddLayer(Shapefile, True)　　　// シェープファイルをレイヤとして登録

2.オラクルDBを使った逆ジオコーディングと小地域ポリゴンの取得
次のポイントは、オラクルに発行するSQLです。
以前、ポリゴンを使った逆ジオ用のSQLは、SDO_CONTAINS関数にて実現できることは学習しました。
今回逆ジオ用に使用するテーブルは、以下のような構成となっています。（カラムが多いので、一部割愛しています。）

SQL> desc kokusei
　名前　　　　　　型
—————- ————
　KEY_CODE　　VARCHAR2(11 CHAR)
　PREF　　　　　VARCHAR2(2 CHAR)
　CITY　　　　　VARCHAR2(3 CHAR)
　S_AREA　　　 VARCHAR2(6 CHAR)
　PREF_NAME　 VARCHAR2(12 CHAR)
　CITY_NAME　 VARCHAR2(14 CHAR)
　S_NAME　　　VARCHAR2(96 CHAR)
　GEOMETRY　　MDSYS.SDO_GEOMETRY

逆ジオ用に発行するSQLは、以下の通りとなります。
geometryのget_wkt関数を呼び出すことで、ジオメトリオブジェクトをWKT形式（テキスト）で取得することができます。

SELECT city_name||s_name name,k.geometry.get_wkt() wkt FROM kokusei k WHERE SDO_CONTAINS( k.geometry,sdo_geometry(2001, 4612,sdo_point_type( 139.752685546875, 35.7067070007324,NULL), NULL, NULL)) = ‘TRUE’

3.図形オブジェクト（Shape）の生成とWKTテキストのインポート
先ほど、Shapefileオブジェクトを生成しましたが、これは地図レイヤに相当するものです。レイヤには図形を配置しますが、MapWinGISでは1つ1つの図形を、Shapeオブジェクトとして表現します。
MapWinGISには、WKT形式の図形をShapeに取り込む機能が用意されています。今回はその機能（ImportFromWKTメソッド）を使ってShapeに図形を取り込みます。

Set shPolygon = New MapWinGIS.Shape　　// シェープオブジェクトの作成
shPolygon.ImportFromWKT(“POLYGON ((30 10, 40 40, 20 40, 10 20, 30 10)”)　// WKTテキストのインポート
Shapefile.EditAddShape(shPolygon)　// Shapefile（レイヤ）への図形の追加

プログラムの作成

では、具体的にプログラムを作っていきましょう。

1.地図の初期描画と各種リソース初期化
OSM地図の表示とオラクルDBとの接続処理、メモリ上レイヤ初期化処理を、”OSM表示”ボタン（内部的にはCommandButton1）のクリックイベント処理として実装します。

'広域変数
Dim g_adoConn As New ADODB.Connection
Dim g_dx As Double
Dim g_dy As Double
Dim g_Shapefile As New MapWinGIS.Shapefile

Private Sub CommandButton1_Click()

    'レイヤ情報のクリア
    Map1.RemoveAllLayers

    'プロバイダ情報のクリア（キャッシュは残す）
    Map1.Tiles.Providers.Clear (False)
    
    'OSMの表示を指定
    Map1.Projection = PROJECTION_WGS84
    Map1.TileProvider = OpenStreetMap
    Map1.Tiles.ProviderId = tkTileProvider.OpenStreetMap
    
    '表示位置、ズームレベルの設定（皇居周辺）
    Map1.Latitude = 35.677182
    Map1.Longitude = 139.752692
    Map1.CurrentZoom = 10
    
    'マウスモードをパンに設定
    Map1.CursorMode = cmPan
    'マウスボタンUpでイベント通知
    Map1.SendMouseUp = True
    
    'オラクル接続の確立
    If g_adoConn Is Nothing Or g_adoConn = "" Then
        '接続文字列の設定、接続
        g_adoConn.ConnectionString = "Provider=OraOLEDB.Oracle;Data Source=hoge;User ID=hoge;Password=hoge"
        g_adoConn.Open
    End If
    
    '住所ポリゴン描画用のShapefileオブジェクトの生成
    If g_Shapefile.CreateNew("", SHP_POLYGON) Then
        '面や線の色属性などの設定
        g_Shapefile.DefaultDrawingOptions.FillColor = vbRed
        g_Shapefile.DefaultDrawingOptions.FillTransparency = 40
        g_Shapefile.DefaultDrawingOptions.LineColor = vbYellow
        g_Shapefile.DefaultDrawingOptions.LineWidth = 4
        g_Shapefile.DefaultDrawingOptions.LineStipple = dsDash
         
        'Mapコントロールに編集用レイヤ（Shapefileオブジェクト）を追加
        hndl = Map1.AddLayer(g_Shapefile, True)
    Else
        MsgBox "初期化失敗"
    End If

End Sub

プログラムを解説します。
5行目までは広域変数で、28行目までは以前ご紹介した記事と同じです。
31～35行目まではオラクルのコネクションオブジェクトを生成しています。
38～50行目まではメモリ上にシェープファイル（ポリゴン）を作成し、色などの表示属性を設定したうえで地図コントロールにレイヤとして追加（47行目）しています。

2.MouseUpイベント処理
地図コントロールのマウスアップイベント処理として、広域変数g_dx、g_dyに測地座標を設定する処理を追加します。
今回は、変換した座標をセルにも設定します。

Private Sub Map1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Long, ByVal y As Long)

    '画面座標から測地座標に変換
    If Map1.PixelToDegrees(x, y, g_dx, g_dy) Then
        Cells(1, 1) = g_dx
        Cells(1, 2) = g_dy
    End If

End Sub

プログラムを説明します。
4行目でShapefileインスタンスを作成し、8行目でそのインスタンスを使ってシェープファイルをオープンします。シェープファイル名は、イベントの引数をそのまま使用します。
10～11行目でポリゴンの塗りつぶし色と透過を設定し、13～14行目で線幅と線色を設定します。設定値は、エクセルシートから取得するので、Cellsオブジェクトを使用しています。
最後に17行目でMapコントロールのAddLayerメソッドを使ってShapefileオブジェクトをレイヤとして追加すれば完了です。

3.地図上のダブルクリックイベント処理
最後に地図上をダブルクリックした時のイベント処理です。
9～15行目逆ジオ用のSQLを発行してセルに取得した住所名を設定します。
18行目でオラクルから取得したWKTをシェープオブジェクトに設定し、20～22行目でシェープファイルオブジェクトへ設定して再描画を行います。

Private Sub Map1_DblClick()

    Dim strSql As String
    Dim hndl As Long
    Dim adoRs As New ADODB.Recordset
    Dim shPolygon As New MapWinGIS.Shape

    'SQLの実行
    strSql = "SELECT city_name||s_name name,k.geometry.get_wkt() wkt FROM kokusei k "
    strSql = strSql + "WHERE SDO_CONTAINS( k.geometry,sdo_geometry(2001, 4612,"
    strSql = strSql + "sdo_point_type(" + Str(g_dx) + "," + Str(g_dy) + ",NULL), NULL, NULL)) = 'TRUE'"
    'SQLの実行と結果取得
    adoRs.Open strSql, g_adoConn
    '住所名をセルに設定
    Cells(1, 3) = adoRs("name").Value
    
    '逆ジオ結果のポリゴンをShapeオブジェクト化
    If shPolygon.ImportFromWKT(adoRs("wkt").Value) Then
        '編集用レイヤを一度クリアして取得したポリゴンで再描画
        g_Shapefile.EditClear
        hndl = g_Shapefile.EditAddShape(shPolygon)
        Map1.Redraw
    Else
        MsgBox ("Shapeポリゴンインポート失敗")
    End If

    'レコードセットのクローズ
    adoRs.Close
    Set adoRs = Nothing
    
End Sub

動作確認

簡単に動作確認してみましょう。
東京の中心部や

東京の端の檜原村でも大丈夫そうです。

まとめ

ここまで、以下を学習しました。

• OLEDBによるオラクル接続とGEOMETRYカラムをWKT形式で取得する方法
• 地図コントロールへの動的なレイヤと図形のの追加方法

今回のプログラムは思いつきで作成しましたが、なかなかサクサク動くので何か面白い用途がないかなと思っています。