便利じゃなければプログラムじゃ無い!
仕事が楽になって業務効率がUPするシステムを提供!

Google Maps Geocoding API を使って緯度経度を取得する(xml版)

2017/11/6 (月) • Excel, VBAコメントは受け付けていません。Read More »

img_geocode_xml
今回のエントリーはExcelのマクロでGoogle MapのGeocoding APIを利用する方法についてアップしたいと思います。

以下のよう住所入力用の欄と実行ボタンを配置します。
img_geocode01



1.Google Maps Geocoding APIの使い方

ジオコーディングを行い緯度経度を取得するにはxmlまたはjsonの形式でGoogle Maps Geocoding APIにリクエストします。

リクエストの形式は以下の通りです。
http://maps.googleapis.com/maps/api/geocode/outputFormat?parameters

xml形式でリクエストをする場合は「outputFormat」に「xml」、「parameters」に「address=住所」を設定します。
具体的には以下の通りのURLでリクエストする事になります。


http://maps.googleapis.com/maps/api/geocode/xml?address=東京都中央区日本橋

Geocoding APIから返ってくるxml

上記のURLを実際ブラウザで開くと以下のxmlが表示されます。
この中の「lat」、「lng」が緯度・経度になります。

<GeocodeResponse>
 <status>OK</status>
 <result>
  <type>political</type>
  <type>sublocality</type>
  <type>sublocality_level_1</type>
  <formatted_address>日本、〒103-0027 東京都中央区日本橋</formatted_address>
  <address_component>
   <long_name>日本橋</long_name>
   <short_name>日本橋</short_name>
   <type>political</type>
   <type>sublocality</type>
   <type>sublocality_level_1</type>
  </address_component>
  <address_component>
   <long_name>中央区</long_name>
   <short_name>中央区</short_name>
   <type>locality</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>東京都</long_name>
   <short_name>東京都</short_name>
   <type>administrative_area_level_1</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>日本</long_name>
   <short_name>JP</short_name>
   <type>country</type>
   <type>political</type>
  </address_component>
  <address_component>
   <long_name>103-0027</long_name>
   <short_name>103-0027</short_name>
   <type>postal_code</type>
  </address_component>
  <geometry>
   <location>
    <lat>35.6803660</lat>
    <lng>139.7716695</lng>
   </location>
   <location_type>APPROXIMATE</location_type>
   <viewport>
    <southwest>
     <lat>35.6780347</lat>
     <lng>139.7704029</lng>
    </southwest>
    <northeast>
     <lat>35.6844046</lat>
     <lng>139.7786750</lng>
    </northeast>
   </viewport>
   <bounds>
    <southwest>
     <lat>35.6780347</lat>
     <lng>139.7704029</lng>
    </southwest>
    <northeast>
     <lat>35.6844046</lat>
     <lng>139.7786750</lng>
    </northeast>
   </bounds>
  </geometry>
  <place_id>ChIJU_JmlleJGGARa7vrQMGllR4</place_id>
 </result>
</GeocodeResponse>

※今回利用する要素をハイライトしています。

2.VBAサンプルコード

実際にExcelに組み込むサンプルコードを紹介します。

■ジオコード実行ボタンに割り当てるコード

Public Sub GeoCode()
    
'ジオコード実行
If ActiveSheet.Range("A2").Value <> "" Then
    
    'ジオコーディングの結果を配列に格納(緯度、経度、ステータス)
    strData = Split(GeoCoding_LatLang(ActiveSheet.Range("A2").Value), ",")
    
    ActiveSheet.Range("B2").Value = Val(strData(0)) '緯度
    ActiveSheet.Range("C2").Value = Val(strData(1)) '経度
    ActiveSheet.Range("D2").Value = strData(2)      'ステータス

End If

End Sub

■ジオコード結果(xml)から緯度、経度、ステータスを取得してカンマ区切りで返す

