当需要针对轨迹进行操作,或是希望将点聚合成为轨迹降低存储成本时,推荐使用轨迹表。
以某出行APP的数据为例,其建表语句为:
CREATE TABLE trajectory_table(
userid numeric PRIMARY KEY,
traj trajectory
);
创建轨迹支持多种方式,具体如下:
将点表转化为轨迹,具体请参见点表。
由于执行GROUP BY操作时不一定保证点的次序,因此需要调用ST_Sort()函数,将轨迹点根据时间重新排序。
INSERT INTO trajectory_table SELECT userid, ST_Sort(ST_MakeTrajectory(pnts.tjraw, true, '{"intensity"}'::cstring[])) FROM (SELECT sample_points.userid, array_agg( ROW(sample_points.sample_time, sample_points.x, sample_points.y, sample_points.z, sample_points.intensity)) AS tjraw FROM sample_points GROUP BY userid ) pnts;
ST_makeTrajectory提供多种方式构造轨迹:
说明本示例仅展示ID为1的轨迹,通过Geometry类型+时间戳数组+属性JSON串构造轨迹。
INSERT INTO trajectory_table SELECT 3, ST_MakeTrajectory( 'STPOINT'::leaftype, st_geomfromtext('LINESTRING(114.35 39.28 4,114.36 39.28 4,114.35 39.29 4)', 4326), ARRAY['2020-04-11 17:42:30'::timestamp,'2020-04-11 17:43:30'::timestamp,'2020-04-11 17:45:00'::timestamp], '{"leafcount":3,"attributes":{"intensity":{"type":"integer","length":4,"nullable":true,"value":[80,30,50]}}}' );
说明ST_makeTrajectory函数提供多种方式构造轨迹,一些方式需要依赖Ganos的Geometry几何类型,更多信息,请参见几何模型。
JSON字符串构建轨迹:
INSERT INTO trajectory_table SELECT 4, '{"trajectory":{"version":1,"type":"STPOINT","leafcount":3,"start_time":"2020-04-11 17:42:30","end_time":"2020-04-11 17:45:00","spatial":"SRID=4326;LINESTRING(114.35 39.28 4,114.36 39.28 4,114.35 39.29 4)","timeline":["2020-04-11 17:42:30","2020-04-11 17:43:30","2020-04-11 17:45:00"],"attributes":{"leafcount":3,"intensity":{"type":"integer","length":4,"nullable":true,"value":[80,30,50]}}}}' ::trajectory;
对于整条的轨迹,支持进行时空相关的操作,例如相交、包含的判断等。通过建立R树索引,可以加速空间查询。
--创建索引。
CREATE INDEX ON trajectory_table USING gist(traj);
--查找所有与一个二维矩形范围相交的轨迹,返回其userid。
SELECT userid FROM trajectory_table WHERE ST_2DIntersects(traj, ST_MakeEnvelope(114.33,39.28,14.331,39.282));
文档内容是否对您有帮助?