Use ST_makeTrajectory to construct a trajectory object - ApsaraDB RDS

Constructs a trajectory object from spatial geometry data, time information, and optional attributes.

Syntax

Syntax 1 — Time range (tsrange)

trajectory ST_makeTrajectory(leaftype type, geometry spatial, tsrange timespan, cstring attrs_json);

Syntax 2 — Start and end timestamps

trajectory ST_makeTrajectory(leaftype type, geometry spatial, timestamp start, timestamp end, cstring attrs_json);

Syntax 3 — Timestamp array

trajectory ST_makeTrajectory(leaftype type, geometry spatial, timestamp[] timeline, cstring attrs_json);

Syntax 4 — Coordinate arrays

trajectory ST_makeTrajectory(leaftype type, float8[] x, float8[] y, integer srid, timestamp[] timeline, text[] attr_field_names, int4[] attr_int4, float8[] attr_float8, text[] attr_cstring, anyarray attr_any);

Syntax 5 — Table rows

trajectory ST_makeTrajectory(anyarray rows, bool hasz, cstring[] attrnames);

Parameters

Parameter	Description
`type`	The type of the trajectory. Only `ST_POINT` is supported.
`spatial`	The spatial geometry object of the trajectory. Described by a LineString or Point object.
`timespan`	The time range of the trajectory. A closed interval that includes both the start time and the end time.
`start`	The start time of the trajectory.
`end`	The end time of the trajectory.
`timeline`	The trajectory timeline. The number of time points must match the number of points in the LineString object.
`attrs_json`	The trajectory attributes and events in JSON format. Can be null.
`attr_field_names`	The names of all attribute fields for the trajectory.
`x`	The x-axis coordinate array for the spatial geometry object.
`y`	The y-axis coordinate array for the spatial geometry object.
`srid`	The spatial reference identifier (SRID) of the trajectory. Required.
`rows`	The table used to store trajectories. Column 1 must be of the `timestamp` type, and columns 2 and 3 must be of the `float8` type.
`hasz`	Specifies whether the trajectory is three-dimensional. Valid values: `true` (three-dimensional; column 4 must be of the `float8` type) and `false` (two-dimensional; column 4 is treated as a trajectory attribute).
`attrnames`	The attribute names for the trajectory. Default value: `attr1, attr2, ...`.

Usage notes

attrs_json format

The attrs_json parameter accepts a JSON object with the following top-level fields:

`leafcount`: The number of trajectory points. Must match the number of spatial points in the geometry object. All attribute fields must have the same number of values.
`attributes`: The trajectory attribute definitions and value sequences. Required when leafcount is specified.
`events`: A JSON array of key-value pairs, where each key is the event type and each value is the event time.

Each attribute in attributes supports the following properties:

Property	Description
Name	Maximum 60 characters.
`type`	The data type. Valid values: `integer`, `float`, `string`, `timestamp`, and `bool`.
`length`	The field value length. Valid values depend on `type`: `integer` (1, 2, 4, or 8); `float` (4 or 8); `string` (custom, default 64, maximum 253; length is the number of characters, excluding the end identifier); `timestamp` (fixed at 8, cannot be specified); `bool` (fixed at 1, cannot be specified).
`nullable`	Specifies whether the field can be null. Valid values: `true` and `false`. Default value: `true`.
`value`	A JSON array of field values. Use `null` to represent a null element.

Example `attrs_json` value:

{
  "leafcount": 3,
  "attributes": {
    "velocity": {
      "type": "integer",
      "length": 2,
      "nullable": true,
      "value": [120, null, 140]
    },
    "accuracy": {
      "type": "float",
      "length": 4,
      "nullable": false,
      "value": [120, 130, 140]
    },
    "bearing": {
      "type": "float",
      "length": 8,
      "nullable": false,
      "value": [120, 130, 140]
    },
    "vesname": {
      "type": "string",
      "length": 20,
      "nullable": true,
      "value": ["dsff", "fgsd", null]
    },
    "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"}
  ]
}

Time interpolation

When timespan (Syntax 1) or start and end (Syntax 2) are specified, the function interpolates time points based on the number of spatial points to generate the trajectory timeline.

Building trajectories from a table (Syntax 5)

Use array_agg(row(table.*)) to aggregate table rows into a trajectory object.

Custom makeTrajectory function

If the built-in syntaxes do not meet your requirements, create a custom function by extending the first six fixed parameters. For example:

CREATE OR REPLACE FUNCTION _ST_MakeTrajectory(
    type leaftype,
    x float8[],
    y float8[],
    srid integer,
    timespan timestamp[],
    attrs_name cstring[],
    attr1 float8[],
    attr2 float4[],
    attr3 timestamp[]
)
RETURNS trajectory
AS '$libdir/libpg-trajectory-x.y', 'sqltr_traj_make_all_array'
LANGUAGE 'c' IMMUTABLE PARALLEL SAFE;

