MySQL5.6で新しく追加された地理空間データ(GIS)の検索を実装してみた

MySQL 5.6登場！！新機能速攻レビュー - 漢(オトコ)のコンピュータ道
を見ていたら、MySQL5.6の新機能の１つとして、こんな紹介が。

OpenGIS
ジオメトリデータの比較において、これまではMBR（minimum bounding rectangle ≒ 近似的な長方形）に基づいた比較しか出来なかったのだが、ジオメトリの正確な形に基づいた比較ができるバージョンの関数が追加された。例えばMBRバージョンのContains()に対して、ST_Contains()というように、ST_というプレフィックスのついたものが正確な比較をするバージョンとなる。

つまり、（私が使ったことがない）postGISをわざわざ使わなくても、使い慣れたMySQLだけで「この部分の記録を探したい！」という要求に簡単に答えられそうです。さっそく機能を作ってみました。
実際は4/22から検討開始して、今日やっとリリースできました。



簡単に説明すると、クリックして範囲を選んで、「記録検索」アイコンをクリックすれば、その範囲を通るルートをすべて検索する、という機能です。
これに以前から作りこんでいる「全ルート表示」機能を合わせこんで作っています。

全体でやるべきこと（目次）

簡単に言うと、以下の3つをやれば実現できます。

  1. MySQL5.6が動くサーバを準備する
    • メインのサーバでは違うバージョンのMySQLが動いているので、別のサーバを準備してMySQLをインストールします。
  2. GPXのトラックログをMySQLのデータに変換して登録する
  3. GoogleMap上から検索できるようにする

これを順番に説明していきます。

1. MySQL5.6が動くサーバを準備する

自宅にあるバックアップサーバが遊んでいる状態だったので、そのサーバ上にデータベースをインストールします。
今回は、CentOS5.5のサーバ上に、MySQL5.6 + php + Apacheの環境を構築しました。

まず、既にインストール済みのものがあれば削除しておきます。apacheはそのまま使いました。

# yum remove mysql-server
# yum remove mysql
# yum remove php
# yum remove php-cli

MySQL5.6のダウンロードサイトからLinuxのRPMをダウンロードします。
うちのサーバは64bit版のCentOSなので、該当するパッケージを選びました。

インストールはrpmコマンドで1発。

# rpm -Uvh MySQL*

my.cnfを準備します。サーバースペックがいいのでmy-large.cnfを利用しました。

# cp /usr/share/mysql/my-large.cnf /etc/my.cnf

これだけで、MySQLにはアクセスできるようになります。

# mysql
mysql> SET PASSWORD FOR root@localhost=PASSWORD('新しいパスワード');
mysql> exit
# mysql -u root -p

ついでにテーブルを作ってしまいます。

mysql> create database mytest;
Query OK, 1 row affected (0.02 sec)

mysql> use mytest
Database changed

mysql> create table geom_test (
    -> id int(10) unsigned,
    -> g multilinestring NOT NULL,
    -> primary key (id),
    -> spatial key (g)
    -> )  ENGINE=MYISAM;
Query OK, 0 rows affected (0.11 sec)

mysql> describe geom_test;
+---------+------------------+------+-----+---------+-------+
| Field   | Type             | Null | Key | Default | Extra |
+---------+------------------+------+-----+---------+-------+
| id      | int(10) unsigned | NO   | PRI | 0       |       |
| g       | multilinestring  | NO   | MUL | NULL    |       |
+---------+------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

GEOMETRY型を使うにはMYISAMをENGINEとする必要があり、GPSのトラックログは複数の線の組み合わせなのでmultilinestring型を使いました。
データは入れていませんが、これでとりあえずテーブルはできました。


次はPHPのインストールです。
PHPはyumでインストールしようとすると、php-mysqlのインストールでエラーになるため、ソースからコンパイルしました。


makeの途中でコケるので、事前に下記の2つを入れておきました。

# yum install httpd-devel
# yum install libxml2-devel


