ちょっと話が、前回のストアドプロシージャを使う話と前後してしまいますが・・・

iPhone, Android等のスマートフォンの爆発的な普及によって最近、位置情報を活用したアプリケーションが増えてきました。スマートフォンから出力される位置情報は、緯度、経度の情報だけなので、それに紐づく住所情報も取りたいですよね？（たぶん）、今回、MySQLを利用してGPSデータベースを作成してみました。情報があまりないので手順を含めいろいろと不足な部分もあるかと思いますが、検証ではまあまあ、期待される値がとれていると判断しましたのでｗその手順を公開したいと思います。

位置情報の元ネタを探すじゃあ位置情報ってどこにあるの？実は、国土交通省にあったりするんです。しかも無償で自由な目的で利用できたります。知らなかった。今回の説明では、位置情報データをダウンロードし、MySQLの地理情報テーブルに取り込み、それを検索するといった一連の流れを書きたいと思います。以下の環境で実施しました。（別に推奨環境ではありません）

OS: CentOS 5.5 x86-64 ハードディスク：40GB メモリ： 1GB MySQL 5.1.54 スキーマ名：geo 文字コード：utf-8

今回は、東京都のデータのみ利用して手順を説明します事前準備

国土交通省から街区（がいく）データのダウンロード http://nlftp.mlit.go.jp/isj/ から街区データをダウンロードします。ダウンロードするデータの詳細な内容につきましては該当のページで調べてくださいダウンロードデータの形式につきましては、http://nlftp.mlit.go.jp/isj/data.html をご参照ください。今回は、大字・町丁目レベル位置参照情報のデータ形式を利用しますダウンロードファイルの解凍/文字コード変換ダウンロードファイルは、zip形式および文字コードがsjisなので、解凍し、文字コードをutf-8に変更する必要があります。以下の手順により解凍/文字コード変換を行いました [zipファイル解凍] [shell]

pwd

/tmp

unzip 13000-03.0b.zip

Archive: 13000-03.0b.zip inflating: 13_2009.csv inflating: format_2009.html inflating: META_13_2009.xml

ll

合計 636 -rwxr-Sr-x 1 root root 86950 5月 12 12:23 13000-03.0b.zip -rw-r--r-- 1 root root 531855 3月 10 2010 13_2009.csv -rw-r--r-- 1 root root 13942 3月 23 2010 META_13_2009.xml -rw-r--r-- 1 root root 2694 7月 21 2010 format_2009.html [/shell] [utf-8に変換] [shell]

nkf -w 13_2009.csv > tokyo_utf8.csv

※ utf-8に変換されていることを確認

head tokyo_utf8.csv

"都道府県コード","都道府県名","市区町村コード","市区町村名","大字町丁目コード","大字町丁目名","緯度","経度","原典資料コード","大字・字・丁目区分コード" "13","東京都","13101","千代田区","131010001001","一ツ橋一丁目","35.691634","139.756685","1","3" "13","東京都","13101","千代田区","131010001002","一ツ橋二丁目","35.692947","139.757320","1","3" "13","東京都","13101","千代田区","131010002000","一番町","35.687723","139.739668","1","1" [/shell]

MySQLのテーブル作成およびデータのインポート上記データの形式でテーブルを作成し、ダウンロードしたデータをインポートするところまでの手順を説明します。 [テーブル定義]

[sql]

