Sorts the timeline of a trajectory object in ascending order, reordering all associated spatial points and attribute arrays to match the new sequence.
Syntax
trajectory ST_sort(trajectory traj)Parameters
| Parameter | Type | Description |
|---|---|---|
traj | trajectory | The trajectory object to sort. |
Usage notes
When a trajectory is created from data collected out of order — for example, due to network delays or batch ingestion — its timeline may not be chronologically sorted. ST_sort reorders all components of the trajectory (timeline, spatial points, and attribute arrays) by ascending timestamp.
If the trajectory contains attributes, their value arrays are reordered in sync with the timeline. For example, if an attribute has values [0,1,2,...,15] before sorting, the values are repositioned so that each value stays aligned with its original spatial point, not its original array index.
Example
The following example sorts a trajectory whose timestamps are out of order. Two of the 16 timestamps in the input are swapped (09:13:39 appears before 09:14:48), and one timestamp near the end (21:18:30) is out of sequence. After sorting, the timeline and all attribute values are reordered accordingly.
-- Sort a trajectory with out-of-order timestamps
SELECT st_sort(
ST_makeTrajectory(
'STPOINT'::leaftype,
'LINESTRING(-179.48077 51.72814,-179.46731 51.74634,-179.46502 51.74934,-179.46183 51.75378,-179.45943 51.75736,-179.45560 51.76273,-179.44845 51.77186,-179.43419 51.78977,-179.41259 51.81643,-179.41001 51.81941,-179.40751 51.82223,-179.40497 51.82505,-179.40242 51.82796,-179.39981 51.83095,-179.39734 51.83398,-179.39499 51.83709)'::geometry,
ARRAY[
'2017-01-15 09:06:39'::timestamp,
'2017-01-15 09:14:48'::timestamp, -- out of order: should follow 09:13:39
'2017-01-15 09:13:39'::timestamp,
'2017-01-15 09:16:28'::timestamp,
'2017-01-15 09:19:48'::timestamp,
'2017-01-15 09:17:48'::timestamp,
'2017-01-15 09:23:19'::timestamp,
'2017-01-15 09:34:40'::timestamp,
'2017-01-15 09:30:28'::timestamp,
'2017-01-15 09:36:59'::timestamp,
'2017-01-15 09:38:09'::timestamp,
'2017-01-15 09:39:18'::timestamp,
'2017-01-15 09:40:40'::timestamp,
'2017-01-15 09:47:38'::timestamp,
'2017-01-15 21:18:30'::timestamp, -- out of order: should be last
'2017-01-15 09:48:49'::timestamp
],
'{"leafcount": 16, "attributes": {"heading": {"type": "integer", "length": 4, "nullable": false, "value": [0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15]}}}'
)
);Result:
st_sort
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"trajectory":{"version":1,"type":"STPOINT","leafcount":16,"start_time":"2017-01-15 09:06:39","end_time":"2017-01-15 21:18:30","spatial":"LINESTRING(-179.48077 51.72814,-179.46502 51.74934,-179.46731 51.74634,-179.46183 51.75378,-179.4556 51.76273,-179.45943 51.75736,-179.44845 51.77186,-179.41259 51.81643,-179.43419 51.78977,-179.41001 51.81941,-179.40751 51.82223,-179.40497 51.82505,-179.40242 51.82796,-179.39981 51.83095,-179.39499 51.83709,-179.39734 51.83398)","timeline":["2017-01-15 09:06:39","2017-01-15 09:13:39","2017-01-15 09:14:48","2017-01-15 09:16:28","2017-01-15 09:17:48","2017-01-15 09:19:48","2017-01-15 09:23:19","2017-01-15 09:30:28","2017-01-15 09:34:40","2017-01-15 09:36:59","2017-01-15 09:38:09","2017-01-15 09:39:18","2017-01-15 09:40:40","2017-01-15 09:47:38","2017-01-15 09:48:49","2017-01-15 21:18:30"],"attributes":{"leafcount":16,"heading":{"type":"integer","length":4,"nullable":false,"value":[0,2,1,3,5,4,6,8,7,9,10,11,12,13,15,14]}}}}
(1 row)The output trajectory has a sorted timeline from 2017-01-15 09:06:39 to 2017-01-15 21:18:30. The heading attribute values are reordered to follow the new timeline sequence: [0,2,1,3,5,4,6,8,7,9,10,11,12,13,15,14].