MySQLの「ST_Within()」「ST_Contains()」関数でジオメトリの包含関係を判定してみよう
第25回の今回は、MySQLの「ST_Within()」関数と「ST_Contains()」関数を使ったジオメトリの包含判定と、市町村内の学校や駅の抽出など、実データへの応用について解説します。
6:30
はじめに
前回は「ST_Length()」関数を使って、LineStringの長さを計測する方法を紹介しました。今回は、あるジオメトリが別のジオメトリの中に含まれているかを判定する「ST_Within()」関数と、その逆の視点で判定を行う「ST_Contains()」関数を紹介します。
「この学校はどの市にあるか」「この市内にある駅はどれか」といった、位置関係の判定に欠かせない関数です。
ST_Within()の基本
ST_Within(A, B)は、ジオメトリAがジオメトリBの中に完全に含まれていれば 1(TRUE)を、そうでなければ 0(FALSE)を返す関数です。まずはデカルト座標でシンプルな例を見てみましょう。
mysql> SET @point = ST_GeomFromText('POINT(0.4 0.7)');
mysql> SET @polygon = ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))');
mysql> SELECT ST_Within(@point, @polygon);
+-----------------------------+
| ST_Within(@point, @polygon) |
+-----------------------------+
| 1 |
+-----------------------------+
1 row in set (0.000 sec)点(0.4, 0.7)は(0,0)-(1,1)の正方形の中に含まれているので、1(TRUE)が返ります。
ポリゴンの外にある点でFALSEが返ることも確認しておきましょう。
mysql> SET @point = ST_GeomFromText('POINT(1.2 0.8)');
mysql> SET @polygon = ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))');
mysql> SELECT ST_Within(@point, @polygon);
+-----------------------------+
| ST_Within(@point, @polygon) |
+-----------------------------+
| 0 |
+-----------------------------+
1 row in set (0.000 sec)点(1.2, 0.8)はポリゴンの外なので、正しく 0(FALSE)を得ることができました。
境界上の点はどうなる？
ここで気になるのが、ポリゴンの境界線上にある点の扱いです。
mysql> SET @point_edge = ST_GeomFromText('POINT(0 0.5)');
mysql> SET @polygon = ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))');
mysql> SELECT ST_Within(@point_edge, @polygon);
+----------------------------------+
| ST_Within(@point_edge, @polygon) |
+----------------------------------+
| 0 |
+----------------------------------+
1 row in set (0.000 sec)0(FALSE)が返ってきました。境界上の点は「中に含まれる」とは見なされないのが ST_Within()の仕様です。「完全に内部にある」ことを判定する関数なのだと覚えておくと良いでしょう。
ST_Contains()はST_Within()の逆
ST_Contains(A, B)は、ジオメトリAがジオメトリBを含んでいるかを判定します。ST_Within()とは視点が逆になるだけで、結果は同じです。先ほどST_Within()で試した各点について、ST_Within()、ST_Contains()の両関数での結果を比べてみましょう。
mysql> SET @point_in = ST_GeomFromText('POINT(0.4 0.7)');
mysql> SET @point_out = ST_GeomFromText('POINT(1.2 0.8)');
mysql> SET @point_on = ST_GeomFromText('POINT(0 0.5)');
mysql> SET @polygon = ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))');
mysql> SELECT ST_Within(@point_in, @polygon) AS within_in,
-> ST_Contains(@polygon, @point_in) AS contains_in,
-> ST_Within(@point_out, @polygon) AS within_out,
-> ST_Contains(@polygon, @point_out) AS contains_out,
-> ST_Within(@point_on, @polygon) AS within_on,
-> ST_Contains(@polygon, @point_on) AS contains_on\G
*************************** 1. row ***************************
within_in: 1
contains_in: 1
within_out: 0
contains_out: 0
within_on: 0
contains_on: 0
1 row in set (0.000 sec)mysql> SET @point_in = ST_GeomFromText('POINT(0.4 0.7)');
mysql> SET @point_out = ST_GeomFromText('POINT(1.2 0.8)');
mysql> SET @point_on = ST_GeomFromText('POINT(0 0.5)');
mysql> SET @polygon = ST_GeomFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))');
mysql> SELECT ST_Within(@point_in, @polygon) AS within_in,
-> ST_Contains(@polygon, @point_in) AS contains_in,
-> ST_Within(@point_out, @polygon) AS within_out,
-> ST_Contains(@polygon, @point_out) AS contains_out,
-> ST_Within(@point_on, @polygon) AS within_on,
-> ST_Contains(@polygon, @point_on) AS contains_on\G
*************************** 1. row ***************************
within_in: 1
contains_in: 1
within_out: 0
contains_out: 0
within_on: 0
contains_on: 0
1 row in set (0.000 sec)引数の順序が違うだけで、両者が全く同じ結果を返していることが分かるでしょう。
点以外でも使える
ST_Within()は点だけでなく、線(LineString)や面(Polygon)との関係に対しても使えます。それぞれ実行例を紹介します。
まず、ポリゴンの中に完全に含まれる線と、一部中にあるがはみ出す線とを確認してみましょう。完全に外側にある線は割愛しました。
mysql> SET @line_in = ST_GeomFromText('LINESTRING(0.3 0.2, 0.7 0.8)');
mysql> SET @line_out = ST_GeomFromText('LINESTRING(0.6 0.5, 1.2 0.8)');
mysql> SELECT ST_Within(@line_in, @polygon) AS line_in,
-> ST_Within(@line_out, @polygon) AS line_out;
+---------+----------+
| line_in | line_out |
+---------+----------+
| 1 | 0 |
+---------+----------+
1 row in set (0.000 sec)
mysql> SET @poly_in = ST_GeomFromText('POLYGON((0.3 0.3, 0.4 0.3, 0.5 0.4, 0.3 0.4, 0.3 0.3))');
mysql> SET @poly_out = ST_GeomFromText('POLYGON((0.8 0.6, 1.1 0.5, 1.2 0.7, 0.9 0.7, 0.8 0.6))');
mysql> SELECT ST_Within(@poly_in, @polygon) AS poly_in,
-> ST_Within(@poly_out, @polygon) AS poly_out;
+---------+----------+
| poly_in | poly_out |
+---------+----------+
| 1 | 0 |
+---------+----------+
1 row in set (0.000 sec)次にポリゴンです。こちらも線での確認と同様に、完全に中に含まれるポリゴンと一部のみが重なっているポリゴンで試してみます。
いずれも「完全に含まれているときに 1(TRUE)、一部でもはみ出している場合は 0(FALSE)」となることが分かります。「完全に含まれる」という条件がポイントです。
柏市内の学校を抽出する
では、実際のデータで試してみましょう。千葉県の学校データ(chiba_school)テーブルと行政区域データ(chiba_city)テーブルを使って、県内の柏市内にある学校を抽出します。
mysql> SELECT s.OGR_FID,s.p29_002, s.p29_003, s.p29_004
-> FROM chiba_school s, chiba_city c
-> WHERE c.n03_004 = '柏市'
-> AND ST_Within(s.SHAPE, c.SHAPE);
+---------+---------------+---------+-------------------------------+
| OGR_FID | p29_002 | p29_003 | p29_004 |
+---------+---------------+---------+-------------------------------+
| 1878 | B112210005966 | 16001 | 柏市立＊＊東小学校 |
| 951 | B112210005957 | 16001 | 柏市立＊＊西小学校 |
| 697 | A112310002558 | 16011 | 柏＊幼稚園 |
: : : : :
| 2135 | F113110102700 | 16007 | 東＊大学 |
| 1997 | C112310000262 | 16002 | ＊＊＊＊大学付属柏中学校 |
+---------+---------------+---------+-------------------------------+
133 rows in set (0.257 sec)このクエリでは、ST_Within()を使用して柏市の行政区域ポリゴンの中に含まれる学校のPOINTデータを判定しています。幼稚園から大学まで様々な学校133件が含まれていることが分かります(実在の学校名のため、名称(p29_004)は筆者がマスクしました)。地図上にプロットしてみると、学校が集まっている地域と疎な地域が見えてきますね。
市町村別の学校数ランキング
GROUP BYと組み合わせれば、市町村ごとの学校数も集計できます。
mysql> SELECT c.n03_004 AS city,
-> COUNT(*) AS schools
-> FROM chiba_school s, chiba_city c
-> WHERE ST_Within(s.SHAPE, c.SHAPE)
-> GROUP BY c.n03_004
-> ORDER BY schools DESC
-> LIMIT 10;
+--------------+---------+
| city | schools |
+--------------+---------+
| 千葉市 | 357 |
| 船橋市 | 165 |
| 松戸市 | 142 |
| 柏市 | 133 |
| 市川市 | 125 |
| 市原市 | 110 |
| 木更津市 | 64 |
| 八千代市 | 60 |
| 習志野市 | 55 |
| 浦安市 | 55 |
+--------------+---------+
10 rows in set (3.371 sec)ST_Within()を使って、それぞれの地域Polygon内に含まれる学校(Point)の数を求めています。千葉、船橋、松戸、柏など人口が多そうな都市には学校が多くあることが見て取れます。
実は地域に関するデータには、それぞれの地域を表す名称やコードなどの情報が含まれていることが多いです。地理情報の機能を使わずとも、この値を使ってグルーピングすることもできます。今回は地理情報(位置情報)データを使用したST_Within() の使用例と理解していただければと思います。
実際に学校データに含まれる地区コード(p29_001列)を使ったグルーピングも紹介します。
mysql> SELECT s.p29_001 AS city,
-> COUNT(*) AS schools
-> FROM chiba_school s
-> GROUP BY s.p29_001
-> ORDER BY schools DESC
-> LIMIT 10;
+-------+---------+
| city | schools |
+-------+---------+
| 12204 | 165 |
| 12207 | 142 |
| 12217 | 133 |
| 12203 | 125 |
| 12219 | 110 |
| 12101 | 88 |
| 12106 | 68 |
| 12206 | 64 |
| 12221 | 60 |
| 12103 | 56 |
+-------+---------+
10 rows in set (0.001 sec)コードを名称に変換するためのもう一工夫が必要なことに加え、政令指定都市である千葉市は区ごとにコードを持っているため、先ほどとは少々異なる集計結果を得ています。
我孫子市内の駅を抽出する
行政区域と鉄道データを使った実行例として、最後に我孫子市内にある駅を抽出してみましょう。
mysql> SELECT s.n02_005 AS station, s.n02_003 AS line, s.n02_004 AS company
-> FROM station s, chiba_city c
-> WHERE c.n03_004 = '我孫子市'
-> AND ST_Within(s.SHAPE, c.SHAPE);
+--------------+-----------+-----------------------+
| station | line | company |
+--------------+-----------+-----------------------+
| 布佐 | 成田線 | 東日本旅客鉄道 |
| 新木 | 成田線 | 東日本旅客鉄道 |
| 湖北 | 成田線 | 東日本旅客鉄道 |
| 東我孫子 | 成田線 | 東日本旅客鉄道 |
| 天王台 | 常磐線 | 東日本旅客鉄道 |
| 我孫子 | 成田線 | 東日本旅客鉄道 |
| 我孫子 | 常磐線 | 東日本旅客鉄道 |
+--------------+-----------+-----------------------+
7 rows in set (0.005 sec)我孫子市内の駅として7つの行が返ってきました。よく見ると、station + line でユニークとなるようなデータなので、我孫子駅が常磐線と成田線の両方に登場していることに気づきます。そのため実際の駅数としては6個ということになります。
注意点としては、今回何度も強調したように ST_Within()は「完全に含む」場合を判定します。つまり複数の自治体に跨がる駅の場合は、どちらの地域でも「FALSE」となってしまいます。
おわりに
今回はST_Within()関数とST_Contains()を関数を使って、ジオメトリの包含関係を判定する方法を紹介しました。両者は視点が逆なだけで同じことを判定しています。
- ST_Within(school.SHAPE, city.SHAPE): school.SHAPEがcity.SHAPEに含まれる
- ST_Contains(city.SHAPE, school.SHAPE): city.SHAPEの中にschool.SHAPEが含まれる
ややこしいですが、必要な時にマニュアルを確認して引数の順序を把握すると良いでしょう。
なお、本稿の鉄道データは「国土交通省国土数値情報ダウンロードサイト」よりダウンロードしたものを使用しています。地図画像は「OpenStreetMap」を使用しています。
- この記事のキーワード
この記事をシェアしてください
バックナンバー
この記事の筆者
筆者の人気記事
Think ITでは、技術情報が詰まったメールマガジン「Think IT Weekly」の配信サービスを提供しています。メルマガ会員登録を済ませれば、メルマガだけでなく、さまざまな限定特典を入手できるようになります。
