MySQL 8.0で強化されたGIS機能と使用事例の紹介＋シェープファイルとGeoJSONファイルのインポート手順について

Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
MySQL 8.0で強化されたGIS機能と使用事例の紹介
とシェープファイルのインポート手順について
updated: 2019/07/26
Yoshiaki Yamasaki / 山﨑由章
MySQL Senior Solution Engineer, Asia Pacific and Japan

View Slide

MySQL 8.0で強化されたGIS機能と使用事例の紹介
＋シェープファイルとGeoJSONファイルのインポート
手順について
updated: 2019/07/26
Yoshiaki Yamasaki / 山﨑由章
MySQL Senior Solution Engineer, Asia Pacific and Japan

View Slide

Safe Harbor Statement
以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。
また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはでき
ません。以下の事項は、マテリアルやコード、機能を提供することをコミットメントするも
のではない為、購買決定を行う際の判断材料になさらないで下さい。
オラクル製品に関して記載されている機能の開発、リリースおよび時期については、
弊社の裁量により決定されます。

View Slide

はじめに
4

View Slide

今日伝えたいこと
• MySQLにもGIS機能があります！積極的に開発しています！！
• MySQLのGIS機能を使用した事例もあります！
• ご意見下さい！
– 本日は終日会場にいます
– 懇親会も参加します
• 試してみて気づいた点があれば、是非フィードバック下さい！
– フィードバックできるページ
• MySQL Bugs
https://bugs.mysql.com/
– バグ報告だけでなく、機能追加リクエストも受け付けています
5

View Slide

今日伝えたいこと
• GDAL 2.3.2がリリースされ、ogr2ogrを使用してMySQL 8.0へ
シェープファイルをインポートできるようになりました！
– 注意事項が4点あります
• MySQL ShellやJSON関数を使えば、GeoJSON形式のファイルもMySQLへ
インポートして活用できます！
• 周辺ツールに対するフィードバックも是非お願します！
6

View Slide

昨年の苦い（？）思い出
7

View Slide

8
• シェープファイルの取り込み、POINTデータのインサートに以下4つの
方法を試して頂きましたが、いずれもエラーになっていました
1．MySQL Workbench 8.0.11
2．QGIS 2.18.20、3.0.1
3．SQL実行（MySQL Workbenchから
4．org2ogr

View Slide

9
2．QGIS 2.18.20、3.0.1
3．SQL実行（MySQL Workbenchから
4．org2ogr
⇒2.～4．はGDALに依存した問題だったので、githubでissueを登録しました。
また、issueを登録してくれている方がいました。

View Slide

10
2．QGIS 2.18.20、3.0.1
3．SQL実行（MySQL Workbenchから）
4．org2ogr
⇒2.～4．はGDALに依存した問題だったので、githubでissueを登録しました。
また、issueを登録してくれている方がいました。
⇒これらのissueはGDAL 2.3.2で修正されました！！

View Slide

関連するissue
• ogr2ogr with MySQL 8 #782
https://github.com/OSGeo/gdal/issues/782
• Issue with Mysql import #899
• ogr2ogr with MySQL 8 (Wrong SRID) #905
• ogr2ogr with MySQL 8 (Wrong Axis Order) #906
⇒これらのissueはGDAL 2.3.2で修正済み
11

View Slide

MySQLのGIS機能の歴史
12

View Slide

