2017/10/23のMyNA(日本MySQLユーザ会)会ネタ第2弾、です。
※第1弾はこちら。
- MySQLのキーワードと予約語(「MySQL 8.0で予約語が増えた」問題)
MyNA会で**「MySQLは8.0で地球が丸いということを知った」**という名言?が生まれましたが(あれ?最新情報セミナーのほうだったっけか?記憶が曖昧)、SRIDをサポートしたMySQL 8.0.3 RCで試してみます。
- MySQL最新情報セミナー 2017年10月 in 東京(togetterまとめ)
- MyNA(日本MySQLユーザ会)会 2017年10月(togetterまとめ)
※MySQL 5.7でのGIS機能(まだ地球は丸くなかった?)、および5.7に至るまでのGIS機能の歴史については、山﨑さんのこちらの資料がわかりやすいです。
- MySQL 5.7で刷新されたGIS機能のご紹介(Oracle山﨑さんの「FOSS4G 2017 Tokyo コアデイ」での発表資料)
1. MySQL 8.0のGIS機能
…はっきりいって、リファレンスマニュアルを読み切れていません。
- 11.5 Extensions for Spatial Data(MySQL 8.0 Reference Manual)
- 12.15 Spatial Analysis Functions(MySQL 8.0 Reference Manual)
とりあえず、斜め読みでチャレンジしてみます。
- 12.15.1 Spatial Function Reference(MySQL 8.0 Reference Manual)
2. PostGISの結果と比較してみる
距離を計算するには、「ST_Distance()」関数を使います。
なお、最新情報セミナー/MyNA会でも話題に出ましたが、座標のxy/yxの指定が逆みたいです(SRID=4326:WGS84地理座標系の場合)。
手もとにPostgreSQL環境を展開していないので、ネットにある記事からちょっと拝借しました(すみません)。
- 第17章: ジオグラフィー型(Boundless:Introduction to PostGIS (Japanese))
PostGISの結果では、LAX(ロサンゼルス)~CDG(パリ)間は「9124665.26917268m」だそうですが、
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> 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> 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関連投稿記事へのリンクを集めました。