緯度経度から住所をExcelで取得したいです


今、A列の2行目から緯度、B列の2行目から経度が、ずらりと並んでおります。
この状況におきまして。
C列の各セルに、隣のA列・B列のセルに記載されてある緯度・経度から、住所を取得してC列のセルに入力していくような処理はできないでしょうか。
緯度経度はずらりと5000行ほど並んでいるので、どうにかして効率化させたいです。
よろしくお願い致します。

回答の条件
  • 1人10回まで
  • 登録:2016/09/09 22:37:27
  • 終了:2016/09/11 14:01:23

ベストアンサー

id:a-kuma3 No.1

a-kuma3回答回数4365ベストアンサー獲得回数18012016/09/10 16:13:16

ポイント2000pt

以下のコードを標準モジュールに貼り付けて、シートを表示した状態で set_address サブルーチンを実行してください。

Function search_address(lat, lng, Optional retry = True)
    URL = "http://maps.googleapis.com/maps/api/geocode/xml?latlng=" & lat & "," & lng

    Set xhr = CreateObject("MSXML2.XMLHTTP")
    xhr.Open "GET", URL, False
    xhr.send ""
    If xhr.StatusText = "OK" Then
        Set doc = xhr.responseXML.DocumentElement
        Set stat = doc.FirstChild
        If stat.Text = "OK" Then
            Set a = doc.getElementsByTagName("formatted_address")
            search_address = a(0).Text
        Else
            If stat.Text = "OVER_QUERY_LIMIT" And retry Then
                Application.Wait Now + TimeValue("00:00:02")
                search_address = search_address(lat, lng, False)
            Else
                Set e = stat.NextSibling
                search_address = "ERROR : " & stat.Text & " : " & e.Text
            End If
        End If
    Else
        search_address = "ERROR : " & xhr.StatusText
    End If
End Function

Function is_need_search(cell)
    If IsEmpty(cell) Or cell.Value = "" Then
        is_need_search = True
    ElseIf InStr(cell.Value, "ERROR") = 1 Then
        is_need_search = True
    Else
        is_need_search = False
    End If
End Function


Sub set_address()
    start_row = 2   ' 開始行
    lat_col = 1     ' 緯度:A 列
    lng_col = 2     ' 経度:B 列
    address_col = 3 ' 住所:C 列

    last_row = Cells(Rows.Count, 1).End(xlUp).Row
    error_before = False
    For r = start_row To last_row
        Set dest = Cells(r, address_col)
        If is_need_search(dest) Then
            lat = Cells(r, lat_col).Value       ' 緯度
            lng = Cells(r, lng_col).Value       ' 経度
            Address = search_address(lat, lng)
            dest.Value = Address
            If InStr(Address, "ERROR") = 1 Then
                If before_error Then
                    Exit Sub
                Else
                    before_error = True
                End If
            Else
                before_error = False
            End If
        End If
        DoEvents
    Next
End Sub

Google Geocoding API のリバース ジオコーディングというのを使いました。
Google Maps Geocoding API  |  Google Maps Geocoding API  |  Google Developers

API は、呼び出し回数の制限があります。
https://developers.google.com/maps/documentation/geocoding/usage-limits?hl=ja
50回/秒、2,500回/日です。

なので、5000件かそこらあるようなので、すべて取得するには 2~3日ほどかかります。

1秒当たりの回数制限に引っかかった場合には、2秒待って、1回だけリトライをするようにしました。
リトライでも取得できない場合、もしくは、他のエラーが出た場合には、C 列のセルに "ERROR" で始まる文字列を書き込んでいます。

エラーが連続して 2行続くと、そこで行方向の処理を中断します。
1日の呼び出し回数制限に引っかかったときに、無駄な呼び出しをしないためです。


一日の利用回数の制限もありますし、それなりに遅いので、繰り返して実行できるようにしています。
C 列が空の場合、もしくは "ERROR" で始まる場合に住所を検索に行きます。

一回動かすと、2,500件くらいを処理できるはずで、最後の方が "ERROR : OVER_QUERY_LIMIT ~" というセルがふたつあると思います。
一日待ってから もう一度 動かすと、続きから処理を行います。


住所の候補は複数が返されるのですが、先頭のものを使っています。
C 列に入れる住所は、API の応答にある値をそのまま使っています。
「日本」が入ったり、郵便番号があったりしますので、加工したいと言われそうな気もしてます :-)



緯度、経度から住所を調べられる Web API には Yahoo! ジオコーダ API というのもあります。
http://developer.yahoo.co.jp/webapi/map/openlocalplatform/v1/geocoder.html

利用回数制限が 50,000回/日と一回で処理できる可能性があるのですが、いくつか適当な座標で試してみたところ、番地が入らないことが多くて、ちょっと精度が悪いかな、と思って使いませんでした。

他5件のコメントを見る
id:a-kuma3

サービスだと、提供側が仕組みで制限をかけるということもできるので、利用規約というかライセンスは平易な文章が多い気がします。

オープンソースなプロダクト(ソースやライブラリ)だと、再配布とか改変して利用とかあるので、難しいなという気はします。
# 緩いのもありますけど

2016/09/17 15:36:12
id:moon-fondu

そうなんですか。いやいや、サービスで提供しているAPIもオープンソースのプロダクトも、私にとってはどっちも難しいですね(汗)a-kuma3さんのサポート無しには使いこなせないです、いつもありがとうございます<m(__)m>

2016/09/24 08:21:20
  • id:pegazul
    こういうのは使えんのだろうか、緯度経度と言っても百分率だったり分秒だったりで違いがあるのかもだけど.(自分では使ったことないw)

    >3.Excelシートにまとめた緯度、経度リストから住所を一括変換できる。逆ジオコーディングと言う機能です。
    http://ouwvob06.wp.xdomain.jp/forums/topic/%E5%9C%B0%E7%90%86%E9%99%A2%E3%83%9E%E3%83%83%E3%83%97%E3%82%B7%E3%83%BC%E3%83%88%E3%81%8C%E4%BE%BF%E5%88%A9%E3%81%A7%E3%81%99/
  • id:moon-fondu
    pegazulさんのリンクにある方法は、kmlファイル?というあまり馴染みのないファイルを使用するのが少し腰が引けまして。やっぱり、仕事でも使っているExcelで何とかしたい性分なのです。
  • id:moon-fondu
    Yoshiyaさんもコメントありがとうございます。まだコメント欄限定、のようですが、回答欄にもお気軽にお書きいただきサポートいただけると助かります。
  • id:a-kuma3
    面白そうだったんで、id:pegazul さんがコメントした、国土地理院の「地理院マップシート」を試してみました。
    残念ながら、そのマップシートはぼくの環境では「必要なモジュールがロードできない」とかで動かなかったんですが、ドキュメントに書いてあった農研機構の逆ジオコーディング API を試してみました。
    http://www.finds.jp/rgeocode/index.html.ja

    番地が細かいところまで取得できなかったり、字や番地だけ別の要素で返されたりという癖はあるのですが、何ヶ所か試した感じでは Yahoo の API よりは精度が良さそうな感じ。

    ライセンスとか利用回数の記載はゆるゆるなので、一見 使いやすそうな感じはしますが、逆に使い倒すと遅くなったり、エラーになったり、ということはあるのかも、と想像したりしました。
  • id:moon-fondu
    地理院マップシートですか~もう少し余裕が出来たら使ってみます…。

この質問への反応(ブックマークコメント)

トラックバック

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません