軌跡模型 - AnalyticDB

簡介

軌跡資料是針對移動對象（Moving Feature）所記錄的連續位置變化資訊，例如車輛的軌跡、人的軌跡等。軌跡資料是一類典型的時空資料，分析和理解這些軌跡資料能協助人們進行深入研究。

Ganos Trajectory是對象關係型資料庫ADB PG的一個擴充，提供了一組資料類型、函數和預存程序，協助使用者高效地管理、查詢和分析時空軌跡資料。

快速入門

建立擴充

Create extension ganos_spatialref;
Create extension ganos_geometry;
Create Extension Ganos_trajectory;

軌跡的枚舉類型

CREATE TYPE leaftype AS ENUM ('STPOINT', 'STPOLYGON');

建立軌跡表

Create Table traj_table (id integer, traj trajectory) DISTRIBUTED BY (id);

插入軌跡資料

insert into traj_table values
(1, 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":4,"nullable":false,"value":[120, 130, 140]},"accuracy":{"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"bearing":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]},"acceleration":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]}}}')),
(2, 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":4,"nullable":false,"value":[120, 130, 140]},"accuracy":{"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"bearing":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]},"acceleration":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]}}}')),
(3, 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":4,"nullable":false,"value":[120, 130, 140]},"accuracy":{"type":"integer","length":4,"nullable":false,"value":[120, 130, 140]},"bearing":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]},"acceleration":{"type":"float","length":4,"nullable":false,"value":[120, 130, 140]}}}')),
(4, 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));

建立軌跡索引

--建立軌跡索引，加速時空過濾效率
create index tr_index on traj_table using gist (traj);

--空間查詢時，加速空間過濾
select id, traj_id from traj_test where st_3dintersects(traj, ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))'));

--時間查詢時，加速時間過濾
select id, traj_id from traj_text where st_TContains(traj,'2008-02-02 13:30:44'::timestamp,'2008-02-03 17:30:44'::timestamp);

--時空查詢時，加速時空過濾
select id, traj_id from traj_test where st_3dintersects(traj, ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))'),'2008-02-02 13:30:44'::timestamp,'2008-02-03 17:30:44'::timestamp);

建立特定維度軌跡索引

--當我們只需要對軌跡進行特定維度分析時，可以只建立特定維度索引。當我們不關心軌跡的z維時，可以使用trajgist_op_2dt建立二維+時間索引
create index tr_timespan_time_index on traj_table using gist (traj trajgist_op_2dt);

--建立特定維度索引後對2維+時間查詢效果會更快
select id, traj_id from traj_test where st_2dintersects(traj, ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))'),'2008-02-02 13:30:44'::timestamp,'2008-02-03 17:30:44'::timestamp);

--可以同時有多個索引存在，建立任意一個即可支援所有查詢，當有多個時將自動選擇最優的索引
create index tr_timespan_time_index on trajtab using gist (traj trajgist_op_2d);
select id, traj_id from traj_test where st_2dintersects(traj, ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))'));

查詢軌跡

select st_startTime(traj), st_endTime(traj) from traj_table ;
    st_starttime     |     st_endtime      
---------------------+---------------------
 2010-01-01 14:30:00 | 2010-01-01 15:30:00
 2010-01-01 14:30:00 | 2010-01-01 15:30:00
 2010-01-01 14:30:00 | 2010-01-01 15:30:00
 2010-01-01 14:30:00 | 2010-01-01 15:30:00
 2010-01-01 14:30:00 | 2010-01-01 15:30:00
 2010-01-01 11:30:00 | 2010-01-01 15:00:00
 2010-01-01 11:30:00 | 2010-01-01 15:00:00
 2010-01-01 11:30:00 | 2010-01-01 15:00:00
(8 rows)

軌跡查詢

--通過插值函數查詢軌跡點的屬性
Select ST_velocityAtTime(traj, '2010-01-01 12:45') from traj_table  where id > 5; 
st_velocityattime 
-------------------                 
5                 
5  
4.16666666666667
(3 rows)

分析軌跡間的相近性

Select ST_euclideanDistance((Select traj From traj_table Where id = 6), 
                            (Select traj From traj_table Where id = 7));
 st_euclideandistance 
----------------------
   0.0334968923954815
(1 row)

刪除擴充

DROP Extension Ganos_Raster;
DROP extension ganos_geometry;
Drop Extension Ganos_trajectory;

SQL參考

詳細SQL參考請參見Trajectory SQL參考。