untitled .engineer

技術系のブログ（仮）

MySQL8.0でGIS機能を試す No.3 - 複数のPOINT型からLINESTRING型を作る

GIS MySQL

目次

本エントリの概要

  • ２つのPOINT型からそれらを結ぶLINESTRING型を生成する方法を調べてみた記録です。
  • WKTを経由する方法とバイナリのまま生成する方法を記載します。
  • 正直わからないことがあり、不安なままです。

前置き

  • 今回雑にWKTとか関数について書きますが、私自身基礎知識は @sakaik さんのプレゼンテーションで学習した部分が多いので、そのあたりは先にご覧いただくのがよいと思います。

sakaik.hateblo.jp

検証環境

  • Windows10
  • MySQL8.0.12

データは前に作成したportalsテーブルを使います。
MySQL8.0でGIS機能を試すためのデータ準備 - untitled .engineer

WKTを経由する方法

POINT型から緯度経度を取り出します。ST_X()とST_Y()が使えそうです。
データは相変わらず前のテーブルを使います。

SET @geom1 = (SELECT geom FROM portals WHERE `id` = 13214);
SELECT ST_X(@geom1),ST_Y(@geom1);

+--------------+--------------+
| ST_X(@geom1) | ST_Y(@geom1) |
+--------------+--------------+
|    35.710948 |   139.462236 |
+--------------+--------------+
1 row in set (0.01 sec)

これらをWKTのLINESTRING()の引数に出してみます。

SET @geom1 = (SELECT geom FROM portals WHERE `id` = 13214);
SET @geom2 = (SELECT geom FROM portals WHERE `id` = 13218);
SET @geomline = CONCAT('LINESTRING(',ST_X(@geom1),' ',ST_Y(@geom1),', ',ST_X(@geom2),' ',ST_Y(@geom2),')');
SELECT ST_AsText(ST_GeomFromText(@geomline, 4612));

+-------------------------------------------------------+
| ST_AsText(ST_GeomFromText(@geomline, 4612))           |
+-------------------------------------------------------+
| LINESTRING(35.710948 139.462236,35.738692 139.326691) |
+-------------------------------------------------------+
1 row in set (0.00 sec)

できているようです。 ...ただ、この方法、なんか違和感ありますね。
勝手な想像ですが、なんとなくパフォーマンスもよくなさそうな気がします。

バイナリのまま生成する方法

WKTを経由しない方法を探した結果、ST_ConvexHull()が使えるっぽいので試してみます。

マニュアル抜粋

mysql> SET @g = 'MULTIPOINT(5 0,25 0,15 10,15 25)';
mysql> SELECT ST_AsText(ST_ConvexHull(ST_GeomFromText(@g)));

マニュアルを見るとMULTIPOINT型を引数にしています。 POINT型からMULTIPOINT型に変換するのを探すと、ST_Union()が使えそうです。

マニュアル抜粋

mysql> SET @g1 = ST_GeomFromText('LineString(1 1, 3 3)');
mysql> SET @g2 = ST_GeomFromText('LineString(1 3, 3 1)');
mysql> SELECT ST_AsText(ST_Union(@g1, @g2));
+--------------------------------------+
| ST_AsText(ST_Union(@g1, @g2)) |
+--------------------------------------+
| MULTILINESTRING( (1 1,3 3),(1 3,3 1) ) |
+--------------------------------------+

実際に試してみます。

SET @geom1 = (SELECT geom FROM portals WHERE `id` = 13214);
SET @geom2 = (SELECT geom FROM portals WHERE `id` = 13218);
SELECT ST_AsText(ST_Union(@geom1, @geom2));
ERROR 3618 (22S00): st_union(POINT, POINT) has not been implemented for geographic spatial reference systems.

「geographic spatial reference systems」ってのは地理座標系(GEOGCS)のことを意味してるのでしょうか。
ということは投影座標系(PROJCS)でないとダメ？ マニュアルには特にそんな記述は見当たらないのですが・・・・

ではPOINTを投影座標系に変換してみましょう。 ST_Transform()という関数があります。
使うのはJGD2000、東京エリアを対象とするのでUTMゾーンは54、そのSRIDは3100です。
参考：GISのための測地成果、測地系、楕円体、投影座標系、EPSGコードのまとめ http://d.hatena.ne.jp/tmizu23/20091215/1260868350

