MySQLで学ぶGIS入門第26回

MySQLの「ST_Intersects()」関数でジオメトリの「交わり」を判定してみよう

第26回の今回は、MySQLの「ST_Intersects()」関数を使ったジオメトリの交わり判定と、実際の地図データで市町村を通過する鉄道路線を抽出する応用例について解説します。

坂井恵 (さかいけい)

6:30

はじめに

前回は「ST_Within()」関数と「ST_Contains()」関数を使って、ジオメトリの包含関係を判定する方法を紹介しました。今回は、2つのジオメトリが「交わっているか」を判定する「ST_Intersects()」関数を紹介します。

記事の後半では、実際の活用例として「この路線はどの市を通るか」「この市を通る鉄道路線はどれか」といった、空間的な重なりを調べる事例を紹介します。

ST_Intersects()の基本

ST_Intersects(A, B)は、ジオメトリAとジオメトリBが少しでも重なっていれば1(TRUE)を、まったく重なっていなければ0(FALSE)を返す関数です。まずはデカルト座標系で、2本の線が交わるかを確認してみましょう。

mysql> SET @line1 = ST_GeomFromText('LINESTRING(0 1, 2 1)');
mysql> SET @line2 = ST_GeomFromText('LINESTRING(0 2, 2 0)');

mysql> SELECT ST_Intersects(@line1, @line2) majiwari;
+----------+
| majiwari |
+----------+
|        1 |
+----------+
1 row in set (0.000 sec)

2本の線が交わっているので、1(TRUE)が返ります。

この話の流れであれば、通常は「交わらない場合は 0(FALSE)になります」という説明が続くのですが、それでは面白くないので「1点で接する場合はどうなるでしょうか」というテーマを掲げたいと思います。下図のような関係です。

せっかくなので、ここでは、ST_Intersects()の挙動を知るために、まったく交わらないもの、1点で接するもの、交わるものをいっぺんに確認する例を紹介します。

mysql> SET @line11 = ST_GeomFromText('LINESTRING(0 1, 4 1)');
mysql> SET @line12 = ST_GeomFromText('LINESTRING(1 3, 1 2)');
mysql> SET @line13 = ST_GeomFromText('LINESTRING(2 3, 2 1)');
mysql> SET @line14 = ST_GeomFromText('LINESTRING(3 3, 3 0)');

mysql> SELECT ST_Intersects(@line11, @line12) 1and2,
    ->        ST_Intersects(@line11, @line13) 1and3,
    ->        ST_Intersects(@line11, @line14) 1and4;
+-------+-------+-------+
| 1and2 | 1and3 | 1and4 |
+-------+-------+-------+
|     0 |     1 |     1 |
+-------+-------+-------+
1 row in set (0.000 sec)

ここでは、長い横線を1本引いて(@line11)、その線に対して上から下に垂らすように届かない線(@line12)、ぴったり届いた線(@line13)、突き抜ける線(@line14)の3本の線を用意しました。横線と、それぞれの縦線の ST_Intersects()の結果を確認しています。

結果は、下記のようになりました。

届かない@line12 　　0(FALSE)
ぴったり接する@line13 1(TRUE)
突き抜ける@line14 　1(TRUE)

ST_Intersects()は、必ずしも（直感的な言葉で感じるところの）「交わる」ものだけでなく、1点でも接していればTRUEとなることが確認できました。

このように、関数の挙動を確認したいときに自分でさまざまな値を入れて試せる力は、SQLを学ぶ上でとても有効なので、ぜひ身につけていただきたいところです（GIS関数に限らずすべての関数について言えることです）。

点と線

ST_Intersects()は、線どうし以外のジオメトリの交わりも判定できます。点と線の関係を見てみましょう。

mysql> SET @line1  = ST_GeomFromText('LINESTRING(0 1, 4 1)');
mysql> SET @point1 = ST_GeomFromText('POINT(3 1)');
mysql> SET @point2 = ST_GeomFromText('POINT(3 2)');

mysql> SELECT ST_Intersects(@line1, @point1) lineandpoint1,
    ->        ST_Intersects(@line1, @point2) lineandpoint2;