Replace x.y with the Ganos Trajectory version. For example, if the version is 4.5, use libpg-trajectory-4.5. Call ST_Version to query the current version.

Examples

-- (1) Construct a trajectory using a timestamp range (tsrange)
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]},"vesname":{"type":"string","length":20,"nullable":true,"value":["adsf","sdf","sdfff"]},"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"}]}'
);

Output:

{"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]},"vesname":{"type":"string","length":20,"nullable":true,"value":["adsf","sdf","sdfff"]},"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"]}},"events":[{"1":"2010-01-01 14:30:00"},{"2":"2010-01-01 15:00:00"},{"3":"2010-01-01 15:30:00"}]}}
(1 row)

-- (2) Construct a trajectory using start and end timestamps
SELECT ST_MakeTrajectory(
    'STPOINT'::leaftype,
    st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326),
    '2010-01-01 14:30'::timestamp,
    '2010-01-01 15:30'::timestamp,
    '{"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]},"vesname":{"type":"string","length":20,"nullable":true,"value":["adsf","sdf","sdfff"]},"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"}]}'
);

Output:

{"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]},"vesname":{"type":"string","length":20,"nullable":true,"value":["adsf","sdf","sdfff"]},"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"]}},"events":[{"1":"2010-01-01 14:30:00"},{"2":"2010-01-01 15:00:00"},{"3":"2010-01-01 15:30:00"}]}}
(1 row)

-- (3) Construct a trajectory using a timestamp array
SELECT ST_MakeTrajectory(
    'STPOINT'::leaftype,
    st_geomfromtext('LINESTRING (114 35, 115 36, 116 37)', 4326),
    ARRAY['2010-01-01 14:30'::timestamp, '2010-01-01 15:00'::timestamp, '2010-01-01 15:30'::timestamp],
    '{"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]},"vesname":{"type":"string","length":20,"nullable":true,"value":["adsf","sdf","sdfff"]},"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"}]}'
);

Output:

{"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]},"vesname":{"type":"string","length":20,"nullable":true,"value":["adsf","sdf","sdfff"]},"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"]}},"events":[{"1":"2010-01-01 14:30:00"},{"2":"2010-01-01 15:00:00"},{"3":"2010-01-01 15:30:00"}]}}
(1 row)

-- (4) Construct a trajectory with a null attrs_json
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,
    null
);

Output:

{"trajectory":{"leafsize":3,"starttime":"Fri Jan 01 14:30:00 2010","endtime":"Fri Jan 01 15:30:00 2010","spatial":"LINESTRING(114 35,115 36,116 37)","timeline":["Fri Jan 01 14:30:00 2010","Fri Jan 01 15:00:00 2010","Fri Jan 01 15:30:00 2010"]}}
(1 row)

-- (5) Construct a trajectory from coordinate arrays
SELECT st_makeTrajectory(
    'STPOINT'::leaftype,
    ARRAY[1::float8],
    ARRAY[2::float8],
    4326,
    ARRAY['2010-01-01 11:30'::timestamp],
    ARRAY['velocity'],
    ARRAY[1::int4],
    NULL,
    NULL,
    NULL::anyarray
);

Output:

{"trajectory":{"version":1,"type":"STPOINT","leafcount":1,"start_time":"2010-01-01 11:30:00","end_time":"2010-01-01 11:30:00","spatial":"SRID=4326;POINT(1 2)","timeline":["2010-01-01 11:30:00"],"attributes":{"leafcount":1,"velocity":{"type":"integer","length":4,"nullable":true,"value":[1]}}}}
(1 row)

-- (6) Construct a trajectory from table rows
CREATE TABLE tjrows(t timestamp, x double precision, y double precision, id int, attr text);

INSERT INTO tjrows VALUES
    ('2000-01-01 10:00:00', 3, 5, 1, 'the first point'),
    ('2000-01-01 11:00:00', 4, 6, 2, 'the second point'),
    ('2000-01-01 11:05:00', 5, 7, 3, 'the third point');

SELECT ST_MakeTrajectory(array_agg(row(tjrows.*)), false, '{"id","attr"}'::cstring[])
FROM tjrows;

Output:

{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"2000-01-01 10:00:00","end_time":"2000-01-01 11:05:00","spatial":"LINESTRING(3 5,4 6,5 7)","timeline":["2000-01-01 10:00:00","2000-01-01 11:00:00","2000-01-01 11:05:00"],"attributes":{"leafcount":3,"id":{"type":"integer","length":4,"nullable":true,"value":[1,2,3]},"attr":{"type":"string","length":64,"nullable":true,"value":["the first point","the second point","the third point"]}}}}
(1 row)