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が動くサーバを準備する
自宅にあるバックアップサーバが遊んでいる状態だったので、そのサーバ上にデータベースをインストールします。
今回は、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 Package Client Utilities (MySQL-client-5.6.2_m5-1.linux2.6.x86_64.rpm)
- RPM Package MySQL Server (MySQL-server-5.6.2_m5-1.linux2.6.x86_64.rpm)
- RPM Package Compatibility Libraries (MySQL-shared-5.6.2_m5-1.linux2.6.x86_64.rpm)
インストールは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対応のソースに変更して実現しています。