まずPHPのサイトからphp5.3.6.tar.gzをダウンロードします。

configureのオプションはいろいろ入れないといけないのですが、今回は下記で作りました。

# tar xvfz php-5.3.6.tgz
# cd php-5.3.6
# ./configure \
--with-mysql=mysqlnd \
--with-mysqli=mysqlnd \
--with-pdo-mysql=mysqlnd \
--with-apxs2=/usr/sbin/apxs \
--enable-mbstring \
--enable-mbregex \
--enable-zend-multibyte \
--with-zlib \
--with-libxml-dir=/usr \
--enable-xml
# make
# make test
# make install
# php --version
PHP 5.3.6 (cli) (built: May  6 2011 00:30:41) 
Copyright (c) 1997-2011 The PHP Group
Zend Engine v2.3.0, Copyright (c) 1998-2011 Zend Technologies

これできちんと入ったようです。


下記のコードを作ってコマンドライン実行して、エラーがでないのでとりあえずOK.

<?php
$db1 = mysql_connect("localhost","root","pass") or die("Could not connect database");
mysql_select_db("mytest",$db1) or die("Could not connect database");

2. GPXのトラックログをMySQLのデータに変換して登録する

GPX(XML形式)のファイルを読み込んで、latitude, longitudeに該当する箇所を抜き出し、MULTILINESTRINGとして格納します。説明は省略しますが、その代わりに簡略化したスクリプトのソースを貼り付けておきます。

<?php

// Geometry検索のためにルート情報をDBに格納するプログラム。
// $db1が記録一覧があるサーバ
// $db2が今回立ち上げたサーバ

$db1 = mysql_connect("server1_IP","user","pass") or die("Could not connect database");
mysql_select_db("mytest",$db1) or die("Could not connect database");
$db2 = mysql_connect("server2_IP","user","pass") or die("Could not connect database");
mysql_select_db("mytest",$db2) or die("Could not connect database");

date_default_timezone_set("Asia/Tokyo");

// 記録のIDを取得して、GPXファイルを1つずつ格納する。
$sql = "SELECT id FROM record_data";
$result = mysql_query($sql,$db1) or die('Query failed:'.$sql);
while ($dbdat = mysql_fetch_array($result, MYSQL_ASSOC)){
	insert_trkdata($dbdat['id']);
}

// ---------------------------------------------------------------------------------------
// functions
// ---------------------------------------------------------------------------------------

function insert_trkdata($id){
	global $db1,$db2;

	$mls="";
	
    // IDごとにディレクトリが切ってあり、その中にログファイルが格納されている前提。
	$uploaddir = '/path-to-gpx/'.$id;
	if(is_file($uploaddir . '/track.gpx')){
		$xml = simplexml_load_file($uploaddir . '/track.gpx');
		$i=0;
		$init=1;
		foreach($xml->trk as $trk) {
			$j=0;
			$mlstmp="";
			foreach($trk->trkseg->trkpt as $trkpt) {
				if($j>0){
					$mlstmp.=",";
				}
				$mlstmp.=floatval($trkpt->attributes()->lon)." ".floatval($trkpt->attributes()->lat);
				$j++;
			}
			if($j>=2){
				if($init == 0){
					$mls.=",";
				}
				$mls.="(".$mlstmp.")";
				$init = 0;
			}
			$i++;
		}
		$xml=null;
	}else{
		echo "cannot find file:".$id."\n";
		return;
	}
	if(empty($mls)){
		echo "error: no trakdata:".$id."\n";
		return;
	}

	// DB格納
	$sql = "INSERT INTO geom_test
			(id, g)
			VALUES (
			'".$id."',
			GeomFromText('MultiLineString(".$mls.")')
			)";
	$result = mysql_query($sql,$db2) or die('Query failed:'.$sql);
}

mysql_close($db1);
mysql_close($db2);

3. GoogleMap上から検索できるようにする

