目的
CSVファイルで持っている住所一覧データに対して、それぞれの緯度・経度を取得したい。
背景
Tableauでのマップ形式の可視化には、緯度・経度が必要だが、
使いたいデータソースには「住所」情報のみしかなかった。
データ量は10万件以下程度。
使ったもの
- Excel 2021
- Yahoo!ジオコーダAPI（無料）
https://developer.yahoo.co.jp/webapi/map/openlocalplatform/v1/geocoder.html
試せてないけど他候補
-Google Maps Platform Geocoding API（有料）
https://developers.google.com/maps/documentation/geocoding?hl=ja
-シンプル ジオコーディング 実験（無料）
https://geocode.csis.u-tokyo.ac.jp/home/simple-geocoding/
手順
Yahoo!ジオコーダAPIの利用準備 アプリケーションの登録
こちらの手順に沿って登録。※Yahoo!JAPAN IDが必要。
https://developer.yahoo.co.jp/start/
ガイドライン
https://developer.yahoo.co.jp/guideline/
Client IDを取得できれば準備OK。
Excel上でカラム追加していき緯度・経度を取得
Yahoo!ジオコーダAPIのサイトより、作りたいURLは下記
https://map.yahooapis.jp/geocode/V1/geoCoder?appid=<取得したClient ID>&query=<UTF-8でエンコード化された住所>
元データ住所に対して、下記の流れで取得。
①ENCODE
住所をUTF-8形式にエンコードする（URLに埋め込むために必要）
=ENCODEURL([@住所])
②URL
URL生成
="https://map.yahooapis.jp/geocode/V1/geoCoder?appid="<取得したClient ID>&"&query="&[@ENCODE]
③XML
リクエスト実行してXML形式のレスポンスを取得
=WEBSERVICE([@URL])
④緯度経度抽出
XMLから緯度・経度情報のみ抽出
=INDEX(FILTERXML([@XML],"//Coordinates"),1)
レスポンスフィールドのドキュメントより、Coordinatesが座標情報なので、
その要素のみをFILTERXML関数で取得。
FILTERXML関数 参考：https://www.helpaso.net/archives/2359/
今回の住所は～丁目までで番地情報がないので、
そのままだとレスポンスが「スピル形式」というもので1セルに複数入ってくる。
Ex. 東京都中央区日本橋本町4丁目、4丁目8、4丁目4・・・
これだと困るので先頭のもののみ取得するためにINDEX関数で1つめを取得。
※FILTERXML関数で要素指定できそうなものではと思ったもののうまくいかず断念。方法あるかも？
⑤経度、緯度
抽出したカラムから、カンマで分割して取得（区切り位置で切ってしまった）
サンプルで調べたところ④で返ってくる並びは
[経度,緯度]
まとめ
スピル形式に初めて出会ったところで若干面食らった。
Excelの関数、ほんとに色々ある。
