概要
mysqlで位置を表すレコードを検索する場合は、Spatial Data TypesのPOINT型を使うのが良いです。
その説明をします。
なぜSpatial Data Typesを使う必要があるのか
例えばこんなテーブルがあったとするじゃないですか
CREATE TABLE `location_lat_lng` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`lat` double NOT NULL,
`lng` double NOT NULL,
PRIMARY KEY (`id`),
KEY `lat` (`lat`,`lng`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
latitudeとlongtitudeを表した位置情報のテーブルです。
でこういうクエリを投げるじゃないですか
select * from location_lat_lng
where lat between 35.681 and 35.691
and lng between 139.757 and 139.767;
で、これ、インデックスがそこまで有効に使われません。MySQLは普通のインデックスの複数の範囲指定には弱いのです。詳しくは実践ハイパフォーマンスMySQLの5.4.2とか読むと良いです。
で、こういう要件の検索を高速に行うにはSpatial Data Typesが有効です。こいつらのインデックスはR-Treeインデックスと言って、上記のようなユースケースの検索に強いです。
Spatial Data Typesとは
MySQLの型で、位置、線、図形のような概念のためのデータ型です。でSpatial Data Typesはオブジェクト指向言語のクラス階層のようになっていて、一番上がGEOMETRY型、その下にPOINT型があったりとか、そんな感じになってます。
で、位置の検索する場合はPOINT型を使います。
POINT型を使う
POINT型は上で使ったテーブルのlatとlngを合わせて1つにしたようなデータ型です。
これを使って上記テーブルを書き換えるとこんな感じ
CREATE TABLE `location` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`loc` point NOT NULL,
PRIMARY KEY (`id`),
SPATIAL KEY `loc` (`loc`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
locというPOINT型のカラムになっています。
で、例えば、35.685, 139.762の地点から1kmメートル以内の地点を探す、ってなるとこんな感じ
select id, ST_X(loc), ST_Y(loc) from location where ST_Within(loc, ST_Buffer(POINT(35.685, 139.762), 0.009));
一つずつ解説すると、
- POINT型のインスタンスはPOINT(x, y)な感じで作ります。
- ST_Buffer(g, d)で、gから距離dの図形、つまり今回の場合POINT(35.685, 139.762)を中心とした半径0.009の円。
- で、この距離0.009というのが緯度経度と同じ度数で、地球１周が40,075kmなので 40,075km * (0.009/360) ≒ 1km。
- ST_Within(g1, g2)が、g1がg2に含まれていたらtrueになる関数。
- よって、今回の場合POINT(35.685, 139.762)から半径1kmの円の中に入っていればtrue
- locを直接selectするとlocのバイナリ表現がそのまま出力されるらしく、わけがわからない。なので、selectの部分でPOINTからlat, lngを取り出すST_X, ST_Y関数を使っている。
参考: https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html
このままだと距離順にソートされてないので、近い順で取得するにはどこかでソートする必要があるが、これはmysql側でやってもアプリケーション側でやっても変わらないので、保守性・スケール性から考えてアプリケーションでやった方がいいと思う。
あと、ここら辺はだいたいquery dsl的なやつで書けないから、生SQLを書くことになりそう。