MySQLで学ぶGIS入門第22回

MySQLのGIS機能を使って県や市町村の面積を調べてみよう

第22回の今回は、MySQLのST_Area()関数を使って千葉県の市町村面積ランキングや県全体の面積を算出し、ポリゴンの複雑さも調べる方法について解説します。

6:30

はじめに

前回まで、国土数値情報から取得した千葉県の行政区域データ、学校データ、鉄道データをMySQLに登録してきました。今回からは、登録済みのこれらのデータを使って、様々なデータ操作を紹介していきます。

今回は、Polygonの面積を求める ST_Area() 関数を紹介します。

ST_Area()関数

ST_Area()は、Polygonの面積を返す関数です。引数に「SRID 6668」のような地理座標系のデータを与えた場合、その戻り値の単位は平方メートルになります。前回までに千葉県の行政区域データを登録しているので、このデータを使ってST_Area()関数の動作や活用を見ていきましょう。

まずは柏市の面積を求めてみます。ST_Area()関数に、各自治体の形を示すPolygonデータが格納されているSHAPE列を与えています。1,000,000で割っているのは、見やすさのための平方キロメートルへの変換です。

mysql> SELECT OGR_FID, n03_001, n03_004, n03_007,
    ->        ST_Area(SHAPE) / 1000000 AS area_km2
    ->   FROM chiba_city
    ->  WHERE n03_004 = '柏市';
+---------+-----------+---------+---------+--------------------+
| OGR_FID | n03_001   | n03_004 | n03_007 | area_km2           |
+---------+-----------+---------+---------+--------------------+
|     681 | 千葉県    | 柏市    | 12217   | 114.74049682901403 |
+---------+-----------+---------+---------+--------------------+
1 row in set (0.003 sec)

柏市のホームページによると同市の面積は114.74平方キロメートルなので、今回の結果である約 114.74049...とぴったり一致しました。

複数のポリゴンで構成される市町村

ところで、市町村によっては飛び地や離島などがある場合には、同一市町村でも複数のポリゴンで構成されているものを第19回で紹介しました。富津市を例に見てみましょう。

富津市は東京湾に面したとんがった砂嘴を持つ市です。羽田空港に着陸する飛行機の中から目にしたという人も多いでしょう。

mysql> SELECT OGR_FID, n03_001, n03_004, n03_007,
    ->        ST_Area(SHAPE) / 1000000 AS area_km2 
    ->   FROM chiba_city 
    ->  WHERE n03_004 = '富津市'; 
+---------+-----------+-----------+---------+------------------------+
| OGR_FID | n03_001   | n03_004   | n03_007 | area_km2               |
+---------+-----------+-----------+---------+------------------------+
|    1039 | 千葉県    | 富津市    | 12226   |      205.2990391776052 |
|    1040 | 千葉県    | 富津市    | 12226   |   0.043798241382537045 |
|    1041 | 千葉県    | 富津市    | 12226   |    0.03809096818328585 |
|    1042 | 千葉県    | 富津市    | 12226   |    0.00424534723453659 |
|    1043 | 千葉県    | 富津市    | 12226   |   0.002270498004578454 |
|    1044 | 千葉県    | 富津市    | 12226   |  0.0013032988380284932 |
|    1045 | 千葉県    | 富津市    | 12226   |  0.0012146204541758955 |
|    1046 | 千葉県    | 富津市    | 12226   |  0.0005511412732885594 |
|    1047 | 千葉県    | 富津市    | 12226   |  0.0005103499954135537 |
|    1048 | 千葉県    | 富津市    | 12226   | 0.00035110112456644585 |
|    1049 | 千葉県    | 富津市    | 12226   | 0.00018633084224886412 |
|    1050 | 千葉県    | 富津市    | 12226   | 0.00016293759460918683 |
|    1051 | 千葉県    | 富津市    | 12226   |  0.0001343794139102114 |
|    1052 | 千葉県    | 富津市    | 12226   | 0.00012324700399593053 |
|    1053 | 千葉県    | 富津市    | 12226   | 0.00012320711712370463 |
|    1054 | 千葉県    | 富津市    | 12226   | 0.00008787947045247521 |
|    1055 | 千葉県    | 富津市    | 12226   | 0.00006881170087390404 |
|    1056 | 千葉県    | 富津市    | 12226   |  0.0000631015451560554 |
|    1057 | 千葉県    | 富津市    | 12226   | 0.00005635118165367138 |
|    1058 | 千葉県    | 富津市    | 12226   | 0.00004152025259138211 |
|    1059 | 千葉県    | 富津市    | 12226   | 0.00004118933452001341 |
|    1060 | 千葉県    | 富津市    | 12226   | 0.00003133066308424064 |
+---------+-----------+-----------+---------+------------------------+
22 rows in set (0.004 sec)

