この関数は、軌道セグメントのコレクションとして軌道を返します。
構文
setof record ST_DumpSegements(trajectory traj);Parameters
パラメーター | 説明 |
traj | 軌道オブジェクト。 |
戻り値
この関数は、軌道セグメントのシーケンス番号と軌道セグメントを含む2次元テーブルを返します。
パラメーター | 説明 |
id | シーケンス番号。 |
セグメント | 軌道セグメント。 |
説明
この関数は、指定した軌道のすべての軌道セグメントを返します。
例
基本的な使い方:
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)