ST_DumpSegments

语法

setof record ST_DumpSegements(trajectory traj);

参数

返回值

返回一张二维的表，包含轨迹点的序号和每个轨迹子段对象。

描述

返回轨迹中所有的分段轨迹对象。

示例

• 简单函数

  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;

  返回结果如下：

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

• 分别计算每一段轨迹的长度，时间以及速度。

  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;

  返回结果如下：

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