MySQLの歴史
4.0
全文検索 (MyISAM)
複数テーブルUPDATE/DELETE
組み込みライブラリ型サーバ
Oracle
MySQL Sun
3.23
MyISAM
InnoDB
レプリケーション
5.1
プラグガブル・
ストレージエンジン・
アーキテクチャ
パーティショニング
タスクスケジューラ
5.6
全文検索(InnoDB)
memcached API
UNDO表領域
Global Transaction ID
マルチスレッドスレーブ
オンラインALTER TABLE
トランスポータブル表領域
5.5
InnoDBがデフォルトに
準同期型レプリケーション
PERFORMANCE_SCHEMA
1.0-3.22以前
ストレージエンジン (ISAM, HEAP)
マルチスレッド
Windows対応/64bit対応
日本語文字コード (SJIS/UJIS)
5.0
ストアドプロシージャ
ストアドファンクション
カーソル/トリガ/ビュー
XAトランザクション
INFORMATION_SCHEMA
4.1
GIS(MyISAM)
Unicode対応
サブクエリ
CSV, ARCHIVE
ndbcluster
1995 2000 2005 2010 2015
5.7
2015年10月21日 GA
全文検索CJK対応/GIS (InnoDB)
新コストモデルオプティマイザ
ロスレスレプリケーション
マルチソースレプリケーション
グループレプリケーション
セキュリティ強化
データディクショナリ
NoSQLオプション
・ 1995年：スウェーデンにてMySQL AB設立
・(2005年：オラクルがInnobase Oyを買収)
・ 2008年：Sun MicrosystemsがMySQL ABを買収
・ 2010年：オラクルがSun Microsystemsを買収
8.0
2018年4月19日 GA
トランザクショナルDD
MySQLドキュメントストア
Unicode 9.0サポート
国ごとの照合順序を追加
CTE、Window関数
GIS機能の拡張 (InnoDB)
13

View Slide

GIS機能の歴史
• MySQL 5.7
– Boost.GeometryというC++のオープンソースライブラリを採用して独自実装をやめ、
InnoDBでGIS関連機能を再実装した
• geometryデータ型
• Spatialインデックス
• Spatial関数（使える関数の種類も増加）
• GeoHashサポート
• GeoJSONサポート
※Boost.Geometryコミュニティとも活発に交流し、MySQLチームから
Boost.Geometryへのコントリビュートも行っている
14

View Slide

GIS機能の歴史
• MySQL 8.0
– OpenGIS標準準拠
– 演算、データ変換に役立つ各種のSpatial関数の追加
• st_x(geom, x)
• st_y(geom, y)
• st_srid(geom, srid)
– MySQL 5.7で非推奨になった関数の廃止
–Geography サポート
• st_distance() 関数
–Spatial Data、Spatial Index、Spatial関数のSRIDサポート
15

View Slide

MySQLは積極的に
GIS機能を開発しています！！
16

View Slide

GIS機能の紹介
17

View Slide

geometryデータ型
• OpenGISジオメトリモデルに基づいた以下のデータが使用可能
– GEOMETRY、GEOMETRYCOLLECTION
– 点：POINT、MULTIPOINT
– 線：LINESTRING、MULTILINESTRING
– 多角形：POLYGON、MULTIPOLYGON
※GEOMETRYには、POINT、LINESTRING、POLYGONを格納可能
※GEOMETRYCOLLECTIONには、MULTIPOINT、MULTILINESTRING、MULTIPOLYGONを
格納可能
18

View Slide

Spatial関数
• ジオメトリデータを扱える各種の関数
• 例
– ST_Distance()
• 2つのジオメトリを入力し、2地点間の距離を出力
– ST_Distance_Sphere()
• 2つのジオメトリを入力し、2地点間の最小球面距離を出力
– ST_Contains()
• あるジオメトリに別のジオメトリが含まれているかどうかを判定する
• MySQL 8.0で使えるSpatial関数一覧
– MySQL 8.0 Reference Manual / ... / 12.15.1 Spatial Function Reference
https://dev.mysql.com/doc/refman/8.0/en/spatial-function-reference.html
19

View Slide

GeoHash
• 経緯度の情報を文字列化したもの
– 例：インタークロス・クリエイティブ・センター（経度：141.3858、緯度：43.0560）の
GeoHash
⇒ xpsscq1grujs71ek4btgejbpbpbpbpbp<<後略>>
• 特徴
– 文字列なので、データベースで扱いやすい
（インデックスも活用可能）
– GeoHashの情報は点ではなく範囲（矩形のエリア）を表す
– 文字列の桁数で精度を変えることができる
• 10桁を超えると矩形の一辺が1m以下になるので、経緯度の変わりとしても十分利用可能
• 近接エリアの判定等に利用可能
20

View Slide

