人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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

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

●質問者: moon-fondu
●カテゴリ:コンピュータ 学習・教育
○ 状態 :終了
└ 回答数 : 1/1件

▽最新の回答へ

1 ● a-kuma3
●2000ポイント ベストアンサー

以下のコードを標準モジュールに貼り付けて、シートを表示した状態で 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回/日と一回で処理できる可能性があるのですが、いくつか適当な座標で試してみたところ、番地が入らないことが多くて、ちょっと精度が悪いかな、と思って使いませんでした。


a-kuma3さんのコメント
>> 緯度、経度から住所を調べられる Web API には Yahoo! ジオコーダ API というのもあります。 ... 利用回数制限が 50,000回/日と一回で処理できる可能性があるのですが、いくつか適当な座標で試してみたところ、番地が入らないことが多くて、ちょっと精度が悪いかな、と思って使いませんでした。 << Google Geocoding API と Yahoo! ジオコーダ API の結果を比較したものをダイアリーに載せました。 → [http://d.hatena.ne.jp/a-kuma3/20160911/latlng_to_address_g_vs_y:title]

a-kuma3さんのコメント
ブクマコメントでちゃちゃが入っているので、それにも応えておきます。 >http://b.hatena.ne.jp/Yoshiya/20160911#bookmark-300912679:title> Google Geocoding APIの地図描写を伴わない単独使用は利用規約で禁止されているって以前指摘したはずなんだけどなあ。 << このときは Google のドキュメントには、 >> The Geocoding API may only be used in conjunction with a Google map; geocoding results without displaying them on a map is prohibited. For complete details on allowed usage, consult the Maps API Terms of Service License Restrictions. << という文面があった((当時はぼくも読んだ記憶がありますし、Internet Archive では確認できませんが、そのままの文面は Stackoverflow なんかでも引用されています))のですが、この回答を書いた時点では記載されていません。 ひとつの項として記載してあったように記憶していますが、現時点で公にされている記載では冒頭の以下の部分が内容的に近いくらいで、このような使い方は、特に禁止されてはいません。 >https://developers.google.com/maps/documentation/geocoding/intro?hl=en#BYB:title> Note: This service is generally designed for geocoding static (known in advance) addresses for placement of application content on a map; this service is not designed to respond in real time to user input. For dynamic geocoding (for example, within a user interface element), consult the documentation for the Google Maps JavaScript API client geocoder and/or the Google Play services Location APIs. << Google Suggest のように、ダイナミックに変わる入力にすぐ応答を返すような使い方だと遅いかもよ、ってことです(禁止はされていません)。 スパッと返って欲しいなら JavaScript API なんかを使ってくれよと。 JavaScript API を動かして確認してみると、[]https://maps.googleapis.com/maps/api/geocode/[] へリクエストを出しておらず、別の URL で応答を得ているようです。

moon-fonduさんのコメント
a-kuma3さんありがとうございます!単独使用は禁止…ではないのですね。英語だとわからないですね、日本人へのアナウンスは日本語してほしいですね?(^^; 緯度経度から住所はうまく取得できました。ありがとうございました。

a-kuma3さんのコメント
日本語でもアナウンスはされています。 >https://developers.google.com/maps/documentation/geocoding/intro?hl=ja#BYB> このサービスは、主に静的な(既知の)住所のジオコーディングを行ってアプリケーションの内容をマップ上に配置することを目的に設計されています。リアルタイムのユーザー入力に応答することなどを目的とはしていません。ユーザー インターフェース要素内などでのダイナミック ジオコーディングについては、Google Maps JavaScript API Client Geocoder や Google Play サービス Location API のドキュメントをご覧ください。 << 過去の規約の記載と比較して、「禁止する (prohibit)」という文言があるかどうかポイントなので、過去の文面が出されている英語の方を引用しました。 「はじめに」の部分では、「API キーが必要」とあるのですが、「リバースジオコーディング」の項では API キーは省略可能なパラメータの方に含まれているので、取得しなくても問題はないと思います。 Javascrip API の方では、API キーを指定しないと Geocoder が正しく動作しないので、Web API の方でリクエストを受け付けてくれるということは、文面通り省略可能なのでしょう。

moon-fonduさんのコメント
難しいですね?うーん。

a-kuma3さんのコメント
サービスだと、提供側が仕組みで制限をかけるということもできるので、利用規約というかライセンスは平易な文章が多い気がします。 オープンソースなプロダクト(ソースやライブラリ)だと、再配布とか改変して利用とかあるので、難しいなという気はします。 # 緩いのもありますけど

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

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