MySQLを使ってGPSデータを軌跡データへ変換する手法と、軌跡データの類似性比較手法

640 Views

August 09, 25

スライド概要

2025年8月9日(土)に開催された「FOSS4G KYUSHU 2025」での発表資料です。
https://foss4gtokai.my.canva.site/foss4g-kyushu-2025

MySQLを使ってGPSデータを軌跡データに変換する手法や、軌跡データの類似性を比較する手法などについて解説しています。

Yoshiaki Yamasaki

@yyamasaki1

スライド一覧

日本オラクル株式会社 MySQL HeatWave Data Architect

またはPlayer版

埋め込む »CMSなどでJSが使えない場合

ダウンロード

関連スライド

オブジェクトストレージ上のファイルをSQLで高速に分析/加工できるHeatWave Lakehouseのご紹介

heatwave lakehouse

Yoshiaki Yamasaki 383

オブジェクトストレージ上のファイルをSQLで高速に分析/加工できるHeatWave Lakehouseのご紹介

Yoshiaki Yamasaki >100

学振特別研究員になるために～2025年度申請版

学振 dc1 dc2 jsps pd

大上雅史 758K

研究に使える便利なフリーソフト ImageJ

imagej 放射線技師

片山豊 352.8K

StampFlyで学ぶマルチコプタ制御

伊藤恒平 335K

大規模言語モデルに追加学習で専門知識を教える試み (2023, arXiv:2312.03360)

Kan Hatakeyama 296.5K

各ページのテキスト

MySQLを使ってGPSデータを軌跡データへ変換する⼿法と、軌跡データの類似性⽐較⼿法 FOSS4G KYUSHU 2025 ⼭﨑由章 / Yoshiaki YAMASAKI MySQL HeatWave Data Architect ⽇本オラクル株式会社

⾃⼰紹介 • 名前︓⼭﨑由章（やまさきよしあき） • 所属︓⽇本オラクル株式会社 MySQL Community Team / MySQL Global Business Unit • 役割︓MySQL 及び MySQL HeatWave の普及促進活動、など • Twitter ID︓@yyamasaki1 • 出⾝地︓明⽯市(東経135度⼦午線を通るまち) • 趣味︓美味しいものを⾷べること、⾊々な所に⾏くこと、ボードゲーム、など 2 Copyright © 2025, Oracle and/or its affiliates

MySQLのGIS機能の歴史 2000 2005 MySQL 4.1.0 (2003) MyISAMに Spatial Extensions実装 5.0.16 (2005) InnoDBにも Spatial Extensions実装 5 Copyright © 2025, Oracle and/or its affiliates 2010 2015 Sun 2020 Oracle 5.7.1 (2013) Spatial専⽤の型を実装 Boostライブラリを利⽤した再実装 8.0.11 (2018) GAとなる 8.0.0 (2016) 関数名をST_*系に統⼀関数を⼤幅に追加回転楕円体としての演算 8.0.32 (2023) 座標系変換がほぼ全ての座標系に対応

GISデータを格納するための専⽤のデータが型ある • POINT︓点 • 例︓緯度、経度 • LINESTRING︓線 • 例︓ルート（道筋） • POLYGON︓多⾓形 • 例︓市町村の区画 • GEOMETRY︓POINT、LINESTRING、POLYGONをまとめて扱える ※それぞれの集合を扱えるデータ型もある (MULTIPOINT、MULTILINESTRING、MULTIPOLYGON、GEOMETRYCOLLECTION) 9 Copyright © 2025, Oracle and/or its affiliates

10.

GISデータを変換したり計算したりするための各種Spatial関数がある • テキストデータをGISデータに変換するための関数 • ST_GeomFromText() • ST_PointFromText() • ST_LineStringFromText() • ST_PolygonFromText() • ST_MultiPointFromText() • ST_MultiLineStringFromText() • ST_MultiPolygonFromText() • ST_GeometryCollectionFromText() 10 Copyright © 2025, Oracle and/or its affiliates

11.

12.

GeoJSONを扱うための関数、Geohashを扱うための関数もある • GeoJSONデータをGISデータに変換するための関数 • ST_GeomFromGeoJSON() • GISデータをテキストデータに変換するための関数 • ST_AsGeoJSON() • 経度、緯度からGeohashを求めるための関数 • ST_GeoHash() • Geohashをデコードし、デコードされたGeohash値を含むPOINTデータを返す関数 • ST_PointFromGeoHash() 12 Copyright © 2025, Oracle and/or its affiliates

13.

