ST_makeTrajectory - PolarDB - Alibaba Cloud Documentation Center

This topic describes the ST_makeTrajectory function. This function constructs a trajectory object.

Syntax

Syntax 1

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

Syntax 2

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

Syntax 3

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

Syntax 4

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

Syntax 5

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, which is described by using a LineString or Point object.
timespan	The time range of the trajectory. The time range is a closed interval that includes both the start time and 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 be the same as that of points in the LineString object.
attrs_json	The trajectory attributes and events in the JSON format. The value can be null.
attr_field_names	The array of the names of all attribute fields for the trajectory.
x	The x-axis for the spatial geometry object, which is an array.
y	The y-axis for the spatial geometry object, , which is an array.
srid	The spatial reference identifier (SRID) of the trajectory. This parameter is required.
rows	The table that is used to store trajectories. The value in the first column is of the timestamp data type, and the values in the second and third columns are of the float8 data type.
hasz	Specifies whether the trajectory is a three-dimensional trajectory. true: The trajectory is a three-dimensional trajectory. The values in the fourth column must be of the float8 data type. false: The trajectory is a two-dimensional trajectory. The values in the fourth column indicates the trajectory attribute.
attrnames	The names of the attributes for the trajectory. If you do not specify this parameter, the default value is `attr1, attr2...`.

The following example shows the format of the attrs_json parameter:
```
{
  "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 in the trajectory object. The value must be consistent with the number of spatial points in the spatial geometry object. The value is also the same as the number of values for each attribute field. All attribute fields must have the same number of values.
attributes: the trajectory attributes, including the definitions and the sequence of values for all attribute fields. This parameter is required if the leafcount parameter is specified. The following items describe attribute definitions and requirements:
- The name of an attribute can contain a maximum of 60 characters.
- type: the data type of the field. Valid values: integer, float, string, timestamp, and bool.
- length: the length of the field value. The following list describes the valid values that you can specify for the length parameter based on the value of the type parameter.
  - If type is set to integer, the valid values for length are 1, 2, 4, and 8.
  - If type is set to float, the valid values for length are 4 and 8.
  - If type is set to string, you can specify length based on your business requirements. The default value is 64. The maximum value is 253.The length of a string is the actual number of the string characters, which excludes the end identifier.
  - If type is set to timestamp, the default value 8 is used for length. You cannot specify a value for length.
  - If type is set to bool, the default value 1 is used for length. You cannot specify a value for length.
- nullable: specifies whether the field can be null. Valid values: true and false. Default value: true.
- value: a sequence of field values. The value of value is a JSON array. You can use null to represent a null element in the JSON array.
events: the trajectory events. The value of events is a JSON array. Each element in the JSON array is a key-value pair. key indicates the type of the event. value indicates the time of the event.
If the timespan parameter or the start and end parameters are specified as time, the ST_makeTrajectory function interpolates time points based on the number of spatial points to generate the trajectory timeline.
You can use the array_agg(row(table.*)) function to aggregate the rows of a table and convert the rows into trajectories. For more information, see 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. For example, you can execute the following statement:
```
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;
```
Note x.y in the example is the Ganos version. For example, if the Ganos version is 4.5, the value of x.y is libpg-trajectory-4.5. You can call the ST_Version function to query the Ganos version.

Examples

-- (1) ST_MakeTrajectory with timestamp range
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"}]}');                        st_maketrajectory         
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 {"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) ST_MakeTrajectory with start timestamp and end timestamp
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"}]}');                        st_maketrajectory         
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 {"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) ST_MakeTrajectory with 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"}]}');                        st_maketrajectory         
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 
 {"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) json is null
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); 
                        st_maketrajectory                                                                                                                  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 
{"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) ST_MakeTrajectory make from points
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);
                                                            st_maketrajectory                             

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{"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) ST_MakeTrajectory from table
create table tjrows(t timestamp, x double precision, y double precision, id int, attr text);
CREATE TABLE

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');
INSERT 0 3

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

                       st_maketrajectory

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------
 {"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,"valu
e":["the first point","the second point","the third point"]}}}}
(1 row)