GeoHash
• MySQL 5.7以降では、GeoHashを扱うための関数が使用できる
– ST_GeoHash()：経度、緯度(POINT型のデータでも可)を入力し、GeoHashを出力
– ST_LatFromGeoHash()：GeoHashを入力し、経度を出力
– ST_LongFromGeoHash()：GeoHashを入力し、緯度を出力
– ST_PointFromGeoHash()：GeoHashを入力し、POINT型のデータを出力
※詳細を解説しているマニュアル
MySQL 8.0 Reference Manual / ... / 12.15.10 Spatial Geohash Functions
https://dev.mysql.com/doc/refman/8.0/en/spatial-geohash-functions.html
21

View Slide

GeoJSON
• 空間データを扱うためのフォーマットの一つ
• JSONによる表現で空間データを扱える
– 例：インタークロス・クリエイティブ・センター（経度：141.3858、緯度：43.0560）の
GeoJSONによる表現
⇒ {"type": "Point", "coordinates": [141.3858, 43.0560]}
22

View Slide

GeoJSON
• MySQL 5.7以降では、GeoJSONを扱うための関数が使用できる
– ST_AsGeoJSON()：ジオメトリ型のデータを入力し、GeoJSONデータを出力
– ST_GeomFromGeoJSON()：GeoJSONデータを入力し、ジオメトリ型のデータを出力
※詳細を解説しているマニュアル
MySQL 8.0 Reference Manual / ... / 12.15.11 Spatial GeoJSON Functions
https://dev.mysql.com/doc/refman/8.0/en/spatial-geojson-functions.html
23

View Slide

MySQL ドキュメントストアの活用
24

View Slide

MySQL ドキュメントストアとは？
MySQLでJSONドキュメントを扱う簡単な方法
#1
New Feature
25

View Slide

リレーショナル
データベース
ハイブリッド
データベース
ドキュメント
データベース
SQL
リレーショナルテーブル
外部キー
NoSQL
JSONドキュメント
スキーマレスJSONコレクション
26

View Slide

GeoJSONと関連して特に活用できそうな機能
• MySQL ShellのJSONインポートユーティリティ
– MySQL ShellというコマンドラインツールにJSONファイルをインポートするための
機能がある
• JSON_TABLE()関数
– JSON形式のデータをRDBMSの表形式に変換可能な関数
※JSON_TABLE()関数以外にも、JSON関数は色々あります
https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html
27

View Slide

MySQL Workbenchの活用
28

View Slide

MySQL Workbench
• 管理ツール
• SQLエディタ
• Performance Dashboard
• Visual Explain
• GIS Viewer
• ER図作成、フォワード/
リバースエンジニアリング、
など豊富な機能
データベースアーキテクト、開発者、DBA のための統合ビジュアルツール
商用版のみの機能：
DBドキュメント出力、データモデルの検証、
MySQL Enterprise Backup GUI、MySQL Enterprise Audit GUI
29

View Slide

"Spatial Viewer" and "Geometry Viewer"
• ジオメトリ型のデータを図示可能
30

View Slide

MySQLのGIS機能を使用したシステムの例
31

View Slide

アップルップルアプリケーション
CMSソフトウェア「a-blog cms」の中でMySQLのgeometry型を
使用しています。投稿記事に位置情報を持たせることで、位置
情報を使って記事を絞り込むことなどができます。この機能を
活用し、ユーザーの近くの情報を表示する、といったWebページ
作成を可能にしています。
位置情報を使用したWebページの例
名古屋の情報を発信しているサイトSpyMasterでは、この機能を
活用して、ユーザーの近くのスポット情報を表示できるようにして
います。
SpyMaster
https://spymaster.jp
MySQLのGEOMETRY型とJavaScriptのGeolocation APIの活用事例
https://speakerdeck.com/steelydylan/mysqlfalse-geometry-xing-tojavascriptfalse-
geolocation-api-falsehuo-yong-shi-li
32

View Slide

シェープファイルのインポート手順
33

View Slide

必要なもの
• MySQL 8.0 ※今日のデモでは8.0.15を使用
• MySQL Workbench 8.0 ※今日のデモでは8.0.15を使用
• GDAL 2.3.2以降 ※今日のデモでは2.4.2/64bit版を使用
• シェープファイル ※今日のデモではe-Statの境界データ（小地域、北海道）を
使用
34

