MySQLのGIS機能の機能追加/改善状況 / MySQL GIS Update 202208

2022年8月11日に開催された「FOSS4G TOKAI 2022 セッションday」での発表資料です。
https://foss4gtokai2022.netlify.app/

MySQL 8.0のGIS機能の機能追加/改善状況について説明しています。

Transcript

1. MySQLのGIS機能の機能追加/改善状況 FOSS4G TOKAI 2022 たんけん ぎふのもり 山﨑 由章 / Yoshiaki Yamasaki MySQL Master Principal Solution Engineer MySQL Global Business Unit (Twitter ID：@yyamasaki1) 2022年8月11日
2. • 名前：山﨑 由章（やまさき よしあき） • 所属：日本オラクル株式会社 MySQL Global Business Unit • 役割：MySQLのプリセールス、MySQLの普及促進活動、など • Twitter ID：@yyamasaki1 • 趣味：美味しいものを食べること、色々な所に行くこと、ボードゲーム、など • 備考：岐阜駅周辺で再度立ち寄りたい飲食店（昨日岐阜駅周辺もたんけんしました） • 緯度：35.40890 • 経度：136.75645 ※WGS84 自己紹介 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 2
3. ◼ MySQLのGIS機能については、以下の資料を参照ください ➢ MySQL 8.0で強化されたGIS機能と使用事例のご紹介＋α https://speakerdeck.com/yoshiakiyamasaki/mysql-8-dot-0deqiang-hua-saretagisji- neng-toshi-yong-shi-li-falsegoshao-jie-a ➢ MySQL 8.0 GIS機能チュートリアル https://speakerdeck.com/yoshiakiyamasaki/mysql-8-dot-0-gisji-neng-tiyutoriaru- 6052f01e-445a-4f55-bcda-a0e3fad06332 はじめに Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 3
4. ◼ MySQLにシェープファイルをインポートする方法については、 過去の宮内さんの発表やQiitaの記事を参考にして下さい ➢ MySQLにシェープファイルをインポートするツール(shp2mysql)を作った https://qiita.com/miyauchi/items/b4e810b3becf2cf07e2f ➢ MySQLにシェープファイルをインポートしてWebGISを作る https://www.osgeo.jp/events/foss4g-2020/foss4g-2020-japan-online/foss4g- japan-2020-online-coreday#presentation_a6 はじめに Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 4
5. • MySQLのGIS機能に望むこと https://qiita.com/miyauchi/items/5bdab7ea58f1114d6686 2020年1月に宮内さんにまとめて頂いた要望 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 5
6. Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 6 その後の改善状況に ついてお伝えします
7. • 2019年時点では、ST_Intersects()、ST_Overlaps()の実行速度が非常に遅かった 1. ST_Intersectsの速度改善 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 7 https://speakerdeck.com/yoshiakiyamasaki/mysql-8-dot-0deqiang-hua-saretagisji-neng-toshi-yong-shi-li-falsegoshao-jie-a?slide=57 FOSS4G 2019 Niigataでの発表資料より引用
8. • この問題はMySQL 8.0.20（2020年4月リリース）で修正されました！！ 1. ST_Intersectsの速度改善 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 8 MySQL 8.0.20のリリースノートより引用
9. • ST_Intersects(g1, g2) • g1 が g2 と空間的に交差している場合に1(TRUE)、 交差していない場合に0(FALSE)を返す関数 • ST_Overlaps(g1, g2) • g1 が g2 と空間的にオーバーラップしている場合に1(TRUE)、 オーバーラップしていない場合に0(FALSE)を返す関数 補足：ST_Intersects()、ST_Overlaps() Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 9 https://dev.mysql.com/doc/refman/8.0/en/spatial-relation-functions-object-shapes.html#function_st-intersects https://dev.mysql.com/doc/refman/8.0/en/spatial-relation-functions-object-shapes.html#function_st-overlaps ST_Intersects TRUE FALSE TRUE TRUE ST_Overlaps TRUE FALSE FALSE FALSE
10. • ST_Union を使うことで、2つのジオメトリの和集合を求めることができるが、 3つ以上のジオメトリの和集合は求められない • ST_Union の集約関数版はOGC仕様に明示的に定義されていないが、 PostGISでは実装されている 2. ST_Union等の集計(集約)関数版の実装 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 10 ST_Union
11. • ST_Union の集約関数版はまだ実装されていない • MySQL 8.0.24 で ST_Collect 関数が実装された • ジオメトリの集合を入力し、単一のジオメトリコレクションを返す関数 • 重複するジオメトリを排除する DISTINCT オプションも使える 2. ST_Union等の集計(集約)関数版の実装 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 11 ST_Collect
12. mysql> CREATE TABLE foss4g(id INT AUTO_INCREMENT PRIMARY KEY, event_date DATE, event_name VARCHAR(256), location POINT SRID 4326); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO foss4g VALUES(1, STR_TO_DATE('2019,8,23','%Y,%m,%d'), 'FOSS4G TOKAI 2019', ST_GeomFromText('POINT(35.17132 136.88339)', 4326) ); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO foss4g VALUES(2, STR_TO_DATE('2019,7,12','%Y,%m,%d'), 'FOSS4G 2019 Hokkaido', ST_GeomFromText('POINT(43.05637 141.38595)', 4326) ); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO foss4g VALUES(3, STR_TO_DATE('2019,9,13','%Y,%m,%d'), 'FOSS4G 2019 NIIGATA', ST_GeomFromText('POINT(37.92701 139.06029)', 4326) ); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO foss4g VALUES(4, STR_TO_DATE('2019,10,13','%Y,%m,%d'), 'FOSS4G 2019 KOBE.KANSAI', ST_GeomFromText('POINT(34.68527 135.19924)', 4326) ); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT ST_AsText(ST_Collect(location)) AS result FROM foss4g; +-------------------------------------------------------------------------------------------------+ | result | +-------------------------------------------------------------------------------------------------+ | MULTIPOINT((35.17132 136.88339),(43.05637 141.38595),(37.92701 139.06029),(34.68527 135.19924)) | +-------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ST_Correctの実行例 2019年のFOSS4G開催場所の位置情報を単一のジオメトリコレクションに集約 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 12
13. mysql> CREATE TABLE test(id INT AUTO_INCREMENT PRIMARY KEY, geom GEOMETRY); Query OK, 0 rows affected (0.01 sec) mysql> mysql> INSERT INTO test(geom) VALUES(ST_GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))')); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test(geom) VALUES(ST_GeomFromText('POLYGON((1 0,2 0,2 1,2 1,1 0))')); Query OK, 1 row affected (0.00 sec) mysql> INSERT INTO test(geom) VALUES(ST_GeomFromText('POLYGON((1 0,2 0,2 1,2 1,1 0))')); Query OK, 1 row affected (0.00 sec) mysql> mysql> SELECT ST_AsText(ST_Collect(geom)) AS result FROM test1; +---------------------------------------------------------------------------------------+ | result | +---------------------------------------------------------------------------------------+ | MULTIPOLYGON(((0 0,0 1,1 1,1 0,0 0)),((1 0,2 1,2 1,2 0,1 0)),((1 0,2 1,2 1,2 0,1 0))) | +---------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ST_AsText(ST_Collect(DISTINCT geom)) AS result FROM test1; +---------------------------------------------------------------+ | result | +---------------------------------------------------------------+ | MULTIPOLYGON(((0 0,0 1,1 1,1 0,0 0)),((1 0,2 1,2 1,2 0,1 0))) | +---------------------------------------------------------------+ 1 row in set (0.00 sec) ST_Correctの実行例 3つのポリゴンを1つに集約（重複排除なし、あり） Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 13 0,0 1,0 0,1 1,1 1,0 2,0 1,1 2,1 ST_Correct 0,0 1,0 0,1 1,1 2,0 2,1
14. mysql> SET @g1 = ST_GeomFromText('POLYGON((0 0,1 0,1 1,0 1,0 0))'); Query OK, 0 rows affected (0.00 sec) mysql> SET @g2 = ST_GeomFromText('POLYGON((1 0,2 0,2 1,1 1,1 0))'); Query OK, 0 rows affected (0.00 sec) mysql> mysql> SELECT ST_AsText(ST_Union(@g1, @g2)); +------------------------------------+ | ST_AsText(ST_Union(@g1, @g2)) | +------------------------------------+ | POLYGON((2 1,0 1,0 0,1 0,2 0,2 1)) | +------------------------------------+ 1 row in set (0.00 sec) 参考：ST_Unionでポリゴンを集約 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 14 0,0 1,0 0,1 1,1 1,0 2,0 1,1 2,1 ST_Union 0,0 1,0 0,1 1,1 2,0 2,1
15. • 当時のST_Trsansformは地理座標系のみをサポートしていて、 地理座標系のデータ  投影座標系のデータ の変換が出来なかった 3. ST_Transformの改善 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 15 https://qiita.com/miyauchi/items/5bdab7ea58f1114d6686 宮内さんのまとめより引用
16. • MySQL 8.0.30（2022年7月リリース）で ST_Transformの機能が強化され、 WGS84 / Web Mercator（SRID：3857）をサポートした • 例えば、GPSで得られた位置情報（※1）をOpenStreetMapやGoogle マップ等の ウェブ地図上の位置情報（※2）に変換できるようになった • ウェブ地図のAPIでは緯度経度を直接扱えるものもあるが、 緯度経度の情報を距離を基準にした情報に変換することで、 アプリケーションで扱いやすくなるケースもある 3. ST_Transformの改善 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 16 ※1 測地系：WGS84、座標系：地理座標系、SRID：4326 ※2 測地系：WGS84、座標系：投影座標系、SRID：3857
17. mysql> SELECT ST_AsText( -> ST_Transform( -> ST_GeomFromText('POINT(35.55591 136.91764)', 4326), 3857) -> ) AS 岐阜県立森林文化アカデミー; +--------------------------------------------+ | 岐阜県立森林文化アカデミー | +--------------------------------------------+ | POINT(15241601.96541673 4239686.075814405) | +--------------------------------------------+ 1 row in set (0.00 sec) ST_Transformの実行例 SRID：4326 ⇒ 3857 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 17
18. Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 18 その他の機能強化
19. • MySQL 8.0.23で追加 • ST_FrechetDistance ：2つのジオメトリ間の離散フレシェ距離を返す関数 • ST_HausdorffDistance ：2つのジオメトリ間の離散ハウスドルフ距離を返す関数 • MySQL 8.0.24で追加（MySQLの独自拡張） • ST_LineInterpolatePoint ：LINSTRNG上の始点から指定した比率の位置にある POINTを求める • ST_LineInterpolatePoints ： LINSTRNG上の始点から指定した比率の位置にある POINT群を求める • ST_PointAtDistance ：LINSTRNG上の始点から指定した距離だけ 離れた地点のPOINTを求める 以下のSpatial関数が追加されました！ Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 19
20. mysql> SELECT ST_AsText(ST_LineInterpolatePoint(ST_GeomFromText('LINESTRING(0 0,10 10)', 3857), 0.5)); +-----------------------------------------------------------------------------------------+ | ST_AsText(ST_LineInterpolatePoint(ST_GeomFromText('LINESTRING(0 0,10 10)', 3857), 0.5)) | +-----------------------------------------------------------------------------------------+ | POINT(5 5) | +-----------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ST_LineInterpolatePointの実行例 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 20
21. mysql> SELECT ST_AsText(ST_LineInterpolatePoints(ST_GeomFromText('LINESTRING(0 0,10 10)', 3857), 0.5)); +------------------------------------------------------------------------------------------+ | ST_AsText(ST_LineInterpolatePoints(ST_GeomFromText('LINESTRING(0 0,10 10)', 3857), 0.5)) | +------------------------------------------------------------------------------------------+ | MULTIPOINT((5 5),(10 10)) | +------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> mysql> SELECT ST_AsText(ST_LineInterpolatePoints(ST_GeomFromText('LINESTRING(0 0,10 10)', 3857), 0.3)); +------------------------------------------------------------------------------------------+ | ST_AsText(ST_LineInterpolatePoints(ST_GeomFromText('LINESTRING(0 0,10 10)', 3857), 0.3)) | +------------------------------------------------------------------------------------------+ | MULTIPOINT((3 3),(6 6),(9 9)) | +------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ST_LineInterpolatePointsの実行例 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 21
22. mysql> SELECT ST_AsText(ST_PointAtDistance(ST_GeomFromText('LINESTRING(0 0,10 10)', 3857), 1.41421356)); +-------------------------------------------------------------------------------------------+ | ST_AsText(ST_PointAtDistance(ST_GeomFromText('LINESTRING(0 0,10 10)', 3857), 1.41421356)) | +-------------------------------------------------------------------------------------------+ | POINT(0.9999999983219685 0.9999999983219685) | +-------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) ST_PointAtDistanceの実行例 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 22 ≒√2
23. Copyright © 2022 Oracle and/or its affiliates. All rights reserved. 23 MySQLの今後の機能強化にも ご期待ください！ ※機能改善リクエスト、バグ報告はこちらで受け付けています https://bugs.mysql.com/
24. 24 Copyright © 2022 Oracle and/or its affiliates. All rights reserved. Q&A