+---------------+---------------+
| lineandpoint1 | lineandpoint2 |
+---------------+---------------+
|             1 |             0 |
+---------------+---------------+
1 row in set (0.000 sec)

下図のような関係にある点と線ですが、線上にあるpoint1はST_Intersects()の戻りが 1(True)、線と離れた点は 0(False)となっていることが分かります。

点と点は？

点どうしの交わりも判定できます。交わる点の例として@point1 どうしの比較、交わらない例として@point1と@point2を ST_Intersects()で比べた例です。

mysql> SELECT ST_Intersects(@point1, @point1) pandp1,
    ->        ST_Intersects(@point1, @point2) pandp2;
+--------+--------+
| pandp1 | pandp2 |
+--------+--------+
|      1 |      0 |
+--------+--------+
1 row in set (0.000 sec)

もっとも、点どうしが交わるというのは「点が一致する」という意味なので ST_Intersects()を使うまでもなく、以下のように一致判定だけで済みます。点どうしを ST_Intersects()で比較するケースはほぼないと言って良いでしょう。

mysql> SELECT @point1=@point1 pandp1,
    ->        @point1=@point2 pandp2;
+--------+--------+
| pandp1 | pandp2 |
+--------+--------+
|      1 |      0 |
+--------+--------+
1 row in set (0.000 sec)

線とポリゴン

線とポリゴンでも交わりの比較ができます。ここには少々面白い挙動があるかもしれません。自分でいろいろ試せる力をつける例として、あらかじめいくつかのLINESTRINGをテーブルに入れておき、それとPOLYGONとの比較をする方法でアプローチしてみます。

-- 対照とするLINESTRINGをテーブルに登録

mysql> CREATE TABLE g26 (
    ->     id    VARCHAR(20) PRIMARY KEY,
    ->     g     GEOMETRY NOT NULL
    -> );

mysql> INSERT INTO g26 VALUES ('line1', ST_GeomFromText('LINESTRING(-1 2, 5 2)'));
mysql> INSERT INTO g26 VALUES ('line2', ST_GeomFromText('LINESTRING(1 1, 3 3)'));
mysql> INSERT INTO g26 VALUES ('line3', ST_GeomFromText('LINESTRING(6 1, 7 2)'));
mysql> INSERT INTO g26 VALUES ('line4', ST_GeomFromText('LINESTRING(2 1, 5 1)'));

-- 基準ポリゴン
SET @polybase = ST_GeomFromText('POLYGON((0 0, 4 0, 4 4, 0 4, 0 0))');

4本の線のそれぞれが基準ポリゴンに対してどのような「交わり」判定をされるのかを確認してみましょう。

-- 基準POLYGONとLINESTRINGの交わり判定
mysql> SELECT id, ST_Intersects(@polybase, g) FROM g26;
+-------+-----------------------------+
| id    | ST_Intersects(@polybase, g) |
+-------+-----------------------------+
| line1 |                           1 |
| line2 |                           1 |
| line3 |                           0 |
| line4 |                           1 |
+-------+-----------------------------+
4 rows in set (0.000 sec)

結果は上記の通りです。ポリゴンを完全に突き抜けるline1、ポリゴンの境界線を1回またぐline4が「交わる(1:True)」と判定されるのは予想通りですね。ポリゴンのまったく外側に存在するline3が「0(False)」と判定されるのも当然のことです。

面白いのが、line2とポリゴンの関係です。line2はポリゴンの中にすっぽり収まる線で、直感的には「ポリゴンと交わっていない」と感じる人も多いでしょう。しかし、実際には「1(True)」が返ってきて「交わっている」と判定されていることが分かります。これは、ポリゴンとは境界線（取り囲んでいる線の部分）ではなく、囲まれた範囲である中身そのものだということを示しています。

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

ST_Intersects()の動作を理解したところで、実際の地図データで試してみましょう。第19回から21回で登録した地方自治体境界データと鉄道データを使います。特定の自治体ポリゴンと路線のLINESTRINGとの交わりを判定することで、その自治体内を通過する路線を判定するという試みです。

では、まず我孫子市を通過する鉄道路線を抽出してみましょう。