なんと22行も返ってきました。これが何を表しているかは実際に各Polygonを地図に載せて1つ1つ確認してみないと分かりませんが、面積から見ても小さな島や岩礁といったものが非常に多くあるものと推測されます。

実際にプロットしてみると、有名な第一海堡・第二海堡もこのデータでは富津市に属していることが確認できます。

市を構成する各Polygonごとではなく、市全体の面積を求めるには、SUM()で合計を取ります。

mysql> SELECT n03_001, n03_004, n03_007,
    ->        SUM(ST_Area(SHAPE)) / 1000000 AS area_km2
    ->   FROM chiba_city
    ->  WHERE n03_004 = '富津市'
    ->  GROUP BY n03_004, n03_001, n03_007;
+-----------+-----------+---------+-------------------+
| n03_001   | n03_004   | n03_007 | area_km2          |
+-----------+-----------+---------+-------------------+
| 千葉県    | 富津市    | 12226   | 205.3924950302158 |
+-----------+-----------+---------+-------------------+
1 row in set (0.004 sec)

千葉県市町村面積ランキング

面積を求める機能を使った応用例として、千葉県内の全市町村の面積ランキングを算出してみましょう。まずは大きいほうから。

mysql> SELECT n03_004, SUM(ST_Area(SHAPE)) / 1000000 AS area_km2 
    ->   FROM chiba_city 
    ->  WHERE n03_001='千葉県'
    ->  GROUP BY n03_004 
    ->  ORDER BY area_km2 DESC 
    ->  LIMIT 10;
+--------------+--------------------+
| n03_004      | area_km2           |
+--------------+--------------------+
| 市原市       | 368.1578513528805  |
| 君津市       | 318.78474990623346 |
| 千葉市       | 271.76833964152786 |
| 香取市       | 262.35541752458806 |
| 南房総市     | 229.52358210367865 |
| 成田市       | 213.83648206888242 |
| 富津市       | 205.3924950302158  |
| 鴨川市       | 191.13906001942217 |
| いすみ市     | 157.50730324531486 |
| 山武市       | 146.7684232721315  |
+--------------+--------------------+
10 rows in set (0.127 sec)

1位は市原市で約368平方キロメートル。房総半島の市が上位に入っているのが目につく中で、意外にも都市部である千葉市が県内3番目の面積を誇っていることが分かります。

小さいほうも見てみましょう。

mysql> SELECT n03_004, SUM(ST_Area(SHAPE)) / 1000000 AS area_km2 
    ->   FROM chiba_city 
    ->  WHERE n03_001='千葉県'
    ->  GROUP BY n03_004 
    ->  ORDER BY area_km2 ASC LIMIT 10;
+-----------------+---------------------+
| n03_004         | area_km2            |
+-----------------+---------------------+
| 所属未定地      | 0.09822453995062204 |
| 浦安市          |   18.82230417562929 |
| 酒々井町        |  19.008866993754882 |
| 神崎町          |  19.897409796112758 |
| 習志野市        |  20.970196188465206 |
| 鎌ケ谷市        |   21.08641596393139 |
| 一宮町          |  22.975739071902346 |
| 九十九里町      |   24.44114908052758 |
| 御宿町          |   24.69769983591676 |
| 白子町          |   27.50367921652374 |
+-----------------+---------------------+
10 rows in set (0.127 sec)

総務省による財政力指数が県内トップの浦安市が、なんと千葉県で1番小さな自治体であることが分かりました。アミューズメントパークのパワー、おそるべしです。

千葉県全体の面積

ST_Area()による面積計算のまとめとして、個別の市町村だけでなく、千葉県全体の面積も算出してみましょう。

今回は千葉県データしかDBに登録していないので、n03_001='千葉県' というWHERE条件指定を省略してもよいのですが、複数の都道府県や全国のデータを登録した人向けに「千葉県」のデータのみを抽出する条件を加えました。

mysql> SELECT SUM(ST_Area(SHAPE)) / 1000000 AS area_km2
    ->   FROM chiba_city
    ->  WHERE n03_001='千葉県';
