mysql8でspatial indexを効かせるためにはSRIDを統一しておく必要があるかもしれない話

# 総論

spatial index効かせたかったら列にSRIDをつけろ

# GISを使って地図情報を扱ったり近傍検索したり

mysqlは8になってGIS関連が超パワーアップしたと聞いて。

- [MySQLに本格GIS機能がやってきた～MySQL8\.0最新情報～@OSC2018北海道](https://www.slideshare.net/sakaik/mysqlgismysql80)
- [MySQL 8\.0で強化されたGIS機能のご紹介：「FOSS4G 2018 Hokkaido」での発表資料](https://www.slideshare.net/yoyamasaki/mysql-80gisfoss4g-2018-hokkaido)

# 準備

何はともあれ環境作成

```bash
[qyen@mullet mullet]$ mysql --version
mysql  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)
```

## テーブルを作る

```sql
CREATE TABLE `locations` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `geohash` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `location` geometry NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `locations_id_unique` (`id`),
  KEY `locations_name_index` (`name`),
  KEY `locations_geohash_index` (`geohash`),
  SPATIAL KEY `location` (`location`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
```

名前と住所とロケーション。あとgeohashでオシャレ感を演出的な。

## データ投入

[位置参照情報ダウンロードサービス](http://nlftp.mlit.go.jp/isj/index.html)から大字・町丁目レベルでダウンロードして投入。
googlemapで使いたいのでWGS 94(SRID4326)を使おう。

```sql
LOAD DATA INFILE '/path/to/13_2015_utf8.csv'
REPLACE INTO TABLE
geo.spots
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n' STARTING BY ''
IGNORE 1 LINES
(@dummy, @prefecture, @dummy, @city, @dummy, @area, @lat, @lon, @dummy, @dummy)
SET
name = CONCAT(@city, @area), 
address = CONCAT(@prefecture, @city, @area), 
location = ST_GeomFromText(CONCAT('POINT(', @lon, ' ', @lat, ')'),4326),
created_at=now(),
updated_at=now();
```

みたいな。(実際はlaravel使ってコマンド化してインポートした。)

- [空間参照系の概要 \- Qiita](https://qiita.com/boiledorange73/items/b98d3d1ef3abf7299aba)
- [MySQLに街区レベル位置参照情報のCSVデータをインポートする \- xykのブログ](http://xyk.hatenablog.com/entry/2016/07/19/234456)

# 空間検索って中2感あふれる言葉だよね

どうやらst_bufferで範囲を作ってst_withinで含まれているか検索するのが良いようだ。

- [MySQL \(InnoDB\) で空間検索 \(st\_within\) \- Qiita](https://qiita.com/kkdd/items/2e26d9dd25673e52c1af)

```sql
select count(*) from locations 
where ST_Within(location,ST_GeomFromText(St_AsText(ST_Buffer(ST_GeomFromText(St_AsText(ST_GeomFromText('POINT(35.668440825448 139.74229644907)' , 4326))),0.0089831528411952)),4326))
```

...くっそ遅ぇ。全国分のCSV食わせて189539件のデータから検索するのに7秒ぐらいかかってる。

explainしてみる。

```sql
explain select count(*) from locations 
where ST_Within(location,ST_GeomFromText(St_AsText(ST_Buffer(ST_GeomFromText(St_AsText(ST_GeomFromText('POINT(35.668440825448 139.74229644907)' , 4326))),0.0089831528411952)),4326))
```
| id | select_type |table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|:------------|:---------|:----------|:--------|:--------|:--------|:--------|:-----|:-----|:-----|:-----|:----|
|1|SIMPLE|locations|null|**ALL**|null|null|null|null|187266|100|Using where|

おい、type=ALL おい。

# こんなにSRIDアンマッチに煩いシステムなのに列定義にSRIDを指定しないでいいのだろうか:thinking: [^1]

[^1]: なんかなろう系いけ好かないタイプの主人公みたいな見出しだ。

> In 5.7 and earlier, the only requirements on an indexed geometry column were that the type should be a geometry type and that the column shouldn’t be nullable. Unfortunately, we were allowed to insert geometries in different SRIDs into the same index. That never made sense, and it gets worse when some geometries are in geographic SRSs.
>Therefore, MySQL 8.0 adds a way to restrict geometry columns to only one SRID:

> (いいかげん翻訳) 5.7以前だとnotnullかつgeometryならindex張れたけどSRID違いだったり一部の空間関数使うとクソ重かったから8.0以降では列をSRIDで絞ってindexにSRIDを強制させる方法ができたよ。
>[Geographic Indexes in InnoDB \| MySQL Server Blog](https://mysqlserverteam.com/geographic-indexes-in-innodb/)

```sql
mysql> CREATE TABLE places (
 -> pk INT PRIMARY KEY,
 -> position POINT NOT NULL SRID 4326,
 -> name VARCHAR(200)
 -> );
Query OK, 0 rows affected (0,00 sec)
```

>We can easily create an index on our geometry column.

```sql
mysql> CREATE SPATIAL INDEX position ON places (position);
Query OK, 0 rows affected (0,00 sec) Records: 0 Duplicates: 0 Warnings: 0
```

>Since this column is in SRID 4326, the index will also be in SRID 4326. SRID 4326 is a geographic SRS, so this will be a geographic index. The query optimizer will automatically use this index to optimize execution of queries with spatial relation functions (ST_Contains, ST_Within, etc.), if it finds that that is the cheapest access method. All the spatial relation functions support geographic computations.
> (いいかげん翻訳) 列をSRID 4326にしたからindexも4326になってクエリオプティマイザが4326系の検索にインデックスを使うようになるぞ。
>[Geographic Indexes in InnoDB \| MySQL Server Blog](https://mysqlserverteam.com/geographic-indexes-in-innodb/)

なるほど。

# 列にSRIDを指定し直して実行

```sql
alter table locations drop index location;
alter table locations modify location geometry not null SRID 4326 ;
alter table locations add spatial index(location);
```

して

```sql
explain select count(*) from locations 
where ST_Within(location,ST_GeomFromText(St_AsText(ST_Buffer(ST_GeomFromText(St_AsText(ST_GeomFromText('POINT(35.668440825448 139.74229644907)' , 4326))),0.0089831528411952)),4326))
```
| id | select_type |table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|
|:------------|:---------|:----------|:--------|:--------|:--------|:--------|:-----|:-----|:-----|:-----|:----|
|1|SIMPLE|locations|null|range|location|location|34|null|1|100|Using where|

おお。早くなった。


# つーか公式ブログてめぇ

>One perhaps surprising fact is that the server still allows us to crate indexes on columns that are not restricted to a single column, but with a warning that the index will never be used:
> (いいかげん翻訳) SRIDを指定してない列にインデックス張っても警告されない上に絶対にそのインデックスを使われないとかマジ驚きだよね :grinning:
>[Geographic Indexes in InnoDB \| MySQL Server Blog](https://mysqlserverteam.com/geographic-indexes-in-innodb/)

驚きだよねじゃなくて直せや。