DocsICP FilingConsole
官方文档
首页

ST_makeTrajectory

更新时间：
复制 MD 格式
产品详情
我的收藏

Constructs a trajectory object from spatial geometry, timestamps, and optional attribute data.

Syntax

Syntax 1 — Time range:

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 — From table rows:

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

Parameters

ParameterDescription
typeThe leaf type of the trajectory. Only ST_POINT is supported.
spatialThe spatial geometry object, described as a LineString or Point.
timespanThe time range of the trajectory. This is a closed interval that includes both the start time and end time.
startThe start time of the trajectory.
endThe end time of the trajectory.
timelineThe trajectory timeline. The number of time points must equal the number of points in the LineString.
attrs_jsonThe trajectory attributes and events in JSON format. Accepts null.
xThe x-axis coordinate array for the spatial geometry object.
yThe y-axis coordinate array for the spatial geometry object.
sridThe spatial reference identifier (SRID) of the trajectory. Required.
attr_field_namesThe names of all attribute fields in the trajectory.
rowsThe table used to store trajectory data. The first column must be timestamp, and the second and third columns must be float8.
haszSpecifies whether the trajectory is three-dimensional. Valid values: true (3D trajectory — the fourth column must be float8) and false (2D trajectory — the fourth column represents a trajectory attribute).
attrnamesThe names of trajectory attributes. Default value: attr1, attr2, ....

attrs_json format

The attrs_json parameter accepts a JSON object with the following 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 number of trajectory points. Must match the number of points in the spatial geometry object. All attribute fields must have the same number of values.

attributes: The trajectory attributes, including field definitions and value sequences. Required when leafcount is specified.

  • Attribute names can be up to 60 characters.

  • type: The data type of the field. Valid values: integer, float, string, timestamp, and bool.

  • length: The field value length. Valid values depend on the type:

    • integer: 1, 2, 4, or 8

    • float: 4 or 8

    • string: any value based on your requirements; default is 64, maximum is 253. Length counts actual characters, excluding the end identifier.

    • timestamp: fixed at 8; cannot be set.

    • bool: fixed at 1; cannot be set.

  • nullable: Whether the field accepts null values. Valid values: true and false. Default: true.

  • value: A JSON array of field values. Use null to represent a null element.

events: A JSON array of trajectory events. Each element is a key-value pair where the key is the event type and the value is the event time.

Usage notes

  • Time interpolation: When timespan (Syntax 1) or start/end (Syntax 2) is specified, ST_makeTrajectory interpolates time points based on the number of spatial points to generate the trajectory timeline.

  • Building from a table: Use array_agg(row(table.*)) to aggregate table rows into a trajectory (Syntax 5).

  • Custom syntax: If none of the built-in syntaxes meet your requirements, create a custom function with additional parameters after the first six fixed 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 Ganos version number. For example, for Ganos 4.5, use libpg-trajectory-4.5. Call ST_Version() to query the current Ganos version.

Examples

Syntax 1 — Build a trajectory from a time range. When a tsrange is provided, the function interpolates time points across the spatial points.

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

Syntax 2 — Build a trajectory from explicit 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"}]}'
);

Syntax 3 — Build a trajectory with an explicit 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"}]}'
);

Syntax 1 with null attributes — Omit attribute data by passing null for 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"]}}

Syntax 4 — Build 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]}}}}

Syntax 5 — Build a trajectory from table rows using array_agg:

-- Create a table and insert sample 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');

-- Build the trajectory. The first column is the timestamp, the second and third are x/y coordinates.
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"]}}}}
Previous：OverviewNext：ST_append