BLOG ブログ


2020.12.20 TECH

Excelでジオコーディングを行ってみた

※本記事は、NorthDetail Advent Calendar 2020の一環として投稿しています

こんにちは、iihara-tktsです。
最近Google API関連の作業を行う機会が結構あり、せっかくなのでその一環で行った作業の1つのExcelでジオコーディングを行った話を紹介したいと思います。

ジオコーディングとは

各種情報に対して、関連する地理座標(典型的には緯度・経度)を付加することをジオコーディングと言うそうです。例えば、住所から緯度経度の座標の情報に変換することをジオコーディング、その逆の座標情報から住所に変換することを逆ジオコーディングと言います。

使用する関数

今回使用するExcelの関数は以下の3つになります。

WEBSERVICE関数

WEBSERVICE(【URL】)

指定した【URL】からXMLまたはJSON形式でデータを取得する関数です。
WEBSERVICE関数はmacOS版のExcelでは使用できないようなので注意してください。

FILTERXML関数

FILTERXML(【XML】, 【パス】)

【XML】によって指定されたXMLから指定した【パス】にあるデータを取得する関数になります。
指定した【パス】に複数のデータがある場合は配列の形式で返却されます。

INDEX関数

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さえ利用できるのであれば、プログラミングを行わなくとも簡単にジオコーディングできますので、興味のある方がいれば是非試してみていただければと思います。


一覧に戻る


LATEST ARTICLE 最新の記事

CATEGORY カテゴリー