PolarDB:ST_DumpSegments

Syntax

setof record ST_DumpSegements(trajectory traj);

Parameters

Return values

The function returns a two-dimensional table that contains the sequence numbers of trajectory segments and the trajectory segments.

Description

The function returns all trajectory segments of a specified trajectory.

Examples

• Basic usage:

  WITH traj_table AS
  (
    SELECT ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 35, 116 37)', 4326), ARRAY['2010-1-11 14:30'::timestamp,'2010-1-11 14:45', '2010-1-11 15:00'], '{"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]}, "acceleration": {"type": "string", "length": 20, "nullable" : true,"value": ["120", "130", "140"]}, "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"}]}') traj
  )
  SELECT (ST_DumpSegments(traj)).* FROM traj_table;

  Sample result:

  id |  segment                                        
  ---
   1 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2010-01-11 14:30:00","end_time":"2010-01-11 14:45:00","spatial":"SRID=4326;LINESTRING(114 35,115 35)","timeline":["2010-01-11 14:30:00","2010-01-11 14:30:00"],"attributes":{"leafcount":2,"velocity":{"type":"integer","length":2,"nullable":true,"value":[120,130]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[120.0,130.0]},"bearing":{"type":"float","length":8,"nullable":false,"value":[120.0,130.0]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["120","130"]},"active":{"type":"timestamp","length":8,"nullable":false,"value":["2010-01-01 14:30:00","2010-01-01 15:00:00"]}},"events":[{"1":"2010-01-01 14:30:00"},{"2":"2010-01-01 15:00:00"},{"3":"2010-01-01 15:30:00"}]}}
   2 | {"trajectory":{"version":1,"type":"STPOINT","leafcount":2,"start_time":"2010-01-11 14:45:00","end_time":"2010-01-11 15:00:00","spatial":"SRID=4326;LINESTRING(115 35,116 37)","timeline":["2010-01-11 14:30:00","2010-01-11 15:00:00"],"attributes":{"leafcount":2,"velocity":{"type":"integer","length":2,"nullable":true,"value":[130,140]},"accuracy":{"type":"float","length":4,"nullable":false,"value":[130.0,140.0]},"bearing":{"type":"float","length":8,"nullable":false,"value":[130.0,140.0]},"acceleration":{"type":"string","length":20,"nullable":true,"value":["130","140"]},"active":{"type":"timestamp","length":8,"nullable":false,"value":["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"}]}}

• Calculate the distance, duration, and velocity of each segment:

  WITH traj_table AS
  (
         SELECT st_maketrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 115 35, 116 37)', 4326), array['2010-1-11 14:30'::TIMESTAMP,'2010-1-11 14:45', '2010-1-11 15:00'], '{"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]}, "acceleration": {"type": "string", "length": 20, "nullable" : true,"value": ["120", "130", "140"]}, "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"}]}') traj ), segments AS
  (
         SELECT (st_dumpsegments(traj)).*
         FROM   traj_table ), tmp_result AS
  (
         SELECT id,
                st_length(segment)   AS distance,
                st_duration(segment) AS duration
         FROM   segments)
  SELECT id,
         distance,
         duration,
         distance/(extract(epoch FROM duration)/ 3600.0) AS velocity
  FROM   tmp_result;

  Sample result:

   id |     distance     | duration |     velocity     
  ----+------------------+----------+------------------
    1 | 91287.7884496231 | 00:15:00 | 365151.153798492
    2 | 239530.615197115 | 00:15:00 | 958122.460788461
  (2 rows)