Spatial関数の⼀覧は、ドキュメントで確認できます • MySQL 8.4 Reference Manual / Spatial Function Reference https://dev.mysql.com/doc/refman/8.4/en/spatial-function-reference.html 13 Copyright © 2025, Oracle and/or its affiliates

https://dev.mysql.com/doc/refman/8.4/en/spatial-function-reference.html

14.

15.

16.

1. GPSデータを格納するためのテーブルを作成する • 時刻とGPSポイント(緯度経度)の情報を格納する列を含んだテーブルを作成するテーブル作成例） CREATE TABLE gps_points ( id INT AUTO_INCREMENT PRIMARY KEY, route_id INT NOT NULL, -- ルート識別⼦ timestamp DATETIME NOT NULL, -- GPSポイントが記録された時刻 (タイムスタンプ) geom POINT NOT NULL SRID 4326 -- GPSポイント (WGS84で格納) ); 16 Copyright © 2025, Oracle and/or its affiliates

17.

2. テーブルにデータを⼊れる例） INSERT INTO gps_points (route_id, timestamp, geom) VALUES (1, '2025-08-08 19:32:19', ST_GeomFromText('POINT(33.593207 130.401885)', 4326)), (1, '2025-08-08 19:33:09', ST_GeomFromText('POINT(33.593298 130.401940)', 4326)), (1, '2025-08-08 19:34:13', ST_GeomFromText('POINT(33.593188 130.401588)', 4326)), (1, '2025-08-08 19:35:13', ST_GeomFromText('POINT(33.592957 130.401008)', 4326)), (1, '2025-08-08 19:37:15', ST_GeomFromText('POINT(33.592464 130.399689)', 4326)), (1, '2025-08-08 19:38:10', ST_GeomFromText('POINT(33.592308 130.398995)', 4326)), (1, '2025-08-08 19:39:16', ST_GeomFromText('POINT(33.592100 130.398521)', 4326)), (1, '2025-08-08 19:40:13', ST_GeomFromText('POINT(33.592054 130.397843)', 4326)), (1, '2025-08-08 19:41:12', ST_GeomFromText('POINT(33.591939 130.397202)', 4326)), (1, '2025-08-08 19:42:18', ST_GeomFromText('POINT(33.591484 130.396461)', 4326)), (1, '2025-08-08 19:43:12', ST_GeomFromText('POINT(33.591304 130.395793)', 4326)), (1, '2025-08-08 19:44:14', ST_GeomFromText('POINT(33.590714 130.395762)', 4326)), (1, '2025-08-08 19:45:00', ST_GeomFromText('POINT(33.590549 130.396011)', 4326)), (1, '2025-08-08 19:46:35', ST_GeomFromText('POINT(33.590462 130.395970)', 4326)), (1, '2025-08-08 19:47:17', ST_GeomFromText('POINT(33.590226 130.396063)', 4326)); 17 Copyright © 2025, Oracle and/or its affiliates

18.

3. Spatial関数を使って、GPSのポイントデータを軌跡データに変換する実⾏例） mysql> SELECT ST_ASTEXT(CAST(ST_Collect(geom) AS LINESTRING)) FROM -> (SELECT route_id, -> timestamp, route_id毎にデータを時刻順にソート -> geom, -> ROW_NUMBER() OVER (PARTITION BY route_id ORDER BY timestamp) -> FROM gps_points) AS sorted_points -> GROUP BY route_id¥G *************************** 1. row *************************** ST_ASTEXT(CAST(ST_Collect(geom) AS linestring)): LINESTRING( 33.593207 130.401885,33.593298 130.40194,33.593188 130.401588,33.592957 130.401008, 33.592464 130.399689,33.592308 130.398995,33.5921 130.398521,33.592054 130.397843, 33.591939 130.397202,33.591484 130.396461,33.591304 130.395793,33.590714 130.395762, 33.590549 130.396011,33.590462 130.39597,33.590226 130.396063) 1 row in set (0.00 sec) ※ST_Collect関数︓単⼀のジオメトリ値を集合に変換する関数、この例ではPOINT型のデータをMULTIPOINT型に変換している ※CAST関数︓データ型を変換する関数、この例ではMULTIPOINT型のデータをLINESTRING型に変換している ※ST_ASTEXT関数︓ジオメトリデータをテキストデータに変換する関数 18 Copyright © 2025, Oracle and/or its affiliates

19.

20.

