This topic describes the capabilities of the GanosBase trajectory engine in processing sampling points, including trajectory filtering, trajectory segmentation, trajectory resampling, and trajectory simplification. The features help you preprocess moving objects and improve business development efficiency.
GanosBase trajectories
Trajectories are spatiotemporal objects that are crucial for analyzing and processing moving objects, such as pedestrians, vehicles, ships, and aircraft.
Trajectory data can be analyzed as a set of discrete points, or as a continuous line represented by a spatial polyline that evolves over time. Continuous trajectory lines are less sensitive to sampling frequency and can be used for various spatial operations as polylines. Discrete trajectory points are more sensitive to sampling methods and frequency but provide algorithmic simplicity. Functions for computing similarity and segmenting trajectories often operate based on data points.
For example, if a shared bicycle reports its coordinates (114.35, 39.28) at 17:42:30 on April 11, 2020, the following record is added to the database.
Time | x | y |
2020-04-11 17:42:30 | 114.35 | 39.28 |
Other information such as speed or direction may also be recorded at the sampling points. For example, if the speed of the bicycle at the preceding time is 4.3, the following record is generated.
Time | x | y | Speed |
2020-04-11 17:42:30 | 114.35 | 39.28 | 4.3 |
Over time, a series of trajectory points is accumulated. The following table shows the records for three points.
Time | x | y | Speed |
2020-04-11 17:42:30 | 114.35 | 39.28 | 4.3 |
2020-04-11 17:43:30 | 114.36 | 39.28 | 4.8 |
2020-04-11 17:45:00 | 114.35 | 39.29 | 3.5 |
The points form a spatiotemporal trajectory, as shown in the following figure.
The native GanosBase trajectory engine of PolarDB introduces the trajectory type, indexing, and spatiotemporal operators to resolve issues related to the storage, retrieval, and analysis of moving objects.
Common processing operations for trajectory sampling points
Background information
In most business scenarios, trajectories are sampled and stored in databases by systems, such as transportation management system (TMS) and automatic identification system (AIS), based on GPS signals. The GPS sampling frequency is relatively consistent. However, the following issues may occur due to the limits of vehicle movement, which can impact the usability of the trajectory data:
Abnormal trajectory points caused by GPS signal issues, such as drift points from poor signal quality.
Excessive invalid points due to transportation behavior, such as ships circling a port while awaiting a berth or vehicles resting at service areas.
Loss of trajectory points resulting in uneven sampling, which can lead to errors in subsequent analysis, such as similarity calculations.
Large data volumes that reduce frontend rendering efficiency, which can cause program failures and stuttering.
Processing methods
Starting from version 5.2, GanosBase introduces functions for processing trajectory sampling points. The functions allow users to effectively manage and analyze trajectory data to meet a wide range of business analysis and presentation requirements.
ST_removeDriftPoints (trajectory filtering)
The ST_removeDriftPoints function removes points in a trajectory that show significant deviations from the expected motion pattern. In most cases, the deviations are due to sampling errors. The function also removes speed anomalies caused by objects jumping within a short time frame due to dense but imprecise sampling. For more information, see ST_removeDriftPoints.
ST_Split (trajectory segmentation)
Processing trajectory sampling points is challenging due to the diverse requirements of different businesses. Various applications may require adjustments to trajectory points, which makes flexible trajectory segmentation capabilities essential for users to autonomously handle each segment of the trajectory. Trajectory segmentation has three categories:
cut_point: selects specific sampling points along a trajectory and splits the trajectory at the points. As shown in the following figure, if you select point B as a cut point, the trajectory is divided into two sub-trajectories, both of which include point B. A common use case for this is when a trajectory is too long and needs to be split into shorter segments for easier analysis or processing.
cut_edge: This operation is similar to cut point segmentation, but the selectable points are not limited to the original sampling points. Users can select points along the trajectory boundaries, such as point C derived from interpolation. Splitting at point C results in two segments that both contain point C. This type of segmentation is used to divide trajectories into fixed shapes, such as by time intervals or spatial grids.
drop_edge: selects an edge and removes it. After removal, the two endpoints of the deleted edge become part of two separate sub-trajectories. In most cases, this method is used to extract meaningful parts of a trajectory while discarding less relevant sections. For example, in the context of shared bicycles, you may want to remove stationary periods (where the bike is parked) and retain only the active travel segments.
GanosBase provides the ST_Split function to divide a trajectory into multiple segments by using geometric objects. For more information, see ST_Split.
ST_Resample (trajectory resampling)
ST_Resample refers to a resampling method for trajectory data to resolve the uneven distribution of trajectory points, which can occur due to noise reduction or other preprocessing methods. The uneven distribution can negatively impact the performance and analysis of local trajectory segments. GanosBase provides two resampling strategies:
add_point: increases the density of points along the trajectory. This is suitable for detailed analysis, such as density statistics or similarity matching.
drop_point: decreases the density of points along the trajectory. This is suitable for extracting long-term trends or simplifying the trajectory.
GanosBase provides the ST_Resample function for trajectory resampling. For more information, see ST_Resample.
Trajectory simplification
Trajectory simplification, also known as trajectory lossy compression, reduces the number of sampling points in a trajectory while preserving the general movement direction. This process effectively decreases data storage requirements and improves rendering performance.
In most cases, the Douglas-Peucker algorithm is used to simplify two-dimensional (2D) trajectories, as shown in the preceding figure. The algorithm starts by specifying a tolerance value that defines the maximum deviation between the original and simplified trajectories. The algorithm connects the start and end points of the trajectory and checks whether intermediate points meet the tolerance criterion. This involves repeatedly selecting the point with the largest distance from the simplified trajectory, generating a new simplified version, and checking if the distance from all original points to the simplified trajectory is within the tolerance. This process continues until all points are within the specified limit. However, the Douglas-Peucker algorithm applies only to 2D trajectories and requires that all sampling points lie on the same plane. In scenarios involving spatiotemporal trajectories, a trajectory may remain stationary for a prolonged period. From a three-dimensional (3D) perspective, a stationary point creates a long vertical line along the time axis. When observed in two dimensions, this vertical line collapses into a single point. Applying the traditional 2D Douglas-Peucker algorithm in this situation may lead to the elimination of one of the endpoints of the line, which distorts the 3D spatial structure of the trajectory. To address the issue, GanosBase uses Synchronized Euclidean Distance (SED) instead of the perpendicular Euclidean distance used in the standard Douglas-Peucker algorithm for trajectories with timestamps to ensure a more accurate and effective compression of spatiotemporal trajectories.
GanosBase provides the following functions for trajectory simplification:
Function | Description | References |
ST_Compress | Simplifies 2D trajectories by using the Douglas-Peucker algorithm. | |
ST_CompressSED | Simplifies spatiotemporal trajectories (a trajectory that includes spatial and temporal dimensions) by using the SED criterion. |
Best practices
Case 1: Remove drift points from ship latitude and longitude information
To accurately construct a ship navigation trajectory, extract the latitude and longitude information of the ship from the point table populated with data collected by AIS, construct a trajectory from the data, and then remove drift points from the trajectory and store the cleaned trajectory.
SELECT
ST_removeDriftPoints (-- Remove drift points.
ST_SetSRID ( -- Specify an SRID.
ST_MakeTrajectory ( -- Construct a trajectory object.
ARRAY_AGG ( ROW ( traj.arrival_time :: TIMESTAMP, st_x ( traj.pts ) :: DOUBLE PRECISION, traj.lat :: DOUBLE PRECISION, traj.rowid )
), FALSE, '{"rowid"}' :: cstring [] ), 4326 ),
40,
10,
'1 minute' :: INTERVAL
)
FROM
(
SELECT
time
ST_makepoint ( lon, lat ) pts,
lat,
rowid
FROM
point_table
WHERE
time IS NOT NULL
AND lon IS NOT NULL
AND lat IS NOT NULL
ORDER BY
rowid
) traj INTO trajectory_table;Case 2: Resample drone trajectory sampling points and count the point density
To analyze the movement patterns of a drone, segment the drone trajectory into 5-minute intervals, resample the data points within each interval and calculate the density of the trajectory points, and then use the calculated point density for each 5-minute segment as input for a machine learning model.
SELECT
ST_Density(
ST_Resample(ST_OnlyST(traj),
'{"add_point.period_lesser":"5 minute"}')
), 100, '30 minute'
) FROM table;Case 3: Simplify freight vehicle trajectory
A freight management customer collects extensive vehicle trajectory data for business analysis. To focus on specific journey segments, longer edges in the trajectory are removed to create shorter, analyzable sub-trajectories.
With traj AS(
SELECT
'{"trajectory":{"version":1,"type":"STPOINT","leafcount":19,"start_time":"2000-01-01 00:01:19.067179","end_time":"2000-01-01 03:24:25.946085","spatial":"LINESTRING(-100 -100 -100,-88.8925775739675 -86.6512698383691 -92.3767832526937,-79.6904716538265 -80.6515727923252 -84.2357598245144,-75.8435507711644 -73.7572890928326 -80.5007370118983,-70.6238425321256 -67.8213750167439 -74.5733173238113,-61.6014582272619 -61.0636760429479 -67.9874239303172,-56.1098577060426 -54.4264591250879 -64.5007972046733,-46.9800617334743 -49.4026757289345 -61.6160059720278,-41.7122942996211 -46.3224360072054 -56.5283147455193,-35.5646221285375 -38.1688933617746 -49.2775720101781,-31.7230528349367 -33.6970051738123 -44.1693710885011,-23.1585765127093 -26.5895827477798 -40.6539742602035,-16.7020264320696 -21.6133877349397 -37.3055470525287,-12.1044529232507 -14.1236051704424 -28.2295028120279,-3.77185660181567 -7.74744770256802 -24.3842111621052,0.488159407706304 -3.68223926316326 -19.9478872027248,6.33406881305078 4.54123636645575 -15.0410129944794,15.6666049417108 10.5611746329814 -11.2770220567472,14 11 -10)","timeline":["2000-01-01 00:01:19.067179","2000-01-01 00:12:36.116007","2000-01-01 00:23:53.164835","2000-01-01 00:35:10.213663","2000-01-01 00:46:27.262491","2000-01-01 00:57:44.311319","2000-01-01 01:09:01.360147","2000-01-01 01:20:18.408975","2000-01-01 01:31:35.457803","2000-01-01 01:42:52.506631","2000-01-01 01:54:09.555459","2000-01-01 02:05:26.604287","2000-01-01 02:16:43.653115","2000-01-01 02:28:00.701943","2000-01-01 02:39:17.750771","2000-01-01 02:50:34.799599","2000-01-01 03:01:51.848427","2000-01-01 03:13:08.897255","2000-01-01 03:24:25.946085"]}}'::trajectory as a
)
SELECT (ST_split(a, '{"drop_edge.spatial_distance_2d":10}')) FROM traj;