Function GeoCoding_LatLang(ByVal adress As String) As String
'GoogleMaps API XML形式でジオコードを取得
'戻り値:緯度(lat),経度(lng),ステータスをカンマ区切り

Dim HttpReq         As MSXML2.XMLHTTP60
Dim DomDoc          As MSXML2.DOMDocument60
Dim strGeocode      As String
Dim xmlresult       As IXMLDOMNode
Dim xmlLat          As IXMLDOMNode
Dim xmlLng          As IXMLDOMNode
Dim xmlStatus       As IXMLDOMNode
Dim xmlType         As IXMLDOMNode
Dim URL             As String
Dim wCount          As Long

'Google Maps Geocoding API
URL = "https://maps.googleapis.com/maps/api/geocode/xml?address=" & Encode_Uni2UTF(adress)

'XMLHTTPオブジェクトをセット
Set HttpReq = New MSXML2.XMLHTTP60

With HttpReq
    .Open "GET", URL, varAsync:=False           '非同期モードで通信を開始
    .send                                       'リクエストを送信
    If .Status <> 200 Then Exit Function        'リクエストが成功しなかったら終了
    Set DomDoc = New MSXML2.DOMDocument60
End With

'XMLから情報を抽出する
With DomDoc
    
    'XMLドキュメントを読み込む
    .LoadXML (HttpReq.responseText)
    
    'resultの件数をカウントする
    Set xmlresult = .SelectSingleNode("//GeocodeResponse")
    wCount = 0
    For Each xmlresult In xmlresult.ChildNodes
        If xmlresult.nodeName = "result" Then
            wCount = wCount + 1
        End If
    Next
    
    'status要素を取得
    Set xmlStatus = .SelectSingleNode("//GeocodeResponse/status")
    
    'ステータスの状態をチェック
    Select Case xmlStatus.Text
    
        'ジオコード成功の場合
        Case "OK"
            'lat要素(緯度)を取得
            Set xmlLat = .SelectSingleNode("//GeocodeResponse/result/geometry/location/lat")
            strGeocode = xmlLat.Text
            
            'lng要素(経度)を取得
            Set xmlLng = .SelectSingleNode("//GeocodeResponse/result/geometry/location/lng")
            strGeocode = strGeocode & "," & xmlLng.Text & ","
            
            'location_type要素(指定した住所の状況)を取得
            Set xmlType = .SelectSingleNode("//GeocodeResponse/result/geometry/location_type")
            If xmlType.Text = "ROOFTOP" Then strGeocode = strGeocode & "OK"
            If xmlType.Text = "APPROXIMATE" Then strGeocode = strGeocode & "位置情報は近似値です"
            If xmlType.Text = "RANGE_INTERPOLATED" Then strGeocode = strGeocode & "ジオコーディング出来ません"
            If xmlType.Text = "GEOMETRIC_CENTER" Then strGeocode = strGeocode & "-"
            
        
        '以下ステータスがOKでは無く問題があった場合
        '緯度、経度は空白で返す
        Case "ZERO_RESULTS"
            strGeocode = ",,住所から緯度経度を出力出来ませんでした。"
        
        Case "OVER_QUERY_LIMIT"
            strGeocode = ",,クエリ数が割り当て量を超えています。"
        
        Case "REQUEST_DENIED"
            strGeocode = ",,リクエストが拒否されました。"
        
        Case "INVALID_REQUEST"
            strGeocode = ",,照会条件(address、components、latlngのいずれか)がありません。"
        
        Case "UNKNOWN_ERROR"
            strGeocode = ",,サーバーエラーでリクエストが処理できませんでした。"
    
    End Select
    
    '複数の結果が返ってきた場合
    If wCount >= 2 Then
        strGeocode = ",,住所を確認して下さい。結果が複数あります。"
    End If
    
    '結果を返す
    GeoCoding_LatLang = strGeocode
    
End With

Set HttpReq = Nothing
Set DomDoc = Nothing
    
End Function

■文字コードの変換

