はじめに
本記事は RDBMS-GIS Advent Calendar 2020 の1日目です。
MySQLでは5.7でGIS機能をBoost.GeometryというC++のオープンソースライブラリを採用して再実装し、8.0でも引き続き機能強化しています。そして、MySQL 8.0.20以降であれば、ST_Intersects()というSpatial関数も実用的な速度で実行できるようになりました。
そこで本記事では、ST_Intersects()を使って日本地図の範囲検索を試した結果を紹介します。
(MySQL部分の処理のみ試してます)
本記事をきっかけにMySQLのGIS機能に興味を持ってもらえると嬉しいです。
やること
1．e-Statのデータを基にして配布されている、日本の市町村の境界データをMySQL 8.0に取り込む
2．Googleマップから範囲検索したいエリアの経度、緯度を確認し、MySQL上でジオメトリデータを生成する
3．MySQLに取り込んだデータを検索する。この時、ST_Intersects()関数に検索対象エリアのジオメトリデータを指定し、データを絞り込む
4．検索結果に出てきた住所をGoogleマップで確認し、範囲検索できていることを確認する
注意事項
厳密には、e-Statの境界データはSRID:4612、GoogleマップのデータはSRID:3857であるため、Googleマップで確認した経度、緯度をそのまま検索時に指定すると、わずかなずれ(数センチ程度？)が発生する可能性があります。本記事では直感的な分かりやすさを優先し、その点は無視しています。
前提
この記事では、以下の前提が整っていることを想定しています。
・MySQL 8.0.20以降をインストール済み (最新バージョンを使用することを推奨)
・ブラウザが使用できる
手順
1．e-Statのデータを基にして配布されている日本の市町村の境界データをMySQL 8.0に取り込む
こちらのページで、e-Statで配布されている境界データを基にして作成したMySQL用のデータを配布しています。今回は兵庫県のデータを検索していますので、"h27ka28(Hyogo).zip"をダウンロードして解凍し、MySQLに取り込みます。
mysql> CREATE DATABASE geotest;
mysql> USE geotest
mysql> source h27ka28(Hyogo).dmp
2．Googleマップから範囲検索したいエリアの経度、緯度を確認し、MySQL上でジオメトリデータを生成する
Googleマップから範囲検索したいエリアの経度、緯度を確認します。Googleマップ上でポイントを選択し、右クリックすることで経度、緯度を確認出来ます。
今回は、明石駅と明石市立天文科学館の経度緯度を確認し、以下の範囲についてジオメトリデータを生成します。
(著作権とライセンスを考慮し、以下の画像はGoogleマップではなく、オープンストリートマップを使用しています。オープンストリートマップの著作権とライセンスについてはこちらを参照下さい)
それぞれの緯度、経度
・明石駅：34.64926 134.99272
・明石市立天文科学館：34.64959 135.00147
ジオメトリデータの生成
上記の緯度、経度をST_GeomFromText()関数に指定して、範囲のジオメトリデータを生成します。POLYGONで四角形を生成する場合は5点を指定し、最初の点と最後の点が同じ点になることに注意して下さい。また、検索対象データのSRIDが4612であるため、ST_GeomFromText()関数のSRIDを指定するパラメーターに4612も指定しています。
mysql> SET @g1 = ST_GeomFromText('POLYGON((34.64926 134.99272, 34.64959 134.99272, 34.64959 135.00147, 34.64926 135.00147, 34.64926 134.99272))', 4612);
3．MySQLに取り込んだデータを検索する。この時、ST_Intersects()関数に検索対象エリアのジオメトリデータを指定し、データを絞り込む
先ほど生成したジオメトリデータをST_Intersects()関数に指定して、範囲が重なっているデータだけを取り出します。次のステップで住所から検索結果が正しいことを確認するために、pref_name列、city_name列、s_name列を取り出します。
mysql> SELECT pref_name, city_name, s_name FROM geotest.hyogo WHERE ST_Intersects(@g1, SHAPE);
+-----------+-----------+-----------------------+
| pref_name | city_name | s_name |
+-----------+-----------+-----------------------+
| 兵庫県 | 明石市 | 人丸町 |
| 兵庫県 | 明石市 | 山下町 |
| 兵庫県 | 明石市 | 大明石町１丁目 |
| 兵庫県 | 明石市 | 明石公園 |
+-----------+-----------+-----------------------+
4 rows in set (0.00 sec)
4．検索結果に出てきた住所をGoogleマップで確認し、範囲検索できていることを確認する
上記ステップで検索結果に出てきた4つの住所(※)をGoogleマップで検索します。ST_Intersects()関数により、検索時に指定した範囲と重なっているエリアの情報が検索できていたことが分かります。
(著作権とライセンスを考慮し、検索結果の画像は掲載しませんが、「兵庫県明石市人丸町」、「兵庫県明石市山下町」、「兵庫県明石市大明石町1丁目」、「兵庫県明石市明石公園」のそれぞれについて検索し、検索範囲と重なっているエリアであることを確認してみて下さい)