Trajectory参考的点表操作_云数据库 RDS(RDS)-阿里云帮助中心

对于对存储空间占用不敏感，且主要根据轨迹编号（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访问数据库时可能存在兼容性问题，如提示传回预期之外的结果，请使用其它客户端尝试。