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

概述

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

重要说明

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

快速入门

  • 创建扩展

    CREATE EXTENSION ganos_trajectory CASCADE;
  • 创建轨迹表

    CREATE TABLE traj_table (id integer, traj trajectory);
  • 插入轨迹数据

    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 INDEX tr_index ON traj_table USING trajgist (traj);
    
    --空间查询时,加速空间过滤
    SELECT id, traj FROM traj_table 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 FROM traj_table WHERE st_TIntersects(traj, '2010-01-01 12:30:44'::timestamp,'2010-01-01 14:30:44'::timestamp);
    
    --时空查询时,加速时空过滤
    SELECT id, traj FROM traj_table 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))'),'2010-01-01 13:30:44'::timestamp,'2010-01-03 17:30:44'::timestamp);
  • 查询轨迹起、止时间
    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
    (4 rows)
  • 分析轨迹间的相近性

    WITH traj AS (
      SELECT ST_makeTrajectory('STPOINT', 'LINESTRING(1 1, 5 6, 9 8)'::geometry, '[2010-01-01 11:30, 2010-01-01 15:00)'::tsrange,
      '{"leafcount":3,"attributes":{"velocity": {"type": "integer", "length": 2,"nullable" : true,"value": [120,130,140]}, "accuracy": {"type": "float", "length": 4, "nullable" : false,"value": [120,130,140]}, "bearing": {"type": "float", "length": 8, "nullable" : false,"value": [120,130,140]}, "acceleration": {"type": "string", "length": 20, "nullable" : true,"value": ["120","130","140"]}, "active": {"type": "timestamp", "nullable" : false,"value": ["Fri Jan 01 11:35:00 2010", "Fri Jan 01 12:35:00 2010", "Fri Jan 01 13:30:00 2010"]}}, "events": [{"2" : "Fri Jan 02 15:00:00 2010"}, {"3" : "Fri Jan 02 15:30:00 2010"}]}') a,
      ST_makeTrajectory('STPOINT', 'LINESTRING(1 0, 4 2, 9 6)'::geometry, '[2010-01-01 11:30, 2010-01-01 15:00)'::tsrange,
      '{"leafcount":3,"attributes":{"velocity": {"type": "integer", "length": 2,"nullable" : true,"value": [120,130,140]}, "accuracy": {"type": "float", "length": 4, "nullable" : false,"value": [120,130,140]}, "bearing": {"type": "float", "length": 8, "nullable" : false,"value": [120,130,140]}, "acceleration": {"type": "string", "length": 20, "nullable" : true,"value": ["120","130","140"]}, "active": {"type": "timestamp", "nullable" : false,"value": ["Fri Jan 01 11:35:00 2010", "Fri Jan 01 12:35:00 2010", "Fri Jan 01 13:30:00 2010"]}}, "events": [{"2" : "Fri Jan 02 15:00:00 2010"}, {"3" : "Fri Jan 02 15:30:00 2010"}]}') b)
    SELECT ST_euclideanDistance(a, b) FROM traj;
     st_euclideandistance
    ----------------------
       0.0888997369940162
    (1 row)
  • 删除扩展

    DROP EXTENSION ganos_trajectory CASCADE;

SQL参考