MySQL 8.0にシェープファイルを取り込んで、MySQL Workbenchでポリゴンの形を確認する

はじめに

本記事は RDBMS-GIS Advent Calendar 2018 の25日目です。

MySQLでは5.7でGIS機能をBoost.GeometryというC++のオープンソースライブラリを採用して再実装し、8.0でも引き続き機能強化しています。本記事をきっかけにMySQLのGIS機能に興味を持ってもらえると嬉しいです。

やること

1．e-Statから「沖縄県全域」のシェープファイルをダウンロードする
2．MySQL 8.0にシェープファイルを取り込む
3．取り込んだシェープファイルのデータをSQLで確認する
4．取り込んだシェープファイルのデータをMySQL Workbenchから確認し、ポリゴンの形を確認する
5．任意の小地域の形をMySQL WorkbenchとGoogleマップで確認して、形が同一であることを確認する

前提

この記事では、以下の前提が整っていることを想定しています。
・MySQL 8.0をインストール済み
・MySQL Workbench 8.0をインストール済みで、MySQL 8.0のデータベースに接続できる
・GDAL 2.3.2をインストール済み
・ブラウザが使用できる

手順

1．e-Statから「沖縄県全域」のシェープファイルをダウンロードする

以下の手順でe-Statから「沖縄県全域」のシェープファイルをダウンロードします。

　■e-Statからシェープファイルをダウンロードする手順
　　地図で見る ⇒ 境界データダウンロード ⇒ 小地域 ⇒ 国税調査 ⇒ 2015年 ⇒ 小地域（町丁・字等別） ⇒ 世界測地系緯度経度・Shape形式

なお、e-Statからシェープファイルをダウンロードする手順は、こちらの動画でも解説されています。

ダウンロードしたzipファイルを任意の作業ディレクトリで展開します。以下の4つのファイルが同梱されていますが、同じディレクトリ配下に置いておきます。

　・h27ka47.shp
　・h27ka47.dbf
　・h27ka47.prj
　・h27ka47.shx

2．MySQL 8.0にシェープファイルを取り込む

ダウンロードしたシェープファイルを以下の手順でMySQL 8.0に取り込みます。

　1．シェープファイルを取り込む用のデータベース(スキーマ)を作成
　2．ogr2ogrでシェープファイルをインポート

mysql> CREATE DATABASE geotest;
Query OK, 1 row affected (0.03 sec)

C:\> ogr2ogr.exe -f "MySQL" MySQL:"geotest,host=127.0.0.1,user=root,password=root,port=3306" C:\work\e-Stat\h27ka47.shp

3．取り込んだシェープファイルのデータをSQLで確認する

取り込んだシェープファイルのデータを確認します。

geometry_columnsテーブルには取り込んだシェープファイルのテーブル名、SRIDなどの情報が格納されています。h27ka47テーブルには、小地域のポリゴンデータや県名、市名などが格納されています。

mysql> USE geotest;
Database changed
mysql> SHOW TABLES;
+-------------------+
| Tables_in_geotest |
+-------------------+
| geometry_columns  |
| h27ka47           |
+-------------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM geotest.geometry_columns;
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
| F_TABLE_CATALOG | F_TABLE_SCHEMA | F_TABLE_NAME | F_GEOMETRY_COLUMN | COORD_DIMENSION | SRID | TYPE    |
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
| NULL            | NULL           | h27ka47      | SHAPE             |               2 | 4612 | POLYGON |
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM geotest.h27ka47;
+----------+
| COUNT(*) |
+----------+
|     1449 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT OGR_FID, ST_AsText(SHAPE), pref_name, city_name, s_name FROM geotest.h27ka47 LIMIT 1\G
*************************** 1. row ***************************
         OGR_FID: 1
ST_AsText(SHAPE): POLYGON((24.3540401488793 124.040161017281,24.3540401382305 124.040111182607,24.3541311487418 124.040036901344,24.3549394714773 124.039749443741,24.355981075101 124.040211785876,24.3564809545756 124.040436913978,24.3565893623622 124.040611775896,24.3565226155107 124.040761867424,24.3557559905447 124.040524301686,24.3551895060592 124.040449260268,24.3544228086827 124.040436880088,24.3541312235033 124.040286995966,24.3540401488793 124.040161017281))
       pref_name: 沖縄県
       city_name: 竹富町
          s_name: NULL
