緯度経度から大まかな住所を取得するために,簡易的なリバースジオコーダを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ファイルの中身は,次のような感じです.

"都道府県名","市区町村名","大字・町丁目名","街区符号・地番","座標系番号","X座標","Y座標","緯度","経度","住居表示フラグ","代表フラグ","更新前履歴フラグ","更新後履歴フラグ"
"東京都","千代田区","麹町六丁目","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標準のリバースジオコーダを使えば良いのでは?
と思われるかも知れませんが, このあたりをご覧ください.

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

kamolandをフォローしましょう


© 2017 KMIソフトウェア