ST_sort-AnalyticDB(AnalyticDB)-阿里云帮助中心

Sorts the timeline of a trajectory object in ascending order and returns the sorted trajectory. When the timeline is sorted, the corresponding spatial points and all attribute values are reordered to match, keeping the trajectory internally consistent.

Use ST_sort when a trajectory's timestamps are out of order — for example, when data is collected from multiple sources or ingested in non-chronological batches.

Syntax

trajectory ST_sort(trajectory traj);

Parameters

Parameter	Description
`traj`	The trajectory object to sort.

Example

The following example creates a trajectory with 16 spatial points whose timestamps are intentionally out of order, then sorts it.

-- Create a trajectory with out-of-order timestamps and sort it.
-- Timestamps at index 1 and 2 (09:14:48 / 09:13:39) are swapped,
-- and several other pairs are also disordered.
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
      '2017-01-15 09:13:39'::timestamp,  -- out of order
      '2017-01-15 09:16:28'::timestamp,
      '2017-01-15 09:19:48'::timestamp,  -- out of order
      '2017-01-15 09:17:48'::timestamp,  -- out of order
      '2017-01-15 09:23:19'::timestamp,
      '2017-01-15 09:34:40'::timestamp,  -- out of order
      '2017-01-15 09:30:28'::timestamp,  -- out of order
      '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
      '2017-01-15 09:48:49'::timestamp   -- out of order
    ],
    '{"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]}}}'
  )
);

The output is a trajectory with:

Timeline sorted in ascending order: from 2017-01-15 09:06:39 to 2017-01-15 21:18:30
Spatial points reordered to match the sorted timeline
Attribute values reordered to match the sorted timeline — heading changes from [0,1,2,...,15] to [0,2,1,3,5,4,6,8,7,9,10,11,12,13,15,14]

                                                                  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)