MySQL
mysql5.7
MySQL8.0

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

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

※第1弾はこちら。

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

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

1. MySQL 8.0のGIS機能

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

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

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

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

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

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)の間は、

オラクル東京~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)を計算することができます。

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関連投稿記事へのリンクを集めました。