MySQLの「ST_Union()」でポリゴンを合体させてみよう

はじめに

前回はST_Area()関数を使って、POLYGONの面積を計算する方法を紹介しました。今回は、2つのPolygonを1つに合体させる「ST_Union()」関数を紹介します。

複数の市町村をまとめて「地域」として扱ったり、エリアを拡張したりと、様々な場面で活用できる機能です。

ST_Union()の基本

ST_Union()は、2つのジオメトリを与えられると、それらを合体させ、1つのジオメトリを返す関数です。まずはシンプルな例として、互いに重なる2つの四角形を合体させてみましょう。

mysql> SET @g1 = ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))');
mysql> SET @g2 = ST_GeomFromText('POLYGON((0.5 0.5, 0.5 1.2, 1.5 1.2, 1.5 0.5, 0.5 0.5))');

mysql> SELECT ST_AsText(ST_Union(@g1, @g2));
+------------------------------------------------------------------+
| ST_AsText(ST_Union(@g1, @g2))                                    |
+------------------------------------------------------------------+
| POLYGON((0.5 1,0 1,0 0,1 0,1 0.5,1.5 0.5,1.5 1.2,0.5 1.2,0.5 1)) |
+------------------------------------------------------------------+
1 row in set (0.000 sec)

2つの四角形の交点となる場所を的確に判断して、1つのポリゴンへと合体されました。DBeaverで可視化すると、合体前と合体後の違いがよく分かります。

余力のある人は実際にST_Union() の結果の各座標が確かに新しい八角形を表していることを確認してみましょう。もともとの各図形(@g1, @g2)には存在していなかった交点座標が登場していることを確認できます。

実際の地図データで合体してみよう

シンプルな動作説明のためにデカルト座標のデータを例にして説明しましたが、ST_Union()関数はもちろん、地理座標系のポリゴンも同様に使用できます。

千葉県の行政区域データを使って、我孫子市のポリゴンに四角い領域を合体させてみましょう。

mysql> SELECT ST_Union(
    ->    SHAPE, 
    ->    ST_GeomFromText('POLYGON((
    ->      35.88 140.05,
    ->      35.88 140.06,
    ->      35.9  140.06,
    ->      35.9  140.05,
    ->      35.88 140.05))', 6668)) AS g
    ->   FROM chiba_city 
    ->  WHERE OGR_FID = 725;

WHERE句に指定している「OGR_FID=725」は我孫子市のメイン部分のポリゴンです(我孫子市は飛び地があり2つのポリゴンから形成されているため、あらかじめ検索してその大きいほうのIDを把握しておきました)。この我孫子市の形に、小さな四角形を合体させています。

我孫子市の北東の角あたりに、ちょこんと四角い出っ張りが追加されているのが分かりますね。このように、地理座標系データ(SRIDを持っているもの)でもST_Union()関数を使うことができます。むしろこちらのほうが多く使われることでしょう。

複数の市を合体させたい...けどエラー？

さて、ここで欲が出てきます。複数の市町村を合体させて「東葛地域」のようなまとまりを作りたくなりますよね。そこで、このようなクエリを書いてみました。

mysql> SELECT ST_Union(SHAPE) g
    ->   FROM chiba_city
    ->  WHERE n03_004 IN ('我孫子市', '柏市', '流山市', '松戸市');
ERROR 1582 (42000): Incorrect parameter count in the call to native function 'ST_Union'

東葛地域の中から4市を選んで大きなPolygonを得たかったのですが、エラーになってしまいました。マニュアルを確認すると、MySQLの ST_Union は「ST_Union(g1, g2)」の形式でのみ使用可能となっているようです。つまり、先ほど試したような集約関数としての動作はしない関数であるということです。

実は、PostgreSQLのGIS機能extensionであるPostGISでは ST_Union()を集約関数として使用できます。そのため、PostGISの経験があったりWebで検した実行例を見たりすると同様の動作がMySQLでもできると期待してしまいますが、残念ながらMySQLでは2引数に対する関数であることを、覚えておきましょう。

なお、他のRDBMSのマニュアルをいくつか軽く見て回ったところ、MySQLと同様に2引数に対する関数として実装されているものが多いようです。とはいえ「ポリゴンの合成」については、集約関数として使えれば便利なので、実装に期待したいところです。

3つ以上のポリゴンの合成：サブクエリを使った回避策

それでも、実務上は3つ以上のポリゴンを合体させたいシーンは多く発生します。ST_Union(a, ST_Union(b, ST_Union(c, ST_Union(d, ST_Union(e, ST_Union(f,g)))))) のように並べれば実現はできますが、「ソレジャナイ」と感じる人のほうが多いでしょう。

では、3つ以上のポリゴンを合成する方法を一緒に模索していきましょう。まず、おさらいとして、サブクエリでジオメトリデータを取得する方法を使い、2つのポリゴンを合成する実行例です。

