MySQLで地理データ上にある2点間の距離を測定してみよう

はじめに

前回までで、基本的な地理情報データの扱い方や測地系についてひと通り解説してきました。

今回からはしばらく、それらのデータ同士の関係を得るための便利な関数をいくつか紹介していこうと思います。今回は、地理データ上にある2点間の距離を得る「ST_Distance()」関数です。

ST_Distance()関数の使い方

新しい関数を使ってみるときは、必ず1次情報であるリファレンスマニュアルを参照する習慣をつけましょう。マニュアルには、ST_Distance()関数は以下の引数を取ることが記載されています。

ST_Distance(g1, g2 [, unit])

オプション引数である「unit」は結果を受け取る際の長さの単位で'metre'(メートル)または'foot'(フィート)を指定できます。省略時は'metre'になるので、本連載では指定せずに進めることにします。

g1, g2は2つの地理データで、POINT/LINESTRING/POLYGONのすべての組み合わせを指定できます(MySQL 8.0の初期のバージョンでは、一部の組み合わせのみ対応していました)。

サンプルデータ

以下のリストは、千葉県内のとある駅周辺にある飲食店の情報です。

mysql> SELECT id, name, ST_AsText(pos) FROM restaurant ;
+----+----------------------+-----------------------------+
| id | name                 | ST_AsText(pos)              |
+----+----------------------+-----------------------------+
|  1 | ラーメンT              | POINT(35.871958 140.010395) |
|  2 | ラーメンH              | POINT(35.871402 140.011747) |
|  3 | カレーC               | POINT(35.871836 140.011138) |
|  4 | ファストフードM         | POINT(35.872169 140.012188) |
|  5 | レストランC             | POINT(35.871424 140.010493) |
|  6 | カレーH               | POINT(35.873222 140.011038) |
+----+----------------------+-----------------------------+

リストの中には現れていませんが、測地系はJGD2011(SRID=6668)です。これまでの連載で習得した知識から、これらのデータを登録するためのSQLを書くことができるでしょうか。ぜひ考えてみてください。

一例として、以下のようなSQLがあります。

CREATE TABLE restaurant (
 id INTEGER PRIMARY KEY AUTO INCREMENT,
 name VARCHAR(30),
 pos GEOMETRY SRID 6668
);

INSERT INTO restaurant (name, pos) VALUES ('ラーメンT', ST_GeomFromText('POINT(35.871958 140.010395)',6668)), ('ラーメンH', ST_GeomFromText('POINT(35.871402 140.011747)',6668)), ('カレーC', ST_GeomFromText('POINT(35.871836 140.011138)',6668)), ('ファストフードM',ST_GeomFromText('POINT(35.872169 140.012188)',6668)), ('レストランC', ST_GeomFromText('POINT(35.871424 140.010493)',6668)), ('カレーH', ST_GeomFromText('POINT(35.873222 140.011038)',6668));

ID 1～6の飲食店を地図にマッピングすると、下図のような状態であることが分かります。ここでは、過去の連載で紹介したDBeaverを使用しています(一部、筆者加工)。

距離の測定

いま、駅前の、黒いニコニコマークが描かれている場所にいるとします。この場所は北緯35.872362度、東経140.011102度です。この場所と各飲食店の距離を計算してみましょう。

ST_Distance()の2つの引数に、この場所のPOINT情報と、DBに登録済みのpos列を指定します。

mysql> SELECT 
   ->     id, 
   ->     name, 
   ->     ST_DISTANCE(
   ->       ST_GeomFromText(
   ->            'POINT(35.872362 140.011102)',6668),
   ->            pos
   ->       ) d 
   ->   FROM restaurant 
   ->   ORDER BY d;
+----+------------------------+--------------------+
| id | name                   | dist               |
+----+------------------------+--------------------+
|  3 | カレーC                 | 58.454097883480216 |
|  1 | ラーメンT                |  78.01307467519922 |
|  6 | カレーH                 |  95.59840905952436 |
|  4 | ファストフードM           | 100.38611295454443 |
|  5 | レストランC              | 117.71586541358725 |
|  2 | ラーメンH               | 121.40543865433648 |
+----+-----------------------+--------------------+
6 rows in set (0.000 sec)

あるいは、以下のように現在位置を変数にセットして利用するとSELECT文の見通しが良いかもしれません。

SET @g1=ST_GeomFromText('POINT(35.872362 140.011102)',6668);
SELECT id, name, ST_DISTANCE(@g1, pos) d 
 FROM restaurant order by d;

SQLで得られた結果を図と見比べてみてください。ニコニコマークから3番が一番近く、次いで 1, 6, 4...の順になっているという結果は、目視でも何となく正しそうだと判断できます。

