DockerのMySQLに，GDALを使ってシェープファイルをインポートしてみる

#概要
　本記事では，Docker環境において，シェープファイルをもとにしたジオメトリデータを提供するサーバを構築してみます．
ジオメトリデータはMySQLに格納し，クライアントからのリクエストに応じてデータを受け渡すことを想定します．

　シェープファイル のジオメトリデータを MySQL にインポートするには，GDALというGISライブラリの ogr2ogr を用います．GDALのインポート方法はいくつかあるのですが，本記事ではGDALのDockerイメージを使用します．例としてNode.js（Express）のDockerアプリにGDALを組み込んで，Docker Composeで構築していきます．

#環境
- Windows10 Pro 64bit
- Docker for Windows 19.03.2
- docker-conpose 1.24.1
- Node.js 12.13.0 ※expressの雛形形成のためにローカルで使います
- npm --version 6.12.0

#実装
###Expressの雛形を準備
　ExpressのDocker環境の構築は，基本的に [docker-compose(Docker for mac)で実践的なnode.js開発環境を作る](https://qiita.com/devalon/items/dd0fdce02156855b5df5) の手順を継承します．

　まず，作業ディレクトリで`express`と`express-generator`をインストールします．ただし，global環境へのインストールは避けたいので，下記のようにします．

```
$ npm install --save express
$ npm install --save express-generator
```

　次に，雛形を作るコマンドを実行します．npmでライブラリをローカルにインストール際にはpathが通らないので，下記のようにcmdファイルを指定します.`myapp`は各自任意の命名をしてください．
　テンプレートエンジンはhtmlをほぼそのまま扱える `ejs` を指定していますが，各自の開発に合わせて設定してください．なにも指定しない場合のデフォルトは，`jade`になります．ejsやjadeの違いは[こちら](https://qiita.com/otsukayuhi/items/8556b014ea363eabe11f)を参照．

```
$ ./node_modules/.bin/express.cmd myapp --view=ejs
```

　以上で ，`myapp` 配下に Express の雛形が生成されました．以降の作業ディレクトリは，`./myapp` 配下になります．

###docker-compose.ymlの作成
　`myapp`直下に，下記の docker-compose.yml を置きます．

```docker-compose.yml
version: '2'
services:
  nginx:
    image: nginx:alpine
    container_name: nginx
    ports:
      - "80:80"
    volumes:
      - "./conf.d:/etc/nginx/conf.d"
    links:
      - node_express
    networks:
      - app-net

  node_express:
    image: node:6.9-alpine
    container_name: node_express
    hostname: node_express
    volumes:
      - ".:/src"
    working_dir: /src
    command: [sh, -c, npm install && npm start]
    ports:
      - "3000:3000"
    links:
      - mysql
    networks:
      - app-net

  mysql:
    image: mysql:5.7
    container_name: mysql
    hostname: mysql
    command: mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci
    ports:
      - "3306:3306"
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
    volumes:
      - ./db/mysql-data:/var/lib/mysql
    networks:
      - app-net

  gdal:
    image: osgeo/gdal:ubuntu-full-3.0.2
    container_name: gdal
    hostname: gdal
    volumes: 
      - "./geodata:/home"
    tty: true
    networks:
      - app-net

networks:
  app-net:
    driver: bridge
```

　ポイントは，以下の３点．

- gdalコンテナは`tty: true`をつけないとコンテナが維持されない．
- gdalコンテナは`./geodata:/home`をマウントし，ここにMySQLに入力したいシャープファイルを配置する．
- 各コンテナに`networks`を指定し，この後 MySQL の入出力データを各コンテナから通信できるようにする．

　また，[参考記事](https://qiita.com/devalon/items/dd0fdce02156855b5df5)と同様に`./conf.d`フォルダを作成し，その配下に下記 nginx 用のファイルを置きます．

```nodejs.conf
server {
    listen 80;
    server_name _;
    client_max_body_size 10M;
    location / {
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header Host $http_host;
        proxy_set_header X-NginX-Proxy true;
        proxy_pass http://node_express:3000/;
    }
}
```

###シェープファイルのダウンロード と 作業ディレクトリへの配置
　今回は例として，国土交通省が公開している国土数値情報のうち，`低位地帯データ（神奈川県）`のシェープファイルをインポートしてみます．
　[ダウンロードサイト](http://nlftp.mlit.go.jp/ksj/gml/datalist/KsjTmplt-G08-v1_0.html)にアクセスし，**神奈川県にチェック**のうえアンケートに回答しダウンロードします．
`G08-15_14_GML.zip`がDLされたと思うので，解凍します．解凍したフォルダ内に`G08-15_14.shp`があることを確認します．

　作業ディレクトリに戻り，docker-compose.ymlと同じ階層に ，docker-compose.yml内でgdalコンテナのマウント先に設定した`geodataフォルダ`を作成します．ここに先程解凍したデータをフォルダごと配置します．

###Dockerコンテナの立ち上げ
　ここまでの作業でDockerコンテナを立ち上げる準備ができたので，`docker-compose.yml`と同じ階層で下記コマンドを実行してコンテナを起動します．
　起動後，ブラウザから`http://localhost:3000`にアクセスして何か表示されれば成功です．また，`$ docker ps`コマンドでコンテナが４つ起動していることを確認します．


```
$ docker-compose up -d --build
```

###GDAL（ogr2ogr）を使って シェープファイル を MySQL にインポート
　まずはMySQLのDockerコンテナに入り，データベースを作成します．DBの名前は`flood_map`としていますが，ご自身の開発に合わせてDB名を設定します．

```
$ docker exec -i -t mysql bash
$ mysql -h 127.0.0.1 -u root -proot
mysql> create database flood_map;
```

　次に，gdalのDockerコンテナに入り，*ogr2ogr* を使ってMySQLにデータを入れていきます．
　*home* ディレクトリ配下にシェープファイルを含むファイル群がマウントされますので，ご自身の環境にあわせてシェープファイルを指定します．MySQLのホスト名はMySQLのコンテナ名を指定することでうまくいきます．

```
$ docker exec -i -t gdal bash
$ ogr2ogr -f "MySQL" MySQL:"flood_map,host=mysql,user=root,password=root,port=3306" ./home/G08-15_14_GML/G08-15_14.shp
```

###MySQLでデータを確認しインデックスを張る
　再び MySQL コンテナに戻り，SQLを実行してみてデータが入っていることを確認します．たくさん表示すると問題があるので，上から10件のデータを表示してみます．

```
mysql> use flood_map;
mysql>　SELECT ogr_fid,AsText(Envelope(ExteriorRing(shape))) from g08_15_14 limit 10;

+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ogr_fid | AsText(Envelope(ExteriorRing(shape)))                                                                                                                                                |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|       1 | POLYGON((139.52142804001 35.6371572663072,139.523032038531 35.6371572663072,139.523032038531 35.6379951881551,139.52142804001 35.6379951881551,139.52142804001 35.6371572663072))    |
|       2 | POLYGON((139.521452996084 35.6376134289269,139.521644357377 35.6376134289269,139.521644357377 35.6376736855994,139.521452996084 35.6376736855994,139.521452996084 35.6376134289269)) |
|       3 | POLYGON((139.52196220354 35.6369668672147,139.522425630462 35.6369668672147,139.522425630462 35.6371686759932,139.52196220354 35.6371686759932,139.52196220354 35.6369668672147))    |
|       4 | POLYGON((139.541073491975 35.6360655457882,139.543607066891 35.6360655457882,139.543607066891 35.6382958203003,139.541073491975 35.6382958203003,139.541073491975 35.6360655457882)) |
|       5 | POLYGON((139.540973950349 35.6380131944947,139.541300259083 35.6380131944947,139.541300259083 35.6382231275051,139.540973950349 35.6382231275051,139.540973950349 35.6380131944947)) |
|       6 | POLYGON((139.543183728112 35.6372670693054,139.543547637788 35.6372670693054,139.543547637788 35.6377649038623,139.543183728112 35.6377649038623,139.543183728112 35.6372670693054)) |
|       7 | POLYGON((139.543439171221 35.6369067894177,139.543604469296 35.6369067894177,139.543604469296 35.6370422598022,139.543439171221 35.6370422598022,139.543439171221 35.6369067894177)) |
|       8 | POLYGON((139.540877273964 35.6326687791813,139.543489041583 35.6326687791813,139.543489041583 35.6348299205534,139.540877273964 35.6348299205534,139.540877273964 35.6326687791813)) |
|       9 | POLYGON((139.20295027585 35.6304782158446,139.204943801785 35.6304782158446,139.204943801785 35.6336142552756,139.20295027585 35.6336142552756,139.20295027585 35.6304782158446))    |
|      10 | POLYGON((139.541693310682 35.6317030003044,139.544202942505 35.6317030003044,139.544202942505 35.6332801242532,139.541693310682 35.6332801242532,139.541693310682 35.6317030003044)) |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
```

　SQLの説明をすると，MySQLにはジオメトリデータを扱うための関数が用意されています．今回 *g08_15_14* テーブルの *shape* カラムの中に格納されているものは，POLYGON型（≒多角形）のデータで，それぞれが低位地帯の１領域を表します．*ExteriorRing()*で POLYGON の外周を構成する座標の集合を取得できます．またDB内にはデータがバイナリで保存されていますが，*AsText(Envelope())* で人間の読める形に変換しています．

　データが格納されていることが確認できたら，インデックスを張ります．MySQLにはジオメトリデータ用のインデックスが用意されています．

```
mysql> ALTER TABLE g08_15_14 ADD SPATIAL INDEX(shape);
```

###DBアクセス用のユーザ作成
　rootを使ってDBにアクセスするのはセキュリティ的によろしくないので，権限を絞ったユーザを作成します．APIサーバからアクセスする際はユーザを使ってアクセスすることを想定します．

```
mysql> CREATE USER 'user1'@"%" IDENTIFIED BY "user1";
mysql> GRANT SELECT ON flood_map.g08_15_14 TO user1@"%";
```

###【補足】SRIDについて
　ジオメトリデータにはSRID（空間参照識別子）があります．SRIDが何かについて詳細は[こちら](https://www.agtech.co.jp/products/actian/docs_portal/ActianX/11.1/index.html#page/GeospatialUser/GeospatialDataTypes.5.03.htm)．今回の神奈川県の低位地帯データは，JGD2011の緯度経度の座標系ですので，本来はSRIDは6668が設定されているべきです．ただ，MySQL5.7でogr2ogrを使うと，SRIDを1から連番で振ってしまいます．※MySQL8であれば正しく設定される模様...

```
mysql> SELECT * from geometry_columns;
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
| F_TABLE_CATALOG | F_TABLE_SCHEMA | F_TABLE_NAME | F_GEOMETRY_COLUMN | COORD_DIMENSION | SRID | TYPE    |
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
| NULL            | NULL           | g08_15_14    | SHAPE             |               2 |    1 | POLYGON |
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
```

SRIDが6668だと思ってるとエラーになります．

```
mysql> SET @g1 = GeomFromText('Polygon((139.695773 35.532169,139.695773 35.5068084,139.615388 35.5068084,139.615388 35.532169,139.695773 35.532169))', 6668);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select count(*) from g08_15_14 where MBRIntersects(SHAPE, @g1) = 1;
ERROR 3033 (HY000): Binary geometry function mbrintersects given two geometries of different srids: 1 and 6668, which should have been identical.
```
　下記であれば通ります．

```
mysql> SET @g1 = GeomFromText('Polygon((139.695773 35.532169,139.695773 35.5068084,139.615388 35.5068084,139.615388 35.532169,139.695773 35.532169))', 1);
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> select count(*) from g08_15_14 where MBRIntersects(SHAPE, @g1) = 1;
+----------+
| count(*) |
+----------+
|       29 |
+----------+
1 row in set (0.04 sec)
```

###コンテナの終了
　下記コマンド実行．

```
$ docker-compose down
```

#応用例（クライアント側の実装）
[leafletで洪水ハザードマップを作成する【OpenStreetMap, 国土数値情報】](https://qiita.com/hiromasano/items/221f0b5c5629bc1112be)

# まとめ
　本記事では，Docker環境でジオメトリデータを扱うためのサーバ環境を構築し，実際にデータをMySQLに入れてみました．MySQL内のデータを取り出すためには，node.jsでSQLを叩くAPIを用意し，フロントからアクセスすれば実現可能です．

#参考文献

- docker-compose(Docker for mac)で実践的なnode.js開発環境を作る: https://qiita.com/devalon/items/dd0fdce02156855b5df5 (accessed 2019/12/8).
- MySQLに対応したogr2ogr(GDAL)をインストールする方法まとめ: https://qiita.com/miyauchi/items/87921eb3ad630db1624a (accessed 2019/12/8).
- GISのための測地成果、測地系、楕円体、投影座標系、EPSGコードのまとめ: http://tmizu23.hatenablog.com/entry/20091215/1260868350 (accessed 2019/12/8).
- MySQL 空間分析関数: https://dev.mysql.com/doc/refman/5.6/ja/spatial-analysis-functions.html (accessed 2019/12/8).