MySQL 8.0.24 では ST_Collect() 関数が実装されました。ジオメトリのデータを collect してくれそうな、見たままの機能を持った関数です。
とりあえずどんな動きをするのか試してみたので、紹介します。なお、今回は 5/11に緊急リリースされた MySQL 8.0.25 にて動作確認をしています。
データ準備
id、カテゴリ番号、そしてジオメトリの３つのカラムを持つテーブル t1 を作成し、データをつっこみます。
CREATE TABLE t1 (id integer, cat integer, g GEOMETRY); INSERT INTO t1 VALUES (1, 1, ST_GeomFromText('POINT(1 1)')); INSERT INTO t1 VALUES (2, 2, ST_GeomFromText('POINT(2 1)')); INSERT INTO t1 VALUES (3, 1, ST_GeomFromText('POINT(3 1)')); INSERT INTO t1 VALUES (4, 1, ST_GeomFromText('POINT(3 2)')); INSERT INTO t1 VALUES (5, 2, ST_GeomFromText('POINT(2 1)')); INSERT INTO t1 VALUES (6, 3, ST_GeomFromText('POINT(3 3)'));
内容確認。内部的にはジオメトリのデータはバイナリになっているので、人間が目視できる形に「変換」するために、ST_AsText() を使うのでしたね。
mysql> SELECT id, cat, ST_AsText(g) FROM t1 ORDER BY id; +------+------+--------------+ | id | cat | ST_AsText(g) | +------+------+--------------+ | 1 | 1 | POINT(1 1) | | 2 | 2 | POINT(2 1) | | 3 | 1 | POINT(3 1) | | 4 | 1 | POINT(3 2) | | 5 | 2 | POINT(2 1) | | 6 | 3 | POINT(3 3) | +------+------+--------------+
単純に全部まとめる
いわゆる SELECT SUM(col1) FROM t1; みたいな、GROUP BY を指定しないグルーピングです。
mysql> SELECT ST_AsText( ST_Collect(g) ) FROM t1; +-------------------------------------------------+ | ST_AsText( ST_Collect(g) ) | +-------------------------------------------------+ | MULTIPOINT((1 1),(2 1),(3 1),(3 2),(2 1),(3 3)) | +-------------------------------------------------+
6件すべての POINT が、ひとつの MULTIPOINT に集約されました。
ST_Collect() の集約では、DISTINCT をかけることもできます。重複除去です。
mysql> SELECT ST_AsText( ST_Collect(DISTINCT g) ) FROM t1; +-------------------------------------------+ | ST_AsText( ST_Collect(DISTINCT g) ) | +-------------------------------------------+ | MULTIPOINT((1 1),(2 1),(3 1),(3 2),(3 3)) | +-------------------------------------------+
2件あった (2 1) の重複が排除された結果が得られました。
普通にグルーピング
今度は、GROUP BY を明記する、普通のグルーピングを試してみます。
mysql> SELECT cat, ST_AsText( ST_Collect(g) ) FROM t1 GROUP BY cat; +------+-------------------------------+ | cat | ST_AsText( ST_Collect(g) ) | +------+-------------------------------+ | 1 | MULTIPOINT((1 1),(3 1),(3 2)) | | 2 | MULTIPOINT((2 1),(2 1)) | | 3 | MULTIPOINT((3 3)) | +------+-------------------------------+
カテゴリ(cat)ごとに、グルーピングしてみました。カテゴリが等しいPOINT値ごとに集約されていることがわかります。 ST_Collect() は集約を行う関数ですから、結果として点がひとつだけになった場合でも、集合型である MULTIPOINT 型を返すのですね（cat=3）。
POINT 型以外も混在する場合
ここで、新たに LINESTRING 型の値を持つ行を追加してみます。
INSERT INTO t1 VALUES (7, 3, ST_GeomFromText('LINESTRING(3 2, 1 0, 4 3)'));
この行が加わった状態で、先ほどと同様に、カテゴリ(cat)ごとに ST_Collect() を使ってグルーピングしてみると、、、
mysql> SELECT cat, ST_AsText( ST_Collect(g) ) FROM t1 GROUP BY cat; +------+--------------------------------------------------------+ | cat | ST_AsText( ST_Collect(g) ) | +------+--------------------------------------------------------+ | 1 | MULTIPOINT((1 1),(3 1),(3 2)) | | 2 | MULTIPOINT((2 1),(2 1)) | | 3 | GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(3 2,1 0,4 3)) | +------+--------------------------------------------------------+
カテゴリ3のものは、POINT と LINESTRING が混在した GEOMETRYCOLLECTION型になっていることがわかります。
長くなるので実行例はひとつだけにしますが、すべてが LINESTRING型の時には MULTILINESTRINGに、すべてが POLYGON型の場合は MULTIPOLYGON型に、混在の時には GEOMETRYCOLLECTION型になります。
INSERT INTO t1 VALUES (8, 4, ST_GeomFromText('LINESTRING(1 2, 3 4, 5 6)')); INSERT INTO t1 VALUES (9, 4, ST_GeomFromText('LINESTRING(3 5, 6 8)'));
mysql> SELECT cat, ST_AsText( ST_Collect(g) ) FROM t1 GROUP BY cat; +------+--------------------------------------------------------+ | cat | ST_AsText( ST_Collect(g) ) | +------+--------------------------------------------------------+ | 1 | MULTIPOINT((1 1),(3 1),(3 2)) | | 2 | MULTIPOINT((2 1),(2 1)) | | 3 | GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(3 2,1 0,4 3)) | | 4 | MULTILINESTRING((1 2,3 4,5 6),(3 5,6 8)) | +------+--------------------------------------------------------+
Window関数でも使える ST_Collect()
ST_Collect() は集約関数のひとつですから、SUM() などと同じように Window関数でも使うことができます。
ST_AsText() があるぶんだけ少し複雑に見えますが、本質は Window関数の 「集約関数(カラム) OVER (PARTITION BY カラム) であり、この結果を我々人間にも分かるように変換してもらうために ST_AsText() をカマしているという形です。
mysql> SELECT id, cat, ST_AsText(g), -> ST_AsText( ST_Collect(g) OVER (PARTITION BY cat) ) gw -> FROM t1 -> ORDER BY id; +------+------+-------------------------+--------------------------------------------------------+ | id | cat | ST_AsText(g) | gw | +------+------+-------------------------+--------------------------------------------------------+ | 1 | 1 | POINT(1 1) | MULTIPOINT((1 1),(3 1),(3 2)) | | 2 | 2 | POINT(2 1) | MULTIPOINT((2 1),(2 1)) | | 3 | 1 | POINT(3 1) | MULTIPOINT((1 1),(3 1),(3 2)) | | 4 | 1 | POINT(3 2) | MULTIPOINT((1 1),(3 1),(3 2)) | | 5 | 2 | POINT(2 1) | MULTIPOINT((2 1),(2 1)) | | 6 | 3 | POINT(3 3) | GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(3 2,1 0,4 3)) | | 7 | 3 | LINESTRING(3 2,1 0,4 3) | GEOMETRYCOLLECTION(POINT(3 3),LINESTRING(3 2,1 0,4 3)) | | 8 | 4 | LINESTRING(1 2,3 4,5 6) | MULTILINESTRING((1 2,3 4,5 6),(3 5,6 8)) | | 9 | 4 | LINESTRING(3 5,6 8) | MULTILINESTRING((1 2,3 4,5 6),(3 5,6 8)) | +------+------+-------------------------+--------------------------------------------------------+
ということで、「データを格納し、検索して取り出す」というジオメトリデータ（空間データ、spatialデータ、GISデータ）の基本的な取り扱いから一歩進んで、データベースへの格納済みデータを MULTI* 型へとまとめることができるようになりました。
相変わらず応用事例や実際の案件に関わっていない私ですので、正直なところ具体的な用途は今ひとつピンと来ていないのですが、ST_Collect() により行えるデータ操作の幅が一気に広がったような気がします。実際の案件で使ってみた方はぜひブログとかでお話を聞かせてください！ あるいはお手伝いさせてください(笑)
