MySQL v8で空間検索を試す

15分

SHARE

MySQL の v8 リリースされてだいぶ経ちましたが私の中では v5.7 ぐらいで止まってます。最近GIS絡みの記事をよく書きますが、DBで空間検索が得意なのはPostgreSQL。以前、QiitaにてOSMデータをPostgreSQL (PostGIS) にインストールして遊んでみた記事を書きましたが、仕事柄 MySQL の利用が多いのでMySQLで空間検索を試してみようという記事。

参考OSMデータをPostGISにインポートしてQGISで可視化する – QiitaQiita

Docker で MySQL v8 を起動する

DockerHubでMySQLを探すとすぐに見つかる。 現時点(2021.6)でのlatestは8.0.25のようなのでこれを利用する。

% docker pull mysql
Using default tag: latest
latest: Pulling from library/mysql
no matching manifest for linux/arm64/v8 in the manifest list entries

上記のようにいきなりつまずく。。。 Docker Hup の MySQLイメージであなたのPCに合うアーキテクチャ(arm64)のイメージは存在しませんよ、と言われる。これの回避は以下のように「x86_64版で良いです」と指定すればOK。

% docker pull mysql --platform linux/x86_64

とりあえず起動してみる

% docker run -d --name mysql -e MYSQL_ROOT_PASSWORD=mysql -p 3306:3306 mysql
WARNING: The requested image's platform (linux/amd64) does not match the detected host platform (linux/arm64/v8) and no specific platform was requested
84ed8d70e2b3459656ce800983096d4eeb7a9e270d5a6b5da02887e61cf969ec

起動時にもアーキテクチャの違いのWARNがでるが問題なく起動する。起動したコンテナに侵入しして起動を確認してみると無事に起動している事がわかる

% docker exec -it mysql /bin/bash
root@84ed8d70e2b3:/# mysql -u root -p
Enter password:   # password=mysql
mysql>
mysql> show variables like 'version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| version                 | 8.0.25                       |
| version_comment         | MySQL Community Server - GPL |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
| version_compile_zlib    | 1.2.11                       |
+-------------------------+------------------------------+
5 rows in set (0.03 sec)

直接MySQLのコンテナにログインして接続したが 3306 ポートマッピングをしているので自PCからも接続可能。その場合は以下のようにすればOK

# mysql client がインストールされていない場合は要インストール 
# brew install mysql-client

% mysql -u root -p -P 3306 -h 127.0.0.1
mysql>

空間情報(データ)の調達

今回はネタとしてリバースジオコーダー (緯度経度を与えるとどの市区町村かを応答できるような機能)を作ることを見据えてデータを調達することにする。MySQLでの空間情報を扱う型は GEOMETORY型と呼ばれておりその種類としてPOINT(点), LINESTRING(線), POLYGON(面(多角形))が定義できる。

その元データには地理情報を扱うに一般的なシェープファイル(shp)やGeoJsonを調達し、それからインポートすることが多い。

今回もそれに習うと国土数値情報ダウンロードサービスの行政区域データというのが提供されており今回の目的にマッチする。たくさんダウンロードの選択があるが最下にある

地域測地系年度ファイル容量ファイル名
全国世界測地系令和3年476.89MBN03-20210101_GML.zip

というデータをダウンロードする。ダウンロード後、中身を取り出すと以下のようにシェープファイルとGeoJsonどちらも提供されている事がわかる

% unzip N03-20210101_GML.zip% ls -l N03-20210101_GML/
total 2959312
-rw-r--r--@ 1 hiyuzawa  staff      11288  3  9 09:21 KS-META-N03-21_210101.xml
-rw-r--r--@ 1 hiyuzawa  staff    9208202  2 25 16:37 N03-21_210101.dbf
-rw-r--r--@ 1 hiyuzawa  staff  688544253  3  3 03:36 N03-21_210101.geojson
-rw-r--r--@ 1 hiyuzawa  staff        145  2 25 16:35 N03-21_210101.prj
-rw-r--r--@ 1 hiyuzawa  staff  243868588  2 25 16:37 N03-21_210101.shp
-rw-r--r--@ 1 hiyuzawa  staff     969364  2 25 16:37 N03-21_210101.shx
-rw-r--r--@ 1 hiyuzawa  staff  543407800  3  3 03:44 N03-21_210101.xml