+------------------+
| area_km2         |
+------------------+
| 5157.50497424981 |
+------------------+
1 row in set (0.125 sec)

約5,157.5平方キロメートルと計算されました。国土地理院が公表している千葉県の面積は5,156.48平方キロメートル(令和8年1月1日現在：境界未定地あり)なので、ほぼ一致していると言ってよいでしょう。

ポリゴンの複雑さを見てみよう

今回は ST_Area()関数を紹介してきましたが、最後にもう1つだけ便利な関数を紹介します。

ST_NumPoints()関数はPolygonやLineStringを構成する点の数を返す関数です。Polygonが多くの点で構成されるほど、より細かな表現や複雑な形の表現がされていることになります。市区町村毎に集計し、ついでにCOUNT(*)でその自治体にPolygonがいくつあるかも算出してみました。

また、Polygonでは中のくりぬきを表現できるため、ここではST_ExteriorRing()関数を使って、外側の形状だけを抜き出して使うようにしています。

mysql> SELECT n03_001, n03_004, n03_007, 
    ->        SUM(ST_NumPoints(ST_ExteriorRing(SHAPE))) AS numPoints,
    ->        COUNT(*) AS numPolygons
    ->   FROM chiba_city
    ->  GROUP BY n03_001, n03_004, n03_007
    ->  ORDER BY numPoints DESC
    ->  LIMIT 10;
+-----------+--------------+---------+-----------+-------------+
| n03_001   | n03_004      | n03_007 | numPoints | numPolygons |
+-----------+--------------+---------+-----------+-------------+
| 千葉県    | 南房総市     | 12234   |     78545 |        1137 |
| 千葉県    | 館山市       | 12205   |     32657 |         374 |
| 千葉県    | 鴨川市       | 12223   |     20112 |         303 |
| 千葉県    | 勝浦市       | 12218   |     11611 |          34 |
| 千葉県    | 銚子市       | 12202   |     11030 |         241 |
| 千葉県    | いすみ市     | 12238   |      8075 |          17 |
| 千葉県    | 富津市       | 12226   |      6723 |          22 |
| 千葉県    | 木更津市     | 12206   |      6608 |          27 |
| 千葉県    | 市原市       | 12219   |      5861 |           7 |
| 千葉県    | 旭市         | 12215   |      5831 |           4 |
+-----------+--------------+---------+-----------+-------------+
10 rows in set (0.012 sec)

南房総市が1,137ものポリゴンからなり(房総半島の先端なので岩や岩礁が多く含まれているのでしょう)、合計すると8万近くの点でその形が表されています。

一方、4つのポリゴンで表される旭市は6,000近くの点により構成されており、1つの形としての複雑さナンバーワンクラスと言えるかもしれません。今回は算出しませんでしたが、ポリゴン1つあたりの平均でランク付けするなど、様々な視点での集計に応用できます。

おわりに

今回は、ST_Area()関数を使ったPolygonの面積計算、そしてST_NumPoints()関数を使った構成点数の算出を紹介しました。自治体(行政区域)データと、この2つの関数を知るだけでも、いろいろな切り口でデータを活用できることが分かったことと思います。

ぜひ、データを使っていろいろと遊んでみてください。

【コラム】千葉県の「所属未定地」とは？

面積ランキングの最小に登場した「所属未定地」、気になりますよね。今回紹介したST_NumPoints()関数も活用して、すぐに確認してみましょう。

mysql>  SELECT n03_004, ST_Area(SHAPE) / 1000000 AS area_km2, 
    ->         ST_NumPoints(ST_ExteriorRing(SHAPE)) AS points,
    ->         ST_AsText(SHAPE) AS g
    ->    FROM chiba_city   WHERE n03_004 = '所属未定地'\G
*************************** 1. row ***************************
 n03_004: 所属未定地
area_km2: 0.09822453995062204
  points: 196
       g: POLYGON((35.670960288 139.958333333,35.670235649 ... ,35.671747153 139.95852856,35.670960288 139.958333333))
1 row in set (0.002 sec)

196個の点から構成される1つのPolygonデータであることが分かります。

…が、この数値を見て、どの場所のどんな形なのかをイメージできる人は、そう多くはないと思います。そこで、本連載でも度々使っている DBeaverの出番です。DBeaverでこの行の SHAPE列を見てみると下図のようになります。これは、船橋市と市川市にまたがる埋め立て地の端のエリアのようです。「今までの境界線をそのまま延長して…」のように簡単にはいかない事情があるのでしょうね。