-- 大字・町丁目レベル位置参照情報テーブル CREATE TABLE geos ( id int(11) NOT NULL AUTO_INCREMENT, jis_prefecture_code int(11) NOT NULL COMMENT 'JIS都道府県コード', prefecture varchar(64) NOT NULL COMMENT '当該範囲の都道府県名', city_code int(11) NOT NULL COMMENT 'JIS市区町村コード', city varchar(128) NOT NULL COMMENT '当該範囲の市区町村名（郡部は郡名、政令指定都市の区名も含む）', area_code bigint NOT NULL COMMENT '大字・町丁目コード（JIS市区町村コード＋独自7桁）', area varchar(128) NOT NULL COMMENT '当該範囲の大字・町丁目名（町丁目の数字は漢数字）', lat decimal(9,6) NOT NULL COMMENT '十進経緯度（少数第6位まで、半角）', lng decimal(9,6) NOT NULL COMMENT '十進経緯度（少数第6位まで、半角）', org_material int(11) NOT NULL COMMENT '大字・町丁目位置参照情報作成における原典資料を表すコード1：自治体資料　2：街区レベル位置参照　3：1/25000地形図　0：その他資料', area_kubun_code int(11) NOT NULL COMMENT '大字/字/丁目の区別を表すコード1：大字　2：字　3：丁目　0：不明', geom point DEFAULT NULL COMMENT '測地情報', updated datetime NOT NULL COMMENT '更新日', created datetime NOT NULL COMMENT '登録日', PRIMARY KEY pr_key(id) ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

[/sql]

ダウンロードデータに含まれていないカラムの中で注目いただきたい型に、geom point DEFAULT NULL COMMENT '測地情報'というものがありますpointというカラムが今回の肝の空間情報型というものです。内部的には、X座標、Y座標を管理しております。まさに位置情報にはうってつけのカラムといってよいのではないかと思います。 [データのロード] 上記項目２にて、ダウンロードしたcsvデータを、geosテーブルにインポート（ロード）する手順について説明します。 csvデータの流し込みには、MySQL のloadコマンドを利用します、以下のようなSQLを生成して実行し、geo_import.sql というファイルに保存しました [geo_import.sql] [sql] load data infile '/tmp/tokyo_utf8.csv' REPLACE INTO TABLE geo.geos FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 LINES (jis_prefecture_code,prefecture,city_code,city,area_code,area_code,lat,lng,org_material,area_kubun_code); [/sql] ※ IGNORE 1 LINES は、CSVデータのヘッダ情報が含まれているため、ロード時にスキップする指定です geo_import.sql をシェル上から実行します [シェル上から実行] [sql]

mysql geo -u mysqluserid -pmysqlpassword < ./geo_import.sql

[/sql]

位置情報データが正常にインポートされたか確認します [インポート確認] [sql]

mysql geo -u mysqluserid -pmysqlpassword -e "select prefecture,city,lat,lng from geo.geos limit 5;"

+------------+--------------+-----------+------------+ | prefecture | city | lat | lng | +------------+--------------+-----------+------------+ | 東京都 | 千代田区 | 35.691634 | 139.756685 | | 東京都 | 千代田区 | 35.687723 | 139.739668 | | 東京都 | 千代田区 | 35.675705 | 139.740497 | | 東京都 | 千代田区 | 35.700021 | 139.758377 | | 東京都 | 千代田区 | 35.675706 | 139.750734 | +------------+--------------+-----------+------------+ [/sql] 以上でデータが正常にインポートできたか確認できたかと思います。位置情報データを作成する上記で正常にデータがインポートできましたが、まだ終わりではありません、先ほどのインポートでは、geomカラムに関しては元データがないので、これから作りこむ必要があります。ここで、SQL文を生成するSQLを実行させます。この部分に関しては、利用する人によりけりな部分なので一番やりやすい方法で試してみるのが良いかと思います。geom_make_update.sql　というsqlファイルを作成し、下記のように作成しました。 [geom_make_update.sql] [sql] -- sql文を生成するsqlを作成 -- concat関数は文字列結合の関数です select concat('update geos set geom = ','GeomFromText(''point(',lng, " ",lat,')'') where id =',id,';') from geos; [/sql]

geom_make_update.sqlの編集が終わりましたらシェル上で以下の通り実行します

緯度/経度をpoint型に変換

上記 a でpoint型に変換したデータを、Geometry型に変換 GeomFromText関数は、他にいろいろ使えますので別途説明します。では、geomフィールドが正常に更新されたか確認してみましょう [更新結果確認]

[sql]

mysql geo -u mysqluserid -pmysqlpassword -e "select prefecture,city,lat,lng,X(geom),Y(geom) from geo.geos limit 5;"

+------------+--------------+-----------+------------+------------+-----------+ | prefecture | city | lat | lng | X(geom) | Y(geom) | +------------+--------------+-----------+------------+------------+-----------+ | 東京都 | 千代田区 | 35.691634 | 139.756685 | 139.756685 | 35.691634 | | 東京都 | 千代田区 | 35.687723 | 139.739668 | 139.739668 | 35.687723 | | 東京都 | 千代田区 | 35.675705 | 139.740497 | 139.740497 | 35.675705 | | 東京都 | 千代田区 | 35.700021 | 139.758377 | 139.758377 | 35.700021 | | 東京都 | 千代田区 | 35.675706 | 139.750734 | 139.750734 | 35.675706 | +------------+--------------+-----------+------------+------------+-----------+ [/sql]

いかがでしょうか？出力結果にて、X(geom）、Y（geom）と記載している部分が、point型のフィールドを分解した結果になります。正常に更新されているのがわかるかと思います。

早速検索してみる

高速性重視、距離はざっくりのSQL 検出された緯度経度からもっとも近い、地区情報を抽出するなんてSQLを書いてみました。SQLの細かい部分の説明は割愛しますが、半径300m以内で抽出したデータでもっとも近いデータを一件表示しているサンプルです。ちなみに、半径300m以内の算出方法については、MySQLで指定した緯度経度から半径nメートル内検索っぽいのを実現するSQL を参考にさせていただきました。ざっくりした計算ではありますが検索の高速性を確保する上では充分満足のいくSQLではないかと思います下記の例では、新宿区三栄町の緯度経度をベースに抽出しております [高速性重視のSQL] [sql] SELECT GEOX.prefecture, GEOX.city, GEOX.area, GEOX.lat, GEOX.lng from ( SELECT jis_prefecture_code, prefecture, city_code, city, X(geom) as lng, Y(geom) as lat, area_code, area, ABS(Y(geom) - 35.687737006999996) + ABS(X(geom) - 139.72462102999998) as QSORT FROM geos WHERE MBRContains(GeomFromText ('LineString(139.727399 35.6905148, 139.721843 35.6849592)'),geom) ) as GEOX order by GEOX.QSORT limit 1; [/sql]

[上記SQLの出力結果] [sql] +------------+-----------+-----------+-----------+------------+ | prefecture | city | area | lat | lng | +------------+-----------+-----------+-----------+------------+ | 東京都 | 新宿区 | 三栄町 | 35.689632 | 139.725794 | +------------+-----------+-----------+-----------+------------+ 1 row in set (0.00 sec) [/sql]

距離は比較的正確、速度はそこそこ遅い・・・。上記のSQLとは方式が異なるため、比較の対象にならないのですが、今回は、GLengthというジオメトリ列関数で len 列に倍精度の数値を返しております。検索が遅い理由は、指定された緯度/経度を、テーブル中から総当りでチェックしているためです。もうちょっとうまい書き方があるかもしれませんが今のところここまでで・・。 [正確性重視のSQL] [sql] SELECT prefecture, city, area, Y(geom) as lat, X(geom) as lng, GLength(GeomFromText(CONCAT('LineString(139.72462102999998 35.687737006999996,', X(geom), ' ', Y(geom),')'))) AS len FROM geos ORDER BY len limit 1;

+------------+-----------+-----------+------------+-----------+---------------------+ | prefecture | city | area | lng | lat | len | +------------+-----------+-----------+------------+-----------+---------------------+ | 東京都 | 新宿区 | 三栄町 | 139.725794 | 35.689632 | 0.00222864467580975 | +------------+-----------+-----------+------------+-----------+---------------------+

[/sql]

・・とまあこんな感じの結果になりました。色々とわかりずらい説明が多くもうしわけないです、実際、緯度経度から、住所情報を取り出すWebService(API）は、データベースを作らなくても、サービスとして利用できるところは多々ありますが、今回はあえて街区情報を、自分のサーバを活用したいというケースを想定して掲載しました。でデータを持てば色々な場面で活用できると思いますので、このページを見ていただいている方のヒントに少しでもになればと考えております。