4. 変換したデータを別のテーブルに格納例） CREATE TABLE gps_line ( id INT AUTO_INCREMENT PRIMARY KEY, route_id INT NOT NULL, -- ルート識別⼦ geom LINESTRING NOT NULL SRID 4326 -- GPSポイントを軌跡に変換した値を格納 ); INSERT INTO gps_line(route_id, geom) SELECT route_id, CAST(ST_Collect(geom) AS LINESTRING) FROM (SELECT route_id, timestamp, geom, ROW_NUMBER() OVER (PARTITION BY route_id ORDER BY timestamp) FROM gps_points) AS sorted_points GROUP BY route_id; ※LINESTRING型のデータをテーブルにINSERTするため、2ページ前の実⾏例で使っていたST_ASTEXT関数は不要 20 Copyright © 2025, Oracle and/or its affiliates

21.

22.

23.

24.

25.

サンプルデータ昨⽇の19時32分から19時47分までの移動データ ※約1分間隔のデータであるため、少し荒いです INSERT INTO gps_points (route_id, timestamp, geom) VALUES (1, '2025-08-08 19:32:19', ST_GeomFromText('POINT(33.593207 130.401885)', 4326)), (1, '2025-08-08 19:33:09', ST_GeomFromText('POINT(33.593298 130.401940)', 4326)), (1, '2025-08-08 19:34:13', ST_GeomFromText('POINT(33.593188 130.401588)', 4326)), (1, '2025-08-08 19:35:13', ST_GeomFromText('POINT(33.592957 130.401008)', 4326)), (1, '2025-08-08 19:37:15', ST_GeomFromText('POINT(33.592464 130.399689)', 4326)), (1, '2025-08-08 19:38:10', ST_GeomFromText('POINT(33.592308 130.398995)', 4326)), (1, '2025-08-08 19:39:16', ST_GeomFromText('POINT(33.592100 130.398521)', 4326)), (1, '2025-08-08 19:40:13', ST_GeomFromText('POINT(33.592054 130.397843)', 4326)), (1, '2025-08-08 19:41:12', ST_GeomFromText('POINT(33.591939 130.397202)', 4326)), (1, '2025-08-08 19:42:18', ST_GeomFromText('POINT(33.591484 130.396461)', 4326)), (1, '2025-08-08 19:43:12', ST_GeomFromText('POINT(33.591304 130.395793)', 4326)), (1, '2025-08-08 19:44:14', ST_GeomFromText('POINT(33.590714 130.395762)', 4326)), (1, '2025-08-08 19:45:00', ST_GeomFromText('POINT(33.590549 130.396011)', 4326)), (1, '2025-08-08 19:46:35', ST_GeomFromText('POINT(33.590462 130.395970)', 4326)), (1, '2025-08-08 19:47:17', ST_GeomFromText('POINT(33.590226 130.396063)', 4326)); 25 Copyright © 2025, Oracle and/or its affiliates

26.

27.

28.

29.

30.

31.

32.

1. GPSデータを収集するテーブルを作成例） CREATE TABLE walk_trajectories ( id INT AUTO_INCREMENT PRIMARY KEY, walker_name VARCHAR(100), trajectory LINESTRING NOT NULL SRID 4326 ); 32 Copyright © 2025, Oracle and/or its affiliates

33.

[beta]

