MapWinGIS－エクセルでGISその4

今回は、MapWinGISとPostGISを使って逆ジオコーディングのプログラムを作成します。
以前の記事では、オラクルにて同様のことを実施しています。

今回作成するプログラム

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

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

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

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

PostGIS接続の環境設定

エクセルVBAの環境からPostGISに接続する選択肢は、OLEDBとODBCの2つがあります。
OLEDB（pgOleDB）は以下からダウンロードできますが、2007年から更新されておらず、更にはパフォーマンスが悪いとの評判もあるので今回はODBCを使うことにします。
https://www.postgresql.org/ftp/projects/pgFoundry/stackbuilder/PgOleDB/PgOleDB1.0.0.20/

ODBCによる接続環境の設定は、以下の手順で行います。
1.PostgreSQLのODBCドライバのダウンロード
2.ODBCドライバのインストール
3.コントロールパネルからDSNの追加
4.VBAの環境でADODBに参照設定を追加

1.ODBCドライバのダウンロード
ODBCドライバは以下からバージョンに合わせたものをダウンロードします。
https://www.postgresql.org/ftp/odbc/versions/msi/
バージョンごとに物件が提供されているようなので、下記SQLにてPostgreSQLのバージョンを確認しておくとよいでしょう。

postgis=# select version();
－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－－
　PostgreSQL 11.2 (Debian 11.2-1.pgdg90+1)．．．

PostgreSQLのバージョンが11.2でしたので、近いバージョンのドライバ（11.1）をダウンロードします。
64ビット版と32ビット版が提供されていますが、エクセルのビット数に合わせてください。OSが64ビットであってもエクセルが32ビットであれば32ビット版を導入してください。

2.ODBCドライバのインストール
ダウンロードしたzipファイルをに含まれるインストーラ（.msiファイル）を実行します。
インストール時の指定は、すべてデフォルトで問題ありません。

3.コントロールパネルからDSNの追加
DSNとは、データソース名のことで、ODBC接続に必要な情報をまとめたものです。Windowsの場合はコントロールパネルからDSNを作成します。
DSNを作成する前に、下記SQLを発行してデータベースの文字コードを確認しておきましょう。

postgis=# SELECT character_set_name FROM information_schema.character_sets;
　character_set_name
－－－－－－－－－－－－－－－－－－－
　UTF8
(1 row)

データベースの文字コード（キャラクタセット）はUTF-8でした。
また、私のエクセルは32ビット環境ですので、それらの前提で以降説明します。

コントロールパネルを起動し、[管理ツール]、[ODBCデータソース（32ビット）]を選択します。
表示される”ODBCアドミニストレータ”ダイアログの”システムDSN”タブを選択し、”追加”ボタンを押下するとドライバの選択画面が表示されますので、”PostgreSQL Unicode”を選択して”完了”ボタンを押下します。（DBの文字コードがSJISの場合は、ANSI版を使用するようです。）

”完了”ボタン押下後に表示される”PostgreSQL Unicode ODBC セットアップ”画面からDSN名（任意）、サーバー名、DB名、ポート名、ユーザー名、パスワードを入力します。”テスト”ボタンを押下すると、接続の確認ができます。
テストOKであれば、”保存”ボタンを押下します。

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

これでPostgreSQLとの接続が可能となりましたので、以下のテストプログラム（テーブル名一覧の取得）を実行すると、シートにテーブル一覧が作成されます。このプログラムは、VBAからはADOのオブジェクト（API）を使っていますが、データベース接続には先ほど作成したODBCデータソース（DSN）を使っています。

Sub sample()
  'オブジェクトの作成
  Dim cn As New ADODB.Connection
  Dim rs As New ADODB.Recordset
  
  '接続文字列の設定
  cn.ConnectionString = "Data Source=PostgreSQL35Wx86"
  cn.Open
  'SQLの発行
  rs.Open "select tablename from pg_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を使った逆ジオコーディングと小地域ポリゴンの取得
逆ジオ用に使用するテーブルは、以下の構成です。（カラムが多いので一部割愛しています。）

postgis=# \d kokusei
　　　　　　Table “public.kokusei”
　Column　|　　　　Type
－－－－－－+－－－－－－－－－－－－－－
key_code　| character varying(11)
pref　　　 | character varying(2)
city　　　　| character varying(3)
s_area　　 | character varying(6)
pref_name | character varying(12)
city_name　| character varying(14)
s_name　　| character varying(96)
geom　　　| geometry(MultiPolygon,4612)
Indexes:
　”kokusei_pkey” PRIMARY KEY, btree (gid)
　”kokusei_geom_idx” gist (geom)

PostGISにて逆ジオを行うためのSQLは、以下となります。
geometryカラムを引数とするST_AsText関数を呼び出すことで、ジオメトリオブジェクトをWKT形式（テキスト）で取得することができます。

SELECT city_name||s_name as name,ST_AsText(geom) as wkt FROM kokusei
WHERE ST_Contains( geom ,ST_GeometryFromText(‘POINT(139.7670944 35.6806302)’,4612));

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 = "Data Source=PostgreSQL35Wx86"
        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行目まではADODBのコネクションオブジェクトを生成しています。
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 as name,ST_AsText(geom) as wkt FROM kokusei "
    strSql = strSql + "WHERE ST_Contains( geom ,ST_GeometryFromText('POINT("
    strSql = strSql + Str(g_dx) + " " + Str(g_dy) + ")',4612))"
    '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

動作確認

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

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

まとめ

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

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

今回は、以前オラクルで作成したものをチョコっと修正しただけで動作しました。
近いうちに、SQLite（Spatialite）版も作成する予定です。