Expands a trajectory into a set of records, one 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 of the trajectory.
| Column | Description |
|---|---|
id | The sequence number of the segment. |
segment | The trajectory segment. |
Description
ST_DumpSegments decomposes a trajectory into its individual segments and returns them as a set of records. Each segment covers the span between two consecutive leaf points, preserving the spatial path, timestamps, and attributes of the original trajectory for that interval.
Use this function when you need to analyze or compute properties — such as distance, duration, or velocity — for each segment independently.
Examples
Return all segments of a trajectory
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
Combine ST_DumpSegments with st_length and st_duration to compute per-segment metrics.
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)