方角を求めてみよう

現在位置からそれぞれのお店へのの距離が分かったところで、やはりそのお店がどちらの方角にあるのかを知りたくなりますよね。MySQLのSpatial機能そのものの紹介ではありませんが、テクニックとして三角関数を使って求める方法を紹介します。

ここでは方角を表す方法として、以下のような仕様で考えることにします。

• 真上（北）をゼロ度として1周を360度として表現
• 真上から時計回り(右回り)の方向に、90度、180度、270度、と回転するように表現

現在位置が(35.872362 140.011102)なので、これらの緯度・経度どうしの差をそれぞれ一辺の長さとする三角形としてATAN値を求めたものが目的地への角度となります。

mysql> SELECT id, name,
   ->        ST_DISTANCE(ST_GeomFromText('POINT(35.872362 140.011102)',6668), pos) dist,
   ->        ROUND(
   ->          MOD(
   ->            DEGREES(ATAN2(ST_LONGITUDE(pos)-140.011102, 
   ->                          ST_LATITUDE(pos)-35.872362)) + 360, 360)
   ->        ,2) direct
   ->   FROM restaurant 
   ->  ORDER BY dist;
+----+------------------------+--------------------+--------+
| id | name                   | dist               | direct |
+----+------------------------+--------------------+--------+
|  3 | カレーC                 | 58.454097883480216 | 176.08 |
|  1 | ラーメンT               |  78.01307467519922  | 240.26 |
|  6 | カレーH                 |  95.59840905952436 | 355.74 |
|  4 | ファストフードM           | 100.38611295454443  | 100.08 |
|  5 | レストランC              | 117.71586541358725  | 212.99 |
|  2 | ラーメンH               | 121.40543865433648  | 146.1 |
+----+------------------------+--------------------+--------+
6 rows in set (0.000 sec)

SQLはいろいろ複雑に見えますが、360度を超えたときの対応として剰余を求めていることと、結果の桁数を減らすために小数点以下2桁で丸めていること、そしてATAN2の結果はラジアンで返るのでDEGREES関数で度に変換している処理が入っているだけです。

なお、この計算方法はごく近い範囲内での方角を求めることを前提として、地球曲面を無視しています。何十キロも離れた地点の角度計算には使えないのでご注意ください。また、今回のクエリでは方位に対して小数点2桁で丸める関数を掛けましたが、実際の利用の際には距離の列に対してもこのようにROUND()関数を使って適当な桁数で丸めることが多いでしょう(この処理をデータベース側で実施するか、データベースは詳細な値を常に返して表示調整はアプリケーション側でやるかは、意見が分かれるところかもしれません)。

このクエリも、現在地点を変数にセットして使用することで少し見通しがよくなります。

SET @g1=ST_GeomFromText('POINT(35.872362 140.011102)',6668);
SELECT id, name,
      ST_DISTANCE(@g1, pos) dist,
      ROUND(
        MOD(
          DEGREES(ATAN2(ST_LONGITUDE(pos)-140.011102, 
                        ST_LATITUDE(pos)-35.872362)) + 360, 360)
      ,2) direct
 FROM restaurant 
ORDER BY dist;

下図に90度ごとの線を書き入れたので、角度が正しそうであることを確認してみてください。なお、各店舗の場所は数字が書いてある部分ではなく、その隣にある青い丸の部分です。

直値を避けた書き方

先ほどのSQLは、三角関数に適用するための一辺の長さを求めるために現在位置の緯度や経度を直値で書いていました。今回のSQLも、現在地点を変数に入れておくことで、その緯度や経度の値を取得する ST_LATITUDE()、 ST_LONGITUDE()関数を活用できるようになります。

mysql> SET @g1=ST_GeomFromText('POINT(35.872362 140.011102)',6668);
mysql> SELECT ST_LATITUDE(@g1),ST_LONGITUDE(@g1);
+------------------+-------------------+
| ST_LATITUDE(@g1) | ST_LONGITUDE(@g1) |
+------------------+-------------------+
|        35.872362 |        140.011102 |
+------------------+-------------------+
1 row in set (0.000 sec)

これらを使うと、SQL中に緯度経度の即値を記述することを回避することができます。

SET @g1=ST_GeomFromText('POINT(35.872362 140.011102)',6668);
SELECT id, name,
      ST_DISTANCE(@g1, pos) dist,
      ROUND(
        MOD(
          DEGREES(ATAN2(ST_LONGITUDE(pos)-ST_LONGITUDE(@g1), 
                        ST_LATITUDE(pos)-ST_LATITUDE(@g1))) + 360, 360)
      ,2) direct
 FROM restaurant 
ORDER BY dist;