概要
軌道データは、車両または人などの移動特徴に関する連続位置更新情報を記録する。 軌道データは、典型的な時空間データの一種である。 軌道データを使用して詳細な分析を行うことができます。
Ganos Trajectoryは、AnalyticDB for PostgreSQLの拡張です。 Ganos Trajectoryは、一連のデータ型、関数、およびストアドプロシージャを提供します。 これにより、時空間軌跡データを管理、クエリ、および分析できます。
クイックスタート
拡張機能を作成します。
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 a trajectory index to accelerate the process of filtering spatio-temporal data records. create index tr_index on traj_table using gist (traj); --Run spatial data queries. You can find that the trajectory index accelerates the process of filtering spatial data records. 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))')); --Run temporal data queries. You can find that the trajectory index accelerates the process of filtering temporal data records. 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); --Run spatio-temporal data queries. You can find that the trajectory index accelerates the process of filtering spatio-temporal data records. 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);特定のディメンションで軌道インデックスを作成します。
--If you want to analyze trajectory data only from a specific dimension, create a trajectory index in that dimension. For example, if you do not want to analyze trajectory data in the z dimension, create a two-dimensional temporal trajectory index by using trajgist_op_2dt. create index tr_timespan_time_index on traj_table using gist (traj trajgist_op_2dt); --Query two-dimensional temporal data. The queries are run at a fast rate. 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 more trajectory indexes based on your business requirements. If you create one trajectory index, all queries run based on the trajectory index. If you create multiple trajectory indexes, AnalyticDB for PostgreSQL selects the optimal trajectory index when you run queries. 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)移動するオブジェクトの軌跡を照会します。
--Use an interpolation function to query the attributes of trajectory points. 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 reference)」をご参照ください。