ST_DumpSegments Overview for Trajectory Decomposition - PolarDB

Expands a trajectory into a set of its constituent segments, returning one row per segment.

Syntax

SETOF RECORD ST_DumpSegments(trajectory traj);

Parameters

Parameter	Description
`traj`	The trajectory object.

Return values

Returns a set of records. Each record represents one segment — the path between two consecutive leaf points in the trajectory — and contains the following fields:

Field	Type	Description
`id`	integer	The 1-based sequence number of the segment within the trajectory.
`segment`	trajectory	The trajectory segment object.

Description

ST_DumpSegments expands one trajectory into N rows, where N equals the number of leaf points minus one. Each row corresponds to the path between two consecutive leaf points, preserving the spatial geometry, timestamps, and attributes for that interval.

To expand the composite return value in SQL, use the .* notation: (ST_DumpSegments(traj)).*.

Examples

Basic usage

The following example builds a 3-point trajectory and dumps it into segments. A trajectory with 3 leaf points produces 2 segments.

WITH traj_table AS (
  SELECT ST_MakeTrajectory(
    'STPOINT'::leaftype,
    st_geomfromtext('LINESTRING (114 35, 115 35, 116 37)', 4326),
    ARRAY['2010-1-11 14:30'::timestamp, '2010-1-11 14:45', '2010-1-11 15:00'],
    '{"leafcount":3,"attributes":{"velocity":{"type":"integer","length":2,"nullable":true,"value":[120,130,140]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120,130,140]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120,130,140]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["120","130","140"]},"active":{"type":"timestamp","nullable":false,"value":["Fri Jan 01 14:30:00 2010","Fri Jan 01 15:00:00 2010","Fri Jan 01 15:30:00 2010"]}},"events":[{"1":"Fri Jan 01 14:30:00 2010"},{"2":"Fri Jan 01 15:00:00 2010"},{"3":"Fri Jan 01 15:30:00 2010"}]}'
  ) traj
)
SELECT (ST_DumpSegments(traj)).* FROM traj_table;

Sample result:

 id | segment
----+---------
  1 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2010-01-11 14:30:00","end_time":"2010-01-11 14:45:00","spatial":"SRID=4326;LINESTRING(114 35,115 35)","timeline":["2010-01-11 14:30:00","2010-01-11 14:30:00"],"attributes":{"leafcount":2,"velocity":{"type":"integer","length":2,"nullable":true,"value":[120,130]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120.0,130.0]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120.0,130.0]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["120","130"]},"active":{"type":"timestamp","length":8,"nullable":false,"value":["2010-01-01 14:30:00","2010-01-01 15:00:00"]}},"events":[{"1":"2010-01-01 14:30:00"},{"2":"2010-01-01 15:00:00"},{"3":"2010-01-01 15:30:00"}]}}
  2 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2010-01-11 14:45:00","end_time":"2010-01-11 15:00:00","spatial":"SRID=4326;LINESTRING(115 35,116 37)","timeline":["2010-01-11 14:30:00","2010-01-11 15:00:00"],"attributes":{"leafcount":2,"velocity":{"type":"integer","length":2,"nullable":true,"value":[130,140]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[130.0,140.0]},"bearing":{"type":"float","length":8,"nullable":false,"value":[130.0,140.0]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["130","140"]},"active":{"type":"timestamp","length":8,"nullable":false,"value":["2010-01-01 15:00:00","2010-01-01 15:30:00"]}},"events":[{"1":"2010-01-01 14:30:00"},{"2":"2010-01-01 15:00:00"},{"3":"2010-01-01 15:30:00"}]}}

Calculate distance, duration, and velocity per segment

Pass each segment to ST_Length and ST_Duration, then derive velocity from the ratio.

WITH traj_table AS (
  SELECT st_maketrajectory(
    'STPOINT'::leaftype,
    st_geomfromtext('LINESTRING (114 35, 115 35, 116 37)', 4326),
    array['2010-1-11 14:30'::TIMESTAMP, '2010-1-11 14:45', '2010-1-11 15:00'],
    '{"leafcount":3,"attributes":{"velocity":{"type":"integer","length":2,"nullable":true,"value":[120,130,140]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120,130,140]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120,130,140]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["120","130","140"]},"active":{"type":"timestamp","nullable":false,"value":["Fri Jan 01 14:30:00 2010","Fri Jan 01 15:00:00 2010","Fri Jan 01 15:30:00 2010"]}},"events":[{"1":"Fri Jan 01 14:30:00 2010"},{"2":"Fri Jan 01 15:00:00 2010"},{"3":"Fri Jan 01 15:30:00 2010"}]}'
  ) traj
),
segments AS (
  SELECT (st_dumpsegments(traj)).*
  FROM traj_table
),
tmp_result AS (
  SELECT id,
         st_length(segment)   AS distance,
         st_duration(segment) AS duration
  FROM segments
)
SELECT id,
       distance,
       duration,
       distance / (extract(epoch FROM duration) / 3600.0) AS velocity
FROM tmp_result;

Sample result:

 id |     distance     | duration |     velocity
----+------------------+----------+------------------
  1 | 91287.7884496231 | 00:15:00 | 365151.153798492
  2 | 239530.615197115 | 00:15:00 | 958122.460788461
(2 rows)

Related functions

ST_MakeTrajectory — constructs a trajectory object from geometry, timestamps, and attributes
ST_Length — returns the spatial length of a trajectory segment
ST_Duration — returns the time duration of a trajectory segment