Slide 1
Slide 1 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
位置情報と連動したアプリケーション開発に
役立つMySQL 8.0のGIS機能
MySQL & PostgreSQL Casual Talks in Fukuoka vol.1
2019/09/10
Yoshiaki Yamasaki / 山﨑 由章
MySQL Principal Solution Engineer, Asia Pacific and Japan
Slide 2
Slide 2 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Safe Harbor Statement
以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。
また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはでき
ません。以下の事項は、マテリアルやコード、機能を提供することをコミットメントするも
のではない為、購買決定を行う際の判断材料になさらないで下さい。
オラクル製品に関して記載されている機能の開発、リリースおよび時期については、
弊社の裁量により決定されます。
2
Slide 3
Slide 3 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Program Agenda
MySQLのGIS機能の歴史
MySQLのGIS機能の使用事例
アプリケーション開発に役立つMySQLのGIS基礎知識
シェープファイルのインポート手順
GeoJSONファイルのインポート手順
まとめ
1
2
3
4
5
3
6
Slide 4
Slide 4 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Program Agenda
MySQLのGIS機能の歴史
MySQLのGIS機能の使用事例
アプリケーション開発に役立つMySQLのGIS基礎知識
シェープファイルのインポート手順
GeoJSONファイルのインポート手順
まとめ
1
2
3
4
5
4
6
Slide 5
Slide 5 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
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)
5
Slide 6
Slide 6 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
GIS機能の歴史
• MySQL 4.1
– MyISAMでのみGIS機能が使えるようになった
• geometryデータ型
• Spatialインデックス
• Spatial関数
• MySQL 5.0
– InnoDBでもgeometryデータ型、Spatial関数を扱えるようになった
• Spatialインデックスは使用できず
※地理情報システムに関するオープンな規格であるOpenGISに基づいた実装が
されていたが、MySQL開発チームによる独自実装であった
6
Slide 7
Slide 7 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
GIS機能の歴史
• MySQL 5.7
– Boost.GeometryというC++のオープンソースライブラリを採用して独自実装をやめ、
InnoDBでGIS関連機能を再実装した
• geometryデータ型
• Spatialインデックス
• Spatial関数（使える関数の種類も増加）
• GeoHashサポート
• GeoJSONサポート
※Boost.Geometryコミュニティとも活発に交流し、MySQLチームから
Boost.Geometryへのコントリビュートも行っている
7
Slide 8
Slide 8 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
GIS機能の歴史
• MySQL 5.7
– 挙動が不明確で分かり難いSpatial関数は非推奨となった
• 例：Contains()は、以下のどちらの関数と同じ動きをするか分かり難いので廃止予定となった
– MBRContains
– ST_Contains()
8
Slide 9
Slide 9 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
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サポート
9
Slide 10
Slide 10 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
MySQLは積極的に
GIS機能を開発しています！！
10
Slide 11
Slide 11 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Program Agenda
MySQLのGIS機能の歴史
MySQLのGIS機能の使用事例
アプリケーション開発に役立つMySQLのGIS基礎知識
シェープファイルのインポート手順
GeoJSONファイルのインポート手順
まとめ
1
2
3
4
5
11
6
Slide 12
Slide 12 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
アップルップル アプリケーション
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
12
Slide 13
Slide 13 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
ハンズオンセミナー参加者の作品
• FOSS4G Hokkaido 2019で「ハンズオンセミナー」と「もくもく会」を開催しました
– JavaScriptとMySQLでGISを作ってみよう
• 当日の説明資料：資料1、資料2、資料3
• 参加者の方が午後のもくもく会で製作された作品です
– Sapporo City WiFi Map
https://sap-wifi-map.tacck.net/
13
Slide 14
Slide 14 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
ハンズオンセミナー参加者の作品
• FOSS4G Hokkaido 2019で「ハンズオンセミナー」と「もくもく会」を開催しました
– JavaScriptとMySQLでGISを作ってみよう
• 当日の説明資料：資料1、資料2、資料3
• 参加者の方が午後のもくもく会で製作された作品です
– Sapporo City WiFi Map
https://sap-wifi-map.tacck.net/
• こちらはローカルのJSONファイルを参照していてまだMySQLまでは使って
いませんでした。しかしその後、、、
14
Slide 15
Slide 15 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
ハンズオンセミナー参加者の作品
15
• MySQLを使った作品も作って下さいました
– SORACOM LTE-M Button と MySQL 8.0 を使って二点間の距離を測る
https://blog.tacck.net/archives/417
Slide 16
Slide 16 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Program Agenda
MySQLのGIS機能の歴史
MySQLのGIS機能の使用事例
アプリケーション開発に役立つMySQLのGIS基礎知識
シェープファイルのインポート手順
GeoJSONファイルのインポート手順
まとめ
1
2
3
4
5
16
6
Slide 17
Slide 17 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
アプリケーション開発に役立つMySQLのGIS基礎知識
• MySQLで扱える空間データ型
• 空間データの表現
• SRIDとは？
• MySQLで使えるSpatial関数
• MySQL Workbenchの活用
• どんなことが出来るか？
17
Slide 18
Slide 18 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
空間データ型（Spatial Data Types）
• POINT：点
• LINESTRING：線
• POLYGON：多角形
18
Slide 19
Slide 19 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
空間データ型（Spatial Data Types）
• POINT：点
– 例：緯度、経度
• LINESTRING：線
– 例：ルート（道筋）
• POLYGON：多角形
– 例：市町村の区画
19
Slide 20
Slide 20 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
空間データ型（Spatial Data Types）
• POINT：点
– 例：緯度、経度
• LINESTRING：線
– 例：ルート（道筋）
• POLYGON：多角形
– 例：市町村の区画
• GEOMETRY：POINT、LINESTRING、POLYGONをまとめて扱える
20
Slide 21
Slide 21 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
空間データ型（Spatial Data Types）
• 集合を扱えるデータ型
– MULTIPOINT：POINTの集合
– MULTILINESTRING：LINESTRINGの集合
– MULTIPOLYGON：POLYGONの集合
– GEOMETRYCOLLECTION： GEOMETRYの集合
21
Slide 22
Slide 22 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
空間データの表現
• WKT（Well-Known Text）
• WKB（Well-Known Binary）
• MySQLの内部表現
22
Slide 23
Slide 23 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
空間データの表現
• WKT（Well-Known Text）
– 幾何学オブジェクトをテキストで表現するための仕様
• WKB（Well-Known Binary）
– 幾何学オブジェクトをバイナリで表現するための仕様
• MySQLの内部表現
– WKBの先頭にSRIDを追加したもの
23
Slide 24
Slide 24 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
WKTの例
• POINT(15 20) ※区切りはスペース
• LINESTRING(0 0, 10 10, 20 25, 50 60)
• POLYGON((0 0,10 0,10 10,0 10,0 0)) ※最初の点に戻る
– POLYGON((0 0,10 0,10 10,0 10,0 0),(5 5,7 5,7 7,5 7, 5 5)) ※中をくりぬくことも可能
24
Slide 25
Slide 25 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
MySQLでデータを扱う時に必要となるSpatial関数
• WKTから空間データを生成する関数
– ST_GeomFromText()
– ST_PointFromText()
– ST_LineStringFromText()
– ST_PolygonFromText()
– ST_MultiPointFromText()
– ST_MultiLineStringFromText()
– ST_MultiPolygonFromText()
– ST_GeometryCollectionFromText()
25
Slide 26
Slide 26 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
MySQLでデータを扱う時に必要となるSpatial関数
• WKBから空間データを生成する関数
– ST_GeomFromWKB()
– ST_PointFromWKB()
– ST_LineStringFromWKB()
– ST_PolygonFromWKB()
– ST_MultiPointFromWKB()
– ST_MultiLineStringFromWKB()
– ST_MultiPolygonFromWKB()
– ST_GeometryCollectionFromWKB()
26
Slide 27
Slide 27 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
MySQLでデータを扱う時に必要となるSpatial関数
• データを変換する関数
– ST_AsText()
– ST_AsBinary()
– ST_SwapXY()
27
Slide 28
Slide 28 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
MySQLでデータを扱う時に必要となるSpatial関数
• 使用例
28
mysql> SELECT ST_GeomFromText('LineString(1 1,2 2,3 3)');
+---------------------------------------------------------------+
| ST_GeomFromText('LineString(1 1,2 2,3 3)') |
+---------------------------------------------------------------+
| ð? ð? @ @ @ @ |
+---------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT ST_AsText(ST_GeomFromText('LineString(1 1,2 2,3 3)'));
+-------------------------------------------------------+
| ST_AsText(ST_GeomFromText('LineString(1 1,2 2,3 3)')) |
+-------------------------------------------------------+
| LINESTRING(1 1,2 2,3 3) |
+-------------------------------------------------------+
1 row in set (0.00 sec)
Slide 29
Slide 29 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
SRID
• 空間参照系(Spatial Reference System)の識別コード(ID)
• 空間参照系は測地系と座標系の組合せからなる
• この話だけで1セッション語れますが、、、
29
Slide 30
Slide 30 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
SRID
• 空間参照系(Spatial Reference System)の識別コード(ID)
• 空間参照系は測地系と座標系の組合せからなる
• この話だけで1セッション語れますが、、、今日は以下のみ紹介
※地理座標系：3次元で位置を特定する
※投影座標系：2次元で(平面の地図に投影して)位置を特定する
30
SRID 測地系 座標系 備考
6668 JGD2011 地理座標系 東日本大震災での地殻変化に対応
4612 JGD2000 地理座標系 日本でよく使われる
4326 WGS84 地理座標系 GPSで使用されている
3857 WGS84 投影座標系
球面(半径6378137m)メルカトル図法
Google Maps等各種ウェブ地図アプリケーションで
使用されている
Slide 31
Slide 31 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
SRID
• MySQL 8.0から、データ型定義時にSRIDを指定可能
– SRIDを指定しないと空間インデックスが使用できないため、SRIDを指定することを推奨
31
Slide 32
Slide 32 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
参考情報
• 周辺知識から理解するMySQL の GIS機能 ～ClubMySQL #4～
https://www.slideshare.net/sakaik/mysql-gis-clubmysql-4
32
Slide 33
Slide 33 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
MySQLで使えるSpatial関数
• ジオメトリデータを扱える各種の関数
• 例
– ST_Length()：引数としてLINESTRINGを与えることで、長さを出力
– ST_Distance()：2つのジオメトリを入力し、2地点間の距離を出力
– ST_Intersects()：2つのジオメトリが空間的に交差しているかどうかを判定
• ST_Overlaps() ＋ ST_Within()
– ST_Overlaps()：2つのジオメトリが重なっているかどうかを判定
– ST_Within()：あるジオメトリが別のジオメトリの内側に存在するかを判定
33
Slide 34
Slide 34 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
MySQLで使えるSpatial関数
• 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
34
Slide 35
Slide 35 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
GeoHash
• 経緯度の情報を文字列化したもの
– 例：JRJP博多ビル12F（経度：130.418976、緯度：33.589527）のGeoHash
⇒ wvuxpfb1nmsvm8 <<後略>>
• 特徴
– 文字列なので、データベースで扱いやすい
（インデックスも活用可能）
– GeoHashの情報は点ではなく範囲（矩形のエリア）を表す
– 文字列の桁数で精度を変えることができる
• 10桁を超えると矩形の一辺が1m以下になるので、経緯度の変わりとしても十分利用可能
• 近接エリアの判定等に利用可能
35
Slide 36
Slide 36 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
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
36
Slide 37
Slide 37 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
GeoJSON
• 空間データを扱うためのフォーマットの一つ
• JSONによる表現で空間データを扱える
– 例： JRJP博多ビル12F（経度：130.418976、緯度：33.589527）のGeoJSONによる表現
⇒ {"type": "Point", "coordinates": [130.418976, 33.589527]}
37
Slide 38
Slide 38 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
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
38
Slide 39
Slide 39 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
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
39
Slide 40
Slide 40 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
GeoJSONと関連して特に活用できそうな機能
• MySQL ShellのJSONインポートユーティリティ
– MySQL ShellというコマンドラインツールにJSONファイルをインポートするための
機能がある
• JSON_TABLE()関数
– JSON形式のデータをRDBMSの表形式に変換可能な関数
⇒GeoJSON形式のファイルをインポートした後で、RDBの形式に変換可能
※JSON_TABLE()関数以外にも、JSON関数は色々あります
https://dev.mysql.com/doc/refman/8.0/en/json-function-reference.html
40
Slide 41
Slide 41 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
MySQL Workbenchの活用
41
Slide 42
Slide 42 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
MySQL Workbench
• 管理ツール
• SQLエディタ
• Performance Dashboard
• Visual Explain
• GIS Viewer
• ER図作成、フォワード/
リバースエンジニアリング、
など豊富な機能
データベースアーキテクト、開発者、DBA のための統合ビジュアルツール
商用版のみの機能：
DBドキュメント出力、データモデルの検証、
MySQL Enterprise Backup GUI、MySQL Enterprise Audit GUI
42
Slide 43
Slide 43 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
"Spatial Viewer" and "Geometry Viewer"
• ジオメトリ型のデータを図示可能
43
Slide 44
Slide 44 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
どんなことが出来るか？
44
Slide 45
Slide 45 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
どんなことが出来るか？
• 位置情報（緯度、経度）をMySQLに格納して距離計算する
– 半径〇m以内にある（近くにある）お店を探す
– 最も近いお店を探す
– ある2点間の直線距離を計測する
• 位置情報をMySQLに格納し、特定の領域内のオブジェクトだけを抽出する
– 表示されている地図領域内に存在する店舗だけを探す
• 市町村の境界データと位置情報をMySQLに格納し、特定区域内の
対象オブジェクトだけを抽出する
– 福岡県福岡市博多区にある店舗だけを探す
などなど
45
Slide 46
Slide 46 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Program Agenda
MySQLのGIS機能の歴史
MySQLのGIS機能の使用事例
アプリケーション開発に役立つMySQLのGIS基礎知識
シェープファイルのインポート手順
GeoJSONファイルのインポート手順
まとめ
1
2
3
4
5
46
6
Slide 47
Slide 47 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
必要なもの
• MySQL 8.0
• MySQL Workbench 8.0
• GDAL 2.3.2以降
• シェープファイル ※今日のデモではe-Statの境界データ（小地域、福岡県）を使用
47
※ダウンロード手順
e-StatからShapeファイルをダウンロードする
https://www.youtube.com/watch?v=2eBWFQizBjU
Slide 48
Slide 48 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
1．シェープファイルの文字コードがUTF-8以外の場合、UTF-8に変換する
(補足あり)
2． MySQL 8.0に接続し、シェープファイルをインポートするための
データベースを作成する（データベース名は任意の名前）
3．ogr2ogrを使ってシェープファイルをインポート
4． MySQLでインポートされたデータを確認
5． Googleで「福岡県福岡市博多区博多駅前1丁目」を検索して、
形状を確認
6． MySQL Workbenchから以下のSQLを実行し、Form Editiorで
ポリゴンデータの形状を確認
48
Slide 49
Slide 49 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
1．シェープファイルの文字コードがUTF-8以外の場合、UTF-8に変換する
[実行例]
> ogr2ogr -f “ESRI Shapefile” -lco ENCODING=UTF-8 -oo ENCODING=CP932
h27ka40_utf8.shp h27ka40.shp
※参考情報：ogr2ogr 備忘録
https://qiita.com/tohka383/items/d3d1bf80db2cfb416330#
※以下環境での実行例
- ローカルのMySQLサーバーへ接続、3306ポートを使用（デフォルト）
- MySQLのユーザー名：root、パスワード：root、認証方式：mysql_native_password
- シェープファイル：h27ka40.shp
49
Slide 50
Slide 50 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
2． MySQL 8.0に接続し、シェープファイルをインポートするための
データベースを作成する（データベース名は任意の名前）
[実行例]
mysql> create database geotest;
Query OK, 1 row affected (0.02 sec)
※以下環境での実行例
- ローカルのMySQLサーバーへ接続、3306ポートを使用（デフォルト）
- MySQLのユーザー名：root、パスワード：root、認証方式：mysql_native_password
- シェープファイル：h27ka40.shp
50
Slide 51
Slide 51 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
3．ogr2ogrを使ってシェープファイルをインポート
[実行例]
> ogr2ogr -f "MySQL"
MySQL:"geotest,host=127.0.0.1,user=root,password=root,port=3306"
h27ka40_utf8.shp
※以下環境での実行例
- ローカルのMySQLサーバーへ接続、3306ポートを使用（デフォルト）
- MySQLのユーザー名：root、パスワード：root、認証方式：mysql_native_password
- シェープファイル：h27ka40.shp
51
Slide 52
Slide 52 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
4． MySQLでインポートされたデータを確認
[実行例]
mysql> USE geotest;
Database changed
mysql> SHOW TABLES;
+-------------------+
| Tables_in_geotest |
+-------------------+
| geometry_columns |
| h27ka40_utf8 |
+-------------------+
2 rows in set (0.01 sec)
52
Slide 53
Slide 53 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
mysql> SELECT * FROM geotest.geometry_columns;
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
| F_TABLE_CATALOG | F_TABLE_SCHEMA | F_TABLE_NAME | F_GEOMETRY_COLUMN | COORD_DIMENSION | SRID | TYPE |
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
| NULL | NULL | h27ka40_utf8 | SHAPE | 2 | 4612 | POLYGON |
+-----------------+----------------+--------------+-------------------+-----------------+------+---------+
1 row in set (0.00 sec)
[実行例の続き]
53
※注：GDAL 2.4.2/32bit版使用時に、SRIDがNULLになる事象を確認しています。
SRIDを含むデータの場合、この部分にSRIDが入っていることを確認して下さい。
Slide 54
Slide 54 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
mysql> SELECT OGR_FID, ST_AsText(SHAPE), pref_name, city_name, s_name FROM geotest.h27ka40_utf8 WHERE
city_name LIKE '博多区' AND s_name='博多駅前１丁目'¥G
*************************** 1. row ***************************
OGR_FID: 1838
ST_AsText(SHAPE): POLYGON((33.5914886701769 130.419195054713,33.5911829024943
130.418840960388,33.5909692127898 130.418593446917,33.5913437119741 130.418087844493,33.5914666847639
130.417925873498,33.5919282622491 130.417317831215,33.5920122736508 130.417207128242,33.5926044104422
130.416427118441,33.5927012828403 130.416265276536,33.5929583410177 130.415916414624,33.5929768903681
130.415891585611,33.5931056922524 130.415719088596,33.5933562200846 130.415397288152,33.5933564972305
130.415396403867,33.593359833282 130.415386827799,33.5934003571591 130.415331251577,33.5944995785547
130.415574161242,33.5947793459063 130.415656372892,33.5951644264885 130.415769524228,33.5952833895663
130.415841367092,33.5951796986848 130.41573705538,33.5954359300895 130.415982682941,33.5955775956091
130.416118473989,33.596179627383 130.416783167319,33.5961804375032 130.41679052748,33.5961971430968
130.41694235617,33.5964599109803 130.417230580337,33.5964518446609 130.417237832136,33.5963733197387
130.417301450815,33.5962573718926 130.417495923935,33.5961040251507 130.417796205838,33.596105582071
130.417797879467,33.5958874000853 130.418226966824,33.5958009510962 130.418388031092,33.5957901183759
130.418421353457,33.5957006268106 130.41868484044,33.5957032420258 130.418686463877,33.5953991711195
130.419652330461,33.5952276765442 130.420244990777,33.595210624244 130.420303818052,33.5951726796024
130.420414006637,33.5950603017167 130.420739849308,33.5950181972882 130.4208644624,33.5949935330506
130.420937564972,33.5948785533184 130.421250455963,33.5948648864396 130.421250252674,33.5946871990053
130.421244531742,33.5945139515213 130.421238823334,33.5940001419025 130.421255960411,33.5938868116925
130.421259196229,33.5938856846219 130.421259203721,33.593223035377 130.421278212272,33.5929868527028
130.421281509346,33.5928522972393 130.421287537713,33.592628676259 130.421311160977,33.5926276902444
130.421311252648,33.5923929997767 130.421324764432,33.5920281171397 130.421358796805,33.5920852825589
130.420334907941,33.5920966582631 130.42013138472,33.5920845792855 130.41992136789,33.5920388665439
130.419832237916,33.5917772607368 130.419529274777,33.5914886701769 130.419195054713))
pref_name: 福岡県
city_name: 博多区
s_name: 博多駅前１丁目
1 row in set (0.26 sec)
[実行例の続き]
54
Slide 55
Slide 55 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
5． Googleで「福岡県福岡市博多区博多駅前1丁目」を検索して、
形状を確認
6． MySQL Workbenchから以下のSQLを実行し、Form Editiorでポリゴン
データの形状を確認
SELECT
*
FROM
geotest.h27ka40_utf8
WHERE
city_name LIKE '博多区'
AND
s_name = '博多駅前１丁目‘
;
55
Slide 56
Slide 56 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
参考：GoogleとMySQL Workbenchでの確認結果
56
Googleでの確認結果 MySQL Workbenchでの確認結果
Slide 57
Slide 57 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
注意事項と補足
57
Slide 58
Slide 58 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
注意事項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
https://github.com/OSGeo/gdal/issues/1089
- Feature request: Support character encodings with MySQL driver #1633
https://github.com/OSGeo/gdal/issues/1633
58
（シェープファイルのフィールド名に日本語を使用している場合、UTF-8に変換することで桁あふれする可能性が
あることに注意）
Slide 59
Slide 59 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
注意事項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
https://github.com/OSGeo/gdal/issues/910
• libmysql-client version is old for MySQL 8.0 #132
https://github.com/gisinternals/buildsystem/issues/132
59
Slide 60
Slide 60 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
注意事項3
• ogr2ogrでシェープファイルをインポートした際にMySQLに作成された
テーブルのgeometry型の列にSRIDが明示的に定義されていません
– SRIDが明示的に定義されていれば、異なるSRIDのデータの混入を防げます
• MySQLへのデータINSERT時にエラーになります
– SRIDが明示的に定義されていない場合、Spatialインデックスが使用できません
• 今回の例の場合、以下の手順で後からSRIDを追加してSpatialインデックスを再作成できます
(Spatialインデックスがついた状態でSRIDを追加できないため、一旦インデックスを削除して再作成)
– mysql> ALTER TABLE h27ka40_utf8 DROP INDEX SHAPE;
– mysql> ALTER TABLE h27ka40_utf8 MODIFY SHAPE GEOMETRY NOT NULL SRID 4612;
– mysql> ALTER TABLE h27ka40_utf8 ADD SPATIAL INDEX (SHAPE);
– issue登録済みです
• ogr2ogr with MySQL 8: POINT column doesn't have SRID attribute #1015
https://github.com/OSGeo/gdal/issues/1015
60
Slide 61
Slide 61 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
注意事項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
61
Slide 62
Slide 62 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Program Agenda
MySQLのGIS機能の歴史
MySQLのGIS機能の使用事例
アプリケーション開発に役立つMySQLのGIS基礎知識
シェープファイルのインポート手順
GeoJSONファイルのインポート手順
まとめ
1
2
3
4
5
62
6
Slide 63
Slide 63 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
必要なもの
• MySQL 8.0
• MySQL Shell 8.0
• MySQL Workbench 8.0
• GeoJSONファイル ※今日のデモではe-Statの境界データ（小地域、愛知県）を使用
63
※ダウンロード手順
e-StatからShapeファイルをダウンロードする
https://www.youtube.com/watch?v=2eBWFQizBjU
Slide 64
Slide 64 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
1．（ogr2ogrを使ってシェープファイルからGeoJSONファイルを生成する）
2． MySQL 8.0に接続し、シェープファイルをインポートするための
データベースを作成する（データベース名は任意の名前）
3． MySQL Shellを使ってJSONファイルをインポートする
4． MySQLでインポートされたデータを確認
5． JSONエディタで元データを確認
6． MySQL Workbenchから以下のSQLを実行し、Form Editiorで
ポリゴンデータの形状を確認
7． MySQL Workbenchでテーブル形式に変換したデータを確認
64
Slide 65
Slide 65 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
1．ogr2ogrを使ってシェープファイルからGeoJSONファイルを生成する
[実行例]
ogr2ogr -f GeoJSON -oo ENCODING=CP932 h27ka23.geojson h27ka23.shp
※以下環境での実行例
- ローカルのMySQLサーバーへ接続、3306ポートを使用（デフォルト）
- MySQLのユーザー名：root、パスワード：root
- シェープファイル：h27ka23.shp
65
Slide 66
Slide 66 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
2．MySQL 8.0に接続し、シェープファイルをインポートするための
データベースを作成する（データベース名は任意の名前）
[実行例]
mysql> create database geotest;
Query OK, 1 row affected (0.02 sec)
※以下環境での実行例
- ローカルのMySQLサーバーへ接続、3306ポートを使用（デフォルト）
- MySQLのユーザー名：root、パスワード：root
- シェープファイル：h27ka23.shp
66
Slide 67
Slide 67 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
3．MySQL Shellを使ってJSONファイルをインポートする
> mysqlsh -u root -p --import h27ka23.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 23 (X protocol)
Server version: 8.0.16-commercial MySQL Enterprise Server - Commercial
Default schema `geotest` accessible through db.
Importing from file "h27ka23.geojson" to collection `geotest`.`h27ka23` in MySQL Server
at localhost:33060
Processed 48.38 MB in 1 document in 5.2862 sec (0.19 document/s)
Total successfully imported documents 0 (0.00 documents/s)
Result of json_binary::serialize() was larger than max_allowed_packet (4194304) -
truncated
[実行例]
67
Slide 68
Slide 68 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
4．MySQLでインポートされたデータを確認
mysql> USE geotest;
mysql> SHOW TABLES;
+-------------------+
| Tables_in_geotest |
+-------------------+
| h27ka23 |
+-------------------+
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE geotest.h27ka23¥G
*************************** 1. row ***************************
Table: h27ka23
Create Table: CREATE TABLE `h27ka23` (
`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
1 row in set (0.00 sec)
[実行例]
68
Slide 69
Slide 69 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
mysql> SELECT COUNT(*) FROM geotest.h27ka23;
+----------+
| COUNT(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
[実行例の続き]
69
Slide 70
Slide 70 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
5．JSONエディタで元データを確認
70
Slide 71
Slide 71 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
6．MySQLに接続し、JSON_TABLE関数を使ってGeoJSONデータをRDBMSの
テーブル形式に変換
CREATE TABLE geotest.h27ka23_table AS
SELECT j.KEY_CODE, j.PREF_NAME, j.GST_NAME, j.CITY_NAME, j.S_NAME,
ST_GeomFromGeoJSON(j.geometry) as geom
FROM geotest.h27ka23,
JSON_TABLE(doc, '$.features[*]' COLUMNS (
KEY_CODE VARCHAR(20) PATH '$.properties.KEY_CODE',
PREF_NAME VARCHAR(20) PATH '$.properties.PREF_NAME',
GST_NAME VARCHAR(20) PATH '$.properties.GST_NAME',
CITY_NAME VARCHAR(20) PATH '$.properties.CITY_NAME',
S_NAME VARCHAR(20) PATH '$.properties.S_NAME',
geometry JSON PATH '$.geometry')
) j
ORDER BY KEY_CODE;
71
Slide 72
Slide 72 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
7．MySQL Workbenchでテーブル形式に変換したデータを確認（Result Grid）
72
Slide 73
Slide 73 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
デモ手順
7．MySQL Workbenchでテーブル形式に変換したデータを確認（Form Editor）
73
Slide 74
Slide 74 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Program Agenda
MySQLのGIS機能の歴史
MySQLのGIS機能の使用事例
アプリケーション開発に役立つMySQLのGIS基礎知識
シェープファイルのインポート手順
GeoJSONファイルのインポート手順
まとめ
1
2
3
4
5
74
6
Slide 75
Slide 75 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
まとめ
• MySQLはGIS機能を積極的に開発しています！
• シェープファイル、GeoJSONファイルもインポートできるので、
MySQLを使ったGIS実装も是非お試しください！！
– 試してみておかしな点等あれば、フィードバックを頂けると助かります
• MySQLで利用できる日本のGISデータの配布を検討中です！
(再配布がライセンス的に問題無いもの)
– サンプルとしてお薦めのGISデータがあれば、教えて下さい！
75
Slide 76
Slide 76 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
まとめ
• ST_Intersects()、ST_Overlaps()の実行速度が非常に遅いという問題に
ついては、今後のFOSS4Gで調査状況を報告します
76
Slide 77
Slide 77 text
Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
Slide 78
Slide 78 text
No content