1 row in set (0.00 sec)

4．取り込んだシェープファイルのデータをMySQL Workbenchから確認し、ポリゴンの形を確認する

MySQL Workbenchでh27ka47テーブルのデータを確認します。

MySQL Workbenchを起動し、geotestデータベースに接続します。そして左側の"SHEMAS"ツリーから"geotest.h27ka47"テーブルを右クリックし、"Select Rows"を選択します。

検索結果が表示されます。SHAPE列については、geometry型(バイナリデータ)であるため"BLOB"と表示されています。

右側の"Form Editor"をクリックすることで、検索したデータを1件1件確認できます。また、"Form Editor"ではgeometry型(POLYGON)の値を図とテキスト形式で表示してくれます。

5．任意の小地域の形をMySQL WorkbenchとGoogleマップで確認して、形が同一であることを確認する

任意の小地域の形をMySQL WorkbenchとGoogleマップで確認します。この例では、「沖縄県那覇市牧志３丁目」について確認しています。

■MySQL Workbenchから確認
MySQL Workbenchで以下のSQLを実行し、"Form Editor"で形を確認します。

SELECT * FROM geotest.h27ka47 WHERE Pref_name='沖縄県' AND City_name='那覇市' AND S_name='牧志３丁目';

■Googleマップから確認
Googleマップで「沖縄県那覇市牧志３丁目」を検索し、MySQL Workbenchで確認した形と同一であることを確認します。
(Googleマップの画像を引用することにライセンス的な問題があるため、検索結果の画像は掲載していません。実際に検索して、MySQL Workbenchで確認した形と同一であることを確認してみて下さい)

おまけ

Googleマップで任意の地点の緯度経度を確認し、その緯度経度を指定してh27ka47を検索することで、その地点が含まれる小地域を確認できます。

なお、今回取り込んだシェープファイルとGoogleマップで使用しているSRIDが異なるため、それによる誤差が生じますが、説明簡略化のためにこの記事ではその点は無視しています。

1．Googleマップで任意の地点の緯度経度を確認

Googleマップ上で任意の地点を右クリックすることで、その場所の緯度、経度を確認出来ます。
牧志駅辺りの緯度、緯度を確認すると、「緯度：26.217152、経度：127.692614」でした。

2．確認した緯度経度を指定してh27ka47テーブルを検索

以下のようST_Contains関数を使用することで、指定した経度緯度の地点が含まれる小地域を検索できます。

mysql> SELECT OGR_FID, pref_name, city_name, s_name FROM geotest.h27ka47 WHERE ST_Contains(SHAPE,ST_GeomFromText('POINT(26.217152 127.692614)',4612, 'axis-order=lat-long'))\G
*************************** 1. row ***************************
  OGR_FID: 51
pref_name: 沖縄県
city_name: 那覇市
   s_name: 牧志３丁目
1 row in set (0.38 sec)

備考

・「沖縄県全域」のシェープファイルでは問題ありませんが、他の地域のシェープファイルでは、MySQLにインポートする際にエラーが発生したものもあります。こちらの記事でも言及している通り、それらについては一旦QGISで開き、文字コードをUTF-8に変換して保存することで、エラーを回避できました。なお、元のファイルの文字コードはSHIFT-JISでした。

・上記のようなエラーを回避するためにogr2ogrが生成するSQL文を手動で修正出来ればと思い、以下のissueも登録しています。

　Feature request: Create SQL Script option to ogr2ogr #1090
　https://github.com/OSGeo/gdal/issues/1090

・MySQL自体にもシェープファイルをインポート/エクスポートできるツールがあると便利だと思い、以下の機能追加リクエストも登録しています。

　Bug#90023 [Feature request] Shape File import/export tool
　https://bugs.mysql.com/bug.php?id=90023