轨迹数据是针对移动对象（Moving Feature）所记录的连续位置变化信息，例如车辆的轨迹、人的轨迹等。轨迹数据是一类典型的时空数据，分析和理解这些轨迹数据能帮助人们研究许多重要问题。

概述

Ganos Trajectory是对象关系型数据库PostgreSQL的一个扩展，提供了一组数据类型、函数和存储过程，帮助用户高效地管理、查询和分析时空轨迹数据。

重要说明

ganos trajectory 1.6 版本和1.0版本不兼容，如要从1.0版本升级到1.6版本，请联系阿里云技术支持。

快速入门

  • 创建扩展

    1
    Create Extension Ganos_trajectory cascade;

  • 轨迹的枚举类型

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

  • 创建轨迹表

    1
    Create Table traj_table (id integer, traj trajectory);

  • 插入轨迹数据

    1
    2
    3
    4
    5
    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));

  • 创建轨迹空间索引

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    --创建轨迹索引，加速时空过滤效率。
    create index tr_index on trajtab using trajgist (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_TWithin(traj, ST_ToBox('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);
  • 创建特定维度轨迹索引
    1
    2
    3
    4
    5
    6
    7
    8
    9
    --当我们只需要对轨迹进行特定维度分析时，可以只建立特定维度的索引。如我们不关心轨迹的z维时，可以使用trajgist_op_2dt建立二维+时间索引。
    create index tr_timespan_time_index on trajtab using trajgist (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 trajgist (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))'));
  • 查询轨迹起、止时间
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    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)

  • 轨迹查询

    1
    2
    3
    4
    5
    6
    7
    8
    --通过插值函数查询轨迹点的属性。
    Select ST_velocityAtTime(traj, '2010-01-01 12:45') from traj_table  where id > 5;
    st_velocityattime
    -------------------                
    5                
    5 
    4.16666666666667
    (3 rows)

  • 分析轨迹间的相近性

    1
    2
    3
    4
    5
    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)

  • 删除扩展

    1
    Drop Extension Ganos_trajectory cascade;

SQL参考

详细SQL手册请参见 Trajectory SQL参考。