LoginSignup
18
15

More than 5 years have passed since last update.

MySQLのPOINT型を用いて距離が近いレコードを検索する

Posted at

概要

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というのが緯度経度と同じ度数で、地球1周が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関数を使っている。

image.png

参考: https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html

このままだと距離順にソートされてないので、近い順で取得するにはどこかでソートする必要があるが、これはmysql側でやってもアプリケーション側でやっても変わらないので、保守性・スケール性から考えてアプリケーションでやった方がいいと思う。

あと、ここら辺はだいたいquery dsl的なやつで書けないから、生SQLを書くことになりそう。

18
15
1

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
18
15