MySQL 8.0.24 では、Spatial機能（GIS機能）にもう一つの大きな進化がありました。CAST() 関数と CONVERT() 関数の Spatial型対応です。
テーブルとデータの用意
このエントリでは、以下のデータを使用します。（このエントリの途中で更に増えます）
CREATE TABLE t2 (id integer, g GEOMETRY);
INSERT INTO t2 VALUES (1, ST_GeomFromText('POINT(1 1)'));
INSERT INTO t2 VALUES (2, ST_GeomFromText('POINT(3 3)'));
INSERT INTO t2 VALUES (3, ST_GeomFromText('POINT(2 2)'));
INSERT INTO t2 VALUES (4, ST_GeomFromText('POINT(4 4)'));
INSERT INTO t2 VALUES (5, ST_GeomFromText('LINESTRING(1 2, 2 3, 3 4)'));
INSERT INTO t2 VALUES (6, ST_GeomFromText('LINESTRING(1 3, 1 4, 2 5, 1 3)'));
INSERT INTO t2 VALUES (7, ST_GeomFromText('LINESTRING(3 4, 4 2, 5 4)'));
INSERT INTO t2 VALUES (8, ST_GeomFromText('POLYGON((5 2, 7 3, 8 6, 6 5, 5 2))'));
INSERT INTO t2 VALUES (9, ST_GeomFromText('POLYGON((5 3, 7 4, 8 7, 6 7, 5 3),(6 4, 7 5, 6 5, 6 4))'));
登録したデータを、ジオメトリ型とともに表示、確認してみます。
mysql> SELECT id, ST_AsText(g), ST_GeometryType(g) FROM t2;
+------+--------------------------------------------------+--------------------+
| id | ST_AsText(g) | ST_GeometryType(g) |
+------+--------------------------------------------------+--------------------+
| 1 | POINT(1 1) | POINT |
| 2 | POINT(3 3) | POINT |
| 3 | POINT(2 2) | POINT |
| 4 | POINT(4 4) | POINT |
| 5 | LINESTRING(1 2,2 3,3 4) | LINESTRING |
| 6 | LINESTRING(1 3,1 4,2 5,1 3) | LINESTRING |
| 7 | LINESTRING(3 4,4 2,5 4) | LINESTRING |
| 8 | POLYGON((5 2,7 3,8 6,6 5,5 2)) | POLYGON |
| 9 | POLYGON((5 3,7 4,8 7,6 7,5 3),(6 4,7 5,6 5,6 4)) | POLYGON |
+------+--------------------------------------------------+--------------------+
POLYGON型からのMULTIPOLYGON
POLYGON型からMULTIPOLYGON型への最もシンプルな変換は、要素をひとつだけ持つ MULTPOLYGON型への変換です。
mysql> SELECT id, ST_AsText(g),
-> ST_AsText( CAST(g AS MULTIPOLYGON)) MP
-> FROM t2
-> WHERE ST_GeometryType(g)='POLYGON';
+------+--------------------------------------------------+---------------------------------------------------------+
| id | ST_AsText(g) | MP |
+------+--------------------------------------------------+---------------------------------------------------------+
| 8 | POLYGON((5 2,7 3,8 6,6 5,5 2)) | MULTIPOLYGON(((5 2,7 3,8 6,6 5,5 2))) |
| 9 | POLYGON((5 3,7 4,8 7,6 7,5 3),(6 4,7 5,6 5,6 4)) | MULTIPOLYGON(((5 3,7 4,8 7,6 7,5 3),(6 4,6 5,7 5,6 4))) |
+------+--------------------------------------------------+---------------------------------------------------------+
カッコが多くなってやや見にくいですが、確かに、元のPOLYGONデータ値をひとつだけ含む MULTIPOLYGON 型が生成されていることがわかります。
ST_Collect() を使って集約して、MULTIPOLYGON 型にすることもできます。
mysql> SELECT ST_AsText( CAST(ST_Collect(g) AS MULTIPOLYGON)) MP
-> FROM t2
-> WHERE ST_GeometryType(g)='POLYGON';
+---------------------------------------------------------------------------------+
| MP |
+---------------------------------------------------------------------------------+
| MULTIPOLYGON(((5 2,7 3,8 6,6 5,5 2)),((5 3,7 4,8 7,6 7,5 3),(6 4,6 5,7 5,6 4))) |
+---------------------------------------------------------------------------------+
1 row in set (0.00 sec)
POLYGON型からMULTILINESTRING型へ
これもイメージどおりの動作です。POLYGONとして記述したWKTの始点から終点までのLINESTRINGへと変換されます。中をくり抜く記述がされているPOLYGONは、外の要素、中の要素それぞれがの線で2要素の MULTILINESTRING になります。
mysql> SELECT id, ST_AsText(g) ORIGINAL,
-> ST_AsText( CAST(g AS MULTILINESTRING)) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='POLYGON';
+------+--------------------------------------------------+----------------------------------------------------------+
| id | ORIGINAL | CASTED |
+------+--------------------------------------------------+----------------------------------------------------------+
| 8 | POLYGON((5 2,7 3,8 6,6 5,5 2)) | MULTILINESTRING((5 2,7 3,8 6,6 5,5 2)) |
| 9 | POLYGON((5 3,7 4,8 7,6 7,5 3),(6 4,7 5,6 5,6 4)) | MULTILINESTRING((5 3,7 4,8 7,6 7,5 3),(6 4,6 5,7 5,6 4)) |
+------+--------------------------------------------------+----------------------------------------------------------+
POLYGON型からLINESTRING型へ
これは、変換可能な条件がやや厳しいです。中をくりぬく記述のあるPOLYGONは2要素以上から構成されるため、シングルの LINESTRINGにはへ感できません。
mysql> SELECT id, ST_AsText(g) ORIGINAL,
-> ST_AsText( CAST(g AS LINESTRING)) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='POLYGON';
ERROR 4032 (22S01): Invalid cast from POLYGON to LINESTRING.
これは、id=9 のほうの POLYGON が2要素に変換されるため、LINESTRING へと変換できずにエラーとなったものです。
内側をくり抜く指定のない id=8 のほうだけを指定すると、以下のとおり、変換できます。
mysql> SELECT id, ST_AsText(g) ORIGINAL,
-> ST_AsText( CAST(g AS LINESTRING)) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='POLYGON' AND id=8;
+------+--------------------------------+---------------------------------+
| id | ORIGINAL | CASTED |
+------+--------------------------------+---------------------------------+
| 8 | POLYGON((5 2,7 3,8 6,6 5,5 2)) | LINESTRING(5 2,7 3,8 6,6 5,5 2) |
+------+--------------------------------+---------------------------------+
LINESTRING型からMULTILINESTRING型へ
これは非常にシンプルです。単一の LINESTRING を、要素をひとつだけ持つ MULTILINESTRIN Gへ変換するのですから、何の心配事もありません。
mysql> SELECT id, ST_AsText(g) ORIGINAL,
-> ST_AsText( CAST(g AS MULTILINESTRING)) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='LINESTRING';
+------+-----------------------------+------------------------------------+
| id | ORIGINAL | CASTED |
+------+-----------------------------+------------------------------------+
| 5 | LINESTRING(1 2,2 3,3 4) | MULTILINESTRING((1 2,2 3,3 4)) |
| 6 | LINESTRING(1 3,1 4,2 5,1 3) | MULTILINESTRING((1 3,1 4,2 5,1 3)) |
| 7 | LINESTRING(3 4,4 2,5 4) | MULTILINESTRING((3 4,4 2,5 4)) |
+------+-----------------------------+------------------------------------+
もちろん、ST_Collect() を使って LINESTRING を集約したものを、MULTILINESTRIGN にすることもできます。
mysql> SELECT ST_AsText( CAST(ST_Collect(g) AS MULTILINESTRING)) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='LINESTRING';
+----------------------------------------------------------------------------------+
| CASTED |
+----------------------------------------------------------------------------------+
| MULTILINESTRING((1 2,2 3,3 4),(1 3,1 4,2 5,1 3),(3 4,4 2,5 4),(1 3,2 5,1 4,1 3)) |
+----------------------------------------------------------------------------------+
というかまぁこの例は、ST_Collect() 自体が集約結果を MULTI* 型にしてくれるので、明示的な CAST() なしでも同じ結果を得られるんですけどね。
mysql> SELECT ST_AsText( ST_Collect(g) ) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='LINESTRING';
+----------------------------------------------------------------------------------+
| CASTED |
+----------------------------------------------------------------------------------+
| MULTILINESTRING((1 2,2 3,3 4),(1 3,1 4,2 5,1 3),(3 4,4 2,5 4),(1 3,2 5,1 4,1 3)) |
+----------------------------------------------------------------------------------+
LINESTRING型からMULTIPOINT型へ
これもシンプルですね。LINESTRINGを構成する各点をバラバラにして、MULTIPOINTに入れてくれるものです。
mysql> SELECT id, ST_AsText(g) ORIGINAL,
-> ST_AsText( CAST(g AS MULTIPOINT)) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='LINESTRING';
+------+-----------------------------+-------------------------------------+
| id | ORIGINAL | CASTED |
+------+-----------------------------+-------------------------------------+
| 5 | LINESTRING(1 2,2 3,3 4) | MULTIPOINT((1 2),(2 3),(3 4)) |
| 6 | LINESTRING(1 3,1 4,2 5,1 3) | MULTIPOINT((1 3),(1 4),(2 5),(1 3)) |
| 7 | LINESTRING(3 4,4 2,5 4) | MULTIPOINT((3 4),(4 2),(5 4)) |
| 61 | LINESTRING(1 3,2 5,1 4,1 3) | MULTIPOINT((1 3),(2 5),(1 4),(1 3)) |
+------+-----------------------------+-------------------------------------+
LINESTRING型からPOLYGON型へ
これが結構くせ者です。
まず、よくあるLINESTRING（始点と終点が異なる点）の場合を見てみましょう。
mysql> SELECT id, ST_AsText(g) ORIGINAL,
-> ST_AsText( CAST(g AS POLYGON)) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='LINESTRING' AND id=5;
ERROR 4032 (22S01): Invalid cast from LINESTRING to POLYGON.
この LINESTRING から POLYGON への変換はできない（輪になっていないのだからPOLYGONにはできない）というエラーが出ています。
では次に id=6 として用意した、始点と終点が同じ点となっている LINESTRING を POLYGON に変換してみましょう。
mysql> SELECT id, ST_AsText(g) ORIGINAL,
-> ST_AsText( CAST(g AS POLYGON)) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='LINESTRING' AND id=6;
ERROR 4033 (22S04): Invalid cast from LINESTRING to POLYGON. A polygon ring is in the wrong direction.
先ほどとは異なるエラーが表示されました。これね、知らないとここで詰む話なのですが、POLYGONのリングには「向き」があるんです。私も聞きかじり(読みかじり)の知識なのですけど（たしか shapefile の仕様あたりに書いてあった気が）、POLYGONの線を辿ったときに、線の「右側」のほうが内側にならなければならないのです。なので最初のPOLYGONは右回り、最初に刳り抜く指定は左回り、、、のようになっている必要があります。たぶんこのルールに抵触したのでしょう。たぶん。
・・・・おや。
でも、id=6の LINESTRING を見てみると、右回りですね。。まぁとりあえず逆回りになるようにして、試してみましょう。
逆回りとなる LINESTRING データを1件追加してみます（id=61）。
INSERT INTO t2 VALUES (61, ST_GeomFromText('LINESTRING(1 3, 2 5, 1 4, 1 3)'));
mysql> SELECT id, ST_AsText(g) FROM t2 WHERE id=61;
+------+-----------------------------+
| id | ST_AsText(g) |
+------+-----------------------------+
| 61 | LINESTRING(1 3,2 5,1 4,1 3) |
+------+-----------------------------+
このLINESTRINGについて、POLYGONにCASTを試みます。
mysql> SELECT id, ST_AsText(g) ORIGINAL,
-> ST_AsText( CAST(g AS POLYGON)) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='LINESTRING' AND id=61;
+------+-----------------------------+----------------------------+
| id | ORIGINAL | CASTED |
+------+-----------------------------+----------------------------+
| 61 | LINESTRING(1 3,2 5,1 4,1 3) | POLYGON((1 3,2 5,1 4,1 3)) |
+------+-----------------------------+----------------------------+
お。POLYGONになった！！
とりあえず直感と異なるけど、今日の所は３つの仮説を立てて、詳細後日追試してみることにします。
仮説（１）「右側が内側」という私の記憶が誤り。実は逆。
仮説（２）SRID=0 の時、x軸y軸は入れ替えた状態(横軸がy)で評価するものである、そういう仕様なのである
仮説（３）MySQLのバグ（そもそも逆の動作をしている説／SRIDによって動作がおかしい説）
POINT型からMULTIPOINT型
これも、LINESTRING型から MULTILINESTRING型への変換と同様に、単体のものを1要素だけを持つMULTI*型に変換するもの、そしてグルーピングにより単体の集合を MULTIPOINTにまとめるものがあります。後者は ST_Collect 自体がMULTIPOINTに変更してくれるので実はCAST不要です。
mysql> SELECT id, ST_AsText(g) ORIGINAL,
-> ST_AsText( CAST(g AS MULTIPOINT)) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='POINT';
+------+------------+-------------------+
| id | ORIGINAL | CASTED |
+------+------------+-------------------+
| 1 | POINT(1 1) | MULTIPOINT((1 1)) |
| 2 | POINT(3 3) | MULTIPOINT((3 3)) |
| 3 | POINT(2 2) | MULTIPOINT((2 2)) |
| 4 | POINT(4 4) | MULTIPOINT((4 4)) |
+------+------------+-------------------+
mysql> SELECT ST_AsText( CAST(ST_Collect(g) AS MULTIPOINT)) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='POINT';
+-------------------------------------+
| CASTED |
+-------------------------------------+
| MULTIPOINT((1 1),(3 3),(2 2),(4 4)) |
+-------------------------------------+
MULTIPOINT型からPOINT型
MULTIPOINT型からの変換を確認する元データを格納するために、テーブル t21 を作成します。
CREATE TABLE t21 AS
SELECT CAST(ST_Collect(g) AS MULTIPOINT) g
FROM t2
WHERE ST_GeometryType(g)='POINT';
mysql> SELECT ST_AsText(g) FROM t21;
+-------------------------------------+
| ST_AsText(g) |
+-------------------------------------+
| MULTIPOINT((1 1),(3 3),(2 2),(4 4)) |
+-------------------------------------+
この MULTIPOINT型データからの CAST() を試しましょう。
mysql> SELECT ST_AsText( CAST(g AS POINT) ) P2LS
-> FROM t21;
ERROR 4032 (22S01): Invalid cast from MULTIPOINT to POINT.
複数要素を持つ MULTIPOINT 型は 単一要素であるPOINT型へ変換できなことがわかります。
同じMULTIPOINTでも、要素がひとつしかない場合は POINT型にキャスト可能です。以下の例は、FROMサブクエリ内で 元のデータ(t2テーブル)のPOINTを一旦 1要素のみを持つMULTIPOINT型にした後、POINT型へのCAST()を試したものです。
mysql> SELECT id, ST_AsText(CASTED) ORG, ST_AsText( CAST(CASTED AS POINT)) BTP
-> FROM (
-> SELECT id, g ,
-> CAST(g AS MULTIPOINT) CASTED
-> FROM t2
-> WHERE ST_GeometryType(g)='POINT'
-> ) t;
+------+-------------------+------------+
| id | ORG | BTP |
+------+-------------------+------------+
| 1 | MULTIPOINT((1 1)) | POINT(1 1) |
| 2 | MULTIPOINT((3 3)) | POINT(3 3) |
| 3 | MULTIPOINT((2 2)) | POINT(2 2) |
| 4 | MULTIPOINT((4 4)) | POINT(4 4) |
+------+-------------------+------------+
なお、要素数がひとつだけの場合に MULTI* → 単体型 への変換ができるので、予め ST_NumGeometries() 関数を使って要素数を確認し、要素数が1 のデータのみを変換対象にするなどの扱いをすることになろうかと思います。
mysql> SELECT ST_AsText(g), ST_NumGeometries(g) FROM t21;
+-------------------------------------+---------------------+
| ST_AsText(g) | ST_NumGeometries(g) |
+-------------------------------------+---------------------+
| MULTIPOINT((1 1),(3 3),(2 2),(4 4)) | 4 |
+-------------------------------------+---------------------+
MULTIPOINT型からLINESTRING型
まさにこの変換が欲しかった！という機能。
なんらかのルールに基づき点の集合を MULTIPOINTにまとめた後、それらをつないで線にするといった活用法が考えられます。
mysql> SELECT ST_AsText(g) ORG, ST_AsText( CAST(g AS LINESTRING) ) P2LS
-> FROM t21;
+-------------------------------------+-----------------------------+
| ORG | P2LS |
+-------------------------------------+-----------------------------+
| MULTIPOINT((1 1),(3 3),(2 2),(4 4)) | LINESTRING(1 1,3 3,2 2,4 4) |
+-------------------------------------+-----------------------------+
ただし、この、MULTIPOINTを作る際の順序指定がなかなか難しそうで、手元で軽く試した範囲では、順序指定するに至りませんでした。継続調査が必要です（できないはずはないので、私のSQL力の問題だとは思いますが）。例えば上記では、MULTIPOINTを作る時（このテーブルt21を作った時の話です）に ST_X による順序指定ができたら良いのにな、と考えています。あるいは上の実行結果で見るMULTIPOINT型を、順序を並べ替えながら LINESTRINGにCASTできたら、、という考え方もありそうですかね。
その他のキャスト
MULTILINESTRINGからの変換（MULTIPOLYGON/POLYGON/LINESTRINGへ変換可能）と、MULTIPOLYGONからの変換（POLYGON/MULTILINESTRINGへ変換可能）は今回は割愛します。このうち、
- MULTILINESTRING→LINESTRING は、例の、1要素のみの場合は簡単そうです
- MULTIPOLYGON → POLYGONも同様
- MULTIPOLYGON → MULTILINESTRING も、淡々と要素を分解して入れるだけかな
ということで、あとは 線の集合からPOLYGON系へ変換する場合ですね。これはやや複雑そうな気がします。集合内にある閉じた線が、ひとつの親POLYGONになりたいのか、刳り抜くPOLYGON要素なのかを判断する情報が、MULTILINESTRING内に含まれていないですからね。
まとめ
MySQL 8.0.24 での CAST() 関数のジオメトリ対応を試してみました。これまでは、「点や線やポリゴンのデータをMySQLに入れて」「判定しながら取り出す」という使い方止まりだったところ、この対応により、「まとめたり」「型を変換したり」ができるようになり、使えるシーンが少し広がったのではないでしょうか。
今後は、複数のPOLYGONを1個にまとめる（外周を辿ってくれるみたいな）とか、複数のLINESTRINGを一本のLINESTRINGにまとめる（これは CAST() を駆使するとできそうな気がする。あとで試そう）などの関数群も増えていくといいですね。
MySQLリファレンスマニュアルのSpatial関数のページを見た範囲では、今のところ MySQL 8.0.27 （通常どおりリリースされれば、2021年10月頃リリースされるもの）までの間にジオ関係の新たな関数が追加されるという情報はありませんが、少しずつ機能が増えて、様々な利用シーンに対応できるようになることを楽しみにしています。
今回の MySQL 8.0.24 は、ジオ的には非常に「大漁」でした！（なお、今回の動作確認は、その後2021年5月に緊急リリースされた MySQL 8.0.25を使用しました）
