Returns all trajectory points of a trajectory as a set of (id, point) record pairs, one row per trajectory point.
Syntax
setof record ST_DumpPoints(trajectory traj);Parameters
| Parameter | Description |
|---|---|
traj | The trajectory object. |
Return values
Returns a two-column table with one row per trajectory point.
| Column | Type | Description |
|---|---|---|
id | integer | The 1-based sequence number of the trajectory point. |
point | trajectory | The trajectory point object. Each point contains the spatial coordinate (spatial), timestamps (timeline), and all attribute values defined in the original trajectory. |
Example
The following example creates a 3-point STPOINT trajectory and expands it into individual rows using ST_DumpPoints.
WITH traj_table AS (
SELECT ST_MakeTrajectory(
'STPOINT'::leaftype,
st_geomfromtext('LINESTRING (114 35, 114 35, 116 37)', 4326),
ARRAY['2010-1-11 14:30'::timestamp, '2010-1-11 14:30', '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_DumpPoints(traj)).* FROM traj_table;Use (ST_DumpPoints(traj)).* to expand all columns of the returned setof record in a single SELECT.
Sample result:
id | point
----+-------
1 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":1,"start_time":"2010-01-11 14:30:00","end_time":"2010-01-11 14:30:00","spatial":"SRID=4326;POINT(114 35)","timeline":["2010-01-11 14:30:00"],"attributes":{"leafcount":1,"velocity":{"type":"integer","length":2,"nullable":true,"value":[120]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120.0]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120.0]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["120"]},"active":{"type":"timestamp","length":8,"nullable":false,"value":["2010-01-01 14:30: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":1,"start_time":"2010-01-11 14:30:00","end_time":"2010-01-11 14:30:00","spatial":"SRID=4326;POINT(114 35)","timeline":["2010-01-11 14:30:00"],"attributes":{"leafcount":1,"velocity":{"type":"integer","length":2,"nullable":true,"value":[130]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[130.0]},"bearing":{"type":"float","length":8,"nullable":false,"value":[130.0]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["130"]},"active":{"type":"timestamp","length":8,"nullable":false,"value":["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"}]}}
3 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":1,"start_time":"2010-01-11 15:00:00","end_time":"2010-01-11 15:00:00","spatial":"SRID=4326;POINT(116 37)","timeline":["2010-01-11 15:00:00"],"attributes":{"leafcount":1,"velocity":{"type":"integer","length":2,"nullable":true,"value":[140]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[140.0]},"bearing":{"type":"float","length":8,"nullable":false,"value":[140.0]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["140"]},"active":{"type":"timestamp","length":8,"nullable":false,"value":["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"}]}}The trajectory is split into 3 rows. Each point value is a self-contained trajectory object with leafcount=1, carrying the spatial coordinate, timestamp, and attribute values for that specific point.
Related functions
ST_MakeTrajectory— constructs a trajectory object from geometry, timestamps, and attribute data