时空聚合函数 - 云原生多模数据库 Lindorm

时空聚合函数可以将乱序存储的点按序排列并进行聚合计算，多用于车联网、GIS、物联网等有大量时空数据产生，且有轨迹分析需求的场景。

引擎与版本

聚合函数仅适用于宽表引擎。
宽表引擎为2.6.5及以上版本。如何查看或升级当前版本，请参见宽表引擎版本说明和升级小版本。

重要

要求Lindorm SQL为2.6.8及以上版本。如何查看Lindorm SQL的版本，请参见SQL版本说明。

函数列表

Lindorm GanosBase支持的聚合函数如下表所示。

函数	说明
ST_Length_Rows	将乱序存储的点按时间进行排序并拼接为轨迹，计算并返回该轨迹的球面长度，单位为米（m）。
ST_Resample	基于给定的降采样时间和空间阈值，将乱序存储的点按时间从早到晚排序并按阈值降采样，生成轨迹。
ST_TrajectoryProfile	将乱序存储的点按时间排序并拼接为轨迹，基于时间阈值将该轨迹分割为轨迹段，计算并返回每条轨迹段的起止点的坐标和时间。

ST_Length_Rows

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

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

重要

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

语法

double ST_Length_Rows(point column)

参数说明

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

示例

本示例的查询基于如下建表语句和示例数据：

CREATE TABLE test_len (carid VARCHAR, collect_time TIMESTAMP, p GEOMETRY(POINT), PRIMARY KEY(carid, collect_time));

INSERT INTO test_len (carid, collect_time,p) VALUES('car1', '2023-09-12 00:04:02', ST_GeomFromText('POINT (111.40269 35.61695)')),
       ('car1', '2023-09-12 00:09:07', ST_GeomFromText('POINT (111.40127 35.616096)')),
       ('car1', '2023-09-12 00:14:03', ST_GeomFromText('POINT (111.400604 35.616013)')),
       ('car1', '2023-09-12 00:20:13', ST_GeomFromText('POINT (111.399734 35.613983)')),
       ('car1', '2023-09-12 00:27:21', ST_GeomFromText('POINT (111.40217 35.616386)')),
       ('car2', '2023-09-12 00:27:21', ST_GeomFromText('POINT (111.40217 35.616386)'));

示例1：计算p列中，所有carid为car1的点形成的轨迹的球面长度。

SELECT ST_LENGTH_ROWS(p) FROM test_len WHERE carid='car1';

返回结果如下：

+--------------------+
| st_length_rows(p)  |
+--------------------+
| 805.55323541493414 |
+--------------------+

示例2：计算按carid分组聚合后，每条轨迹的球面长度。

SELECT carid,ST_LENGTH_ROWS(p) as len FROM test_len WHERE carid<='car2' GROUP BY carid;

返回结果如下：

+-------+--------------------+
| carid |        len         |
+-------+--------------------+
| car1  | 805.55323541493414 |
| car2  | 0                  |
+-------+--------------------+

ST_Resample

基于给定的降采样时间和空间阈值，将乱序存储的点按时间从早到晚排序并按阈值降采样，生成轨迹。

在使用ST_Resample函数时需提供单个轨迹对象和轨迹点范围的过滤条件。不支持与GROUP BY语句联用。

语法

String ST_Resample(String geomColumnName, String timeColumnName, String config)
String ST_Resample(String xColumnName, String yColumnName, String timeColumnName, String config)

参数说明

参数	描述
geomColumnName	指定的Geometry对象。类型为POINT。
xColumnName	经度列。类型为DOUBLE，必须与yColumnName参数一起使用。
yColumnName	纬度列。类型为DOUBLE，必须与xColumnName参数一起使用。
timeColumnName	时间列名，轨迹点按该时间列增序构造。支持TIME/TIMESTAMP/LONG类型，LONG类型单位必须为毫秒（ms）。
config	以JSON格式给定的降采样参数，类型为STRING。格式为：{"参数名": 取值}。

config包含以下可选参数：

参数	描述
downsample_time	降采样时间阈值。降采样后相邻轨迹点之间的时间间隔均大于此参数值。单位为毫秒（ms），默认值为-1，支持INT和LONG类型。
downsample_distance	降采样距离阈值，即依据指定的简化算法（vm、dp或topologypreserving）对轨迹的几何形状进行简化时给定的计算阈值。单位与点坐标单位相同，默认值为-1.0，支持INT、LONG、FLOAT和DOUBLE类型。
simplifer	进行降采样时的简化算法，类型为STRING，支持以下选项： vw：Visvalingam-Whyatt算法。 dp：Douglas-Peucker算法。 topologypreserving：保留轨迹线串拓扑结构的Douglas-Peucker算法。仅在与downsample_distance联合使用时生效，默认值为topologypreserving。

