Trajectory data records the continuous location change information about a moving object, such as a vehicle or a person. Trajectory data is typical spatio-temporal data. You can analyze and understand trajectory data to study many important issues.

Overview

Ganos Trajectory is an extension of ApsaraDB for PolarDB. Ganos Trajectory provides a series of data types, functions, and stored procedures to help you efficiently manage, query, and analyze spatio-temporal trajectory data.

Quick start

  • Create an extension.

    Create Extension Ganos_trajectory cascade;
  • Create the enumeration type for a spatial geometry object.

    CREATE TYPE leaftype AS ENUM ('STPOINT', 'STPOLYGON');
  • Create a trajectory table.

    Create Table traj_table (id integer, traj trajectory);
  • Insert trajectory data.

    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 spatial index for the trajectory table.

    -- Create a function-based spatial index to accelerate the filtering of spatial data.
    create index tr_spatial_geometry_index on trajtab using gist (st_trajectoryspatial(traj));
    
    -- Accelerate the filtering of spatial data for a spatial data query.
    select id, traj_id from traj_test where st_intersects(st_trajectoryspatial(traj), ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))')) = true and st_intersects(st_trajectoryspatial(traj), ST_GeomFromText('POLYGON((116.5172424485498 39.904984732832744,116.5543342491403 39.904984732832744,116.5543342491403 39.93294918082651,116.5172424485498 39.93294918082651,116.5172424485498 39.904984732832744))')) = true;
  • Create temporal indexes for the trajectory table.
    -- Create a function-based temporal index to accelerate the filtering of time.
    create index tr_timespan_time_index on traj_table using gist (st_timespan(traj));
    
    -- Create function-based indexes on the start time and end time of a trajectory object.
    create index tr_starttime_index on traj_tablee using btree (st_starttime(traj));
    create index tr_endtime_index on traj_table using btree (st_endtime(traj));
    
    -- Accelerate the filtering of time for a query.
    select id, traj_id from traj_split where st_starttime(traj) > '2008-02-02 13:30:44'::timestamp and st_endtime(traj) < '2008-02-03 17:30:44'::timestamp;
  • Create a spatio-temporal composite index (btree_gist) for the trajectory table.

    btree_gist can be used to create a spatio-temporal composite index that is applicable to trajectory data queries that involve the filtering of both time and spatial data. A spatio-temporal composite index simplifies SQL statements for spatial-temporal data queries and improves query efficiency.

    -- Use btree_gist to create a spatio-temporal composite index on the start time, end time, and spatial data of a trajectory object.
    create index tr_traj_test_stm_etm_sp_index on traj_test using gist (st_starttime(traj),st_endtime(traj),st_trajectoryspatial(traj));
    
    -- Query spatio-temporal data.
    select id,traj_id from traj_test where st_starttime(traj) > '2008-02-02 13:30:44'::timestamp and st_endtime(traj) < '2008-02-03 17:30:44'::timestamp and st_intersects(st_trajectoryspatial(traj), ST_GeomFromText('POLYGON((116.46747851805917 39.92317964155052,116.4986540687358 39.92317964155052,116.4986540687358 39.94452401711516,116.46747851805917 39.94452401711516,116.46747851805917 39.92317964155052))')) = true and st_intersects(st_trajectoryspatial(traj), ST_GeomFromText('POLYGON((116.5172424485498 39.904984732832744,116.5543342491403 39.904984732832744,116.5543342491403 39.93294918082651,116.5172424485498 39.93294918082651,116.5172424485498 39.904984732832744))')) = true;
  • Query the start time and end time of trajectory objects.

    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)
  • Query trajectory object information.

    -- 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)
  • Analyze the proximity between trajectory objects.

    postgres=# 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)
  • Delete the extension.

    Drop Extension Ganos_trajectory cascade;

SQL reference

For more information, see Trajectory SQL reference.