MySQL、InnoDBで任意の位置座標から最も近い何件かみたいな情報を取得したい時のメモ
テーブルはこんな感じ
mysql> desc shop;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| name | varchar(100) | NO | | NULL | |
| address | varchar(100) | NO | | NULL | |
| lnglat | geometry | NO | MUL | NULL | |
+---------+--------------+------+-----+---------+-------+
3 rows in set (0.04 sec)
mysql> show create table shop;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| shop | CREATE TABLE `shop` (
`name` varchar(100) NOT NULL,
`address` varchar(100) NOT NULL,
`lnglat` geometry NOT NULL,
SPATIAL KEY `lnglat` (`lnglat`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
で普通に任意の位置から最も近い10件を取得したいとするとこんな感じになります
mysql> explain select * from shop ORDER BY ST_Distance(lnglat,ST_GeomFromText('POINT(135.9876 35.9876)')) limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: shop
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 5459
filtered: 100.00
Extra: Using filesort
1 row in set, 1 warning (0.00 sec)
SpatialIndex貼っているのに効かない感じです
どうやらST_Distanceでは効かない模様
それで悩んだあげくこうしたら効きました
mysql> explain select * from shop where ST_Within(lnglat,ST_Buffer(POINT(139.663987,35.676577), 0.01)) ORDER BY ST_Distance(lnglat,ST_GeomFromText('POINT(135.9876 35.9876)')) limit 10\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: shop
partitions: NULL
type: range
possible_keys: lnglat
key: lnglat
key_len: 34
ref: NULL
rows: 25
filtered: 100.00
Extra: Using where; Using filesort
1 row in set, 1 warning (0.00 sec)
キモは where ST_Within(lnglat,ST_Buffer(POINT(139.663987,35.676577), 0.01))
で
lnglat
が ST_Buffer(POINT(139.663987,35.676577), 0.01)
が示すポリゴン内にあるかを ST_Within
で調べています
ST_Buffer
で指定座標から半径0.01度のポリゴンを作成します
それ何mやねんという話がありますが、詳しくは地球の円周から導いてもらうとして
すごくざっくり1度あたり10000m(正確には111000m強)です、つまり1mあたり0.0001度です
なので0.01度だとすごくざっくり半径100mの円のポリゴンができあがります
ググるとCONCATして近傍を探るSQLを組み立てるような記事が多いのですが、そんなものは
SpatialIndex効かないので、今のところこのやり方しかSpatialIndex効かなさそうです