返回值

返回降采样后按时间排序的轨迹点序列。返回值类型为STRING，格式为：[{\"x\":经度,\"y\":纬度,\"t\":时间}, {\"x\":经度,\"y\":纬度,\"t\":时间}, ...]。

示例

本示例的查询基于如下建表语句和示例数据：

CREATE TABLE gps_points (account_id VARCHAR, collect_time TIMESTAMP, gps_point GEOMETRY(POINT), PRIMARY KEY(account_id, collect_time));

INSERT INTO gps_points(account_id, collect_time, gps_point) VALUES ('001', '2023-11-10 11:00:30', ST_MakePoint(113.665431, 34.773)),
       ('001', '2023-11-10 11:00:31', ST_MakePoint(113.665432, 34.773)),
       ('001', '2023-11-10 11:00:32', ST_MakePoint(113.665433, 34.773)),
       ('001', '2023-11-10 11:00:33', ST_MakePoint(113.665434, 34.774));

查询表gps_points中，指定车辆在指定时间范围内的行驶轨迹，返回使用Visvalingam-Whyatt算法按照0.0001的距离阈值降采样后的轨迹。

SELECT ST_Resample(gps_point, collect_time,'{"downsample_distance": 0.0001, "simplifier": "vw"}') as resampled_traj FROM gps_points WHERE collect_time >=  '2023-11-10 00:00:00' and collect_time <= '2023-11-11 00:00:00'  and account_id='001';

返回结果如下：

+----------------------------------------------------------------------------------------------------------------------+
|                                             resampled_traj                                                           |
+----------------------------------------------------------------------------------------------------------------------+
| [{"x":113.665431,"y":34.773,"t":"2023-11-10 11:00:30.0"},{"x":113.665434,"y":34.774,"t":"2023-11-10 11:00:33.0"}]                                           |
+----------------------------------------------------------------------------------------------------------------------+

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\":终点时间}"}。

示例

本示例的查询基于如下建表语句和示例数据：

CREATE TABLE test_traj (carid VARCHAR, collect_time TIMESTAMP, p GEOMETRY(POINT), status VARCHAR, PRIMARY KEY(z-order(p), carid, collect_time));

INSERT INTO test_traj (carid, collect_time, p, status) VALUES('car1', '2023-09-12 00:04:02', ST_GeomFromText('POINT (111.40269 35.61695)'), 'normal'),
    ('car1', '2023-09-12 00:09:07', ST_GeomFromText('POINT (111.40127 35.616096)'), 'normal'),
    ('car1', '2023-09-12 00:14:03', ST_GeomFromText('POINT (111.400604 35.616013)'), 'normal'),
    ('car1', '2023-09-12 00:20:13', ST_GeomFromText('POINT (111.399734 35.613983)'), 'normal'),
    ('car1', '2023-09-12 00:27:21', ST_GeomFromText('POINT (111.40217 35.616386)'), 'normal'),
    ('car2', '2023-09-12 00:27:21', ST_GeomFromText('POINT (111.40217 35.616386)'), 'normal');

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

SELECT carid, st_trajectoryprofile(p, collect_time, 30000) as trajprofile FROM test_traj WHERE st_dwithinsphere(st_geomfromtext('POINT (111.40217 35.616386)'), p, 100.0) and collect_time >= '2023-09-12 00:09:07' and collect_time <= '2023-09-20 00:09:07' group by carid;

说明

使用时，如果查询利用了二级索引，该索引需要添加所有查询涉及到的列（包括聚合键、过滤条件列和函数参数列）为冗余列，避免回查主表。例如，在上述示例中，二级索引需要同时冗余carid、p和collect_time列。

返回结果如下：

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| carid |                                                                                                                                          trajprofile                                                                                                                                          |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| car1  | {"0":"{\"endY\":35.616096,\"endX\":111.40127,\"startY\":35.616096,\"startTime\":1694448547000,\"startX\":111.40127,\"endTime\":1694448547000}","1":"{\"endY\":35.616386,\"endX\":111.40217,\"startY\":35.616386,\"startTime\":1694449641000,\"startX\":111.40217,\"endTime\":1694449641000}"} |
| car2  | {"0":"{\"endY\":35.616386,\"endX\":111.40217,\"startY\":35.616386,\"startTime\":1694449641000,\"startX\":111.40217,\"endTime\":1694449641000}"}                                                                                                                                               |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+