概要
軌道データは、車両または人などの移動特徴に関する連続位置更新情報を記録する。 軌道データは、典型的な時空間データの一種である。 軌道データを使用して詳細な分析を行うことができます。
GanosBase Trajectoryは、AnalyticDB for PostgreSQLの拡張です。 GanosBase 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 filtering of spatio-temporal data. create index tr_index on traj_table using gist (traj); -- Use the trajectory index to accelerate filtering of spatial data in a query on spatial data. 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))')); -- Use the trajectory index to accelerate filtering of temporal data in a query on temporal data. 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); -- Use the trajectory index to accelerate filtering of spatio-temporal data in a query on spatio-temporal data. 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); -- Perform a query on two-dimensional spatio-temporal data. The query is accelerated. 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 multiple trajectory indexes, AnalyticDB for PostgreSQL selects the optimal trajectory index when you perform 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 リファレンス
詳細については、「基本概念」をご参照ください。