轨迹模型
轨迹数据是针对移动对象(Moving Feature)所记录的连续位置变化信息,例如车辆的轨迹、人的轨迹等。轨迹数据是一类典型的时空数据,分析和理解这些轨迹数据能帮助人们研究许多重要问题。
概述
Ganos Trajectory是云原生关系型数据库POLARDB的一个扩展,提供了一组数据类型、函数和存储过程,帮助用户高效地管理、查询和分析时空轨迹数据。
快速入门
-
创建扩展
Create Extension Ganos_trajectory cascade;
-
轨迹的枚举类型
CREATE TYPE leaftype AS ENUM ('STPOINT', 'STPOLYGON');
-
创建轨迹表
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_spatial_geometry_index on traj_table using gist (st_trajectoryspatial(traj)); --空间查询时,加速空间过滤 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 index tr_timespan_time_index on traj_table using gist (st_timespan(traj)); --创建基于函数的轨迹起止时间 create index tr_starttime_index on traj_table using btree (st_starttime(traj)); create index tr_endtime_index on traj_table using btree (st_endtime(traj)); --查询时,加速时间过滤 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;
-
创建轨迹时间+空间复合索引(btree_gist)
btree_gist支持时间+空间的复合索引,适合轨迹查询条件中既有时间过滤,又有空间过滤的情况,使得时空复合查询SQL语法简单,且查询效率高。
--建立btree_gist 起始时间、终止时间、空间复合索引 create index tr_traj_test_stm_etm_sp_index on traj_test using gist (st_starttime(traj),st_endtime(traj),st_trajectoryspatial(traj)); --时空查询 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;
-
查询轨迹起、止时间
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)
-
轨迹查询
--通过插值函数查询轨迹点的属性 Select ST_velocityAtTime(traj, '2010-01-01 12:45') from traj_table where id > 5; st_velocityattime ------------------- 5 5 4.16666666666667 (3 rows)
-
分析轨迹间的相近性
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)
-
删除扩展
Drop Extension Ganos_trajectory cascade;
快速入门
-
创建扩展
Create Extension Ganos_trajectory cascade;
-
轨迹的枚举类型
CREATE TYPE leaftype AS ENUM ('STPOINT', 'STPOLYGON');
-
创建轨迹表
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_spatial_geometry_index on traj_table using gist (st_trajectoryspatial(traj)); --空间查询时,加速空间过滤 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 index tr_timespan_time_index on traj_table using gist (st_timespan(traj)); --创建基于函数的轨迹起止时间 create index tr_starttime_index on traj_table using btree (st_starttime(traj)); create index tr_endtime_index on traj_table using btree (st_endtime(traj)); --查询时,加速时间过滤 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;
-
创建轨迹时间+空间复合索引(btree_gist)
btree_gist支持时间+空间的复合索引,适合轨迹查询条件中既有时间过滤,又有空间过滤的情况,使得时空复合查询SQL语法简单,且查询效率高。
--建立btree_gist 起始时间、终止时间、空间复合索引 create index tr_traj_test_stm_etm_sp_index on traj_test using gist (st_starttime(traj),st_endtime(traj),st_trajectoryspatial(traj)); --时空查询 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;
-
查询轨迹起、止时间
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)
-
轨迹查询
--通过插值函数查询轨迹点的属性 Select ST_velocityAtTime(traj, '2010-01-01 12:45') from traj_table where id > 5; st_velocityattime ------------------- 5 5 4.16666666666667 (3 rows)
-
分析轨迹间的相近性
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)
-
删除扩展
Drop Extension Ganos_trajectory cascade;
SQL参考
详细SQL手册请参见Trajectory SQL参考。
版权声明
本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
评论