今、A列の2行目から緯度、B列の2行目から経度が、ずらりと並んでおります。
この状況におきまして。
C列の各セルに、隣のA列・B列のセルに記載されてある緯度・経度から、住所を取得してC列のセルに入力していくような処理はできないでしょうか。
緯度経度はずらりと5000行ほど並んでいるので、どうにかして効率化させたいです。
よろしくお願い致します。
以下のコードを標準モジュールに貼り付けて、シートを表示した状態で 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回/日と一回で処理できる可能性があるのですが、いくつか適当な座標で試してみたところ、番地が入らないことが多くて、ちょっと精度が悪いかな、と思って使いませんでした。
サービスだと、提供側が仕組みで制限をかけるということもできるので、利用規約というかライセンスは平易な文章が多い気がします。
2016/09/17 15:36:12オープンソースなプロダクト(ソースやライブラリ)だと、再配布とか改変して利用とかあるので、難しいなという気はします。
# 緩いのもありますけど
そうなんですか。いやいや、サービスで提供しているAPIもオープンソースのプロダクトも、私にとってはどっちも難しいですね(汗)a-kuma3さんのサポート無しには使いこなせないです、いつもありがとうございます<m(__)m>
2016/09/24 08:21:20