※本記事は、NorthDetail Advent Calendar 2020の一環として投稿しています
こんにちは、iihara-tktsです。
最近Google API関連の作業を行う機会が結構あり、せっかくなのでその一環で行った作業の1つのExcelでジオコーディングを行った話を紹介したいと思います。
各種情報に対して、関連する地理座標(典型的には緯度・経度)を付加することをジオコーディングと言うそうです。例えば、住所から緯度経度の座標の情報に変換することをジオコーディング、その逆の座標情報から住所に変換することを逆ジオコーディングと言います。
今回使用するExcelの関数は以下の3つになります。
WEBSERVICE(【URL】)
指定した【URL】からXMLまたはJSON形式でデータを取得する関数です。
WEBSERVICE関数はmacOS版のExcelでは使用できないようなので注意してください。
FILTERXML(【XML】, 【パス】)
【XML】によって指定されたXMLから指定した【パス】にあるデータを取得する関数になります。
指定した【パス】に複数のデータがある場合は配列の形式で返却されます。
INDEX(【配列】, 【行番号】)
【配列】で指定した配列から、【行番号】のデータを取り出すことができます。
それでは実際にジオコーディングを行っていきます。
今回は住所から緯度経度を取得してExcel上に表示します。
まず、ジオコーディングに必要な情報を入力します。
以下の画像にある通り、①に緯度経度の情報を取得したい住所、②にGoogleのAPIキーを入力しています。
※GoogleのAPIキーは不正利用防止のため、隠しております。
次に③のセルにジオコーディング用のURLを設定します。
https://maps.googleapis.com/maps/api/geocode/xml?address=【住所】&key=【APIキー】
「address=」の後にジオコーディングを行いたい住所、「key=」の後にGoogleのAPIキーを指定する必要がありますので、
①、②で設定したセルを指定します。
③でGoogleAPIを呼び出す準備ができましたので、WEBSERVICE関数を利用してジオコーディングを行います。 ④のセルに「=WEBSERVICE(E3)」と入力しています。
WEBSERVICE関数が実行されると以下のような結果がセルに入力されます。
<?xml version=""1.0"" encoding=""UTF-8""?>
<GeocodeResponse>
<status>OK</status>
<result>
<type>street_address</type>
<formatted_address>1-chōme-6 Kita 1 Jōnishi, Chuo Ward, Sapporo, Hokkaido 060-0001, Japan</formatted_address>
<address_component>
<long_name>6</long_name>
<short_name>6</short_name>
<type>premise</type>
</address_component>
<address_component>
<long_name>1-chōme</long_name>
<short_name>1-chōme</short_name>
<type>political</type>
<type>sublocality</type>
<type>sublocality_level_3</type>
</address_component>
<address_component>
<long_name>Kita 1 Jōnishi</long_name>
<short_name>Kita 1 Jōnishi</short_name>
<type>political</type>
<type>sublocality</type>
<type>sublocality_level_2</type>
</address_component>
<address_component>
<long_name>Chuo Ward</long_name>
<short_name>Chuo Ward</short_name>
<type>political</type>
<type>sublocality</type>
<type>sublocality_level_1</type>
</address_component>
<address_component>
<long_name>Sapporo</long_name>
<short_name>Sapporo</short_name>
<type>locality</type>
<type>political</type>
</address_component>
<address_component>
<long_name>Hokkaido</long_name>
<short_name>Hokkaido</short_name>
<type>administrative_area_level_1</type>
<type>political</type>
</address_component>
<address_component>
<long_name>Japan</long_name>
<short_name>JP</short_name>
<type>country</type>
<type>political</type>
</address_component>
<address_component>
<long_name>060-0001</long_name>
<short_name>060-0001</short_name>
<type>postal_code</type>
</address_component>
<geometry>
<location>
<lat>43.0631990</lat>
<lng>141.3556302</lng>
</location>
<location_type>ROOFTOP</location_type>
<viewport>
<southwest>
<lat>43.0618500</lat>
<lng>141.3542812</lng>
</southwest>
<northeast>
<lat>43.0645480</lat>
<lng>141.3569792</lng>
</northeast>
</viewport>
</geometry>
<place_id>ChIJJalEMXYpC18RV3g_IYr9els</place_id>
<plus_code>
<global_code>8RM33974+77</global_code>
<compound_code>3974+77 Sapporo, Hokkaido, Japan</compound_code>
</plus_code>
</result>
</GeocodeResponse>
色々な情報がGoogleから返却されていますが、今回取得したい情報は緯度と経度になるので、latとlngで囲まれているデータを抽出します。⑤のセルに緯度を⑥のセルに経度を表示したいので、それぞれFILTERXML関数を使用してlatとlngで囲まれているデータのみを抽出します。また、上記のXMLにもある通り、latとlngのデータは3つ存在するため、INDEX関数を利用して1つ目のデータだけを表示するようにします。
今回はそれぞれ以下のようにセルに入力しています。
⑤=INDEX(FILTERXML(F3,”//lat”),1)
⑥=INDEX(FILTERXML(F3,”//lng”),1)
これで下記の画像のように緯度と経度が表示されます。
逆ジオコーディングはジオコーディングとは逆に以下のURLの「latlng=」の値の後にカンマ区切りで緯度と経度を指定するとWEBSERVICE関数でXML形式のデータの取得ができます。
https://maps.google.com/maps/api/geocode/xml?latlng=【緯度】,【経度】&output=xml&key=【APIキー】&language=ja
データの取得ができれば、XML内のデータの抽出方法はジオコーディングと同様の方法で取得できます。
今回はExcelのWEBSERVICE関数を使用してジオコーディングを行いました。
Excelさえ利用できるのであれば、プログラミングを行わなくとも簡単にジオコーディングできますので、興味のある方がいれば是非試してみていただければと思います。