Construct a trajectory object with ST_makeTrajectory - PolarDB

ST_makeTrajectory builds a trajectory object from spatial geometry, time data, and optional attributes. It returns a value of type trajectory.

Syntax

Five variants are available. Choose the one that matches how your time data is structured.

Syntax 1 — Use when you have a timestamp range (tsrange):

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

Syntax 2 — Use when you have explicit start and end timestamps:

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

Syntax 3 — Use when you have an array of timestamps, one per spatial point:

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

Syntax 4 — Use when your spatial data is in separate coordinate arrays rather than a geometry object:

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 — Use when aggregating rows directly from a table:

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

Parameters

Common parameters (Syntaxes 1–3)

Parameter	Description
`type`	The type of the trajectory. Only `STPOINT` is supported.
`spatial`	The spatial geometry object, described as a `LineString` or `Point`.
`timespan`	The time range of the trajectory. A closed interval that includes both the start and end time.
`start`	The start time of the trajectory.
`end`	The end time of the trajectory.
`timeline`	The trajectory timeline. The number of timestamps must equal the number of points in the `LineString`.
`attrs_json`	The trajectory attributes and events in JSON format. Accepts `null`.

Spatial and temporal parameters (Syntax 4)

Parameter	Description
`x`	The x-axis values of the spatial geometry, as an array.
`y`	The y-axis values of the spatial geometry, as an array.
`srid`	The spatial reference identifier (SRID) of the trajectory. Required.
`timeline`	The trajectory timeline.
`attr_field_names`	The names of all attribute fields, as an array.

Table aggregation parameters (Syntax 5)

Parameter	Description
`rows`	The aggregated table rows. The first column must be `timestamp`, and the second and third columns must be `float8`.
`hasz`	Specifies whether the trajectory is three-dimensional. `true`: three-dimensional — the fourth column must be `float8`. `false`: two-dimensional — the fourth column is treated as a trajectory attribute.
`attrnames`	The names of the trajectory attributes. Defaults to `attr1, attr2...`.

The attrs_json parameter

attrs_json is a JSON object with two top-level fields: leafcount and attributes, plus an optional events array.

{
  "leafcount": 3,
  "attributes": {
    "velocity": {
      "type": "integer",   // Valid values: integer, float, string, timestamp, bool
      "length": 2,         // integer: 1, 2, 4, or 8
      "nullable": true,    // Default: true
      "value": [120, null, 140]
    },
    "accuracy": {
      "type": "float",
      "length": 4,         // float: 4 or 8
      "nullable": false,
      "value": [120, 130, 140]
    },
    "bearing": {
      "type": "float",
      "length": 8,
      "nullable": false,
      "value": [120, 130, 140]
    },
    "vesname": {
      "type": "string",
      "length": 20,        // string: any value up to 253; default 64; excludes null terminator
      "nullable": true,
      "value": ["dsff", "fgsd", null]
    },
    "active": {
      "type": "timestamp", // length is always 8; cannot be set manually
      "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"}
  ]
}

Field rules:

leafcount: the number of trajectory points. Must equal the number of spatial points and the number of values in each attribute field.
attributes: required when leafcount is specified.
- Attribute names can be up to 60 characters.
- type: integer, float, string, timestamp, or bool.
- length valid values by type:
  - integer: 1, 2, 4, or 8
  - float: 4 or 8
  - string: any value up to 253; default is 64; length counts characters, not the null terminator
  - timestamp: fixed at 8; cannot be set manually
  - bool: fixed at 1; cannot be set manually
- nullable: true or false; default is true.
- value: a JSON array. Use null for missing values.
events: a JSON array of key-value pairs, where the key is the event type and the value is the event time.

Usage notes

With Syntaxes 1 and 2 (tsrange or start/end), the function interpolates timestamps based on the number of spatial points to build the timeline.
Use array_agg(row(table.*)) to aggregate table rows before passing them to Syntax 5.
If the syntax listed in the "Syntax" section does not meet your requirements, you can customize the parameters that follow the first six fixed parameters to create a custom makeTrajectory function. Call ST_Version to get your Ganos version, then reference the corresponding library:
```
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 your Ganos version — for example, libpg-trajectory-4.5 for Ganos 4.5.

Examples

Build a vessel trajectory with attributes using a time range

This example constructs a three-point trajectory for a vessel with velocity, accuracy, bearing, name, and timestamp attributes, using a tsrange to define the time span.

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"}]}'
);

Build a trajectory with explicit start and end timestamps

Use this form when you know the start and end times and want the function to interpolate the intermediate 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"}]}'
);

Build a trajectory with per-point timestamps

Use this form when you have a precise timestamp for each spatial point, rather than interpolating from a range.

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"}]}'
);

Build a trajectory without attributes

Pass null for attrs_json when attribute data is not needed.

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
);

Build a trajectory from coordinate arrays

Use Syntax 4 when your x and y values are stored as separate arrays rather than in a geometry object.

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
);

Build a trajectory from a table

Use array_agg(row(table.*)) to aggregate table rows and pass them to Syntax 5. Set hasz to false for a two-dimensional trajectory; columns after the third are treated as attributes.

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;