SQLiteで簡易リバースジオコーダ

緯度経度から大まかな住所を取得するために，簡易的なリバースジオコーダをSQLiteで作った話

拙作のアプリ山旅ロガーGOLD で，測定結果の大まかな住所を表示するという機能を実装するために，
簡易的なリバースジオコーダを制作したときの話です．

方式

正確にやるなら，行政区画のポリゴンを用意して，その内側か外側かを判定するのだと思いますが，
それは大変なので，一番近い市区町村を採用するという簡易的な方法を使います．

ですので，結果は正確ではありません．「大まかな住所」という前提です

市区町村の緯度経度のデータは，国土交通省の，位置参照情報ダウンロードサービスからダウンロードしたものを使います．

検索するためのツールですが，MySQLやPostgreSQLなどでやっても良いのですが，
対象が固定の読み取り専用データなので，SQLiteで済ませることにしました．

この方が，運用管理が不要なので楽でもありますし．

SQLiteのテーブル定義

cities.sqlite3というファイルを作ることにします．sqlite3コマンド

sqlite3 cities.sqlite3

で起動して，

create table cities (
lat real not null,
lon real not null,
pref_name text not null,
city_name text not null
);

でテーブルを作成します．
緯度経度と都道府県名，市区町村名を持っているだけの単純なものです．

データの準備

位置参照情報ダウンロードサービス

から，都道府県単位で全ての都道府県のデータをダウンロードします．

47個のzipファイルをダウンロードすることになりますが，それを全て展開して，
中に入っているファイルのうち，CSVファイルだけを使います．

北海道(2014年版)は「01_2014.csv」で，これが47都道府県で47番まであります．

CSVファイルの中身は，次のような感じです．

"都道府県名","市区町村名","大字・町丁目名","街区符号・地番","座標系番号","Ｘ座標","Ｙ座標","緯度","経度","住居表示フラグ","代表フラグ","更新前履歴フラグ","更新後履歴フラグ"
"東京都","千代田区","麹町六丁目","5","9","-34965.0","-9246.0","35.684800","139.731181","1","1","0","0"

今回は都道府県名(カラム0)，市区町村名(カラム1)，緯度(カラム7)，経度(カラム8)を使います．

SQLiteで読ませるCSVを作るために，
-必要なカラムの値だけをテーブル定義のカラム順に抽出する
-緯度経度が重複している行は除去する

という前処理を行いますが，今回は以下のようなperlのプログラムを作りました．
自分の好きな言語で作ればよいと思います．

conv.pl

#!/usr/bin/perl

my $pcode = shift;

my $fname = $pcode ."_2014.csv";
open(F, $fname) or die;

$pcodeint = int($pcode);

my %latlonmap = {};

my $first = 1;

while (<F>) {
  if ($first) {
    $first = 0;
    next;
  }
  my @cols = split(/,/);
  my $lat = $cols[7];
  my $lon = $cols[8];
  $lat =~ s/"//g;
  $lon =~ s/"//g;

  $latlon = $lat." ".$lon;

  my $prefName = $cols[0];
  $prefName =~ s/"//g;

  my $cityName = $cols[1];
  $cityName =~ s/"//g;

  if (!defined($latlonmap{$latlon})) {
    # CSV
    print "$lat,$lon,$prefName,$cityName\n";
    $latlonmap{$latlon} = 1;
  }
}

これをバッチファイルから

perl conv.pl 01 >>all.csv
perl conv.pl 02 >>all.csv
・・・
perl conv.pl 47 >>all.csv

という感じで47ファイル分呼び出して，全都道府県のデータを含んだ all.csv というのを作りました．

データのロード

sqlite3コマンドで起動して，

sqlite3 cities.sqlite3

citiesテーブルへのcsv取り込みを実行します．

.mode csv
.import all.csv cities

環境にもよるでしょうけど，10～数10秒で終わると思います．件数は，

select count(*) from cities;
3717036

3717036件でした．

※注意
CSVからそのままロードしているため，SQLiteのDB内の都道府県名，市区町村名は，
文字コードがSJISになっています．

DBのサイズ的にSJISのままが良いと思いますが，取得後に必要に応じてUTF-8に変換するなどの考慮が必要です．

インデックスの作成

緯度経度で検索するので，緯度経度にインデックスを作成します．

sqlite3 cities.sqlite3

create index cities_i1 on cities (lat, lon);

・検索したときの実行計画を確認

explain query plan
select * from cities
where lat > 34.788395 - 0.01 and lat < 34.788395 + 0.01 and lon > 135.234160 - 0.01 and lon < 135.234160 + 0.01
order by abs(lat - 34.788395) + abs(lon - 135.234160)
limit 1;

0,0,0,"SEARCH TABLE cities USING INDEX cities_i1 (lat>? AND lat<?)"
0,0,0,"USE TEMP B-TREE FOR ORDER BY"

インデックス(cities_i1)が使われているようです．

検索の実行

検索したい位置の緯度経度が(34.788395, 135.234160)の場合，基本はこうですが，

select * from cities
where lat > 34.788395 - 0.01 and lat < 34.788395 + 0.01 and lon > 135.234160 - 0.01 and lon < 135.234160 + 0.01
order by abs(lat - 34.788395) + abs(lon - 135.234160)
limit 1;

この0.01という検索範囲は別に根拠があるわけでもなく，
この範囲ではヒットしない場合もあります．

そのため自分は，これを少しずつ大きくして検索を繰り返し，ヒットした時点で打ち切るようにしています．

具体的には，
0.01, 0.02, 0.05, 0.065, 0.08, 0.1, 0.15, 0.2~
という風に増やしています．もちろん根拠はありません．．．

ちなみに最初から大きい値で検索してしまうと，インデックスで絞りきれないため，遅くなります．

余談

元々の目的が，Androidアプリで緯度経度から住所を取得するという話なら，
Android標準のリバースジオコーダを使えば良いのでは？
と思われるかも知れませんが，
このあたりをご覧ください．
Issue 9153 – gmaps-api-issues – Bug: android geocoder is not working

実際自分の手元にも，リバースジオコーダが動作しない端末があります orz

kamoland