View Slide

デモ手順
1．シェープファイルの文字コードがUTF-8以外の場合、UTF-8に変換する
2． MySQL 8.0に接続し、シェープファイルをインポートするための
データベースを作成する（データベース名は任意の名前）
3．ogr2ogrを使ってシェープファイルをインポート
4． MySQLでインポートされたデータを確認
5． Googleで「北海道札幌市白石区東札幌5条1丁目」を検索して、
形状を確認
6． MySQL Workbenchから以下のSQLを実行し、Form Editiorで
ポリゴンデータの形状を確認
35

View Slide

デモ手順
1．シェープファイルの文字コードがUTF-8以外の場合、UTF-8に変換する
[実行例]
> ogr2ogr -f "ESRI Shapefile" -lco ENCODING=UTF-8 -oo ENCODING=CP932
h27ka01_utf8.shp h27ka01.shp
※参考情報：ogr2ogr 備忘録
https://qiita.com/tohka383/items/d3d1bf80db2cfb416330#
※以下環境での実行例
- ローカルのMySQLサーバーへ接続、3306ポートを使用（デフォルト）
- MySQLのユーザー名：root、パスワード：root、認証方式：mysql_native_password
- シェープファイル：h27ka01.shp
36

View Slide

補足
• FOSS4G Hokkaido 2019 での発表時にSlidoで以下のコメントを頂きました
– 「Shapeの文字コードをUTF-8に変更するとカラム名やカラムの長さを突破することが
あるので推奨できません。GeoJSONにしてからインポート推奨がいいと思います。」
• GeoJSONに変換してインポートする手順についても整理して、
FOSS4G TOKAI 2019で発表したいと思います
37

View Slide

デモ手順
[実行例]
mysql> create database geotest;
Query OK, 1 row affected (0.02 sec)
38

View Slide

デモ手順
3．ogr2ogrを使ってシェープファイルをインポート
[実行例]
> ogr2ogr -f "MySQL"
MySQL:"geotest,host=127.0.0.1,user=root,password=root,port=3306"
h27ka01_utf8.shp
39

View Slide

mysql> SELECT * FROM geometry_columns;
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
| F_TABLE_CATALOG | F_TABLE_SCHEMA | F_TABLE_NAME | F_GEOMETRY_COLUMN | COORD_DIMENSION | SRID | TYPE |
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
| NULL | NULL | h27ka01_utf8 | SHAPE | 2 | 4612 | POLYGON |
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
1 row in set (0.00 sec)
mysql> SELECT OGR_FID, ST_AsText(SHAPE), pref_name, city_name, s_name FROM geotest.h27ka01_utf8 WHERE
City_name='白石区' AND S_name LIKE '東札幌五条１丁目'¥G
*************************** 1. row ***************************
OGR_FID: 3427
ST_AsText(SHAPE): POLYGON((43.0550809625361 141.387224543675,43.0550502503096
141.3869756233,43.0547956186902 141.386634897518,43.0554402089588 141.385733087378,43.0558313933916
141.385147563851,43.0562073782988 141.384561479229,43.0562504366654 141.384619566842,43.0564183414694
141.384846424007,43.0569802153486 141.385605384596,43.0573103628311 141.386051346905,43.0569143505206
141.38664998984,43.0567840871079 141.386797496904,43.0565725208237 141.387010208612,43.0564259538551
141.387140872716,43.0562709902474 141.387249306647,43.0559812036326 141.387404321055,43.0559001806069
141.3874413246,43.0560030294864 141.388388102444,43.0552097870596 141.388510734379,43.0551769103868
141.388142060102,43.0550809625361 141.387224543675))
pref_name: 北海道
city_name: 白石区
s_name: 東札幌五条１丁目
[実行例の続き]
41
※注：GDAL 2.4.2/32bit版使用時に、SRIDがNULLになる事象を確認しています。
SRIDを含むデータの場合、この部分にSRIDが入っていることを確認して下さい。

View Slide

