63
62

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

mysql空間テーブルの作り方

Last updated at Posted at 2015-06-12

注意

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

63
62
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
63
62

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?