mysqlのSpatialIndexを試す

位置情報検索を試しました。
MySQLのバージョンは8.0.18。
テーブルはInnoDBで作成しました。
データは北緯 45~30 、東経 145~129 の間でランダムに400万件生成しました。

試す

1キロ圏内の検索
データは POINT の引数の lat,lon が逆になっています。

Indexなし(39.66 sec)

mysql> show create table `place_geos` \G
*************************** 1. row ***************************
       Table: place_geos
Create Table: CREATE TABLE `place_geos` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `geohash` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `geopoint` point NOT NULL /*!80003 SRID 3857 */,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4652971 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> explain SELECT ST_Y(geopoint) AS lat, ST_X(geopoint) AS lon FROM `place_geos` WHERE (ST_Within(geopoint, ST_Srid(ST_Buffer(POINT(139.761989, 35.712678), 0.009), 3857)));
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows    | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
|  1 | SIMPLE      | place_geos | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 4173642 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.01 sec)

mysql> SELECT ST_Y(geopoint) AS lat, ST_X(geopoint) AS lon FROM `place_geos` WHERE (ST_Within(geopoint, ST_Srid(ST_Buffer(POINT(139.761989, 35.712678), 0.009), 3857))); 
+------------+-------------+
| lat        | lon         |
+------------+-------------+
| 35.7054243 |  139.763378 |
| 35.7043374 | 139.7625432 |
| 35.7068365 |  139.765995 |
| 35.7179114 | 139.7584237 |
| 35.7174718 | 139.7561928 |
|  35.713119 |  139.757795 |
| 35.7085355 | 139.7619387 |
+------------+-------------+
7 rows in set (39.66 sec)

Indexあり(0.54 sec)

mysql> SELECT COUNT(*) FROM `place_geos`;
+----------+
| COUNT(*) |
+----------+
|  4194304 |
+----------+
1 row in set (0.32 sec)

mysql> explain SELECT ST_Y(geopoint) AS lat, ST_X(geopoint) AS lon FROM `place_geos` WHERE (ST_Within(geopoint, ST_Srid(ST_Buffer(POINT(139.761989, 35.712678), 0.009), 3857)));
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | place_geos | NULL       | range | geopoint      | geopoint | 34      | NULL |    3 |   100.00 | Using where |
+----+-------------+------------+------------+-------+---------------+----------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> SELECT ST_Y(geopoint) AS lat, ST_X(geopoint) AS lon FROM `place_geos` WHERE (ST_Within(geopoint, ST_Srid(ST_Buffer(POINT(139.761989, 35.712678), 0.009), 3857))); 
+------------+-------------+
| lat        | lon         |
+------------+-------------+
| 35.7068365 |  139.765995 |
| 35.7054243 |  139.763378 |
| 35.7043374 | 139.7625432 |
| 35.7085355 | 139.7619387 |
|  35.713119 |  139.757795 |
| 35.7179114 | 139.7584237 |
| 35.7174718 | 139.7561928 |
+------------+-------------+
7 rows in set (0.54 sec)

トラブルシュート

lat, lon が逆 (mac 環境)

これでかなりハマりました。
macでのみ再現し Ubuntu では再現しなかったです。
lat と lon を逆にしてデータを作成しました。

mysql> SELECT ST_Geohash(POINT(30, 125), 12);
ERROR 1690 (22003): latitude value is out of range in 'st_geohash'
mysql>
mysql> SELECT ST_Geohash(POINT(125, 30), 12);
+--------------------------------+
| ST_Geohash(POINT(125, 30), 12) |
+--------------------------------+
| wv2fsmq4xj7d                   |
+--------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ST_Geohash(POINT(190, 30), 12);
ERROR 1690 (22003): longitude value is out of range in 'st_geohash'

SPATIAL INDEX が作成できない

NOT NULL を指定すると作成できます。

rails g migration が使用できない

geometory 型には対応していないようなので、直接SQLを発行して対応します。