本文介绍时空函数中的聚合函数。

ST_Length_Rows

将乱序存储的点按时间进行排序并拼接为轨迹,计算并返回该轨迹的球面长度,单位为米(m)。

ST_Length_Rows函数可以与GROUP BY语句联用,用于返回指定列中的点聚合而成的轨迹的球面长度。

重要 您需要通过主键来保证点的顺序,否则计算出的长度可能与预期不符。例如,在车联网场景中,可以使用车辆ID和时间戳timestamp作为主键来保证点的顺序。

语法

double ST_Length_Rows(point column);

参数

参数 描述
point column 指定的Geometry对象。类型为Point。

示例

  • 计算p列中,所有ID为1的点形成的轨迹的球面长度。
    SELECT ST_LENGTH_ROWS(p) FROM tdrive WHERE id=1;

    返回结果:

    +-------------------------+
    |    ST_LENGTH_ROWS(p)    |
    +-------------------------+
    | 441415.1379062134844505 |
    +-------------------------+
  • 计算按ID分组聚合后,每条轨迹的球面长度。
    SELECT id,ST_LENGTH_ROWS(p) FROM tdrive WHERE id<4 GROUP BY id;

    返回结果:

    +----+--------------------------+
    | id |    ST_LENGTH_ROWS(p)     |
    +----+--------------------------+
    | 1  | 441415.1379062134844505  |
    | 2  | 1672105.5085354075296573 |
    | 3  | 1336898.1989478711426478 |
    +----+--------------------------+

ST_TrajectoryProfile

将乱序存储的点按时间排序并拼接为轨迹,基于时间阈值将该轨迹分割为轨迹段,计算并返回每条轨迹段的起止点的坐标和时间。

ST_TrajectoryProfile函数需要与GROUP BY语句联用,用于计算聚合分组后,每个组中各个轨迹段的起止点的坐标和时间。

ST_TrajectoryProfile函数通常也会与ST_DWithinSphere等空间关系函数联用。用于计算轨迹段进入指定区域时第一个点的坐标和时间,以及该轨迹段离开指定区域前最后一个点的坐标和时间。

语法

String ST_TrajectoryProfile(String geomColumnName, String timeColumnName,long thresh);
String ST_TrajectoryProfile(String xColumnName, String yColumnName, String timeColumnName,long thresh);

参数

参数 描述
geomColumnName 指定的Geometry对象。类型为Point。
说明 如果您在存储点数据时,使用的数据类型是Point类型,查询时请选择该参数。
xColumnName 经度列。类型为DOUBLE,必须与yColumnName参数一起使用。
说明 如果您使用区分经纬度的方式存储点数据,查询时请选择该参数。
yColumnName 纬度列。类型为DOUBLE,必须与xColumnName参数一起使用。
说明 如果您使用区分经纬度的方式存储点数据,查询时请选择该参数。
timeColumnName 时间列,轨迹点按该时间列增序构造。
thresh 可选参数。时间阈值,单位为毫秒(ms)。按时间排序后,相邻两轨迹点的时间间隔小于该阈值则属于同一轨迹段,大于该阈值则分属不同轨迹段。默认值为10000 ms。

返回值

返回JSON String,格式为:

{"轨迹段编号":"{\"endY\":终点纬度,\"endX\":终点经度,\"startY\":起点纬度,\"startTime\":起点时间,\"startX\":起点经度,\"endTime\":终点时间}"}。

示例

查询test表中,指定时间范围和空间范围内,每个设备采集到的轨迹段的出入点坐标和出入时间。

SELECT device_code, st_trajectoryprofile(lon, lat, collect_time, 30000) FROM test WHERE st_dwithinsphere(st_geomfromtext('POINT (120.20495 30.187485810)'), lon, lat, 1000.0) and collect_time >= 1660673959000 and collect_time <= 1660750359000 group by device_code;
说明 使用时,如果查询利用了二级索引,该索引需要添加所有查询涉及到的列(包括聚合键、过滤条件列和函数参数列)为冗余列,避免回查主表。例如,在上述示例中,二级索引需要同时冗余device_code、lon、lat和collect_time列。

返回结果:

+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| device_code  |                                                                                                          ST_TRAJECTORYPROFILE(lon, lat)                                                                                                                                                         |
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 136229080991 | {"0":"{\"endY\":30.187505,\"endX\":120.204922,\"startY\":30.187542,\"startTime\":1660722531000,\"startX\":120.205022,\"endTime\":1660723161000}","1":"{\"endY\":30.187467,\"endX\":120.204883,\"startY\":30.187508,\"startTime\":1660723192000,\"startX\":120.20493,\"endTime\":1660724452000}"}|
+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
返回结果的示例图如下:返回结果