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

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

2.

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

3.

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

4.

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

5.

はじめに 5

6.

FOSS4G Hokkaido 2018での苦い（？）思い出 6

7.

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

8.

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

9.

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

10.

関連する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

11.

MySQLのGIS機能の歴史 11

12.

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

13.

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

14.

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

15.

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

16.

GIS機能の紹介 16

17.

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

18.

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

19.

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

20.

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

21.

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

22.

GeoJSON • 空間データを扱うためのフォーマットの一つ • JSONによる表現で空間データを扱える – 例：万代島ビル（経度： 139.059370、緯度：37.925318）のGeoJSONによる表現 ⇒ {"type": "Point", "coordinates": [139.059370, 37.925318]} 22

23.

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

24.

FOSS4G TOKAIで頂いたフィードバック • 「GeoHashだけでなくQuadKeyやGeoHex、Locapointにも対応してください」 24

25.

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

26.

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

27.

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

28.

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

29.

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

30.

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

31.

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

32.

MySQL Workbenchの活用 32

33.

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

34.

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

35.

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

36.

アップルップルアプリケーション 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

37.

FOSS4G Hokkaido 2019 ハンズオンセミナー受講者の作品 37

38.

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

39.

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

40.

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

41.

必要なもの • MySQL 8.0 • MySQL Workbench 8.0 • GDAL 2.3.2以降 • シェープファイル ※今日のデモではe-Statの境界データ（小地域、新潟県）を使用 41

42.

デモ手順 1．シェープファイルの文字コードがUTF-8以外の場合、UTF-8に変換する (補足あり) 2． MySQL 8.0に接続し、シェープファイルをインポートするためのデータベースを作成する（データベース名は任意の名前） 3．ogr2ogrを使ってシェープファイルをインポート 4． MySQLでインポートされたデータを確認 5． Googleで「新潟県新潟市中央区万代島」を検索して、形状を確認 6． MySQL Workbenchから以下のSQLを実行し、Form Editiorでポリゴンデータの形状を確認 42

43.

デモ手順 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

44.

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

45.

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

46.

デモ手順 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

47.

デモ手順 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

50.

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

51.

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

52.

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

53.

注意事項と補足 53

54.

注意事項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

55.

注意事項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

56.

注意事項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

57.

注意事項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

58.

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

59.

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

60.

必要なもの • MySQL 8.0 • MySQL Shell 8.0 • MySQL Workbench 8.0 • GeoJSONファイル ※今日のデモではe-Statの境界データ（小地域、新潟県）を使用 60

61.

デモ手順 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

62.

デモ手順 1．ogr2ogrを使ってシェープファイルからGeoJSONファイルを生成する [実行例] > ogr2ogr -f GeoJSON -oo ENCODING=CP932 h27ka15.geojson h27ka15.shp ※以下環境での実行例 - ローカルのMySQLサーバーへ接続、3306ポートを使用（デフォルト） - MySQLのユーザー名：root、パスワード：root - シェープファイル：h27ka15.shp 62

63.

デモ手順 2．MySQL 8.0に接続し、シェープファイルをインポートするためのデータベースを作成する（データベース名は任意の名前） [実行例] mysql> create database geotest; Query OK, 1 row affected (0.02 sec) ※以下環境での実行例 - ローカルのMySQLサーバーへ接続、3306ポートを使用（デフォルト） - MySQLのユーザー名：root、パスワード：root - シェープファイル：h27ka15.shp 63

64.

デモ手順 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

65.

デモ手順 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

66.

mysql> SELECT COUNT(*) FROM geotest.h27ka15; +----------+ | COUNT(*) | +----------+ | 1 | +----------+ 1 row in set (0.00 sec) [実行例の続き] 66

67.

デモ手順 5．JSONエディタで元データを確認 67

68.

デモ手順 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

69.

デモ手順 7．MySQL Workbenchでテーブル形式に変換したデータを確認（Result Grid） 69

70.

デモ手順 7．MySQL Workbenchでテーブル形式に変換したデータを確認（Form Editor） 70

71.

MySQLに取り込んだGISデータについて 71

72.

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

73.

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

74.

まとめ 74

75.

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

76.

まとめ • ST_Intersects()、ST_Overlaps()の実行速度が非常に遅いという問題については、今後のFOSS4Gで調査状況を報告します 76

77.


78.

None

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

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

Want more?

Transcript