2. 歩⾏者のGPSデータを軌跡データに変換してからテーブルに格納
例）
INSERT INTO walk_trajectories (walker_name, trajectory) VALUES
('Red Walker', ST_GeomFromText('LINESTRING(
37.79562087383118 -122.46702694852982,<略>)', 4326));
INSERT INTO walk_trajectories (walker_name, trajectory) VALUES
('Orange Walker', ST_GeomFromText('LINESTRING(
37.79482108844495 -122.4645294833186,<略>)', 4326));
INSERT INTO walk_trajectories (walker_name, trajectory) VALUES
('Blue Walker', ST_GeomFromText('LINESTRING(
37.795951956800835 -122.46451100656672,<略>)', 4326));
INSERT INTO walk_trajectories (walker_name, trajectory) VALUES
('Green Walker', ST_GeomFromText('LINESTRING(
37.79479601418929 -122.46357793761146,<略>)', 4326));
33

Copyright © 2025, Oracle and/or its affiliates

34.

3. 特定のルートの軌跡データをテーブルに格納例） INSERT INTO walk_trajectories (walker_name, trajectory) VALUES ('Sample Route', ST_GeomFromText('LINESTRING( 37.79360088412314 -122.46428847312926,37.79744139891328 -122.46510386466979, 37.79937853647067 -122.46860817074773)', 4326)); 34 Copyright © 2025, Oracle and/or its affiliates

35.

36.

4. 特定のルートと歩⾏者のルートの類似度を⽐較実⾏例） mysql> SELECT walker_name, id, -> ST_FrechetDistance(trajectory, -> (SELECT trajectory -> FROM walk_trajectories -> WHERE walker_name='Sample Route')) as similarity -> FROM walk_trajectories -> ORDER BY similarity; +---------------+----+--------------------+ | walker_name | id | similarity | +---------------+----+--------------------+ | Sample Route | 5 | 0 | | Orange Walker | 2 | 241.65190882342154 | | Blue Walker | 3 | 261.68893003364565 | | Green Walker | 4 | 429.19109867209164 | | Red Walker | 1 | 453.59273618286403 | +---------------+----+--------------------+ 5 rows in set (0.00 sec) 36 Copyright © 2025, Oracle and/or its affiliates

37.

38.

39.

40.

41.

42.

参考資料 Ø MySQL 8.0で強化されたGIS機能と使⽤事例のご紹介＋α https://speakerdeck.com/yoshiakiyamasaki/mysql-8-dot-0deqiang-hua-saretagisji-nengtoshi-yong-shi-li-falsegoshao-jie-a Ø MySQL 8.0 GIS機能チュートリアル https://speakerdeck.com/yoshiakiyamasaki/mysql-8-dot-0-gisji-neng-tiyutoriaru-6052f01e445a-4f55-bcda-a0e3fad06332 Ø 周辺知識から理解するMySQL の GIS機能〜ClubMySQL #4 ※坂井さん(OSGeo.JP 運営委員＆⽇本MySQLユーザ会副代表)の発表資料 https://www.slideshare.net/sakaik/mysql-gis-clubmysql-4 42 Copyright © 2025, Oracle and/or its affiliates

43.

参考資料 Ø MySQLで処理するGIS 〜地球が丸いことを覚えたMySQL〜 https://speakerdeck.com/yoshiakiyamasaki/mysql-gis-foss4g-tokai-2023 Ø FOSS4G HOKKAIDO 2019 でのハンズオン資料 Ø 43 JavaScriptとMySQLでGISを作ってみよう https://foss4g.hokkaido.jp/archive/2019/ Copyright © 2025, Oracle and/or its affiliates

44.

参考ブログ Ø MySQLを⽤いたGPSデータの軌跡データへの変換 https://blogs.oracle.com/mysql-jp/post/transforming-gps-data-into-trajectories-usingmysql-jp Ø MySQLにおける空間軌跡の類似性分析 https://blogs.oracle.com/mysql-jp/post/analyzing-the-similarity-of-spatial-trajectories-inmysql-jp 44 Copyright © 2025, Oracle and/or its affiliates

45.

参考資料 n MySQLにシェープファイルをインポートする⽅法については、宮内さん(FOSS4G TOKAI実⾏委員)の発表資料やQiitaの記事を参考にして下さい 45 Ø MySQLにシェープファイルをインポートするツール(shp2mysql)を作った https://qiita.com/miyauchi/items/b4e810b3becf2cf07e2f Ø MySQLにシェープファイルをインポートしてWebGISを作る https://www.osgeo.jp/events/foss4g-2020/foss4g-2020-japan-online/foss4gjapan-2020-online-coreday#presentation_a6 Copyright © 2025, Oracle and/or its affiliates

46.

47.

柔軟なMySQLの利⽤⽅法 MySQLサーバーは全て共通のソースコードのためハイブリッド構成も可能 MySQLを⾃社で運⽤管理 MySQLのマネージドサービスクラウドネイティブなMySQL オンプレミスでのMySQL • バージョン選択や構成を最も柔軟に選択可能 MySQL HeatWave • MySQLチームが100％開発・提供するクラウド・サービス • Amazon RDS (MySQL)の 1/3以下のコスト • データ損失なし、⾃動フェイルオーバーの⾼可⽤性機能をマネージドサービスで提供 MySQL Operator for k8s • MySQL サーバーをKubernetes上に構築し運⽤管理 IaaS上でのMySQL • OCIのマーケットプレイスのイメージから簡単に環境構築商⽤版MySQL • コミュニティ版に加え、サポートやセキュリティに優れた商⽤版も MySQL :: MySQL Operator for Kubernetes テクニカルアップデートいずれの利⽤⽅法でもMySQL開発チームと連携した専⾨部隊によるサポートサービスをご利⽤いただけます※ 47 Copyright © 2025, Oracle and/or its affiliates ※コミュニティ版を除く

https://www.mysql.com/jp/news-and-events/web-seminars/mysql-operator-for-kubernetes-20220829-webinar-ja/

48.

Oracle Premier Support for MySQL • 最⼤のMySQLのエンジニアリングおよびサポート組織 • MySQL開発チームによるサポート • 29⾔語で世界クラスのサポートを提供 • メンテナンス・リリース、バグ修正、パッチ、アップデートの提供 • 24時間x365⽇サポート • MySQL コンサルティング・サポート開発チームと⼀体となったサポートサービス Get immediate help for any MySQL issue, plus expert advice ⇒ 商⽤版MySQL サーバー及び MySQL HeatWave 利⽤者に提供 48 Copyright © 2025, Oracle and/or its affiliates

49.

MySQLの最新情報配信 MySQLホームページ http://www.mysql.com/jp MySQL イベント http://www.mysql.com/jp/news-and-events/ MySQLニュースレター英語版&⽇本語版（⽉刊） https://www.mysql.com/jp/news-and-events/newsletter/ MySQL Twitter ⽇本語公式アカウント @mysql_jp 49 Copyright © 2025, Oracle and/or its affiliates

50.

51.

MySQL コミュニティの紹介 • MySQL コミュニティへの貢献プロセスの運営 • MySQL ユーザーグループへの⽀援 https://dev.mysql.com/community/mug/ • 全世界でのサードパーティによるカンファレンスやイベントへの⽀援や参加 https://dev.mysql.com/community/ • 教育ビデオの作成 • MySQL 短編動画 (MySQL Shorts) • MySQL ⼊⾨編シリーズ (MySQL 101 for Biginners) • https://www.youtube.com/@mysql • MySQL RockStar プログラム • MySQLの利⽤促進に最も精⼒的に取り組んだ MySQL コミュニティ・メンバーへの表彰 • 第1回: https://blogs.oracle.com/mysql/post/mysql-rockstars-2022 • MySQL ACE プログラム • MySQL プロジェクトでの ACE プログラムの運営 • https://ace.oracle.com/pls/apex/ace_program/r/oracle-aces/home 51 Copyright © 2025, Oracle and/or its affiliates

52.

MySQL への貢献 • MySQL オープンソースプロジェクトのコントリビューターコミュニティへの参加: https://forums.oracle.com/ords/apexds/post/contributing-code-to-mysql-8037 • コントリビュータになるために持つべきこと • • • • MySQL の機能を変更/修正したい、あるいは新しい機能を追加したいといった要望 MySQL ソースコードのダウンロード http://dev.mysql.com/downloads/ bugs.mysql.com のアカウント http://bugs.mysql.com or 有効な GitHub アカウント https://github.com • Oracle Contribution Agreement (OCA) への署名 https://oca.opensource.oracle.com/ • OCAは、コントリビューターとオラクルの両⽅を法的攻撃から保護する短い法的契約です。OCAに署名することにより、コントリビューターはオラクルがコントリビューターのコードをオラクル・ソフトウェアで使⽤することが法的に許可されていること、およびコントリビューターの知る限りにおいて、そのコードに特許的な問題がないことに同意することになります。 52 Copyright © 2025, Oracle and/or its affiliates

53.

MySQL 無償認証制度 • MySQL コミュニティチームは、Oracle University および Oracle Academy と協⼒し、 mylearn.oracle.com を介して、2ヶ⽉間の指定期間内に使⽤できる無料のトレーニングバウチャー/クレジットを受講者に提供します。 • ご興味のある⽅は、以下についての詳細をお知らせくだされば、MySQL コミュニティから連絡いたします。 • • • • 名前姓 Email アドレス居住国 • https://education.oracle.com/ 53 Copyright © 2025, Oracle and/or its affiliates

54.

連絡先 • • • • • • • • • • MySQL コミュニティとのコンタクト先⼀覧: MySQL コミュニティページ, https://dev.mysql.com/community/ MySQL Slack, https://mysqlcommunity.slack.com The Oracle MySQL ブログ, https://blogs.oracle.com/mysql/ The Oracle MySQL Japan ブログ, https://blogs.oracle.com/mysql-jp/ Planet MySQL, https://planet.mysql.com/ LinkedIn, https://www.linkedin.com/groups/60715/ ブログ, https://lefred.be/ MySQL フォーラム, http://lists.mysql.com/ ディスカッションフォーラム, http://forums.mysql.com 54 Copyright © 2025, Oracle and/or its affiliates

55.