この日記は、RDBMS-GIS(MySQL,PostgreSQLなど) Advent Calendar 2020 の 17日目のエントリーです。
はじめに
MySQLで取り扱うことができる空間情報の型には、点、線、ポリゴン があります。それぞれ にそれらの集合を扱える型が存在してるので、都合６つとなります。これら相互の変換について考えてみたいと思います。
なお、緑色線は本日記および今後の日記にて紹介を予定しているものです（スマートなやり方ではなく力ワザ(ちからわざ)のものも含む)。赤い点線は、今のところ試したことがないもの。
今回使うデータ
今回のお試しの範囲では、テーブルにデータを入れておく必要もないのですが、実際のSQL利用イメージに近づけるためにこのようにしています。以下のテーブルおよびデータを登録しておきます。
mysql> CREATE TABLE s1 (id integer, sp GEOMETRY); mysql> INSERT INTO s1 VALUES (1, ST_GeomFromText('MULTIPOINT(1 1, 2 2, 3 2, 4 5, 6 6)')); mysql> INSERT INTO s1 VALUES (2, ST_GeomFromText('LINESTRING(1 0, 2 3, 4 8, 3 3, 2 7)'));
こんな感じで格納されています。
mysql> SELECT id, ST_AsText(sp) FROM s1; +------+-------------------------------------------+ | id | ST_AsText(sp) | +------+-------------------------------------------+ | 1 | MULTIPOINT((1 1),(2 2),(3 2),(4 5),(6 6)) | | 2 | LINESTRING(1 0,2 3,4 8,3 3,2 7) | +------+-------------------------------------------+
今回の目標
- １つの MULTIPOINT として格納されているものを、それぞれの点にバラして POINT として得ます。
- 1つの LINESTRING として格納されているものを、点にバラして POINT として得ます。
LINESTRING から点を抽出（点に分解）
まず先に、専用の関数があってわかりやすい LINESTRING から考えてみます。
線を構成するポイントの数を得る ST_NumPoints() 、そして、N番目のポイントを得る ST_PointN() の動作を確認してみましょう。
mysql> SELECT id, ST_AsText(sp), ST_NumPoints(sp) n, ST_AsText(ST_PointN(sp, 2)) p -> FROM s1 -> WHERE ID=2; +------+---------------------------------+------+------------+ | id | ST_AsText(sp) | n | p | +------+---------------------------------+------+------------+ | 2 | LINESTRING(1 0,2 3,4 8,3 3,2 7) | 5 | POINT(2 3) | +------+---------------------------------+------+------------+ 1 row in set (0.00 sec)
5個のポイントで構成され、2番目は POINT(2 3) であるという結果が得られました。
次に、このひとつの LINESTRING データをPOINTにバラしたい、つまり5件のデータにしたいということなので、このまま5件に分解してみます。CTEがあって良かった！ MySQL 8.0 バンザイ！
WITH RECURSIVE seq(n) AS (SELECT 1 UNION SELECT n+1 FROM seq WHERE n<(SELECT ST_NumPoints(sp) FROM s1 WHERE id=2)) SELECT id, seq.n, ST_AsText(sp) FROM s1 JOIN seq ON (s1.id=2); +------+------+---------------------------------+ | id | n | ST_AsText(sp) | +------+------+---------------------------------+ | 2 | 1 | LINESTRING(1 0,2 3,4 8,3 3,2 7) | | 2 | 2 | LINESTRING(1 0,2 3,4 8,3 3,2 7) | | 2 | 3 | LINESTRING(1 0,2 3,4 8,3 3,2 7) | | 2 | 4 | LINESTRING(1 0,2 3,4 8,3 3,2 7) | | 2 | 5 | LINESTRING(1 0,2 3,4 8,3 3,2 7) | +------+------+---------------------------------+ 5 rows in set (0.00 sec)
いい感じです。
ついでに、何番目の要素を取得したいかの数字も一緒に出力するようにしてみました。
この数字を使えば、最初に確認した ST_PointN() を使って、ほしいPOINTを得られますね。
WITH RECURSIVE seq(n) AS (SELECT 1 UNION SELECT n+1 FROM seq WHERE n<(SELECT ST_NumPoints(sp) FROM s1 WHERE id=2)) SELECT id, seq.n, ST_AsText(ST_PointN(sp, seq.n)) p FROM s1 JOIN seq ON (s1.id=2) +------+------+------------+ | id | n | p | +------+------+------------+ | 2 | 1 | POINT(1 0) | | 2 | 2 | POINT(2 3) | | 2 | 3 | POINT(4 8) | | 2 | 4 | POINT(3 3) | | 2 | 5 | POINT(2 7) | +------+------+------------+ 5 rows in set (0.00 sec)
はい。これで 1本の LINESTRING を構成する要素を POINT として得ることができました。いわゆるタテモチ・ヨコモチ変換の空間情報版みたいですね。
MULTIPOINT を POINT に
こちら、LINESTRINGのような関数が存在していないので困りました。が、GEOMETRY として考えることで、LINESTRINGと同様のことができると分かったので、紹介します。
対象データは、冒頭で登録した MULTIPOINT のデータです。
mysql> SELECT id, ST_AsText(sp) sp FROM s1 WHERE id=1; +------+-------------------------------------------+ | id | sp | +------+-------------------------------------------+ | 1 | MULTIPOINT((1 1),(2 2),(3 2),(4 5),(6 6)) | +------+-------------------------------------------+
ここでは、ST_NumGeometries() と ST_GeometryN() を使用します。
mysql> WITH RECURSIVE seq(n) AS (SELECT 1 UNION SELECT n+1 FROM seq WHERE n<(SELECT ST_NumGeometries(sp) FROM s1 WHERE id=1)) -> SELECT id, seq.n, ST_AsText(ST_GeometryN(sp, seq.n)) p FROM s1 JOIN seq ON (s1.id=1) -> ; +------+------+------------+ | id | n | p | +------+------+------------+ | 1 | 1 | POINT(1 1) | | 1 | 2 | POINT(2 2) | | 1 | 3 | POINT(3 2) | | 1 | 4 | POINT(4 5) | | 1 | 5 | POINT(6 6) | +------+------+------------+
はい。欲しい結果が得られました。
なお、先ほどの LINESTRING に対して ST_NumGeometries()を使おうとすると、うまくいきません。
ST_NumGeometries() は、 LINESTRING に対しては NULLを返すようです。
mysql> SELECT id,ST_NumGeometries(sp) FROM s1; +------+----------------------+ | id | ST_NumGeometries(sp) | +------+----------------------+ | 1 | 5 | | 2 | NULL | +------+----------------------+
おわりに
とりあえず LINESTRING 型および MULTIPOINT 型から、それらを構成する POINT のデータ群へと変換することが実現できました。
パフォーマンス的なことは検証していませんし、抽出条件に相当するものが2か所に出ている点はもう少しきれいに書けるのではないかと思いますが、まずは最悪の方法だとしても、方法がないわけではないことを示せたことは一歩前進かなと思います。
これを読んで、「なんてひどいSQLだ」と感じた方は（というか感じてほしいです）、ぜひスマートな書き方に挑戦していただき、ブログ等で紹介してほしいです。
これ以外の変換については、来週のエントリで紹介予定です。カワザです(笑)。
