对于对存储空间占用不敏感,且主要根据轨迹编号(ID)进行查询的场景,可以使用点表进行存储,在查询时,再将查询结果聚合成轨迹,并进行需要的操作。
以存储某出行APP的用户轨迹为例。每个采样点记录用户的ID、时间、经度、纬度、海拔高度、以及采样设备的信号强度。建表语句示例如下:
- 创建表。
CREATE TABLE sample_points( userid numeric, -- 用户ID sample_time timestamp, -- 采样时间 x double precision, -- 经度 y double precision, -- 纬度 z double precision, -- 海拔高度 intensity int -- 采样设备信号强度 );
- 插入测试数据。
INSERT INTO sample_points VALUES (1,'2020-04-11 17:42:30',114.35, 39.28, 4, 80), (1,'2020-04-11 17:43:30',114.36, 39.28, 4, 30), (1,'2020-04-11 17:45:00',114.35, 39.29, 4, 50), (2,'2020-04-11 17:42:30',114.3, 39, 34, 60), (2,'2020-04-11 17:43:30',114.3, 39, 38, 58);
- 在userid列上建立索引。
CREATE INDEX on sample_points USING btree(userid);
- 提取userid为1的用户的所有采样点(按照时间排列)。
SELECT * FROM sample_points WHERE userid = 1 ORDER BY sample_time;
可以对提取出的轨迹点进行处理,例如,将采样点中采样不精确的漂移点删除。
SELECT ST_removeDriftPoints( -- 删除不精准的漂移点
ST_SetSRID( -- 更改轨迹的空间参考系(SRID)
ST_MakeTrajectory( -- 构造轨迹对象
array_agg(
ROW(traj.sample_time, traj.x, traj.y, traj.z, traj.intensity) -- 选取需要组成轨迹的列
), true, '{"intensity"}'::cstring[]),4326), 40, 10, '1 minute'::interval
) FROM (SELECT * FROM sample_points WHERE userid = 1 ORDER BY sample_time) traj;
说明
- 示例中先将点聚合成为轨迹,之后再利用轨迹的ST_removeDriftPoints函数去掉不需要的轨迹点。其中,各函数的参数解释,请参见ST_makeTrajectory、ST_SetSRID和ST_removeDriftPoints。
- ROW函数中,第一列必须是timestamp类型,第二、三列必须是double precision类型。如果ST_MakeTrajectory函数的第二个参数为true(即轨迹点存储高度信息),则第四列也必须是double precision类型。如果某一列不是double precision类型,则可以使用
变量名::double precision
或cast(变量名 as double precision)
强制转换。 - 时间类型,请参见PostgreSQL的时间类型。
ST_removeDriftPoint函数返回值为轨迹类型,如果需要将轨迹类型转换为点表类型,则使用ST_AsTable函数。
WITH removed as
(
SELECT ST_removeDriftPoints(
ST_SetSRID(
ST_MakeTrajectory(
array_agg(
ROW(traj.sample_time, traj.x, traj.y, traj.z, traj.intensity)),
true, '{"intensity"}'::cstring[]), 4326), 40, 10, '1 minute'::interval) AS trajcol
FROM (SELECT * FROM sample_points WHERE userid = 1 ORDER BY sample_time) traj
)
SELECT f.* FROM removed,
ST_AsTable(removed.trajcol) AS -- 将轨迹类型转换为点表类型
f(sample_time timestamp,x double precision, y double precision, z double precision, intensity int);
说明
- ST_removeDriftPoints函数要求轨迹的SRID为4326(即WGS84投影坐标系),因此需要先将转化出的轨迹SRID设置为4326。如果不进行此步骤,生成的轨迹SRID默认为0(即未知)。
- 该命令在DMS访问数据库时可能存在兼容性问题,如提示
传回预期之外的结果
,请使用其它客户端尝试。