前回の記事

背景

前回に引き続いてMySQLのお勉強中で，今回は空間情報(GIS : Geographic Information System，地理情報システム)についてです．MySQL8.0以前は平面上の座標しか扱えなかったみたいなのですが，8.0からは曲面上の座標も扱えるようになりました．これにより，緯度と経度から地球上の2点間の距離などを計算することができるようになっています．

前と同様に参考書籍は「MySQL徹底入門 第4版 MySQL 8.0対応」です．

※この記事ではMySQL8.0(Docker, tag:8.0)で実行しています

MySQLでよく用いる空間情報の3つの表現

MySQLでよく用いる空間情報の3つの表現はWKT(Well Known Text), WKB(Well Known Binary), MySQL内部表現になります．WKTは人間にとってわかりやすい表現で，WKBとMySQL内部表現はバイナリ型の表現になります．

座標系で距離を計算してみる

まずは簡単な座標系で距離を計算してみます．空間情報全体に関する公式ドキュメントは以下になります．

Google翻訳して読んでみると，所々で謎の日本語になってしまい，理解するにはやや難しい文章である印象を受けました．ドキュメントを読むより，手を動かして順番に理解していくほうが良さそうです．

(0, 0)と(1, 1)の点の距離は以下で計算できます．

SELECT ST_Distance(
   ST_GeomFromText('POINT(1 1)'),
   ST_GeomFromText('POINT(2 2)')
) AS distance;
+--------------------+
| distance           |
+--------------------+
| 1.4142135623730951 |
+--------------------+

ST_Distance()はST_Distance(g1, g2[, オプション])でg1とg2との間の距離を返します．
POINT()はPoint(x, y)で座標から点を作成します．
ST_GeomFromText()はST_GeomFromText(wkt)でWKT値をジオメトリ型に変換します．

図で表すと次のようになります．この2点間の距離は三平方の定理から√2(≒1.414213...)となるのでSQLの計算結果は正しそうです．

Geometry型を持つテーブルにレコードを保存する

次はテーブルに空間情報をもつレコードを保存してみます．

テーブルを作成して，適当なレコードを入れます．

CREATE TABLE geometry_sample (id INTEGER, location GEOMETRY);

INSERT INTO geometry_sample VALUES (1, ST_GeomFromText('POINT(3 3)'));
INSERT INTO geometry_sample VALUES (2, ST_GeomFromText('LINESTRING(1 4, 3 2, 2 2)'));

LINESTRING()は引数にポイント値を受け取って折れ線を作成します．

SELECT句で取得してみます．すると，locationがよくわからないことになってしまいました．

SELECT * FROM geometry_sample;
+------+----------------------------------------------------------------------------------------------+
| id   | location                                                                                     |
+------+----------------------------------------------------------------------------------------------+
|    1 | 0x00000000010100000000000000000008400000000000000840                                         |
|    2 | 0x00000000010200000002000000000000000000F03F000000000000104000000000000008400000000000000040 |
+------+----------------------------------------------------------------------------------------------+

これはMySQL内部ではバイナリ値で表現されているからです．ST_GeomFromText()でWKTからMySQL内部表現(ここではGeometry型)に変換しています．逆にMySQL内部表現からWKTにするにはST_AsText()を使います．

SELECT id, ST_AsText(location) FROM geometry_sample;
+------+-------------------------+
| id   | ST_AsText(location)     |
+------+-------------------------+
|    1 | POINT(3 3)              |
|    2 | LINESTRING(1 4,3 2,2 2) |
+------+-------------------------+

緯度と経度から大阪駅と東京駅との距離を求めてみる

まずは大阪駅と東京駅の緯度と経度から調べてみます．どうやろうかなぁと思っていたらGoogle Mapで調べることができました．

東京駅のところまで移動して右クリックを押すとメニューが表示されます．メニューの中の「この場所について」をクリックします．

すると下の方に緯度と経度が表示されました．(さすがGoogle先生だわ)

大阪駅も同様に調べて以下のようになりました．

これらを元に距離を求めてみます．

SELECT ST_Distance(
   ST_GeomFromText('POINT(35.681224 139.766998)', 4326),
   ST_GeomFromText('POINT(34.702429 135.495938)', 4326),
   "kilometre"
) AS distance;
+-------------------+
| distance          |
+-------------------+
| 403.8181045534133 |
+-------------------+

ST_Distance()に第3引数が，ST_GeomFromText()に第2引数を追加しました．

ST_Distance()の第3引数は単位を与えることができます．今回は取得した結果をkmに変換して返すようにしています．
ST_GeomFromText()の第2引数にはSRIDを与えることができます．緯度や経度，標高などを用いて座標として地球上の位置を表現する場合の基準のことを測地系といいます．測地系には「WGS 84」などがあり，SRIDとはその測地系に一意に割り振られた数字のことです．「WGS 84」はGoogle Mapでも採用されているデファクトスタンダードのようなもので，SRIDは4326です．

求めた結果は約403.8kmでした．(地球は丸いので直線距離ではなくやや曲線の距離であることに留意です)

どうやって正しいかを確かめようかなと思っていたらまたGoogle Mapが役に立ちました．
先ほどと同じように東京駅のところまで移動して右クリックを押すとメニューが表示されます．メニューの中の「距離を測定」をクリックします．

次に大阪駅のところを選択すると距離を測定できます．（Googleやはりスゴイ）

Google Mapによると403.06kmなので，だいたいあってるかなぁという具合ですね．

ちなみにST_GeomFromText()などに与える緯度と経度の値の範囲がイマイチわからなかったのですが，

If the geometry has a geographic SRS with a longitude or latitude that is out of range, an error occurs:
- If a longitude argument is not in the range (−180, 180], an ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE error occurs (ER_LONGITUDE_OUT_OF_RANGE prior to MySQL 8.0.12).
- If a latitude argument is not in the range [−90, 90], an ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE error occurs (ER_LATITUDE_OUT_OF_RANGE prior to MySQL 8.0.12).

から推測するに，緯度は-180~180，経度は-90〜90の範囲で指定することができるようでした．