GeoJsonは中身を見れるので試しに見てみると features の中にpropertiesとして属性値が5つとポリコンデータが与えられていることがわかる。5つの属性値が何を表しているかもデータをダウンロードしたサイトに記載があるので確認してみると良い。

また総ポリコン数は 121,158 個であることもわかる

% cat N03-20210101_GML/N03-21_210101.geojson | jq . | more
{
  "type": "FeatureCollection",
  "name": "N03-21_210101",
  "crs": {
    "type": "name",
    "properties": {
      "name": "urn:ogc:def:crs:EPSG::6668"
    }
  },
  "features": [
    {
      "type": "Feature",
      "properties": {
        "N03_001": "北海道",
        "N03_002": "石狩振興局",
        "N03_003": "札幌市",
        "N03_004": "中央区",
        "N03_007": "01101"
      },
      "geometry": {
        "type": "Polygon",
        "coordinates": [
          [
            [
              141.3423269384375,
              43.066815837880654
            ],
            [
              141.3428477816998,
              43.066810558860254
            ],
...

% cat N03-20210101_GML/N03-21_210101.geojson | jq '.features | length'
121158

シェープファイルはバイナリファイルなのでQGISなどを利用して開くとそのポリコンの内容が描画されて確認できる。

MySQLへ空間情報のインポートの方法検討

シェープファイル、GeoJsonが調達できたわけだがこれをMySQLにインポートするに便利なツールが用意されているのでそれを利用する。シェープファイルとGeoJsonでそれぞれ方法が以下のように異なる

  • GeoJson – MySQL Shell を利用する
  • シェープファイル – ogr2ogr (gdal) を利用する

どちらも試してみたがシェープファイルの方が分かりやすかったので今回はシェープファイルを利用する。gdal は空間情報を扱うライブラリで以前の記事でもMacへインストールして利用したのを紹介したがMySQLのドライバとか追加でインストールが必要とかそこそこインストールに苦労するのでこれもDocker Hubで良いイメージ(osgeo/gdal)があるのでDocker経由で利用する。

% docker pull osgeo/gdal
% docker run --name gdal -it osgeo/gdal /bin/bash
root@973d4c943459:/# ogr2ogr --version
GDAL 3.4.0dev-27f2e1737bd0d7e0c0..., released 2021/06/19
root@973d4c943459:/# ogr2ogr --formats | grep -i mysql
  MySQL -vector- (rw+): MySQL   # <--- これが必要

環境構築等の準備

MySQLにインポートする際に1つ事前作業が必要。シェープファイルに含まれる属性値がお役所データなのでSJISであり、MySQLでUTF8として管理したいためそのまま入れると文字化けしてしまう。この変換は色々方法があり一長一短のようだが今回はこの変換も ogr2ogr で行う

# 改めてコンテナの起動 (シェープファイルのデータフォルダを /work にマウント)
% docker run --name gdal -v `pwd`/N03-20210101_GML:/work -it osgeo/gdal /bin/bash

root@c7a4f9d8e995:/# cd /work/
root@c7a4f9d8e995:/work# ls -l N03-21_210101.shp
-rw-r--r-- 1 root root 243868588 Feb 25 07:37 N03-21_210101.shp  # 正しくローカルのデータがコンテナ内で参照できている

# シェープファイル内の属性値をSJIS(CP932)からUTF−8に変換
# そこそこ時間がかかります....
root@c7a4f9d8e995:/work# ogr2ogr -lco ENCODING=UTF-8 -oo ENCODING=CP932 N03-21_210101_utf8.shp N03-21_210101.shp

# 以下に完成 (今までshpファイル単体を説明に用いてきたが厳密にはこれらの複数のファイルが一体でシェープファイル)
root@c7a4f9d8e995:/work# ls -l N03-21_210101_utf8.*
-rw-r--r-- 1 root root         5 Jun 26 02:33 N03-21_210101_utf8.cpg
-rw-r--r-- 1 root root  10783256 Jun 26 02:43 N03-21_210101_utf8.dbf
-rw-r--r-- 1 root root       145 Jun 26 02:33 N03-21_210101_utf8.prj
-rw-r--r-- 1 root root 243868588 Jun 26 02:43 N03-21_210101_utf8.shp
-rw-r--r-- 1 root root    969364 Jun 26 02:43 N03-21_210101_utf8.shx

インポートするスクリプト(ogr2ogr)が存在するDockerコンテナとインポート先のMySQLのコンテナが異なるためdocker-compose で両者を通信可能な状態にする。docker-composeについてはQiitaの記事を参照。

シンプルな構成ファイル(docker-compose.yml)を作成する

version: "2"
services:
    mysql:
        image: mysql:latest
        command: mysqld --character-set-server=utf8 --collation-server=utf8_unicode_ci
        ports:
            - "3306:3306"
        environment:
            - MYSQL_ROOT_PASSWORD=mysql
        volumes:
             - ./my.cnf:/etc/mysql/conf.d/my.cnf
    gdal:
        image: osgeo/gdal:latest
        volumes:
            - "./N03-20210101_GML:/work"
        tty: true

MySQLをUTF-8言語設定するために引数付きで command オプションで起動している点、 my.cnf を独自に定義(下記参照)してマウントさせている点、 gdal は永続起動させるために tty: true が必要な点は注意

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

[client]
default-character-set=utf8

docker-compose を起動する

% docker-compose up -d
Creating network "tmp_default" with the default driver
Creating tmp_gdal_1  ... done
Creating tmp_mysql_1 ... done

MySQLへインポート

前置きが長くなったがいよいよインポートする。まずはMySQLにインポート先のDatabaseを定義する必要がある。行政区域は英語で Administrative district と言うらしいのでそれを名称としてDBを作る

% docker-compose exec mysql /bin/bash

root@1bf1d391ddb9:/# mysql -u root -p
mysql> create database administrative_district;
Query OK, 1 row affected (0.06 sec)

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| administrative_district |
| information_schema      |
| mysql                   |
| performance_schema      |
| sys                     |
+-------------------------+
5 rows in set (0.10 sec)

インポートは gdal 側のコンテナにログインして行う。

% docker-compose exec gdal /bin/bash

# cd /work/
# ogr2ogr -f "MySQL" MySQL:"administrative_district,host=mysql,user=root,password=mysql,port=3306" N03-21_
210101_utf8.shp

ogr2ogr で起動したコマンドが MySQLにシェープファイルをインポートするコマンドである。作成したデータベース名、MySQLの接続ホストはdocker-compose内の仮想ネットワークなのでmysqlで接続できる。

MySQLにインポートされた空間データを確認してみる

mysql> use administrative_district;

mysql> show tables;
+-----------------------------------+
| Tables_in_administrative_district |
+-----------------------------------+
| n03_21_210101_utf8                |
+-----------------------------------+
1 row in set (0.00 sec)

mysql> show fields from n03_21_210101_utf8;
+---------+-------------+------+-----+---------+----------------+
| Field   | Type        | Null | Key | Default | Extra          |
+---------+-------------+------+-----+---------+----------------+
| OGR_FID | int         | NO   | PRI | NULL    | auto_increment |
| SHAPE   | geometry    | NO   | MUL | NULL    |                |
| n03_001 | varchar(12) | YES  |     | NULL    |                |
| n03_002 | varchar(30) | YES  |     | NULL    |                |
| n03_003 | varchar(20) | YES  |     | NULL    |                |
| n03_004 | varchar(21) | YES  |     | NULL    |                |
| n03_007 | varchar(5)  | YES  |     | NULL    |                |
+---------+-------------+------+-----+---------+----------------+
7 rows in set (0.01 sec)

上記のように、インポートしたadministrative_districtというDBに接続すると自動でインポートしたファイル名に一致するTableが作成されている(n03_21_210101_utf8)。また、その中身はgeometory型のシェープファイル(POLIGON)が保存されているSHAPE列に加え、シェープファイル内に存在した5つのぞ属性値も列として保存されている。

属性値をもとに細かくデータ分析してみる。

mysql> select count(*) from n03_21_210101_utf8;
+----------+
| count(*) |
+----------+
|   121158 |
+----------+
総レコード数は121,158行。これは前述でGeoJsonのポリコン数を数えた値に一致する

mysql> select n03_001,n03_002,n03_003,n03_004,n03_007  from n03_21_210101_utf8 limit 1\G
*************************** 1. row ***************************
n03_001: 北海道
n03_002: 石狩振興局
n03_003: 札幌市
n03_004: 中央区
n03_007: 01101

各属性値は上記のようで、001が都道府県名 002は北海道の場合の支庁,003は政令指定都市名,004は市区町村名

都道府県別のポリコン数を見てみると県によってかなりのばらつきがあることがわかる。
mysql> select n03_001, count(*) from n03_21_210101_utf8 group by 1;
+--------------+----------+
| n03_001      | count(*) |
+--------------+----------+
| 北海道       |     9586 |
| 青森県       |     2239 |
| 岩手県       |     8230 |
| 宮城県       |     8346 |
| 秋田県       |     2463 |
| 山形県       |      726 |
| 福島県       |      226 |
| 茨城県       |      231 |
| 栃木県       |       28 |
| 群馬県       |       37 |
| 埼玉県       |       81 |
| 千葉県       |     1528 |
| 東京都       |     6177 |
| 神奈川県     |     1306 |
| 新潟県       |     2702 |
| 富山県       |       58 |
| 石川県       |     1704 |
| 福井県       |     2949 |
| 山梨県       |       36 |
| 長野県       |       79 |
| 岐阜県       |       48 |
| 静岡県       |     1755 |
| 愛知県       |     1117 |
| 三重県       |     7488 |
| 滋賀県       |       19 |
| 京都府       |      999 |
| 大阪府       |      143 |
| 兵庫県       |     2195 |
| 奈良県       |       40 |
| 和歌山県     |     4109 |
| 鳥取県       |      824 |
| 島根県       |     3622 |
| 岡山県       |      414 |
| 広島県       |      692 |
| 山口県       |     2993 |
| 徳島県       |     3516 |
| 香川県       |      759 |
| 愛媛県       |     4946 |
| 高知県       |     4535 |
| 福岡県       |     2419 |
| 佐賀県       |      315 |
| 長崎県       |     9189 |
| 熊本県       |     1972 |
| 大分県       |     2367 |
| 宮崎県       |     2541 |
| 鹿児島県     |     8136 |
| 沖縄県       |     5273 |
+--------------+----------+

最小のポリコン数を持つ滋賀県を見てみるとこの19行だけ。行政の統廃合が進んだ結果だろうか？
mysql> select n03_001,n03_002,n03_003,n03_004,n03_007 from n03_21_210101_utf8 where n03_007 like '25%';
+-----------+---------+-----------+-----------------+---------+
| n03_001   | n03_002 | n03_003   | n03_004         | n03_007 |
+-----------+---------+-----------+-----------------+---------+
| 滋賀県    | NULL    | NULL      | 大津市          | 25201   |
| 滋賀県    | NULL    | NULL      | 彦根市          | 25202   |
| 滋賀県    | NULL    | NULL      | 長浜市          | 25203   |
| 滋賀県    | NULL    | NULL      | 近江八幡市      | 25204   |
| 滋賀県    | NULL    | NULL      | 草津市          | 25206   |
| 滋賀県    | NULL    | NULL      | 守山市          | 25207   |
| 滋賀県    | NULL    | NULL      | 栗東市          | 25208   |
| 滋賀県    | NULL    | NULL      | 甲賀市          | 25209   |
| 滋賀県    | NULL    | NULL      | 野洲市          | 25210   |
| 滋賀県    | NULL    | NULL      | 湖南市          | 25211   |
| 滋賀県    | NULL    | NULL      | 高島市          | 25212   |
| 滋賀県    | NULL    | NULL      | 東近江市        | 25213   |
| 滋賀県    | NULL    | NULL      | 米原市          | 25214   |
| 滋賀県    | NULL    | 蒲生郡    | 日野町          | 25383   |
| 滋賀県    | NULL    | 蒲生郡    | 竜王町          | 25384   |
| 滋賀県    | NULL    | 愛知郡    | 愛荘町          | 25425   |
| 滋賀県    | NULL    | 犬上郡    | 豊郷町          | 25441   |
| 滋賀県    | NULL    | 犬上郡    | 甲良町          | 25442   |
| 滋賀県    | NULL    | 犬上郡    | 多賀町          | 25443   |
+-----------+---------+-----------+-----------------+---------+
19 rows in set (0.34 sec)

緯度経度を指定して内包される行政区域(ポリコン)を求める

例えば、まもなくオリンピックですが開会式のオリンピックスタジアムの緯度経度(35.678399922374496 139.71458649618353)が含まれるポリコンを求めるSQLは

mysql> select n03_001,n03_002,n03_003,n03_004,n03_007 from n03_21_210101_utf8 
    -> where st_within(ST_GEOMFROMTEXT('POINT(35.678399922374496 139.71458649618353)', 6668), SHAPE);
+-----------+---------+---------+-----------+---------+
| n03_001   | n03_002 | n03_003 | n03_004   | n03_007 |
+-----------+---------+---------+-----------+---------+
| 東京都    | NULL    | NULL    | 新宿区    | 13104   |
+-----------+---------+---------+-----------+---------+
1 row in set (0.08 sec)

と正しく新宿区が1行応答される。

st_within(ST_GEOMFROMTEXT('POINT(35.678399922374496 139.71458649618353)', 6668)

の部分で求めたい緯度経度をGEOMETORY型に変換しているが、6668は日本測地系の座標系に落とし込むためのオマジナイです(詳しくはSRIDを調べる)。その他、もう数値点試してみると

#愛媛、松山城付近
mysql> select n03_001,n03_003,n03_004,n03_007 from n03_21_210101_utf8
    -> where st_within(ST_GEOMFROMTEXT('POINT(33.850673376610466 132.76639856105783)', 6668), SHAPE);
+-----------+---------+-----------+---------+
| n03_001   | n03_003 | n03_004   | n03_007 |
+-----------+---------+-----------+---------+
| 愛媛県    | NULL    | 松山市    | 38201   |
+-----------+---------+-----------+---------+
1 row in set (0.22 sec)

# 琵琶湖の真ん中
mysql> select n03_001,n03_003,n03_004,n03_007 from n03_21_210101_utf8
    -> where st_within(ST_GEOMFROMTEXT('POINT(35.296228867125144 136.12556172982295)', 6668), SHAPE);
+-----------+---------+-----------+---------+
| n03_001   | n03_003 | n03_004   | n03_007 |
+-----------+---------+-----------+---------+
| 滋賀県    | NULL    | 高島市    | 25212   |
+-----------+---------+-----------+---------+
1 row in set (0.14 sec)

# 東京湾の真ん中 (ヒットなし)
mysql> select n03_001,n03_003,n03_004,n03_007 from n03_21_210101_utf8
    -> where st_within(ST_GEOMFROMTEXT('POINT(35.52788634606617 139.92387213583172)', 6668), SHAPE);
Empty set (0.03 sec)

なるほど。なかなかおもしろい。

おわりに

MySQL v8 にシェープファイルをインポートして空間検索を試してみた。空間検索といえばPostgreSQLが老舗で機能も豊富で性能も高いような印象を受けるがMySQLでも基本的なことはできそう。MySQL Workbenchを使えばMySQLに保存されているGeometory型が可視化することができる記事も数多く見つかるので時間があればまた試してみよう。

今回実験に当たり大いに参考にした記事はこちら。色々とこの記事では記載しなかった詳細は利用上の注意点などが書かれているので参考になる。

参考MySQL 8.0で強化されたGIS機能と使用事例のご紹介＋αSpeaker Deck

前の記事

遺伝的アルゴリズムでテトリス風ゲームをPLAYさせる

次の記事

Kaggle に初チャレンジ