Syntax, parameters, and examples of the ST_makeTrajectory function in AnalyticDB for PostgreSQL-AnalyticDB(AnalyticDB)-阿里云帮助中心

Constructs a trajectory object from spatial geometry data and a time sequence.

Syntax

ST_makeTrajectory has five variants. Choose the one that matches how your time data is structured.

Syntax 1: Time range

Use when you have a start and end time and want the function to interpolate intermediate timestamps.

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

Syntax 2: Start and end timestamps

Use when you have explicit start and end timestamps and want the function to interpolate intermediate timestamps.

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

Syntax 3: Timestamp array

Use when you have a precise timestamp for each trajectory point.

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

Syntax 4: Coordinate arrays

Use when your spatial data is stored as separate x and y 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: Table rows

Use when trajectory points are stored in a database table. Pass rows using array_agg(row(table.*)).

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

Parameters

Common parameters (Syntaxes 1–3)

Parameter	Type	Required	Description
`type`	leaftype	Yes	Trajectory type. Set to `ST_POINT`.
`spatial`	geometry	Yes	Spatial geometry of the trajectory. Accepts a `LineString` or `Point` object.
`timespan`	tsrange	Yes (Syntax 1)	Time range of the trajectory as a closed interval that includes both endpoints.
`start`	timestamp	Yes (Syntax 2)	Start timestamp of the trajectory.
`end`	timestamp	Yes (Syntax 2)	End timestamp of the trajectory.
`timeline`	timestamp[]	Yes (Syntax 3)	Array of timestamps, one per point in the `LineString`. The number of timestamps must equal the number of spatial points.
`attrs_json`	cstring	No	Trajectory attributes and events in JSON format. Accepts `null`.

When you use Syntax 1 or Syntax 2, ST_makeTrajectory interpolates intermediate timestamps based on the number of spatial points in the geometry.

Parameters for Syntax 4

Parameter	Type	Required	Description
`type`	leaftype	Yes	Trajectory type. Set to `ST_POINT`.
`x`	float8[]	Yes	x-coordinates of the trajectory points.
`y`	float8[]	Yes	y-coordinates of the trajectory points.
`srid`	integer	Yes	Spatial reference system identifier (SRID) of the trajectory.
`timeline`	timestamp[]	Yes	Timestamps for the trajectory points.
`attr_field_names`	text[]	Yes	Names of all attribute fields.
`attr_int4`	int4[]	No	Attribute values of type `int4`.
`attr_float8`	float8[]	No	Attribute values of type `float8`.
`attr_cstring`	text[]	No	Attribute values of type `cstring`.
`attr_any`	anyarray	No	Attribute values of any other type.

Parameters for Syntax 5

Parameter	Type	Required	Description
`rows`	anyarray	Yes	Aggregated table rows. The first column must be `TIMESTAMP`, the second and third columns must be `FLOAT8`. If `hasz` is `true`, the fourth column must also be `FLOAT8`.
`hasz`	boolean	Yes	Whether to build a three-dimensional trajectory. `true`: 3D trajectory (the fourth column is used as the z-coordinate). `false`: 2D trajectory (the fourth column is treated as an attribute).
`attrnames`	cstring[]	No	Names of the trajectory attributes. Defaults to `attr1, attr2, ...` if not specified.

The attrs_json parameter

The attrs_json parameter defines trajectory attributes and events using the following JSON structure:

{
  "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"}
  ]
}

leafcount: The total number of trajectory points. This value must match the number of spatial points in the geometry object and the number of values in each attribute field's value array.

attributes: Defines the attribute fields attached to each trajectory point. Required when leafcount is specified. Each attribute field has the following properties:

Property	Description
Field name	Up to 60 characters.
`type`	Data type of the field. Valid values: `integer`, `float`, `string`, `timestamp`, `bool`.
`length`	Storage length of the field value. Valid values depend on `type`: `integer` (1, 2, 4, 8); `float` (4, 8); `string` (default 64, maximum 253; length = number of characters, excluding the null terminator); `timestamp` (fixed at 8, cannot be set); `bool` (fixed at 1, cannot be set).
`nullable`	Whether the field accepts null values. Valid values: `true`, `false`. Default: `true`.
`value`	JSON array of field values, one per trajectory point. Use `null` to represent a missing value.

events: A JSON array of key-value pairs that record events along the trajectory. Each pair maps an event type (key) to the time the event occurred (value).

Examples

Syntax 1: Time range

The function interpolates three evenly spaced timestamps between the start and end time, one for each point in the LineString.

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

The output is a trajectory JSON object with the interpolated timeline and the attribute values you provided:

{"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"],...}}

Syntax 2: Start and end timestamps

Pass separate start and end timestamps instead of a tsrange. The function interpolates timestamps the same way as Syntax 1.

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

Syntax 3: Timestamp array

Provide an explicit timestamp for each point. The number of timestamps must equal the number of points in the LineString.

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

Syntax 1 with no attributes

Pass null as attrs_json to create a trajectory with no attribute fields.

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

Syntax 4: Coordinate arrays

Build a single-point trajectory from x and y coordinate arrays, with a velocity attribute stored as int4.

SELECT ST_MakeTrajectory(
  'STPOINT'::leaftype,
  ARRAY[1::float8],   -- x-coordinates
  ARRAY[2::float8],   -- y-coordinates
  4326,               -- SRID
  ARRAY['2010-01-01 11:30'::timestamp],
  ARRAY['velocity'],  -- attribute field names
  ARRAY[1::int4],     -- int4 attribute values
  NULL,               -- float8 attribute values
  NULL,               -- cstring attribute values
  NULL::anyarray      -- other attribute values
);

Syntax 5: Table rows

Convert rows from a database table into a trajectory object. This is useful for transforming historical point records into trajectory objects in bulk.

-- Create a sample table with timestamp and coordinate columns
CREATE TABLE location_points (
  t  timestamp,
  x  double precision,
  y  double precision,
  id int,
  attr text
);

INSERT INTO location_points 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');

-- Build a 2D trajectory, treating the 4th column onward as attributes
SELECT ST_MakeTrajectory(
  array_agg(row(location_points.*)),
  false,                          -- 2D trajectory
  '{"id","attr"}'::cstring[]      -- attribute field names
)
FROM location_points;

The output trajectory includes the spatial geometry derived from the x and y columns and the id and attr columns as attribute fields.

Usage notes

To create a custom variant of ST_makeTrajectory that accepts different attribute types beyond those in Syntax 4, define a function that wraps sqltr_traj_make_all_array with the first six fixed parameters followed by your custom attribute parameters:
```
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 GanosBase version. For example, for GanosBase 4.5, use libpg-trajectory-4.5. To check your GanosBase version, call ST_Version().Ganos
If you get a permissions error when creating a custom function, .