デモ手順
5． Googleで「北海道札幌市白石区東札幌5条1丁目」を検索して、
形状を確認
6． MySQL Workbenchから以下のSQLを実行し、Form Editiorでポリゴン
データの形状を確認
SELECT
*
FROM
geotest.h27ka01_utf8
WHERE
City_name = '白石区'
AND
S_name = '東札幌五条１丁目';
42

View Slide

参考：GoogleとMySQL Workbenchでの確認結果
43
Googleでの確認結果 MySQL Workbenchでの確認結果

View Slide

注意事項と補足
44

View Slide

注意事項1
• ogr2ogrでShift_JISのシェープファイルをインポートした時に、文字化けにより
エラーが発生する場合があります
– 発生したエラー
• ERROR 1: MySQL error message:Data too long for column 'XXXXX' at row 1 Description: INSERT INTO<後略>
⇒事前にシェープファイルの文字コードをShift_JISからUTF-8に変換することで回避可能
※関連するissue
- ogr2ogr with MySQL 8 (Shape file import error) #1089
- Feature request: Support character encodings with MySQL driver #1633
45

View Slide

注意事項2
• MySQL 8.0でデフォルトの認証方式を使用している場合、ogr2ogrで
接続できません
– MySQL 8.0の設定で「default-authentication-plugin=mysql_native_password」
としてからMySQLユーザーを作成することで回避可能
– ogr2ogrがMySQL 8.0のデフォルト認証方式に対応したドライバを使用していないことが
原因です。本問題に関するissueを登録済みです。
• ogr2ogr with MySQL 8 (MySQL connect failed with caching_sha2_password plugin) #910
• libmysql-client version is old for MySQL 8.0 #132
https://github.com/gisinternals/buildsystem/issues/132
46

View Slide

注意事項3
• ogr2ogrでシェープファイルをインポートした際にMySQLに作成された
テーブルのgeometry型の列にSRIDが明示的に定義されていません
– SRIDが明示的に定義されていれば、異なるSRIDのデータの混入を防げます
• MySQLへのデータINSERT時にエラーになります
– SRIDが明示的に定義されていない場合、Spatialインデックスが使用できません
• 今回の例の場合、以下の手順で後からSRIDを追加してSpatialインデックスを再作成できます
(Spatialインデックスがついた状態でSRIDを追加できないため、一旦インデックスを削除して再作成)
– mysql> ALTER TABLE h27ka01_utf8 DROP INDEX SHAPE;
– mysql> ALTER TABLE h27ka01_utf8 MODIFY SHAPE GEOMETRY NOT NULL SRID 4612;
– mysql> ALTER TABLE h27ka01_utf8 ADD SPATIAL INDEX (SHAPE);
– issue登録済みです
• ogr2ogr with MySQL 8: POINT column doesn't have SRID attribute #1015
47

View Slide

注意事項4
• ST_Intersects()、ST_Overlaps() の実行速度が非常に遅いという問題が
発覚しています
• 本問題に関して、バグ報告を登録して調査中です
– 今後のFOSS4Gで調査状況について報告します
– Bug#96311: ST_Intersects() is very slow on MySQL 8.0
https://bugs.mysql.com/bug.php?id=96311
• ST_Within()は高速に実行できています。 ST_Within()で代替できるケース
では、現状はST_Within() を使用下さい。
※参考情報
- MySQL8.0の空間検索が遅い？の続き1
https://qiita.com/miyauchi/items/893f12679cb21c12c454
- MySQL 8.0にシェープファイルをインポートしてみる
https://qiita.com/miyauchi/items/c8349e1e6339bdf26a20
48

View Slide

GeoJSONファイルのインポート手順
49

View Slide

必要なもの
• MySQL 8.0 ※今日のデモでは8.0.15を使用
• MySQL Shell 8.0 ※今日のデモでは8.0.15を使用
• MySQL Workbench 8.0 ※今日のデモでは8.0.15を使用
• GeoJSONファイル ※今日のデモでは以下で公開されているjapan.geojsonを使用
（出典元：地球地図日本）
https://github.com/dataofjapan/land
50

View Slide

デモ手順
[実行例]
mysql> create database geotest;
Query OK, 1 row affected (0.02 sec)
- MySQLのユーザー名：root、パスワード：root
- GeoJSONファイル：C:¥work¥japan.geojson
51