SET @geom1 = (SELECT geom FROM portals WHERE `id` = 13214);
SET @geom1projcs = ST_Transform(@geom1, 3100);
ERROR 1305 (42000): FUNCTION gis.ST_Transform does not exist
FUNCTION ST_Transform does not exist
  • そういえばそうです。ClubMySQL#4で @sakaik さんが言ってました。”そば屋の出前詐欺”です。
    • 詳しくは上記のリンク先のスライドをご覧ください。
  • ほかに検索してみると以下のような感じ。
ST_SRID()を使ってよいか

マニュアルのST_Transform()のところに並べてST_SRID()という関数があります。

マニュアル抜粋

ST_SRID(g[, srid])
With a single argument representing a valid geometry object g, ST_SRID() returns an integer indicating the ID of the spatial reference system (SRS) associated with g.
With the optional second argument representing a valid SRID value, ST_SRID() returns an object with the same type as its first argument with an SRID value equal to the second argument. This only sets the SRID value of the object; it does not perform any transformation of coordinate values.
[cut]
ST_SRID(g, target_srid) and ST_Transform(g, target_srid) differ as follows:
ST_SRID() changes the geometry SRID value without transforming its coordinates.
ST_Transform() transforms the geometry coordinates in addition to changing its SRID value.
[cut]
It is possible to create a geometry in a particular SRID by passing to ST_SRID() the result of one of the MySQL-specific functions for creating spatial values, along with an SRID value.
[cut]
The two-argument form of ST_SRID() is useful for tasks such as correcting or changing the SRS of geometries that have an incorrect SRID.

Google翻訳にかけてみます。

ST_SRID()は、有効なジオメトリ・オブジェクトgを表す1つの引数を使用して、gに関連付けられた空間参照系（SRS）のIDを示す整数を戻します。
オプションの第2引数が有効なSRID値を表す場合、ST_SRID()は、第1引数と同じ型のオブジェクトを、第2引数に等しいSRID値で返します。 これは、オブジェクトのSRID値を設定するだけです。 座標値の変換は行われません。
[cut]
ST_SRID（g、target_srid）とST_Transform（g、target_srid）は次のように異なります。
ST_SRID()は、座標を変換せずにジオメトリSRID値を変更します。
ST_Transform()は、SRID値を変更するだけでなく、ジオメトリ座標を変換します。
[cut]
特定のSRIDにジオメトリを作成するには、SRID値とともに空間値を作成するためのMySQL固有の関数の1つの結果をST_SRID()に渡すことができます。
[cut]
ST_SRID()の2つの引き数形式は、不適切なSRIDを持つジオメトリのSRSの修正や変更などのタスクに役立ちます。

（Google翻訳すごいですね。技術用語も普通に読めるように翻訳してくれる。）
これを読み解くに、今回のケースで地理座標系のPOINTを投影座標系のPOINTに変換するのにST_SRID(g,srid)を使うのは不適切な気がしますので、試すのは今回はちょっと遠慮しておきます。

とりあえず現時点では変換できないので、ST_Union()を使うには元データを投影座標系などに直すべきなのでしょうか。
（投影座標系で再試行するのは次の機会に）
今回は元データを変えずになんとかする方法はないかと探してみました。

マニュアルを眺めていると「MySQL-Specific Functions That Create Geometry Values」というのがあります。
5.6の日本語マニュアルによると「幾何値を作成する MySQL 固有の関数」です。
OpenGIS準拠ではありませんが、試しにこれを使ってみます。

MultiPoint()関数を使ってみる

POINT型を引数にMULTIPOINT型を返すようです。

SET @geom1 = (SELECT geom FROM portals WHERE `id` = 13214);
SET @geom2 = (SELECT geom FROM portals WHERE `id` = 13218);
SET @geommp = MultiPoint(@geom1 , @geom2);
ERROR 1367 (22007): Illegal non geometric '(@`geom1`)' value found during parsing

あれ、だめですね。では直接クエリで渡してみます。

SET @geommp = MultiPoint((SELECT geom FROM portals WHERE `id` = 13214), (SELECT geom FROM portals WHERE `id` = 13218));
SELECT ST_AsText(@geommp);

+-----------------------------------------------------------+
| ST_AsText(@geommp)                                        |
+-----------------------------------------------------------+
| MULTIPOINT((139.462236 35.710948),(139.326691 35.738692)) |
+-----------------------------------------------------------+
1 row in set (0.00 sec)

