Tweet
Tweet

Slide 1

Slide 1 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MySQL 8.0で強化されたGIS機能と使用事例の紹介 とシェープファイルのインポート手順について updated: 2019/08/24 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 以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。 また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことはでき ません。以下の事項は、マテリアルやコード、機能を提供することをコミットメントするも のではない為、購買決定を行う際の判断材料になさらないで下さい。 オラクル製品に関して記載されている機能の開発、リリースおよび時期については、 弊社の裁量により決定されます。

Slide 3

Slide 3 text

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

Slide 4

Slide 4 text

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

Slide 5

Slide 5 text

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データの配布を検討中です！ 5

Slide 6

Slide 6 text

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

Slide 7

Slide 7 text

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

Slide 8

Slide 8 text

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を登録してくれている方がいました。

Slide 9

Slide 9 text

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で修正されました！！

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

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

Slide 13

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

Slide 14

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

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

Slide 20

Slide 20 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 20

Slide 21

Slide 21 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. GeoJSON • 空間データを扱うためのフォーマットの一つ • JSONによる表現で空間データを扱える – 例：JRセントラルタワーズ（経度：136.883011、緯度：35.172060）のGeoJSONによる表現 ⇒ {"type": "Point", "coordinates": [136.883011, 35.172060]} 21

Slide 22

Slide 22 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 22

Slide 23

Slide 23 text

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

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 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 26

Slide 27

Slide 27 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 27

Slide 28

Slide 28 text

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

Slide 29

Slide 29 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 29

Slide 30

Slide 30 text

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

Slide 31

Slide 31 text

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

Slide 32

Slide 32 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 32

Slide 33

Slide 33 text

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

Slide 34

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

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

Slide 37

Slide 37 text

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

Slide 38

Slide 38 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で ポリゴンデータの形状を確認 38

Slide 39

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

Slide 40

Slide 40 text

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

Slide 41

Slide 41 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 - シェープファイル：h27ka23.shp 41

Slide 42

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

Slide 43

Slide 43 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 | | h27ka23_utf8 | +-------------------+ 2 rows in set (0.01 sec) 43

Slide 44

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

Slide 45

Slide 45 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.h27ka23_utf8 WHERE City_name='中村区' AND S_name LIKE '名駅１丁目'¥G *************************** 1. row *************************** OGR_FID: 1291 ST_AsText(SHAPE): POLYGON((35.1714383792575 136.880530344871,35.1714334916918 136.880578003878,35.1712304148452 136.880676613883,35.1711455917084 136.880960603925,35.170917088022 136.88198536401,35.1717659549363 136.882132452539,35.1719721646628 136.882168167574,35.1729521469771 136.882100876943,35.1734866931952 136.882027901788,35.1737608573632 136.88209162179,35.1738287373692 136.882088727695,35.173777238823 136.882529997,35.1735446657061 136.882663721771,35.1732290147527 136.882836564598,35.1730788175477 136.882902696931,35.1727904003677 136.883097486261,35.1726116266665 136.883200275345,35.172097516127 136.883580626978,35.171881132686 136.883742208184,35.171305375454 136.88417212652,35.1708566373497 136.884290761177,35.1698268543349 136.884626599348,35.1698117437829 136.884639644829,35.1690505776424 136.884866108417,35.1684136804101 136.88505060912,35.1676826495225 136.885244585496,35.1676609880786 136.885257138169,35.1675777685102 136.88451235585,35.1674853057151 136.883757358322,35.1674270995724 136.88331924717,35.1674075668575 136.883247851131,35.1673621787224 136.883081885212,35.1673470105787 136.882799263001,35.1679427752338 136.882342119012,35.168428486375 136.88198387296,35.1689183109133 136.881668146643,35.1694744240097 136.881332052728,35.1694644272673 136.881303292384,35.1713652044956 136.880319183163,35.1715333148049 136.880205063275,35.1715690013892 136.880257256582,35.173701094889 136.879567382601,35.1740314514692 136.879450868808,35.1741274208335 136.879741034936,35.1735293284842 136.879980574331,35.1728639275748 136.880197337619,35.1722314838013 136.880294981521,35.1721522044944 136.88039159533,35.1720218939305 136.880408924968,35.1717381912865 136.880421425892,35.1714383792575 136.880530344871)) pref_name: 愛知県 city_name: 中村区 s_name: 名駅１丁目 1 row in set (0.34 sec) [実行例の続き] 45

Slide 46

Slide 46 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. デモ手順 5． Googleで「愛知県名古屋市中村区名駅１丁目」を検索して、 形状を確認 6． MySQL Workbenchから以下のSQLを実行し、Form Editiorでポリゴン データの形状を確認 SELECT * FROM geotest.h27ka23_utf8 WHERE city_name = '中村区' AND s_name = '名駅１丁目'; 46

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 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 49

Slide 50

Slide 50 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 50

Slide 51

Slide 51 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 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 51

Slide 52

Slide 52 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 52

Slide 53

Slide 53 text

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

Slide 54

Slide 54 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の境界データ（小地域、愛知県）を使用 54

Slide 55

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

Slide 56

Slide 56 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 56

Slide 57

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

Slide 58

Slide 58 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. デモ手順 3．MySQL Shellを使ってJSONファイルをインポートする C:¥> 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 [実行例] 58

Slide 59

Slide 59 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) [実行例] 59

Slide 60

Slide 60 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) [実行例の続き] 60

Slide 61

Slide 61 text

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

Slide 62

Slide 62 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; 62

Slide 63

Slide 63 text

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

Slide 64

Slide 64 text

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

Slide 65

Slide 65 text

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

Slide 66

Slide 66 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MySQLにGISデータを取り込む方法を伝えていますが… 66

Slide 67

Slide 67 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MySQLにGISデータを取り込む方法を伝えていますが… • MySQLのGIS機能に触れて頂くために、シェープファイル、GeoJSON データをインポートする手順を案内することも大事だけど、、、 67

Slide 68

Slide 68 text

Copyright © 2019, Oracle and/or its affiliates. All rights reserved. MySQLにGISデータを取り込む方法を伝えていますが… • MySQLのGIS機能に触れて頂くために、シェープファイル、GeoJSON データをインポートする手順を案内することも大事だけど、、、 • 最初の一歩としては、取り込んだMySQLのデータを配布した方がいいので はないか？、、、と感じ始めています 68

Slide 69

Slide 69 text

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

Slide 70

Slide 70 text

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

Slide 71

Slide 71 text

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

Slide 72

Slide 72 text

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

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

No content