はじめに
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
インポートしたデータの抽出については別記事を書く予定です。