【Snowflake】ネイティブアプリ『逆ジオコーディング』をリリース！

By t.ozawa Snowflake 2023/09/07

緯度経度から住所に変換するアルゴリズムの手順と工夫

終わりに

使用方法チュートリアル

アプリ「PODBReverseGeocodingBeta」をデータマーケットプレイスでダウンロードしていきます。

使用される方は、次の手順でアプリを使用いただけます。

1. アプリのインストール

サイドバーからマーケットプレイスを選択し、「PODB」と検索し、「PODBReverseGeocodingBeta」を選択

または、こちらのURLから「PODBReverseGeocodingBeta」を選択します。

アプリ「PODBReverseGeocodingBeta」専用サイトを開き、ACCOUNTADMINでGetをクリックし、アプリをインストールします。

インストールが始まると、こちらの画面が表示されますので、OKをクリック

インストールには数分かかる場合があります！

2. アプリに入力するテーブルを選択

アプリがインストールされると、APPsのInstalled Apps欄に「PODBReverseGeocodingBeta」が表示されますので、こちらをクリック！

ここから、緯度経度を付与したいテーブルを指定していきますが、

緯度経度が記載されたテーブルが手元にない方は、以下のSQL文を用いて、渋谷駅の緯度経度テーブルを作成していただき、試してみてください！

-- サンプルテーブルを作成
CREATE OR REPLACE TABLE DB.SCHEMA.SAMPLE_TABLE (
  STATION_NAME VARCHAR,
  LATITUDE DOUBLE,
  LONGITUDE DOUBLE
);

-- サンプルテーブルに渋谷駅の緯度経度を挿入します。
INSERT INTO DB.SCHEMA.SAMPLE_TABLE (STATION_NAME, LATITUDE, LONGITUDE)
VALUES ('渋谷駅', 35.6581, 139.7017);

セキュリティアイコンをクリックし、Input Tableを指定します。

緯度経度カラムに住所情報を付与したいテーブルを選択します。

これによって、アプリに入力するテーブルの指定は完了です！

（追記 9/22）

入力するテーブルを変更したい場合は、セキュリティアイコンに戻り、Input Tableを設定しなおします。

ゴミ箱をクリックし、今指定している入力テーブルを解除します。

あとは先ほどと同様に、もう一回Input Tableを指定して、入力テーブルの変更は完了です！

3. 逆ジオコーディングを実行

いよいよ、アプリ「PODBReverseGeocodingBeta」を実行して、入力テーブルの緯度経度カラムから住所情報を付与していきます！

今回ブログの中で実行する入力テーブルは以下のような緯度経度テーブルになっています。

これがどのような出力になるのか、見ていきましょう。

ワークシートを開いて、REVERSE_GEOCOGINGプロシージャを実行します。

第一引数には経度に相当する入力テーブルのカラム名を記述し、第二引数には緯度に相当するカラム名を記述します。

call PODBREVERSEGEOCODINGBETA.app_instance_schema.REVERSE_GEOCODING(
　　'longitude',
　　'latitude'
);

Success！メッセージが出たら実行成功です！

GEO_TBLというテーブルが作成されます。

作成されたGEO_TBLの中身を見てみましょう。

本アプリでは、入力テーブルに対して、アドレスコード、都道府県名/コード、市区町村名/コード、町丁目名/コード、MATCH_CODを紐づけることができます。

補足 MATCH_CODEについて

MATCH_CODEは、緯度経度をどのくらいの粒度で住所に変換できたかを示しています。

数字が小さくなるほど、より詳細に正確に住所に紐づけられていることになり、00 「町丁目までマッチ」が最も詳細かつ正確にマッチしています。

MATCH_CODE	説明
00 （BEST）	町丁目までマッチ
01	町丁目に複数マッチしたため最近傍を紐づけ
10	市区町村までマッチ
11	市区町村に複数マッチしたため最近傍を紐づけ
20	都道府県までマッチ
99 （BAD）	失敗

