8
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

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

Last updated at Posted at 2017-10-25

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

8
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
8
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?