「地球が丸いということを知った」MySQL 8.0で距離を計算してみる

2017/10/23のMyNA(日本MySQLユーザ会)会ネタ第2弾、です。

※第1弾はこちら。

 * **[MySQLのキーワードと予約語](https://qiita.com/hmatsu47/items/a1da0e06f0597acd6502)（「MySQL 8.0で予約語が増えた」問題）**

MyNA会で**「MySQLは8.0で地球が丸いということを知った」**という名言？が生まれましたが（あれ？最新情報セミナーのほうだったっけか？記憶が曖昧）、SRIDをサポートしたMySQL 8.0.3 RCで試してみます。

 * **[MySQL最新情報セミナー 2017年10月 in 東京](https://togetter.com/li/1164160)（togetterまとめ）**
 * **[MyNA(日本MySQLユーザ会)会 2017年10月](https://togetter.com/li/1164158)（togetterまとめ）**

※MySQL 5.7でのGIS機能（まだ地球は丸くなかった？）、および5.7に至るまでのGIS機能の歴史については、山﨑さんのこちらの資料がわかりやすいです。

 * **[MySQL 5.7で刷新されたGIS機能のご紹介](https://www.slideshare.net/yoyamasaki/mysql-57gis/1)（Oracle山﨑さんの「FOSS4G 2017 Tokyo コアデイ」での発表資料）**

# 1. MySQL 8.0のGIS機能

…はっきりいって、リファレンスマニュアルを読み切れていません。

 * **[11.5 Extensions for Spatial Data](https://dev.mysql.com/doc/refman/8.0/en/spatial-extensions.html)（MySQL 8.0 Reference Manual）**
 * **[12.15 Spatial Analysis Functions](https://dev.mysql.com/doc/refman/8.0/en/spatial-analysis-functions.html)（MySQL 8.0 Reference Manual）**

とりあえず、斜め読みでチャレンジしてみます。

 * **[12.15.1 Spatial Function Reference](https://dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html)（MySQL 8.0 Reference Manual）**

# 2. PostGISの結果と比較してみる

距離を計算するには、「ST_Distance()」関数を使います。
なお、最新情報セミナー／MyNA会でも話題に出ましたが、**座標のxy／yxの指定が逆**みたいです（SRID=4326：WGS84地理座標系の場合）。
手もとにPostgreSQL環境を展開していないので、ネットにある記事からちょっと拝借しました（すみません）。

 * **[第17章: ジオグラフィー型](http://workshops.boundlessgeo.com/postgis-intro-jp/geography.html)（Boundless：Introduction to PostGIS (Japanese)）**

PostGISの結果では、LAX（ロサンゼルス）～CDG（パリ）間は「9124665.26917268m」だそうですが、

```mysql:MySQLでLAX～CDG
mysql> SELECT ST_Distance(ST_GeomFromText('POINT(33.9434 -118.4079)', 4326), ST_GeomFromText('POINT(49.0083 2.5559)', 4326));
+----------------------------------------------------------------------------------------------------------------+
| ST_Distance(ST_GeomFromText('POINT(33.9434 -118.4079)', 4326), ST_GeomFromText('POINT(49.0083 2.5559)', 4326)) |
+----------------------------------------------------------------------------------------------------------------+
|                                                                                               9124661.75645021 |
+----------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
```

若干違いがあるようです。

※マニュアルをちゃんと読んでないのと、元々GISには疎いので、やり方が間違っていたらごめんなさい（3つ目の引数、optionsを使うとどうなるのだろう…）。

# 3. ついでに

2017/10/23開催のMySQL最新情報セミナー会場（オラクル）とMyNA会会場（GMO）の間は、

```mysql:オラクル東京～GMO
mysql> SELECT ST_Distance(ST_GeomFromText('POINT(35.6712713 139.718504)', 4326), ST_GeomFromText('POINT(35.65645560000001 139.69
94178)', 4326));
+-----------------------------------------------------------------------------------------------------------------------------------+
| ST_Distance(ST_GeomFromText('POINT(35.6712713 139.718504)', 4326), ST_GeomFromText('POINT(35.65645560000001 139.6994178)', 4326)) |
+-----------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                                2385.1110084135657 |
+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

2.4km弱、という結果が出ました。

# 4. MySQL 5.7では

MySQL 5.7では「まだ地球は丸くなかった？」と書きましたが、実際のところは「多分丸いんだけどまだ正確なところまで知らない」といったほうがいいかもしれません。

MySQL 8.0と違って、「ST_Distance()」関数を普通に使っても距離（m）は出ないので、「ST_Distance_Sphere()」関数を使い、地球を「半径6,370,986m（デフォルト）の真球」と見立てて大雑把に距離（m）を計算することができます。

```mysql:MySQL5.7の場合
mysql> SELECT ST_Distance_Sphere(ST_GeomFromText('POINT(-118.4079 33.9434)'), ST_GeomFromText('POINT(2.5559 49.0083)'));
+-----------------------------------------------------------------------------------------------------------+
| ST_Distance_Sphere(ST_GeomFromText('POINT(-118.4079 33.9434)'), ST_GeomFromText('POINT(2.5559 49.0083)')) |
+-----------------------------------------------------------------------------------------------------------+
|                                                                                         9103055.446530487 |
+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT ST_Distance_Sphere(ST_GeomFromText('POINT(139.718504 35.6712713)'), ST_GeomFromText('POINT(139.6994178 35.65645560000001)'));
+------------------------------------------------------------------------------------------------------------------------------+
| ST_Distance_Sphere(ST_GeomFromText('POINT(139.718504 35.6712713)'), ST_GeomFromText('POINT(139.6994178 35.65645560000001)')) |
+------------------------------------------------------------------------------------------------------------------------------+
|                                                                                                            2384.756337781878 |
+------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
```

MySQL 8.0でSRID=4326を使う場合の「ST_Distance()」関数とはxy／yzの指定が逆（PostGISと同じ）です。
※MySQL 8.0でもSRID=0を使う場合はMySQL 5.7と同様で、SRID=4326を使う場合は「ST_Distance()」関数と同じ順に指定します。

さすがにLAX～CDG間は誤差が大きいですが、オラクル東京～GMO間は実用レベルと言ってもいい程度の誤差ですね。

---

**【おまけ】**
MySQL 8.0、5.7関連投稿記事へのリンクを集めました。

 * **[Qiitaに投稿したMySQL 8.0関連記事](http://hmatsu47.hatenablog.com/qiita_mysql80)**
 * **[Qiitaに投稿したMySQL 5.7関連記事](http://hmatsu47.hatenablog.com/qiita_mysql57)**