'文字列をUTF-8でエンコードする
Function Encode_Uni2UTF(ByRef strUni As String)
Dim buf As Variant
Dim tbuf As Variant
Dim n As Variant
Const CSET = "UTF-8"
Dim ADOstrm As Object 'ADODB.Stream

    On Error GoTo ErrHandler
    
    Set ADOstrm = CreateObject("ADODB.Stream")  'New ADODB.Stream
    ADOstrm.Open
    ADOstrm.Type = adTypeText
    ADOstrm.Charset = CSET
    ADOstrm.WriteText strUni
    ADOstrm.Position = 0
    ADOstrm.Type = adTypeBinary
    ADOstrm.Position = 3
    buf = ADOstrm.Read()
    ADOstrm.Close
    
    Set ADOstrm = Nothing
  
    For Each n In buf
       tbuf = tbuf & "%" & Hex(n)
    Next
    
    Encode_Uni2UTF = tbuf
    
    Exit Function
    
ErrHandler:
    If ADOstrm Is Nothing = False Then ADOstrm.Close
    Set ADOstrm = Nothing
End Function

VBAコードの補足

■コード「GeoCode」
  1. ジオコーディングを実行し結果を取得
    6行目:コード「GeoCoding_LatLang」を使い結果を取得。Split関数で配列に格納します。
  2. 結果をExcelシートに表示
    9~11行目:緯度、経度、ステータスをExcelのセルにセットします。
■コード「GeoCoding_LatLang」
  1. XMLHTTPオブジェクトを使う為の参照設定
    ツール→参照設定で「Microsoft XML, v6.0」をチェックします。
  2. API用のパラメーター
    17行目:住所を文字変換してパラメーターにセットしています。
  3. xmlをセット
    20~27行目:XMLHTTPオブジェクトを使いxmlを開きます。
  4. xmlを読み込む
    32行目:LoadXMLメソッドでxmlを読み込みます。
  5. 結果件数をカウント※2017/11/7追記
    36~42行目:
    住所が不足している場合、複数の候補が結果として返ってくる場合があります。

    例えば「東京都中央区日本橋x-x」の場合は住所が不足している為、以下の様に3件結果が返ってきます。
    東京都中央区日本橋久松町x-x
    東京都中央区日本橋小舟町x-x
    東京都中央区日本橋大伝馬町x-x

    この様なケースに備えてresult要素を事前にカウントしておきます。
    ChildNodesプロパティでGeocodeResponse直下の子ノードを全て取得し、nodeNameプロパティで「result」の件数をカウントします。件数が複数あった場合は緯度・経度を返さないようにします。

  6. 情報を抽出
    45行目:SelectSingleNodeメソッドでxmlのstatus要素を抽出します。
    要素の指定は以下の様に階層構造で指定します。
    ※statusの場合は.SelectSingleNode(“//GeocodeResponse/status”)
    img_geocode02
  7. 各種コードの戻り値
    statasやlocation_typeの戻り値の詳しい説明はGoogle Maps API デベロッパーガイドを参照。

実行結果

上記のコードを実行すると以下のように緯度、経度、ステータスをExcelに反映する事が出来ます。
img_geocode03

【要注意】Google Maps Geocoding API のポリシーと使用制限

Google Maps Geocoding APIは実際にGoogleマップに結果を表示するときにのみ併用で使えるもので、それ以外はポリシーで禁止されています。あくまでGoogle Maps Geocoding APIの使い方やxmlファイルのVBA操作の参考程度でご利用下さい。

また、Google Maps Geocoding APIには使用制限があります。無料で使えるのは1日に2,500回または1 秒に50回のリクエストまで。
このリクエスト数を超えた場合は従量制で課金されることになりますのでご注意下さい!詳しくは公式サイトをご確認下さい。

今回のサンプルファイルは以下のリンクからダウンロード可能です。
サンプルファイルをダウンロードする
以上、今回はGoogle Maps Geocoding APIを使って緯度経度を取得する為のVBAコードでした。

タグ: , , , , , , , , ,

Comments are closed.