MySQL 8.0.24 では Spatial(GIS)関数に、４つの新たな関数が実装されました。
ST_LineInterpolatePoint() ST_LineInterpolatePoints() ST_PointAtDistance() ST_Collect()
前者３つが、LINESTRING上のポイントを返す関数、最後のひとつが複数のジオメトリ値をひとつにまとめる関数です。このエントリでは、前者の３つについて紹介してみます。
準備
関数を試すに当たって、何度も LINESTRING の値を使うので、あらかじめテーブルにつっこんでおきたいと思います。以下のテーブル t1 を作成し、LINESTRING 値を持つ２つのレコードを登録しておきます。測地系を指定する場合でも大きく変わるものではないので、今回の確認は平面座標系（"普通の" x, y 座標系）で行いました。
ひとつめのデータが、(1,1)から(11,1) への2点を結ぶ最もシンプルな線。ふたつめが、(1,1)から (3,1), (3, 4) を経由して (6,4)へと結ぶ線です（イメージしてみてくださいね。スタート地点から右に２つ、そこから上に３つ、さらに右に３つと移動させて作った線ですよ）。
CREATE TABLE t1 (id integer, g GEOMETRY); INSERT INTO t1 VALUES (1, ST_GeomFromText('LINESTRING(1 1, 11 1)')); INSERT INTO t1 VALUES (2, ST_GeomFromText('LINESTRING(1 1, 3 1, 3 4, 6 4)'));
登録された内容も一応確認しておきます。線の長さも一緒に表示してみましょう。
mysql> SELECT id, ST_AsText(g), ST_LENGTH(g) FROM t1; +------+-----------------------------+--------------+ | id | ST_AsText(g) | ST_LENGTH(g) | +------+-----------------------------+--------------+ | 1 | LINESTRING(1 1,11 1) | 10 | | 2 | LINESTRING(1 1,3 1,3 4,6 4) | 8 | +------+-----------------------------+--------------+ 2 rows in set (0.00 sec)
これから新しい関数を試していくために、以下の基本構文を理解しておきましょう。実行例のSQLが長くなりがちですが、このテンプレを理解しておくと、少しは読みやすくなると思います。
mysql> SELECT id, ST_AsText( ★試したい関数をここに書く★ ) p FROM t1;
.
距離を指定して LINESTRING 上のPOINTを返す関数 ST_PointAtDistance()
距離を指定してLINESTRING上の位置を返してくれるのが、ST_PointAtDistance() 関数です。先ほど登録した線のそれぞれについて、まず、スタート位置から「距離３」のPOINTを返してもらいましょう。第一引数に LINESTRING、第二引数に求めたい距離を与えます。
mysql> SELECT id, ST_AsText( ST_PointAtDistance(g, 3) ) p FROM t1; +------+------------+ | id | p | +------+------------+ | 1 | POINT(4 1) | | 2 | POINT(3 2) | +------+------------+ 2 rows in set (0.00 sec)
冒頭で作成したそれぞれの線に対して、スタートから距離3の位置にあるPOINTが返されたことがわかります。分かりやすいように整数にしてみましたが、もちろん整数である必要はありません。
mysql> SELECT id, ST_AsText( ST_PointAtDistance(g, 4.7) ) p FROM t1; +------+--------------+ | id | p | +------+--------------+ | 1 | POINT(5.7 1) | | 2 | POINT(3 3.7) | +------+--------------+ 2 rows in set (0.00 sec)
第一引数で与えられた線よりも長い距離を指定した場合はエラーとなります。
mysql> SELECT id, ST_AsText( ST_PointAtDistance(g, 15) ) p FROM t1; ERROR 1690 (22003): Distance value is out of range in 'st_pointatdistance'
今回のように複数の行が処理対象となる場合、その中にひとつでも長さがオーバーしたものが存在するとエラーとなる点に注意が必要です（今回の２つの線は、長さがそれぞれ 10と8 でしたね）。
mysql> SELECT id, ST_AsText( ST_PointAtDistance(g, 9) ) p FROM t1; ERROR 1690 (22003): Distance value is out of range in 'st_pointatdistance'
距離8（今回のすべての線が収まっている長さ）を指定すれば、もちろんエラーなく動きます。このとき id=2のほう（長さ8）の結果は、この線の終点のPOINTとなっていることがわかります。
mysql> SELECT id, ST_AsText( ST_PointAtDistance(g, 8) ) p FROM t1; +------+------------+ | id | p | +------+------------+ | 1 | POINT(9 1) | | 2 | POINT(6 4) | +------+------------+ 2 rows in set (0.00 sec)
割合を指定して LINESTRING 上のPOINT(s)を返す関数 ST_LineInterpolatePoint(s)()
ここでは２つの関数を紹介します。ST_LineInterpolatePoint() と ST_LineInterpolatePoints() です。Point のところに複数形の s がついているか否かの違いです。
まずは、sがつかないほう。第一引数には LINESTRING を、第二引数には求めたい割合の値を指定します。ここでは 0.4 の位置を求めてみましょう。
mysql> SELECT id, ST_AsText( ST_LineInterpolatePoint(g, 0.4) ) p FROM t1; +------+--------------+ | id | p | +------+--------------+ | 1 | POINT(5 1) | | 2 | POINT(3 2.2) | +------+--------------+ 2 rows in set (0.01 sec)
それぞれ（長さ10の0.4である）４の位置のPOINT、および（長さ８の0.4である）3.2の位置にあるPOINTが返されていることがわかります。
sがつく方の関数は、その名前のとおり複数の結果を返します。同様に 0.4 を第二引数に与えてみると、以下のようにそれぞれ２つのPOINTを持つ MULTIPOINT の値が得られます。
SELECT id, ST_AsText( ST_LineInterpolatePoints(g, 0.4) ) p FROM t1; mysql> SELECT id, ST_AsText( ST_LineInterpolatePoints(g, 0.4) ) p FROM t1; +------+-----------------------------+ | id | p | +------+-----------------------------+ | 1 | MULTIPOINT((5 1),(9 1)) | | 2 | MULTIPOINT((3 2.2),(4.4 4)) | +------+-----------------------------+
各点は、スタート地点から 0.4 そして 0.8 の点となっていることがわかります（自身で確認してみてください）。
今回は 0.4 を指定したので、0.4と0.8の2カ所のPOINTとなりましたが、もっと切り刻むこともできます。0.2で実行した結果が以下となります。
mysql> SELECT id, ST_AsText( ST_LineInterpolatePoints(g, 0.2) ) p FROM t1; +------+---------------------------------------------------------------------------------+ | id | p | +------+---------------------------------------------------------------------------------+ | 1 | MULTIPOINT((3 1),(5 1),(7 1),(9 1),(11 1)) | | 2 | MULTIPOINT((2.6000000000000005 1),(3 2.2),(3 3.8000000000000007),(4.4 4),(6 4)) | +------+---------------------------------------------------------------------------------+ 2 rows in set (0.00 sec)
第二引数は線上の位置を割合で示すものですから、１より大きい値や負の値ではエラーとなります。
mysql> SELECT id, ST_AsText( ST_LineInterpolatePoints(g, 3) ) p FROM t1; ERROR 1690 (22003): Distance value is out of range in 'st_lineinterpolatepoints' mysql> SELECT id, ST_AsText( ST_LineInterpolatePoints(g, -0.2) ) p FROM t1; ERROR 1690 (22003): Distance value is out of range in 'st_lineinterpolatepoints'
割合関数と距離関数との非対称
第一引数にLINESTRINGを与えて、第二引数に距離または割合を与えるという、それぞれ似通った感じのこれらの関数ですが、不思議なことに、「割合」のほうにはLINESTRINGの長さまで繰り返してMULTIPOINTを返す関数があるのに対して、「距離」のほうにはそれがありません。
どういうシーンで使えるのかを想像してみたときに、私は真っ先に「マラソンコースをあらわす 42.195km の LINESTRING に対して例えば 5km ごとの地点の POINT を得る」みたいな活用を思いついたのですが、「ST_PointsAtDistance() 」のように "s" のついた関数は存在しないのです。
最後に
大切なことを書いていませんでしたが、今回紹介した Spatial関数。どれも OpenGIS に対する MySQLの独自の拡張です。独自と言っても、ST_LineInterpolatePoint() は postGIS にも実装されていますし（ただしpostGISには ST_LineInterpolatePoints()関数が存在しない代わりに ST_LineInterpolatePoint()関数の第3引数で繰り返しを行うか否かを指定する）、ST_PointAtDistance()も検索してみると Informix のGIS関数には存在しているようで、まったくMySQL開発チームが勝手に思いつきで作ったものというわけではなさそうです。
今回は平面座標でこれらの関数の動作を試してみましたが、WGS84などの測地系の上でこの関数を使ったときの動作については、今後試してみたいところです。地球の丸さを考慮した経路点を返してくれることを期待していますが、それをこの目で確認してみたいですね。
