緯度経度から大まかな住所を取得するために，簡易的なリバースジオコーダを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標準のリバースジオコーダを使えば良いのでは？
と思われるかも知れませんが， このあたりをご覧ください．

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

© 2023 KMIソフトウェア