More than 1 year has passed since last update.

注意

mysqlでは、空間テーブルはMyISAMでしか作れません(正確には、InnoDBでもテーブルは作れますがインデックスが張れません。InnoDBで空間インデックスが張れるのは、5.7.4LABリリースからのようです。)。
MyISAMでも問題ない場合は、空間検索速度が速いので空間DB使うべきですが、トランザクションやテーブルロックなどの問題もありますので、InnoDBしか許されない場合は、一次元ハッシュコードを用いる方法を検討してください。

Mysql Reference manual

http://dev.mysql.com/doc/refman/5.5/en/spatial-extensions.html

空間テーブルの作り方

空間テーブル作成
CREATE TABLE IF NOT EXISTS `geo_table` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
  `geometry` geometry NOT NULL COMMENT 'ジオメトリ',
  PRIMARY KEY (`id`),
  SPATIAL KEY `geometry` (`geometry`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
  • 空間テーブル自体はInnoDBでも作れますが、現状インデックスを張れるのはMyISAMのみです。

空間データの突っ込み方

空間データ突っ込み
INSERT INTO `geo_table` (`geometry`) 
VALUES (
  GeomFromText('POINT', 4326)
);
  • 空間データ型(geometry型)はGeomFromText関数を使って文字列から作ります。
  • 点データを作成する文字列は、'POINT' です。
  • 4326という数字は、異なる座標系を同じテーブルに入れる際の見分けるIDです(SRIDといいます)。
    いわゆる世界測地系と呼ばれる経緯度を入れる際の値は4326です。
    別に同じ座標系のデータしか入れない際は省略して構いません。
    というか、空間sqlの仕様が策定されているので、それに合わせてこの引数が設定されてるだけで、mysqlの場合、違うSRIDを入れても区別しなかったはず...。

空間データの検索方法(矩形)

例:経度130度、緯度30度から経度140度、緯度40度の間にある点を探す。

空間検索(矩形)
SELECT `id`, X(`geometry`) AS `lng`, Y(`geometry`) AS `lat` 
FROM `geo_table` 
WHERE MBRWithin(`geometry`, GeomFromText('LineString(130.00 30.00, 140.00 40.00)', 4326));
  • 空間検索には、MBR...系の関数を使います。
  • MBRWithin関数は、第二引数の形状に外部で接する長方形の範囲に含まれる、第一引数のgeometryカラムの結果を返す関数です。
  • 第二引数として、GeomFromText関数で作成した経度130度、緯度30度と経度140度、緯度40度を結ぶ線を与えることで、結果的に経度130度、緯度30度と経度140度、緯度40度の間にある長方形内の点を検索しています。
  • mysqlのリファレンスを見ると、空間関数にはいろんな便利な関数があります(衝突判定、交差判定等)が、検索時インデックスが効くのはMBR...系の関数だけなので注意が必要です。
    他の関数を使いたい場合は、まずMBR...系関数でフィルタしてやった後、足切りにひっかかった結果に対して関数計算します。
  • X(), Y()はgeometryデータ型からX座標、Y座標をそれぞれ取り出します。

空間データの検索方法(距離、円検索)

  • 距離検索はいきなりはできないので、円に外接する矩形を求めてやって、その矩形で上記のように検索してやった後、結果に対して距離を求めてフィルタリングする形になります。
  • 矩形の求め方は、 http://blog.fujiu.jp/2011/09/gps-1.html を参考にすると、大雑把ですが緯度方向には緯度1度あたり111.3194908mの距離、経度方向には、緯度によって異なりますが、例えば北緯20度付近では経度1度当たり104.60610km、北緯44度付近では経度1度当たり80.07654kmになるので、これから逆算してやります。
  • なので大阪当たりでは、半径1kmの検索をしたい場合、検索したい経緯度を中心として緯度差0.01797度、経度差0.02221度程度の長方形を作って、検索してやればいいかと。
  • そうして得られた検索結果に、都度距離計算を行って(距離計算ロジックはよく見つかるので省略)、必要に応じてソートしてやる形になると思います。

空間検索の速度について

一次元ハッシュコードを用いる方法の方で比較していますので、そちらを参照してください。
空間インデックスを用いない、GeoHash( http://blog.masuidrive.jp/index.php/2010/01/13/geohash/ )のような検索方法とも簡単に比較してみましたが、ほぼ同一オーダながら若干速かったです。
もし何十万件と位置情報データがたまり、この検索方法ではおっつかなくなった場合は、Solr等を使ってサーバ並べて、しかないかと思います。

mysql以外ではどうするの?

以前、ブログで主要3種類DBの、空間SQLの違いをまとめたことがあります。
今回の記事とは違うSQLなので、そのまま差はわかりませんが、参考にはなるかと思います。