Upgrade to Pro — share decks privately, control downloads, hide ads and more …

MySQL 8.0で強化されたGIS機能と使用事例のご紹介+α

MySQL 8.0で強化されたGIS機能と使用事例のご紹介+α

2019年9月14日(土)に開催された「FOSS4G 2019 NIIGATA コアデイ」での発表資料です。
https://www.osgeo.jp/events/foss4g-2019/foss4g-2019-niigata/foss4g-2019-niigata-coreday

YoshiakiYamasaki

September 14, 2019
Tweet

More Decks by YoshiakiYamasaki

Other Decks in Technology

Transcript

  1. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    MySQL 8.0で強化されたGIS機能と
    使用事例のご紹介+α
    2019/09/14
    FOSS4G 2019 NIIGATA
    Yoshiaki Yamasaki / 山﨑 由章
    MySQL Principal Solution Engineer, Asia Pacific and Japan

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  4. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    今日伝えたいこと
    • GDAL 2.3.2がリリースされ、ogr2ogrを使用してMySQL 8.0へ
    シェープファイルをインポートできるようになりました!
    – 注意事項が4点あります
    • MySQL ShellやJSON関数を使えば、GeoJSON形式のファイルも
    MySQLへインポートして活用できます!
    • MySQLのGIS機能を触って頂くきっかけとして、MySQLに取り込んだ
    GISデータの配布を検討中です!
    4

    View full-size slide

  5. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    はじめに
    5

    View full-size slide

  6. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    FOSS4G Hokkaido 2018での苦い(?)思い出
    6

    View full-size slide

  7. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    FOSS4G Hokkaido 2018での苦い(?)思い出
    7
    • シェープファイルの取り込み、POINTデータのインサートに以下4つの
    方法を試して頂きましたが、いずれもエラーになっていました
    1.MySQL Workbench 8.0.11
    2.QGIS 2.18.20、3.0.1
    3.SQL実行(MySQL Workbenchから
    4.org2ogr

    View full-size slide

  8. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    FOSS4G Hokkaido 2018での苦い(?)思い出
    8
    • シェープファイルの取り込み、POINTデータのインサートに以下4つの
    方法を試して頂きましたが、いずれもエラーになっていました
    1.MySQL Workbench 8.0.11
    2.QGIS 2.18.20、3.0.1
    3.SQL実行(MySQL Workbenchから
    4.org2ogr
    ⇒2.~4.はGDALに依存した問題だったので、githubでissueを登録しました。
    また、issueを登録してくれている方がいました。

    View full-size slide

  9. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    FOSS4G Hokkaido 2018での苦い(?)思い出
    9
    • シェープファイルの取り込み、POINTデータのインサートに以下4つの
    方法を試して頂きましたが、いずれもエラーになっていました
    1.MySQL Workbench 8.0.11
    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 full-size slide

  10. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    関連するissue
    • ogr2ogr with MySQL 8 #782
    https://github.com/OSGeo/gdal/issues/782
    • Issue with Mysql import #899
    https://github.com/OSGeo/gdal/issues/899
    • ogr2ogr with MySQL 8 (Wrong SRID) #905
    https://github.com/OSGeo/gdal/issues/905
    • ogr2ogr with MySQL 8 (Wrong Axis Order) #906
    https://github.com/OSGeo/gdal/issues/906
    ⇒これらのissueはGDAL 2.3.2で修正済み
    10

    View full-size slide

  11. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    MySQLのGIS機能の歴史
    11

    View full-size slide

  12. 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)
    12

    View full-size slide

  13. 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へのコントリビュートも行っている
    13

    View full-size slide

  14. 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サポート
    14

    View full-size slide

  15. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    MySQLは積極的に
    GIS機能を開発しています!!
    15

    View full-size slide

  16. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    GIS機能の紹介
    16

    View full-size slide

  17. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    geometryデータ型
    • OpenGISジオメトリモデルに基づいた以下のデータが使用可能
    – GEOMETRY、GEOMETRYCOLLECTION
    – 点:POINT、MULTIPOINT
    – 線:LINESTRING、MULTILINESTRING
    – 多角形:POLYGON、MULTIPOLYGON
    ※GEOMETRYには、POINT、LINESTRING、POLYGONを格納可能
    ※GEOMETRYCOLLECTIONには、MULTIPOINT、MULTILINESTRING、MULTIPOLYGONを
    格納可能
    17

    View full-size slide

  18. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    Spatial関数
    • ジオメトリデータを扱える各種の関数
    • 例
    – ST_Length():引数としてLINESTRINGを与えることで、長さを出力
    – ST_Distance():2つのジオメトリを入力し、2地点間の距離を出力
    – ST_Intersects():2つのジオメトリが空間的に交差しているかどうかを判定
    • ST_Overlaps() + ST_Within()
    – ST_Overlaps():2つのジオメトリが重なっているかどうかを判定
    – ST_Within():あるジオメトリが別のジオメトリの内側に存在するかを判定
    18

    View full-size slide

  19. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    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
    19

    View full-size slide

  20. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    GeoHash
    • 経緯度の情報を文字列化したもの
    – 例:万代島ビル(経度:139.059370、緯度:37.925318)のGeoHash
    ⇒ xndzu25fyp<<後略>>
    • 特徴
    – 文字列なので、データベースで扱いやすい
    (インデックスも活用可能)
    – GeoHashの情報は点ではなく範囲(矩形のエリア)を表す
    – 文字列の桁数で精度を変えることができる
    • 10桁を超えると矩形の一辺が1m以下になるので、経緯度の変わりとしても十分利用可能
    • 近接エリアの判定等に利用可能
    20

    View full-size slide

  21. 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
    21

    View full-size slide

  22. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    GeoJSON
    • 空間データを扱うためのフォーマットの一つ
    • JSONによる表現で空間データを扱える
    – 例:万代島ビル(経度: 139.059370、緯度:37.925318)のGeoJSONによる表現
    ⇒ {"type": "Point", "coordinates": [139.059370, 37.925318]}
    22

    View full-size slide

  23. 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
    23

    View full-size slide

  24. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    FOSS4G TOKAIで頂いたフィードバック
    • 「GeoHashだけでなくQuadKeyやGeoHex、Locapointにも対応してください」
    24

    View full-size slide

  25. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    FOSS4G TOKAIで頂いたフィードバック
    • 「GeoHashだけでなくQuadKeyやGeoHex、Locapointにも対応してください」
    ⇒以下の機能追加リクエストを登録しました!!(plus+codesも追加)
    是非「Affect me」して下さい!!
    25
    • Bug#96759:Add ST_Quadkey(),ST_PointFromQuadkey(),ST_Lat/LongFromQuadkey() function
    https://bugs.mysql.com/bug.php?id=96759
    • Bug#96760:Add ST_LocaPoint(),ST_PointFromLocaPoint(),ST_Lat/LongFromLocaPoint() function
    https://bugs.mysql.com/bug.php?id=96760
    • Bug#96761:Add ST_GeoHex(),ST_PolygonFromGeoHex() function
    https://bugs.mysql.com/bug.php?id=96761
    • Bug#96762:Add ST_Pluscodes(),ST_PointFromPluscodes(),ST_Lat/LongFromPluscodes() function
    https://bugs.mysql.com/bug.php?id=96762

    View full-size slide

  26. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    補足:各ジオコーディング手法について
    • Quadkey
    – メッシュが正方形、メッシュ上位と下位の分割が4分木であるため、使いやすい
    (GeoHashはメッシュが長方形、メッシュ上位と下位の分割が32分木)
    • GeoHex
    – メッシュが六角形 ⇒ 隣接するメッシュへの距離が等距離になる
    • LocaPoint
    – 人間が認識しやすいコード体系(英文字・英文字・数字×4回)
    • plus+codes
    – 人間が認識しやすいコード体系(region code + city code + neighbourhood code + building code )
    – Googleのエンジニアが開発、Google Mapでもサポートされている
    26

    View full-size slide

  27. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    MySQL ドキュメントストアの活用
    27

    View full-size slide

  28. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    MySQL ドキュメントストアとは?
    MySQLでJSONドキュメントを扱う簡単な方法
    #1
    New Feature
    28

    View full-size slide

  29. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    リレーショナル
    データベース
    ハイブリッド
    データベース
    ドキュメント
    データベース
    SQL
    リレーショナルテーブル
    外部キー
    NoSQL
    JSONドキュメント
    スキーマレスJSONコレクション
    29

    View full-size slide

  30. 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
    30

    View full-size slide

  31. 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
    31

    View full-size slide

  32. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    MySQL Workbenchの活用
    32

    View full-size slide

  33. 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
    33

    View full-size slide

  34. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    "Spatial Viewer" and "Geometry Viewer"
    • ジオメトリ型のデータを図示可能
    34

    View full-size slide

  35. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    MySQLのGIS機能を使用したシステムの例
    35

    View full-size slide

  36. 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
    36

    View full-size slide

  37. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    FOSS4G Hokkaido 2019
    ハンズオンセミナー受講者の作品
    37

    View full-size slide

  38. 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までは使って
    いませんが、ハンズオンをきっかけにGISを実装される方が出てきたことを
    嬉しく思います
    38

    View full-size slide

  39. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    ハンズオンセミナー参加者の作品
    39
    • その後、MySQLを使った作品も作って下さいました
    – SORACOM LTE-M Button と MySQL 8.0 を使って二点間の距離を測る
    https://blog.tacck.net/archives/417

    View full-size slide

  40. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    シェープファイルのインポート手順
    40

    View full-size slide

  41. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    必要なもの
    • MySQL 8.0
    • MySQL Workbench 8.0
    • GDAL 2.3.2以降
    • シェープファイル ※今日のデモではe-Statの境界データ(小地域、新潟県)を使用
    41

    View full-size slide

  42. 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で「新潟県新潟市中央区万代島」を検索して、
    形状を確認
    6. MySQL Workbenchから以下のSQLを実行し、Form Editiorで
    ポリゴンデータの形状を確認
    42

    View full-size slide

  43. 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
    h27ka15_utf8.shp h27ka15.shp
    ※参考情報:ogr2ogr 備忘録
    https://qiita.com/tohka383/items/d3d1bf80db2cfb416330#
    ※以下環境での実行例
    - ローカルのMySQLサーバーへ接続、3306ポートを使用(デフォルト)
    - MySQLのユーザー名:root、パスワード:root、認証方式:mysql_native_password
    - シェープファイル:h27ka15.shp
    43

    View full-size slide

  44. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    補足
    • FOSS4G Hokkaido 2019 での発表時にSlidoで以下のコメントを頂きました
    – 「Shapeの文字コードをUTF-8に変更するとカラム名やカラムの長さを突破することが
    あるので推奨できません。GeoJSONにしてからインポート推奨がいいと思います。」
    • GeoJSONに変換してインポートする手順についても整理しました(後述)
    – 手順が煩雑になるため、文字コード変換しても桁あふれしない場合は、
    こちら手順の方が簡単にインポートできるのでお薦めです
    44
    ※ogr2ogrを使ってシェープファイルをインポートする際に、”-oo ENCODING=CP932”を指定してインポートする
    手順も試しましたが、後述の文字化けに依存するエラーが発生しました。

    View full-size slide

  45. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    補足
    • その後FOSS4G TOKAIでフィードバックを頂きつつ調査した結果、
    結論としては「シェープファイルのフィールド名に日本語を使用していない
    場合はこの手法で問題無い」となりました
    – データ部分のサイズは、ogr2ogrで文字コードを変換する際に合わせて変換される
    – フィールド名はシェープファイルとして最大10バイトという制限があるため、
    日本語で4文字以上のフィールド名を使用している場合は、事前にフィールド名を
    修正することを推奨
    45

    View full-size slide

  46. 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
    - シェープファイル:h27ka15.shp
    46

    View full-size slide

  47. 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"
    h27ka15_utf8.shp
    ※以下環境での実行例
    - ローカルのMySQLサーバーへ接続、3306ポートを使用(デフォルト)
    - MySQLのユーザー名:root、パスワード:root、認証方式:mysql_native_password
    - シェープファイル:h27ka15.shp
    47

    View full-size slide

  48. 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 |
    | h27ka15_utf8 |
    +-------------------+
    2 rows in set (0.01 sec)
    48

    View full-size slide

  49. 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 | h27ka15_utf8 | SHAPE | 2 | 4612 | POLYGON |
    +-----------------+----------------+--------------+-------------------+-----------------+------+---------+
    1 row in set (0.00 sec)
    [実行例の続き]
    49
    ※注:GDAL 2.4.2/32bit版使用時に、SRIDがNULLになる事象を確認しています。
    SRIDを含むデータの場合、この部分にSRIDが入っていることを確認して下さい。

    View full-size slide

  50. 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.h27ka15_utf8 WHERE
    City_name='中央区' AND S_name LIKE '万代島'¥G
    *************************** 1. row ***************************
    OGR_FID: 814
    ST_AsText(SHAPE): POLYGON((37.9232426725295 139.059374657909,37.9231617541823
    139.059516226588,37.9230092055425 139.059385383328,37.9229074836943 139.059549543294,37.9230698762218
    139.059712272404,37.9230244326007 139.059794970949,37.9230044037725 139.059832364056,37.9229825296813
    139.059873164603,37.9227881445128 139.060220878237,37.9238405488986 139.061153562309,37.9242586362481
    139.061515015303,37.9249719262301 139.062144850395,37.9247628490608 139.062531156223,37.9248445685455
    139.062605709245,37.9250643060827 139.062227436983,37.9257749829017 139.062843624098,37.9258171885402
    <<中略>>
    139.062750784307,37.9272359547297 139.06238521585,37.9258639890293 139.061570339505,37.9258235755662
    139.061542753025,37.9257894798066 139.061518590326,37.9253294093601 139.061252386949,37.9251779393372
    139.061072671085,37.9251588795265 139.061054530295,37.9248900759988 139.060799774072,37.9246863453157
    139.060626462834,37.9240887756498 139.060113525324,37.923827574607 139.059887489324,37.9237747347258
    139.059838172089,37.9237504841939 139.059816396186,37.9232426725295 139.059374657909))
    pref_name: 新潟県
    city_name: 中央区
    s_name: 万代島
    1 row in set (0.23 sec)
    [実行例の続き]
    50

    View full-size slide

  51. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    デモ手順
    5. Googleで「新潟県新潟市中央区万代島」を検索して、
    形状を確認
    6. MySQL Workbenchから以下のSQLを実行し、Form Editiorでポリゴン
    データの形状を確認
    SELECT
    *
    FROM
    geotest.h27ka15_utf8
    WHERE
    city_name = '中央区'
    AND
    s_name = '万代島';
    51

    View full-size slide

  52. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    参考:GoogleとMySQL Workbenchでの確認結果
    52
    Googleでの確認結果 MySQL Workbenchでの確認結果

    View full-size slide

  53. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    注意事項と補足
    53

    View full-size slide

  54. 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
    54

    View full-size slide

  55. 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
    55

    View full-size slide

  56. 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 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
    https://github.com/OSGeo/gdal/issues/1015
    56

    View full-size slide

  57. 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
    57

    View full-size slide

  58. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    補足
    • シェープファイルをインポートする手法に関して現状ogr2ogrに頼って
    いますが、MySQL製のインポートツールが欲しいと考えており、
    以下の機能追加リクエストを登録しています。
    是非「Affect me」して下さい!!
    58
    • Bug#90023:[Feature request] Shape File import/export tool
    https://bugs.mysql.com/bug.php?id=90023

    View full-size slide

  59. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    GeoJSONファイルのインポート手順
    59

    View full-size slide

  60. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    必要なもの
    • MySQL 8.0
    • MySQL Shell 8.0
    • MySQL Workbench 8.0
    • GeoJSONファイル ※今日のデモではe-Statの境界データ(小地域、新潟県)を使用
    60

    View full-size slide

  61. 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でテーブル形式に変換したデータを確認
    61

    View full-size slide

  62. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    デモ手順
    1.ogr2ogrを使ってシェープファイルからGeoJSONファイルを生成する
    [実行例]
    > ogr2ogr -f GeoJSON -oo ENCODING=CP932 h27ka15.geojson h27ka15.shp
    ※以下環境での実行例
    - ローカルのMySQLサーバーへ接続、3306ポートを使用(デフォルト)
    - MySQLのユーザー名:root、パスワード:root
    - シェープファイル:h27ka15.shp
    62

    View full-size slide

  63. 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
    - シェープファイル:h27ka15.shp
    63

    View full-size slide

  64. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    デモ手順
    3.MySQL Shellを使ってJSONファイルをインポートする
    > mysqlsh -u root -p --import h27ka15.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 18 (X protocol)
    Server version: 8.0.16-commercial MySQL Enterprise Server - Commercial
    Default schema `geotest` accessible through db.
    Importing from file "h27ka15.geojson" to collection `geotest`.`h27ka15` in MySQL Server
    at localhost:33060
    .. 1
    Processed 50.00 MB in 1 document in 11.9812 sec (0.08 document/s)
    Total successfully imported documents 1 (0.08 document/s)
    [実行例]
    64

    View full-size slide

  65. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    デモ手順
    4.MySQLでインポートされたデータを確認
    mysql> USE geotest;
    mysql> SHOW TABLES;
    +-------------------+
    | Tables_in_geotest |
    +-------------------+
    | h27ka15 |
    +-------------------+
    1 row in set (0.00 sec)
    mysql> SHOW CREATE TABLE geotest.h27ka15¥G
    *************************** 1. row ***************************
    Table: h27ka15
    Create Table: CREATE TABLE `h27ka15` (
    `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)
    [実行例]
    65

    View full-size slide

  66. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    mysql> SELECT COUNT(*) FROM geotest.h27ka15;
    +----------+
    | COUNT(*) |
    +----------+
    | 1 |
    +----------+
    1 row in set (0.00 sec)
    [実行例の続き]
    66

    View full-size slide

  67. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    デモ手順
    5.JSONエディタで元データを確認
    67

    View full-size slide

  68. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    デモ手順
    6.MySQLに接続し、JSON_TABLE関数を使ってGeoJSONデータをRDBMSの
    テーブル形式に変換
    CREATE TABLE geotest.h27ka15_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.h27ka15,
    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;
    68

    View full-size slide

  69. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    デモ手順
    7.MySQL Workbenchでテーブル形式に変換したデータを確認(Result Grid)
    69

    View full-size slide

  70. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    デモ手順
    7.MySQL Workbenchでテーブル形式に変換したデータを確認(Form Editor)
    70

    View full-size slide

  71. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    MySQLに取り込んだGISデータについて
    71

    View full-size slide

  72. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    検討していること
    • チュートリアル目的でMySQLに取り込んだ状態のGISデータを配布すれば、
    もっとMySQLのGIS機能を使用する人が増えるのではないだろうか・・・
    72

    View full-size slide

  73. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    MySQLに取り込んだGISデータの配布を検討中
    • ライセンス的に問題無い日本のGISデータに関して、MySQLの
    ダンプファイルの形式で私のGitHubで配布することを検討中
    • サンプルとしてこのGISデータがお薦め、というご意見募集中
    – 再配布がライセンス的に問題無いオープンデータ
    – 日本全国のPOLYGONデータとPOINTデータを用意したい
    • POLYGONデータ:「e-Statの境界データ(小地域)」を検討中
    • POINTデータ:ご意見募集中!
    73

    View full-size slide

  74. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    まとめ
    74

    View full-size slide

  75. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    まとめ
    • MySQLはGIS機能を積極的に開発しています!
    • シェープファイル、GeoJSONファイルもインポートできるので、
    MySQLを使ったGIS実装も是非お試しください!!
    – 試してみておかしな点等あれば、フィードバックを頂けると助かります
    • MySQLで利用できる日本のGISデータの配布を検討中です!
    (再配布がライセンス的に問題無いもの)
    – サンプルとしてお薦めのGISデータがあれば、教えて下さい!
    75

    View full-size slide

  76. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.
    まとめ
    • ST_Intersects()、ST_Overlaps()の実行速度が非常に遅いという問題
    については、今後のFOSS4Gで調査状況を報告します
    76

    View full-size slide

  77. Copyright © 2019, Oracle and/or its affiliates. All rights reserved.

    View full-size slide