とりあえず地理座標系でもエラーにならず、MULTIPOINTが返りました。ではこれにST_ConvexHull()をかましてみます。

SET @geommp = MultiPoint((SELECT geom FROM portals WHERE `id` = 13214), (SELECT geom FROM portals WHERE `id` = 13218));
SET @geomconv = ST_ConvexHull(@geommp);
SELECT ST_AsText(@geomconv);

+-------------------------------------------------------+
| ST_AsText(@geomconv)                                  |
+-------------------------------------------------------+
| LINESTRING(139.326691 35.738692,139.462236 35.710948) |
+-------------------------------------------------------+
1 row in set (0.00 sec)

LINESTRINGになりました！
ただ、お気づきでしょうか、axisが変わってしまっています。
MULTIPOINT型のときにすでに変わってしまっているので、MultiPoint()の挙動に由来すると思いますが、axisを変えずにLINESTRINGにする方法が見つけられませんでした。

上記で

特定のSRIDにジオメトリを作成するには、SRID値とともに空間値を作成するためのMySQL固有の関数の1つの結果をST_SRID()に渡すことができます。 ST_SRID()の2つの引き数形式は、不適切なSRIDを持つジオメトリのSRSの修正や変更などのタスクに役立ちます。

とあったのを思い出します。
そこでまず、ST_SRID()を使って戻り値のプロパティを見てみます。

SELECT ST_SRID(@geommp);
+------------------+
| ST_SRID(@geommp) |
+------------------+
|                0 |
+------------------+
1 row in set (0.00 sec)

SRIDが0になっています。
0に変換されたのか、そんなことが起こりうるのか、SRID情報が失われたのかはわかりません。
SRIDが失われたことを前提として、ST_SRID(g, target_srid)で修正します。（やっていることは相当適当です。）

SET @geommp = MultiPoint((SELECT geom FROM portals WHERE `id` = 13214), (SELECT geom FROM portals WHERE `id` = 13218));
SET @geomconv = ST_SRID(ST_ConvexHull(@geommp),4612);
SELECT ST_AsText(@geomconv);

+-------------------------------------------------------+
| ST_AsText(@geomconv)                                  |
+-------------------------------------------------------+
| LINESTRING(35.738692 139.326691,35.710948 139.462236) |
+-------------------------------------------------------+
1 row in set (0.00 sec)

戻りました。これでいいのかしら。。。

GeomCollection()を使ってみる

GeomCollection()がGEOMETRYCOLLECTION型を返します。
（GEOMETRYCOLLECTION型はMULTI～と違い、多種の幾何値（POINTでもLINESTRINGでもPOLYGONでも）を包含する型のようです。）
これをST_ConvexHull()できるのかも試してみます。

SET @geom1 = (SELECT geom FROM portals WHERE `id` = 13214);
SET @geom2 = (SELECT geom FROM portals WHERE `id` = 13218);
SET @geomcoll = GeomCollection(@geom1 , @geom2);
ERROR 1367 (22007): Illegal non geometric '(@`geom1`)' value found during parsing

やっぱりだめですね。では直接クエリで渡してみます。

SET @geomcoll = GeomCollection((SELECT geom FROM portals WHERE `id` = 13214), (SELECT geom FROM portals WHERE `id` = 13218));
SELECT ST_AsText(@geomcoll);

+-----------------------------------------------------------------------------+
| ST_AsText(@geomcoll)                                                        |
+-----------------------------------------------------------------------------+
| GEOMETRYCOLLECTION(POINT(139.462236 35.710948),POINT(139.326691 35.738692)) |
+-----------------------------------------------------------------------------+
1 row in set (0.00 sec)

GEOMETRYCOLLECTION型が返りました。やっぱりaxisが逆です。
ではこれにST_ConvexHull()とST_SRID()をかましてみます。

SET @geomcoll = GeomCollection((SELECT geom FROM portals WHERE `id` = 13214), (SELECT geom FROM portals WHERE `id` = 13218));
SET @geomconv = ST_SRID(ST_ConvexHull(@geomcoll),4612);
SELECT ST_AsText(@geomconv);

+-------------------------------------------------------+
| ST_AsText(@geomconv)                                  |
+-------------------------------------------------------+
| LINESTRING(35.738692 139.326691,35.710948 139.462236) |
+-------------------------------------------------------+
1 row in set (0.00 sec)

戻りました。

