Inserts a new attribute into a trajectory at a specified position.
Syntax
trajectory ST_InsertAttr(trajectory traj, anyarray arr, text name, integer loc default -1);Return value
Returns a trajectory with the new attribute inserted at the specified position.
Parameters
| Parameter | Type | Default | Description |
|---|---|---|---|
traj | trajectory | — | The original trajectory. |
arr | anyarray | — | An array of values for the new attribute. |
name | text | — | The name of the new attribute. |
loc | integer | -1 | The position at which to insert the new attribute. Valid values: -n to n, where n is the number of existing attributes. |
loc position behavior
| Value range | Behavior |
|---|---|
0 | Inserts before the leftmost (first) attribute column |
1 to n | Inserts after the k-th column, counted from left to right |
-1 (default) | Inserts after the rightmost (last) attribute column |
-2 to -n | Inserts counting from the right; -n inserts after the leftmost column |
Description
ST_InsertAttr adds a new attribute column to an existing trajectory. The new attribute is defined by an array of values (arr) and a column name (name). Use the loc parameter to control where the new attribute appears relative to existing attributes.
Positive loc values count from the left starting at 0; negative values count from the right starting at -1. The default (-1) appends the attribute after all existing columns.
Example
The following example creates a trajectory with five attributes — velocity, accuracy, bearing, acceleration, and active — then inserts a new integer attribute named add at position 3 (after bearing, before acceleration).
WITH traj AS (
SELECT ST_MakeTrajectory(
'STPOINT'::leaftype,
st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326),
'[2010-01-01 14:30, 2010-01-01 15:30)'::tsrange,
'{"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"]}}}'
) AS tj
)
SELECT ST_InsertAttr(tj, ARRAY[1, 4, 6], 'add', 3) FROM traj;Result:
{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"2010-01-01 14:30:00","end_time":"2010-01-01 15:30:00","spatial":"SRID=4326;LINESTRING(114 35,115 36,116 37)","timeline":["2010-01-01 14:30:00","2010-01-01 15:00:00","2010-01-01 15:30:00"],"attributes":{"leafcount":3,"velocity":{"type":"integer","length":2,"nullable":true,"value":[120,130,140]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120.0,130.0,140.0]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120.0,130.0,140.0]},"add":{"type":"integer","length":4,"nullable":true,"value":[1,4,6]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["120","130","140"]},"active":{"type":"timestamp","length":8,"nullable":false,"value":["2010-01-01 14:30:00","2010-01-01 15:00:00","2010-01-01 15:30:00"]}}}}The resulting attribute order is: velocity → accuracy → bearing → add → acceleration → active. The new add attribute has type integer, length 4, nullable: true, and values [1, 4, 6].