mysql空間テーブルの作り方 #MySQL

注意

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

Mysql Reference manual

空間テーブルの作り方

空間テーブル作成

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なので、そのまま差はわかりませんが、参考にはなるかと思います。