Overview

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.

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

Important notes

Ganos Trajectory 1.6 is incompatible with Ganos Trajectory 1.0. To upgrade Ganos Trajectory from version 1.0 to 1.6, you need to contact Alibaba Cloud technical support personnel.

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 on the time range to accelerate the filtering of time.
    create index tr_timespan_time_index on trajtab 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 trajtab using btree (st_starttime(traj));
    create index tr_endtime_index on trajtab 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