LineString()を使う

どうせMySQLの独自関数を使うならこちらも試してみましょう。 ST_ConvexHull()をかまさず、POINT型を引数に直接LINESTRINGが作れるようです。

マニュアル抜粋

LineString(pt [, pt] ...) Constructs a LineString value from a number of Point or WKB Point arguments. If the number of arguments is less than two, the return value is NULL.

SET @geom1 = (SELECT geom FROM portals WHERE `id` = 13214);
SET @geom2 = (SELECT geom FROM portals WHERE `id` = 13218);
SELECT ST_AsText(LineString(@geom1, @geom2));
ERROR 1367 (22007): Illegal non geometric '(@`geom1`)' value found during parsing

あ、はいはい。

SELECT ST_AsText(LineString((SELECT geom FROM portals WHERE `id` = 13214), (SELECT geom FROM portals WHERE `id` = 13218)));

+---------------------------------------------------------------------------------------------------------------------+
| ST_AsText(LineString((SELECT geom FROM portals WHERE `id` = 13214), (SELECT geom FROM portals WHERE `id` = 13218))) |
+---------------------------------------------------------------------------------------------------------------------+
| LINESTRING(139.462236 35.710948,139.326691 35.738692)                                                               |
+---------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

ここでもやっぱりaxisが変わってしまっています。

SET @geomline = (LineString((SELECT geom FROM portals WHERE `id` = 13214), (SELECT geom FROM portals WHERE `id` = 13218)));
SELECT ST_AsText(ST_SRID(@geomline,4612));

+-------------------------------------------------------+
| ST_AsText(ST_SRID(@geomline,4612))                    |
+-------------------------------------------------------+
| LINESTRING(35.710948 139.462236,35.738692 139.326691) |
+-------------------------------------------------------+
1 row in set (0.00 sec)

戻りました。

「幾何値を作成する MySQL 固有の関数」の特徴

どうもMySQLのGIS関連の独自関数は

  • ユーザー定義変数を引数に取れない
  • SRIDが0に変わる（変換なのか失われるのかは不明）

という特徴があるようです。

それぞれの値の比較

念のためそれぞれの方法で作ったLINESTRINGが同一なのかをST_Equals()で確認してみます。

-- WKT経由
SET @fromid = 13214;
SET @toid = 13218;
SET @geom1 = (SELECT geom FROM portals WHERE `id` = @fromid);
SET @geom2 = (SELECT geom FROM portals WHERE `id` = @toid);
SET @geomline1 = ST_GeomFromText(CONCAT('LINESTRING(',ST_X(@geom1),' ',ST_Y(@geom1),', ',ST_X(@geom2),' ',ST_Y(@geom2),')'), 4612);
-- MultiPoint()経由
SET @geommp = MultiPoint((SELECT geom FROM portals WHERE `id` = @fromid), (SELECT geom FROM portals WHERE `id` = @toid));
SET @geomline2 = ST_SRID(ST_ConvexHull(@geommp),4612);
-- GeomCollection()経由
SET @geomcoll = GeomCollection((SELECT geom FROM portals WHERE `id` = @fromid), (SELECT geom FROM portals WHERE `id` = @toid));
SET @geomline3 = ST_SRID(ST_ConvexHull(@geomcoll),4612);
-- LineString()を利用
SET @geomline4 = ST_SRID((LineString((SELECT geom FROM portals WHERE `id` = @fromid), (SELECT geom FROM portals WHERE `id` = @toid))),4612);
-- それぞれをGEOMETRY型のまま比較
SELECT ST_Equals(@geomline1,@geomline2) AS g1g2,
       ST_Equals(@geomline1,@geomline3) AS g1g3,
       ST_Equals(@geomline1,@geomline4) AS g1g4,
       ST_Equals(@geomline2,@geomline3) AS g2g3,
       ST_Equals(@geomline2,@geomline4) AS g2g4,
       ST_Equals(@geomline3,@geomline4) AS g3g4;

+------+------+------+------+------+------+
| g1g2 | g1g3 | g1g4 | g2g3 | g2g4 | g3g4 |
+------+------+------+------+------+------+
|    1 |    1 |    1 |    1 |    1 |    1 |
+------+------+------+------+------+------+
1 row in set (0.00 sec)

見た感じは問題なさそうです。これでいいのかしら。。。

次回は投影座標系のSRIDを実際に投入することを考えてみます。

参考