この記事では、タイムスタンプ付きのGPSポイントデータを、MySQLでLINESTRINGジオメトリとして表現する軌跡 (トラジェクトリ) へ変換する手順を解説します。ルート分析や交通予測、経路類似度の測定などの用途では、この処理が非常に重要になります。MySQLの強力な空間機能を活用すれば、GPSデータを効率的に管理、処理、可視化できます。
この機能はMySQL Community Edition、Enterprise Edition、およびHeatWave MySQLで利用できます。
軌跡 (トラジェクトリ) とは
軌跡 (トラジェクトリ) とは、物体が時空間を移動した道筋を指します。地理データにおいては、連続的に記録されたGPSポイントを結んだLINESTRINGジオメトリで表されることが多いです。たとえば、車両の移動に伴って取得した一連のGPSポイントをLINESTRING化すると、走行したルートがひと目でわかり、経路分析が容易になります。
GPSポイントの軌跡への変換
以下の例では、GPSポイントが格納されたgps_pointsテーブルを想定し、それぞれのレコードが以下の情報を持っているとします:
route_id: ルート識別子timestamp: GPSポイントが記録された時刻 (タイムスタンプ)geom:POINT形式のGPSジオメトリ
タイムスタンプ付きのGPSポイントをMySQLでLINESTRING形式のジオメトリに変換するには、まずST_Collectを用いてポイントをMULTIPOINTに集約し、その後、このMULTIPOINTをLINESTRINGに変換するという手順になります。以下に、その手順をステップごとに説明します:
1. GPSポイントを格納するテーブルの作成
例として、次のような構造のgps_pointsテーブルを作成します:
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で格納) );
GPSポイントは、WGS84地理座標系 (SRIDは4326) で記録されている点に注意してください。空間参照系 (SRS) やSRIDの詳細については、古いブログ記事 (英語)や最近の記事 (日本語)をご覧ください。
では、サンプルデータを挿入してみましょう:
INSERT INTO gps_points (route_id, timestamp, geom) VALUES
(1, '2024-10-01 10:00:00', ST_GeomFromText('POINT(55.017821 -1.425696)', 4326)),
(1, '2024-10-01 10:05:00', ST_GeomFromText('POINT(55.016469 -1.425955)', 4326)),
(1, '2024-10-01 10:10:00', ST_GeomFromText('POINT(55.015779 -1.427796)', 4326)),
(2, '2024-10-01 11:00:00', ST_GeomFromText('POINT(55.020381 -1.431492)', 4326)),
(2, '2024-10-01 11:05:00', ST_GeomFromText('POINT(55.018511 -1.427667)', 4326)),
(1, '2024-10-01 10:13:00', ST_GeomFromText('POINT(55.013967 -1.434109)', 4326)),
(2, '2024-10-01 11:07:00', ST_GeomFromText('POINT(55.015031 -1.431550)', 4326));
2. GPSポイントをLINESTRINGに集約
以下のクエリでは、gps_pointsテーブルのタイムスタンプ付きGPSポイントをもとに、LINESTRINGジオメトリを生成しています。タイムスタンプ順を保証するための工夫も含まれています。
SELECT ST_ASTEXT(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;
主要な処理を説明しますと:
- 集約 (アグリゲート): ST_Collect関数を使い、同じ route_idを持つPOINTSをまとめてMULTIPOINTにします。
- 並び替え (ソート): ST_Collect自体には順序情報を保持する仕組みがないため、代わりに
ROW_NUMBER()を用いて、タイムスタンプ順に連番を付与します。 - 型変換 (キャスト):
ST_Collectの戻り値であるMULTIPOINTは、経路を表現するためにLINESTRINGへ変換 (キャスト) しなければなりません。この処理を行うのがCAST関数です。
このクエリの結果は、分析や可視化に使用できる一連のLINESTRINGとなります。
+---------------------------------------------------------------------------------------------+ | ST_ASTEXT(CAST(ST_Collect(geom) AS linestring)) | +---------------------------------------------------------------------------------------------+ | LINESTRING(55.017821 -1.425696,55.016469 -1.425955,55.015779 -1.427796,55.013967 -1.434109) | | LINESTRING(55.020381 -1.431492,55.018511 -1.427667,55.015031 -1.43155) | +---------------------------------------------------------------------------------------------+
まとめ
タイムスタンプ付きのGPSポイントを軌跡として扱えるデータに変換することは、位置情報分析における重要なステップです。MySQLのST_CollectやCASTなどの空間関数を利用することで、POINTを簡単にLINESTRINGに集約できます。交通パターンの分析、ルートの予測、移動の可視化などの幅広い用途で、この手法は大きな力を発揮します。MySQL Community Edition、Enterprise Edition、そしてHeatWave MySQLに含まれる空間機能を活用すれば、GPSデータの可能性を引き出し、高度な地理空間解析を実現するための第一歩を踏み出せるでしょう。