mysql> SELECT DISTINCT r.n02_003 AS Line, r.n02_004 AS Company
    ->   FROM railroad r, chiba_city c
    ->  WHERE c.n03_004 = '我孫子市'
    ->    AND ST_Intersects(r.SHAPE, c.SHAPE);
+-----------+-----------------------+
| Line      | Company               |
+-----------+-----------------------+
| 成田線    | 東日本旅客鉄道        |
| 常磐線    | 東日本旅客鉄道        |
+-----------+-----------------------+
2 rows in set (0.011 sec)

我孫子市のポリゴンと鉄道路線のLINESTRINGを ST_Intersects()で判定し、True(1)だったものを表示しています。我孫子市には常磐線と成田線の2路線が走っているということで、正しい結果を得ることができました。

ここでST_Within()を使ってしまうと「路線全体が市内に収まっている」ものだけが抽出されてしまい、市をまたいで走る路線は該当しなくなります。「通っている」という判定にはST_Intersects()が適切です。

東葛地域を通る路線を調べよう

次は複数の市を条件にして、さらに面白い分析をしてみましょう。東葛地域の6市（我孫子市、柏市、流山市、松戸市、野田市、鎌ケ谷市）を通る路線で、2市以上を通過するものを抽出します。

mysql> SELECT r.n02_003 AS Line,
    ->        r.n02_004 AS Company,
    ->        GROUP_CONCAT(DISTINCT c.n03_004 ORDER BY c.n03_004) AS Cities
    ->   FROM railroad r, chiba_city c
    ->  WHERE c.n03_004 IN ('我孫子市', '柏市', '流山市', '松戸市', '野田市', '鎌ケ谷市')
    ->    AND ST_Intersects(r.SHAPE, c.SHAPE)
    ->  GROUP BY r.n02_003, r.n02_004
    -> HAVING COUNT(DISTINCT c.n03_004) >= 2
    ->  ORDER BY COUNT(DISTINCT c.n03_004) DESC, Line;
+------------+------------------+------------------------------------+
| Line       | Company          | Cities                             |
+------------+------------------+------------------------------------+
| 野田線     | 東武鉄道         | 松戸市,柏市,流山市,野田市,鎌ケ谷市 |
| 常磐線     | 東日本旅客鉄道   | 我孫子市,松戸市,柏市,流山市        |
| 北総線     | 北総鉄道         | 松戸市,柏市,鎌ケ谷市               |
| 成田空港線 | 京成電鉄         | 松戸市,柏市,鎌ケ谷市               |
| 常磐新線   | 首都圏新都市鉄道 | 柏市,流山市                        |
| 新京成線   | 新京成電鉄       | 松戸市,鎌ケ谷市                    |
| 武蔵野線   | 東日本旅客鉄道   | 松戸市,流山市                      |
| 流山線     | 流鉄             | 松戸市,流山市                      |
+------------+------------------+------------------------------------+
8 rows in set (0.295 sec)

野田線（東武アーバンパークライン）が我孫子市以外の5市を通過してトップになりました。実際に各市の自治体境界データと、そこを通る野田線を描画してみると、確かにきれいに突き抜けていますね。

また、このあたりに多少の土地勘のある人にとっては「あれ? 常磐線って流山市も通っているの?」と感じるかもしれません（私もそうでした）。確認してみたところ、南柏駅－北小金駅間で流山市がほんの少し常磐線を跨ぐように張り出しているのでした。データを眺めていると意外な発見につながるものです。

発見と言えば、普通に路線を利用している範囲ではほぼ目にすることがない「常磐新線」という名称も興味を引きますね（つくばエクスプレスのことです）。

このクエリでは、まず市区町村ポリゴンを特定し、それと交わる路線をST_Intersects()で取り出してから路線＋市でグルーピングしています。次にその中から市が2つ以上であるものをHAVING句で絞り込み、出力用としてGROUP_CONCAT()を用いて市の名前をコンマ区切りで羅列するようにしました。GIS関数の紹介というよりもSQLそのものの活用事例のような例になりましたが、このようにSQLは工夫次第で色々なことができるので楽しいですね。