ST_DumpPoints_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

将一个轨迹以一组点轨迹对象的方式进行返回。

语法

setof record ST_DumpPoints(trajectory traj);

参数

参数名称	描述

参数名称	描述
traj	轨迹对象。

返回值

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

参数名称	描述

参数名称	描述
id	点序号。
point	单点轨迹点。

描述

返回一组单点轨迹对象。

示例

WITH traj_table AS
       (
         SELECT ST_MakeTrajectory('STPOINT'::leaftype, st_geomfromtext('LINESTRING (114 35, 114 35, 116 37)', 4326), ARRAY['2010-1-11 14:30'::timestamp,'2010-1-11 14:30', '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_DumpPoints(traj)).* FROM traj_table;

返回结果如下：

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