$30 off During Our Annual Pro Sale. View Details »

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

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

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

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

YoshiakiYamasaki

August 11, 2022
Tweet

More Decks by YoshiakiYamasaki

Other Decks in Technology

Transcript

  1. MySQLのGIS機能の機能追加/改善状況
    FOSS4G TOKAI 2022
    たんけん ぎふのもり
    山﨑 由章 / Yoshiaki Yamasaki
    MySQL Master Principal Solution Engineer
    MySQL Global Business Unit
    (Twitter ID:@yyamasaki1)
    2022年8月11日

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  5. • MySQLのGIS機能に望むこと
    https://qiita.com/miyauchi/items/5bdab7ea58f1114d6686
    2020年1月に宮内さんにまとめて頂いた要望
    Copyright © 2022 Oracle and/or its affiliates. All rights reserved.
    5

    View Slide

  6. Copyright © 2022 Oracle and/or its affiliates. All rights reserved.
    6
    その後の改善状況に
    ついてお伝えします

    View Slide

  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での発表資料より引用

    View Slide

  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のリリースノートより引用

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  15. • 当時のST_Trsansformは地理座標系のみをサポートしていて、
    地理座標系のデータ  投影座標系のデータ の変換が出来なかった
    3. ST_Transformの改善
    Copyright © 2022 Oracle and/or its affiliates. All rights reserved.
    15
    https://qiita.com/miyauchi/items/5bdab7ea58f1114d6686
    宮内さんのまとめより引用

    View Slide

  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

    View Slide

  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

    View Slide

  18. Copyright © 2022 Oracle and/or its affiliates. All rights reserved.
    18
    その他の機能強化

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  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

    View Slide

  23. Copyright © 2022 Oracle and/or its affiliates. All rights reserved.
    23
    MySQLの今後の機能強化にも
    ご期待ください!
    ※機能改善リクエスト、バグ報告はこちらで受け付けています
    https://bugs.mysql.com/

    View Slide

  24. 24 Copyright © 2022 Oracle and/or its affiliates. All rights reserved.
    Q&A

    View Slide

  25. View Slide