​

In this blog post, we will walk through how to transform timestamped GPS points into trajectories represented as LINESTRING geometries in MySQL. This process is essential for applications like route analysis, traffic prediction, and path similarity measures. Using MySQL’s powerful spatial support, we can efficiently manage, process, and visualize GPS data. 

This functionality is available in MySQL Community and Enterprise Editions and HeatWave MySQL.

What Are Trajectories?

A trajectory is a path that an object follows through space over time. In geographic data, trajectories are often represented as LINESTRING geometries, which connect sequential GPS points into a continuous route. For example, a series of GPS points collected from a vehicle’s movement can be transformed into a trajectory, making it easier to analyze the route taken.

GPS Points to Trajectories

Imagine we have GPS data stored in a MySQL database in a table called gps_points. Each row represents a single GPS point, including:

  • route_id: Identifier for the route.
  • timestamp: Time the GPS point was recorded.
  • geom: GPS point stored as a POINT geometry.

To transform GPS points with timestamps into LINESTRING geometries in MySQL, the process involves aggregating points into a MULTIPOINT using ST_Collect and then converting the MULTIPOINT into a LINESTRING. Here’s how you can do it step by step:

1. Create a Table for Storing GPS Points

Assume you have a table named gps_points with the following structure:

CREATE TABLE gps_points (
  id INT AUTO_INCREMENT PRIMARY KEY,
  route_id INT NOT NULL, -- Identifier for the route
  timestamp DATETIME NOT NULL, -- Timestamp of the GPS point
  geom POINT NOT NULL SRID 4326 -- The GPS point as a geometry
);

Note that the GPS trajectories are represented using the WGS 84 geographic coordinate system (represented by SRID 4326). For more on spatial reference systems and SRIDs we refer to a previous blog post as well as a more recent one.

Now we can insert some sample data:

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. Aggregate Points into a LINESTRING

The following SQL query constructs LINESTRING geometries from timestamped GPS points in the gps_points table, while ensuring the points are ordered by their timestamp.

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;

The main parts of the query:

  1. Aggregation: The function ST_Collect is used to aggregate POINTS into MULTIPOINT grouping by their route_id.
  2. Sorting: The use of ROW_NUMBER() ensures the points are properly ordered by timestamp since ST_Collect ignores the ordering of points.
  3. Casting: The result of ST_Collect which is a MULTIPOINT must be cast to LINESTRING to represent a path. This is done by using the CAST function.

The result of the query is the set of linestrings that can be used for analysis or visualization.

+---------------------------------------------------------------------------------------------+
| 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)                      |
+---------------------------------------------------------------------------------------------+
Map of GPS coordinates
GPS data from Newcastle Upon Tyne (UK). Colors black and red correspond to route_id 1 and 2 respectively. Map data from OpenStreetMap 13-11-2024.

Summary

Transforming timestamped GPS points into trajectories is a crucial step in geospatial data analysis. MySQL’s spatial functions, such as ST_Collect and CAST, make it easy to aggregate points into meaningful LINESTRING geometries. Whether you’re analyzing traffic patterns, predicting routes, or visualizing movement, these tools provide a robust foundation. With these techniques, you can unlock the potential of your GPS data and pave the way for advanced geospatial analytics. This functionality is available in MySQL Community and Enterprise Editions and HeatWave MySQL.

​