緯度経度から住所に変換するアルゴリズムの手順と工夫

以下の手順で緯度経度から住所に変換しています。

入力テーブルの緯度経度と、都道府県ポリゴンを空間結合し、都道府県名/コードを紐づけ
緯度経度と、1で紐づいた都道府県で絞った市区町村ポリゴンを空間結合し、市区町村名/コードを紐づけ
2で市区町村が複数紐づいた場合に、市区町村ポリゴンの重心から最も近い市区町村名/コードを紐づけ
緯度経度と、3で紐づいた市区町村で絞った町丁目ポリゴンを空間結合し、町丁目名/コードを紐づけ
2で町丁目が複数紐づいた場合に、町丁目ポリゴンの重心から最も近い町丁目名/コードを紐づけ

本来、緯度経度に町丁目ポリゴンを空間結合する処理は、以下のSQLで実行できますが、

select 緯度経度, 町名, 町のコード
from 緯度経度テーブル
left join 町丁目ポリゴンテーブル
on 空間マッチ(緯度経度, ポリゴン) = True //めちゃくちゃ時間がかかる。。。

空間結合の中では、緯度経度とポリゴンを単純に結合すると非常に時間がかかるため、いくつかアルゴリズムに工夫をしています。

1つ目

手順1~5の順番です。都道府県、市区町村、町丁目の順に紐づけを行っており、都道府県で絞り込みを行ったうえで市区町村の結合をすることで、結合試行数を抑えることができます。

同様に市区町村で絞り込みを行ったうえで町丁目の結合をすることで、結合試行数を抑えています。

例えば、渋谷区道玄坂1丁目を紐づける場合、

町丁目ポリゴンを直に結合する場合、総当たりで最大22万件の空間結合を試行するのに対して、

都道府県、市区町村、町丁目と順に結合すると、東京都(1/47レコード) → 渋谷区(1/39レコード) → 道玄坂1丁目(1/84レコード)のように、最大170レコードで試行することができます。

以下のSQLでは、市区町村まであらかじめ紐づけたうえで、町丁目ポリゴンとマッチするアルゴリズム概要を示しています。

select 緯度経度, 町名, 町のコード
from 緯度経度テーブル
left join 町丁目ポリゴンテーブル
on
緯度経度.市区町村 = 町丁目.市区町村 and //市区町村が一致する町丁目で絞って、空間マッチ
空間マッチ(緯度経度, ポリゴン) = True

2つ目

ポリゴンの上限緯度・下限緯度・右限経度・左限経度情報を作成し、

緯度経度が上下左右の領域に含まれているかでポリゴン数を絞ったうえで、ポリゴンと空間結合することで結合試行数を抑えています。

以下のSQLでは、上下左右の領域に入っているかを確認したうえで、町丁目ポリゴンとマッチするアルゴリズム概要を示しています。

select 緯度経度, 町名, 町のコード
from 緯度経度テーブル
left join 町のポリゴンテーブル
on
青枠の左 < 経度 < 青枠の右 and //上下左右で絞り込んでから空間マッチ
青枠の上 < 緯度 < 青枠の下 and 
空間マッチ(緯度経度, ポリゴン) = True

このように、計算時間を抑えるためのアルゴリズムを実装した結果、10万件の緯度経度を入力した場合、約10秒で実行することができます。

終わりに

本日リリースした、逆ジオコーディングアプリケーション「PODBReverseGeocodingBeta」について紹介しました。

無料で商用可能で、簡単に使用できる本アプリを是非お試しください！

また、弊社では NativeAppについて、開発だけでなくMaketplaceへの申請手続まで支援可能です。

PODBには公共データ等を取り揃えており、それらを活用したAppも作成できます。

Maketplaceで公開するもの、自社内で活用する処理等、お気軽にお声がけください。

それでは！