View Slide

デモ手順
2．MySQL Shellを使ってJSONファイルをインポートする
C:¥> mysqlsh -u root -p --import japan.geojson --schema=geotest
Creating a session to 'root@localhost/geotest'
Please provide the password for 'root@localhost': ****
Save password for 'root@localhost'? [Y]es/[N]o/Ne[v]er (default No):
Fetching schema names for autocompletion... Press ^C to stop.
Your MySQL connection id is 40 (X protocol)
Server version: 8.0.15-commercial MySQL Enterprise Server - Commercial
Default schema `geotest` accessible through db.
Importing from file "C:¥work¥japan.geojson" to collection `geotest`.`japan` in MySQL
Server at localhost:33060
.. 1
Processed 13.05 MB in 1 document in 1.2041 sec (0.83 document/s)
Total successfully imported documents 1 (0.83 document/s)
[実行例]
52

View Slide

デモ手順
mysql> USE geotest;
mysql> SHOW TABLES;
+-------------------+
| Tables_in_geotest |
+-------------------+
| japan |
+-------------------+
mysql> SHOW CREATE TABLE geotest.japan¥G
*************************** 1. row ***************************
Table: japan
Create Table: CREATE TABLE `japan` (
`doc` json DEFAULT NULL,
`_id` varbinary(32) GENERATED ALWAYS AS (json_unquote(json_extract(`doc`,_utf8mb4'$._id'))) STORED NOT
NULL,
PRIMARY KEY (`_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
[実行例]
53

View Slide

mysql> SELECT COUNT(*) FROM geotest.japan;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
[実行例の続き]
54

View Slide

デモ手順
4． JSONエディタで元データを確認
55

View Slide

デモ手順
5． MySQL WorkbenchからJSON_TABLE関数を使った以下のSQLを実行
SELECT j.id, j.nam_ja, ST_GeomFromGeoJSON(j.geometry)
FROM test.japan,
JSON_TABLE(doc, '$.features[*]' COLUMNS (
id int PATH '$.properties.id',
nam_ja VARCHAR(20) PATH '$.properties.nam_ja',
geometry JSON PATH '$.geometry')
) j
ORDER BY id
;
56

View Slide

参考：MySQL Workbenchでの確認結果（Result Grid）
57
MySQL Workbenchでの確認結果（Result Grid）

View Slide

参考：MySQL Workbenchでの確認結果（Form Editor）
58
MySQL Workbenchでの確認結果（Form Editor）

View Slide

まとめ
59

View Slide

まとめ
• MySQLはGIS機能を積極的に開発しています！
• シェープファイル、GeoJSONファイルもインポートできるので、
MySQLを使ったGIS実装も是非お試しください！！
– 試してみておかしな点等あれば、フィードバックを頂けると助かります
• ST_Intersects()、ST_Overlaps()の実行速度が非常に遅いという問題
については、今後のFOSS4Gで調査状況を報告します
60

View Slide

また北海道に呼んで下さい！
ありがとうございました！！
61

View Slide

おまけ
62

View Slide

昨日のハンズオンセミナー参加者の作品
• 昨日以下の「ハンズオンセミナー」と「もくもく会」を開催しました
– JavaScriptとMySQLでGISを作ってみよう
• 当日の説明資料：資料1、資料2、資料3
• 参加者の方が午後のもくもく会で製作された作品です
– Sapporo City WiFi Map
https://sap-wifi-map.tacck.net/
• こちらはローカルのJSONファイルを参照していてまだMySQLまでは
使っていないそうですが、ハンズオンをきっかけにGISを実装される方が
出てきたことを嬉しく思います
63

View Slide


View Slide

MySQL 8.0で強化されたGIS機能と使用事例の紹介＋シェープファイルとGeoJSONファイルのインポート手順について

MySQL 8.0で強化されたGIS機能と使用事例の紹介＋シェープファイルとGeoJSONファイルのインポート手順について

YoshiakiYamasaki

More Decks by YoshiakiYamasaki

Other Decks in Technology

Featured

Transcript