はじめに

MySQL8.0に、GISで標準的に使用されているシェープファイルをインポートする方法を記載します。

昨日、下記の記事を書いたのですが、データセットが用意できないとそもそも検証ができません。

MySQL8.0の空間検索が遅い？の続き1
https://qiita.com/miyauchi/items/893f12679cb21c12c454

この記事を参考にしてデータセットを作成し、一緒に検証してくれる人が増えることを期待します。

環境

さくらのVPS 2Gプランを使用。標準OSのCentOS 7（x86_64）をインストールしました。

MySQLのバージョンは8.0.16(最新版)を使用します。

シェープデータの変換およびインポートにはGDAL3.0.1に含まれるogr2ogrを使用します。

シェープデータにはe-Statの境界データを使用します。

全体の流れ

MySQLの準備
ogr2ogrコマンドの準備
ogr2ogrを使ってMySQLにシェープデータをインポート

MySQLの準備

ここで重要なのは認証方式をmysql_native_passwordに変更しておく点です。
なぜなら、おそらくですがGDALがMySQL8.0標準の認証方式に対応していないためです。

MySQLのインストール

CentOSにMySQLの公式リポジトリを追加します。

公式リポジトリ
https://dev.mysql.com/downloads/repo/yum/

$ sudo rpm -ivh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

次にMySQLをインストールします。

$ sudo yum install -y mysql-community-server

現時点(2019.7/17)の最新版である8.0.16がインストールされていることを確認します。

$ mysqld --version
/usr/sbin/mysqld  Ver 8.0.16 for Linux on x86_64 (MySQL Community Server - GPL)

MySQLサーバを起動します。

$ sudo systemctl start mysqld.service

自動起動を有効にしておく。

$ sudo systemctl enable mysqld.service

以上でMySQLのインストールは完了です。

MySQLの設定

rootの初期パスワードを確認します。
ここではeQ=9CCgh>jX#であることが確認できました。

$ grep password /var/log/mysqld.log
2019-07-17T13:13:56.981332Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: eQ=9CCgh>jX#

次にrootパスワードを変更し、セキュリティ関するいくつかの設定をします。

$ sudo mysql_secure_installation

質問は下記で回答しました。

Change the password for root ? ((Press y|Y for Yes, any other key for No) : y
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y

設定ファイル /etc/my.cnf の下記行をアンコメントします。

default-authentication-plugin=mysql_native_password

設定を反映させるためにMySQLサーバを再起動します。

$ sudo systemctl restart mysqld.service

以上でMySQLの設定は完了です。

ogr2ogrコマンドの準備

ogr2ogrコマンドを使うためにGDALをインストールします。
GDALのコンパイルにPROJライブラリが必要であるため、先にPROJライブラリをインストールします。

PROJライブラリをインストール

PROJライブラリのコンパイルにsqliteが必要であるため、sqliteを先にインストールしておきます。

$ sudo yum install -y sqlite-devel

PROJライブラリをインストールします。

$ wget https://download.osgeo.org/proj/proj-6.1.1.tar.gz
$ tar xvfz proj-6.1.1.tar.gz
$ cd proj-6.1.1/
$ ./configure
$ make
$ sudo make install

GDALをインストール

GDALのコンパイルにmysql_configコマンドが必要であるため、mysql_configを含むmysql-community-develパッケージをインストールします。

$ sudo yum install -y mysql-community-devel

GDALをインストールします。MySQLドライバを有効にするため---with-mysqlオプションを指定します。

$ wget https://github.com/OSGeo/gdal/releases/download/v3.0.1/gdal-3.0.1.tar.gz
$ tar xvfz gdal-3.0.1.tar.gz
$ cd gdal-3.0.1/
$ ./configure --with-mysql=/usr/bin/mysql_config
$ make
$ sudo make install

ogr2ogrを使ってMySQLにシェープデータをインポート

シェープデータの準備

e-Statから境界データをダウンロードします。
ここでは平成27年国勢調査町丁・字等別境界データのうち、愛知県のデータをダウンロードします。
いくつかデータ形式が選択できますが、「世界測地系緯度経度 Shapefile」を選択します。

$ wget -O A002005212015DDSWC23.zip "https://www.e-stat.go.jp/gis/statmap-search/data?dlserveyId=A002005212015&code=23&coordSys=1&format=shape&downloadType=5"
$ unzip A002005212015DDSWC23.zip -d A002005212015DDSWC23
$ cd A002005212015DDSWC23/

ogr2ogrコマンドを用いて、シェープファイルの文字エンコーディングをCP932(Shift-JIS)からUTF-8に変換します。

$ ogr2ogr -f "ESRI Shapefile" -lco ENCODING=UTF-8 -oo ENCODING=CP932 h27ka23_utf8.shp h27ka23.shp

データのインポート

データをインポートするためのデータベースを作成します。

$ mysql -u root -p
mysql> CREATE DATABASE geotest;

MySQLにデータをインポートします。
パスワードは適宜変更します。

$ ogr2ogr -f "MySQL" "MySQL:geotest,host=localhost,user=root,password=パスワード" h27ka23_utf8.shp

インポートしたデータの確認

mysqlにログインして、SELECTコマンドを実行します。

$ mysql -u root -p geotest
mysql> SELECT key_code, ST_AsText(SHAPE) FROM h27ka23_utf8 ORDER BY key_code LIMIT 1;

ここでは以下の結果が得られました。

| 23101001005 | POLYGON((35.160771346643 136.938842145444,35.1608301437881 136.938424291226,35.1608501173003 136.938334470847,35.1610470210929 136.938370604812,35.1610455594512 136.938417110451,35.1612019205203 136.938464933458,35.16155916329 136.938529179109,35.1615545646458 136.938555475691,35.1617364712544 136.938598441145,35.161741167614 136.938548840332,35.161911770243 136.93857200556,35.1619180171412 136.938523470471,35.1623067540192 136.938606284437,35.1622885707429 136.938711581907,35.1622335970064 136.939177415621,35.1621481339206 136.939721847138,35.1617339281151 136.939613455624,35.1617441005151 136.93955496258,35.1614778024844 136.939500052065,35.161417934312 136.939533684759,35.1606909879029 136.93936841024,35.160771346643 136.938842145444)) |

最後に

大事なのは下記ポイントです。

MySQLの認証方式をmysql_native_passwordに変更する。
GDALのMySQLドライバを有効にする。
シェープファイルの文字エンコーディングを事前にUTF-8に変換する。

また、インポートされたテーブルの列定義にはSRIDが指定されていません。
このままだとSPATIAL INDEXが使用されないためSRIDを指定しておくのが良いでしょう。

以下の記事が参考になります。

MySQL 8.0でSPATIAL INDEXを使用するには列定義でSRIDを指定する必要がある
https://qiita.com/miyauchi/items/89ae1870c5f611b2558c

インポートしたデータの抽出については別記事を書く予定です。

MySQL 8.0にシェープファイルをインポートしてみる