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

# はじめに

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の境界データを使用します。

# 全体の流れ

1. MySQLの準備
2. ogr2ogrコマンドの準備
3. 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

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