mysql> SELECT ST_AsText(
    ->   ST_Union(
    ->     (SELECT SHAPE FROM chiba_city WHERE n03_004 = '松戸市'),
    ->     (SELECT SHAPE FROM chiba_city WHERE n03_004 = '柏市')
    ->   )) AS g \G
*************************** 1. row ***************************
g: POLYGON((35.834871505 139.941485811,35.835086775 139.941241362,...
1 row in set (0.003 sec)

これで2つのポリゴンから1つのポリゴンが得られます。これを改めてもう1組から得たポリゴンと一緒に ST_Union()に与えれば、4ポリゴンからの合成が実現できます。でも先ほどの「コレジャナイ」に近い考え方で、あまりスマートとは言えません。

-- 4つの市を合体させる例
mysql> SELECT ST_Union(
    ->   ST_Union(
    ->     (SELECT SHAPE FROM chiba_city WHERE n03_004 = '松戸市'),
    ->     (SELECT SHAPE FROM chiba_city WHERE n03_004 = '柏市')
    ->   ),
    ->   ST_Union(
    ->     (SELECT SHAPE FROM chiba_city WHERE n03_004 = '流山市'),
    ->     (SELECT SHAPE FROM chiba_city WHERE n03_004 = '野田市')
    ->   )
    -> ) AS g;

勘の鋭い方は、このクエリの例に我孫子市を使用していないことが気になったかもしれません。あれほど真っ先に我孫子市のデータを使う筆者が、4つも市を使う例の中に入れない違和感に気づいた方は、とても鋭いです。

実は我孫子市には問題があります。いえ、もう少し正確に言いましょう。先ほどのクエリで我孫子市を使用するとエラーになるのです。

クエリをもう一度よく見てください。各サブクエリ要素は、ただ1つのPolygonを返すことが期待されています。「スカラ値を返すクエリ」という言い方をされます。前述したとおり我孫子市には飛び地があり、2つのPolygonから構成されています。つまりサブクエリが2つの行を返すのです。そのため、先ほどのクエリではエラーになってしまう、という問題が発生するのです。GIS機能特有の話というわけではなく、SQL一般のお話ですね。

単一結果を返すことがあらかじめ分かっている地区にしか使えないのだと、安心して使えるクエリとは言えません。他の方法を考えましょう。

再帰CTEを使ったPolygon合体

複数行あるポリゴンも含めて、任意の数の市町村を1つに合体させたい。そんな要望に応える方法はないでしょうか。AIに相談したところ、再帰CTE (Common Table Expression) を使った方法を提案してくれました。

WITH RECURSIVE
  cities AS (
    SELECT OGR_FID, SHAPE, ROW_NUMBER() OVER (ORDER BY OGR_FID) AS rn
      FROM chiba_city
     WHERE n03_004 IN ('我孫子市', '柏市', '流山市', '松戸市', '野田市')
  ),
  merged AS (
    SELECT rn, SHAPE AS geom FROM cities WHERE rn = 1
   UNION ALL
    SELECT c.rn, ST_Union(m.geom, c.SHAPE)
      FROM merged m
      JOIN cities c ON c.rn = m.rn + 1
  )
SELECT geom AS g FROM merged ORDER BY rn DESC LIMIT 1;

このクエリの仕組みを簡単に説明します。

CTEでまず、対象となるすべての行に連番を振り(rn)、citiesという名前の仮想的名テーブルにします。次に番号の付いたcitiesを使って、mergedという仮想的名テーブルとして順次 ST_Union()を重ねていく、という流れです。

最後に、mergedに蓄積されてきたうち、最大のrnを持つレコードを取り出します。これがすべてを合体し終わった結果としてのポリゴンになります。

まさに「コレジャナイ」と紹介した手法そのものですが、それらを自動で行ってくれる点が優れています。

我孫子市の複数ポリゴンも含めて、5市すべてが1つのポリゴンにまとまりました。

この最後のクエリはAIに書いてもらいました。昨今、「AIがSQLを書いてくれるから、人類はもうSQLを学ばなくて良い」という論調を耳にすることも増えてきました。今回紹介した再帰CTEのようなクエリを、私はAIが出してくれたような10秒やそこらで書き上げることはできません。確実にAIの勝利です。

一方で、AIが提案したクエリが本当に正しいのか、本当にこのクエリで業務を進めて良いのかを最終的に判断する力は人間の側が持っている必要があると、筆者は考えています。「AIがやりました。ボク悪くないです」で済めば良いですが、多くのお仕事の場では通用しない主張でしょう。

「クエリを書くチカラから、クエリを読むチカラへ」というのが、もしかしたらこれからのSQL学習のトレンドと言えるのかもしれません。

おわりに

今回はST_Union()関数を使って、2つのPolygonを合体させる方法を紹介しました。3つ以上の場合は再帰CTEやサブクエリを活用することで実現できます。SQLの奥深さに助けられたと言えるでしょう。

なお、今回の地図ポリゴンデータは「国土交通省国土数値情報ダウンロードサイト」からのダウンロード、地図表示はOpenStreetMapの地図画像を使用させていただきました。