サーバ側に対して、経度・緯度で指定したパラメータを渡すと、その範囲のデータを検索して一覧表示させます。
検索はmultilinestring（GPSログ）とpolygon（検索範囲）の重なるすべてのデータになります。


検索用のメソッドはいろいろあり、Equal, Disjoint, Intersects, Touch, Overlap, Cross, Within, Containsなどがあるようです。
こちらのサイトでそれぞれの関係を詳しく説明してくれています。今回は重なるものなら何でもひっかけたいので、Intersectsを使えばよさそうです。


最初の引用元にも書いてありましたが、ST_を付けると正確な比較をしてくれます。MySQL5.6のマニュアルにも記載してあります。

Note
MySQL originally implemented these functions such that they used object bounding rectangles and returned the same result as the corresponding MBR-based functions. As of MySQL 5.6.1, corresponding versions are available that use precise object shapes. These versions are named with an ST_ prefix. For example, Contains() uses object bounding rectangles, whereas ST_Contains() uses object shapes.

As of MySQL 5.6.1, there are also ST_ aliases for existing spatial functions that were already exact. For example, ST_IsEmpty() is an alias for IsEmpty()

上記を見る限り、"ST_"を付けなくてもよさそうですが、念のため付けておきます。


ちなみに、CentOS5.5にインストールされているMySQL5.0のマニュアルを見ると、MBRベースの近似検索しかできないことが明記されています。

Note
Currently, MySQL does not implement these functions according to the specification. Those that are implemented return the same result as the corresponding MBR-based functions.


実際のサーバ側プログラムはPHPで作りました。
簡略化してポイントだけ抜き出すと、例えばこんな感じで作ります。

<?php
$lat1=floatval($_GET['lat1']);
$lon1=floatval($_GET['lon1']);
$lat2=floatval($_GET['lat2']);
$lon2=floatval($_GET['lon2']);

$db2 = mysql_connect("server2_IP","user","pass") or die(json_encode(array()));
mysql_select_db("mytest",$db2) or die(json_encode(array()));

$query = "SELECT id from geom_test ".
	"WHERE ST_Intersects(GeomFromText('Polygon((".
	$lon1." ".$lat1.", ".
	$lon1." ".$lat2.", ".
	$lon2." ".$lat2.", ".
	$lon2." ".$lat1.", ".
	$lon1." ".$lat1." ".
	"))'), g)";
$result = mysql_query($query,$db2) or die(json_encode(array()));
$idlist=array();
while ($dbdat = mysql_fetch_array($result, MYSQL_ASSOC)){
	$idlist[]=$dbdat['id'];
}
mysql_close($db2);

実際はこのIDから記録のタイトルや写真などを引っ張って、json形式で応答させます。


最後にGoogle Maps APIでPolygonを書いて、「記録検索」ボタンを押すと結果が表示されるようにすれば完了。ポップアップで検索結果を表示させるために、jQuery＋Interface elementsを利用しました。


全ルート表示の機能は、1000以上のルートを、ユーザごとの地図に投影する方法ã‚’Google Maps API v3対応のソースに変更して実現しています。


詳しくは実装したページのソースコードをのぞいてみてください（というか説明が大変なので、サボってるだけですが・・・）

最後に

実際に動かしてみたところ、6万のGPSトラックデータに対して検索しても、すぐに応答が返ってきます。
富士山や丹沢あたりの人気のあるエリアを検索すると、最初は少し重く応答に時間がかかりました。しかし、しばらくするとメモリキャッシュに載ったのか、検索をかけてもHDDアクセスすらなく即座に応答が返ります。

自分の日曜大工的なプログラミングでは、ここまで高速な検索はできません。
最初は、四角と線が交差するかどうかをどう判定するのか？から考え始めようとしていましたが、そこからスタートすると性能は出ないし作るのも大変です。これがMySQLを活用するだけでこんなに簡単＆高速に実現できます。

こんな素晴らしい機能を作っていただいたMySQLのデベロッパーの方々に感謝します。