Which spatio-temporal aggregate functions are supported by LindormTable - Lindorm

Spatio-temporal aggregate functions can be used to sort and aggregate points that are stored out of order. These functions are commonly used in scenarios in which a large amount of spatio-temporal data is generated and trajectory analysis is required, such as Internet of Vehicles (IoV), GIS, and Internet of Things (IoT).

Applicable engines and versions

The aggregate functions described in this topic are applicable only to LindormTable.
The aggregate functions described in this topic are supported by LindormTable 2.6.5 and later versions. For more information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Important

The Lindorm SQL version must be 2.6.8 or later. For more information about how to view the Lindorm SQL version, see SQL versions.

Functions

The following table lists the aggregate functions supported by Lindorm GanosBase.

Function	Description
ST_Length_Rows	This function sorts out-of-order points by time, aggregates the points into a trajectory, and then returns the spherical length of the trajectory in meters.
ST_Resample	This function sorts points that are stored out of order by time, downsamples the points based on the specified temporal and spatial thresholds, and aggregates the downsampled data into trajectories.
ST_TrajectoryProfile	This function sorts out-of-order points by time, aggregates the points into a trajectory, divides the trajectory into segments based on the specified temporal threshold, and then returns the coordinates and time of the start and end points of each segment.

ST_Length_Rows

This function sorts out-of-order points by time, aggregates the points into a trajectory, and then returns the spherical length of the trajectory in meters.

You can use the ST_Length_Rows function together with the GROUP BY clause to obtain the spherical length of the trajectory that is aggregated by the points in the specified column.

Important

You must use the primary keys to make sure that the points are aggregated in the correct order. Otherwise, the returned length may not be as expected. For example, you can use the vehicle ID column and the timestamp column as primary keys in IoV scenarios to make sure that points are aggregated in the correct order.

Syntax

double ST_Length_Rows(point column)

Parameters

Parameter	Description
point column	The geometry objects that you want to process by using this function. Specify points for this parameter.

Examples

In the following examples, a table named test_len is created and populated with sample data by executing the following statements:

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)'));

Example 1: Calculate the spherical length of the trajectory aggregated by points that are generated by the vehicle car1.

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

The following result is returned:

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

Example 2: Calculate the spherical length of the trajectories aggregated by the points generated by all vehicles and group the results by vehicle IDs.

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

The following result is returned:

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

ST_Resample

This function sorts points that are stored out of order by time, downsamples the points based on the specified temporal and spatial thresholds, and aggregates the downsampled data into trajectories.

When you use the ST_Resample function, you must specify filter conditions for trajectory points or a single trajectory. This function cannot be used together with the GROUP BY clause.

Syntax

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

Parameters

Parameter	Description
geomColumnName	The geometry objects that you want to process by using this function. Specify points for this parameter.
xColumnName	The longitude column. Specify DOUBLE values for this parameter. This parameter must be specified together with the yColumnName parameter.
yColumnName	The latitude column. Specify DOUBLE values for this parameter. This parameter must be specified together with the xColumnName parameter.
timeColumnName	The time column. Points in the table are constructed in the increasing order of this column. The time column supports the TIME, TIMESTAMP, and LONG data types. If the data type of the time column is LONG, the unit of the values in the column must be milliseconds.
config	The downsampling parameters in the JSON format. Specify STRING values for the downsampling parameters. The values of the downsampling parameters are in the following format: {"Parameter name": Value}

The following table shows the downsampling parameters that you can specified in the config parameter.

Parameter	Description
downsample_time	The temporal downsampling threshold. The time interval between adjacent trajectory points in the downsampled data is greater than this parameter value. Specify INT or LONG values for this parameter. Unit: milliseconds. Default value: -1.
downsample_distance	The spatial downsampling threshold. The geometry of a trajectory is simplified by using the specified algorithm based on this parameter value. Specify INT, LONG, FLOAT, or DOUBLE values for this parameter. The unit of this parameter is the same as that of coordinates. Default value: -1.0.
simplifer	The algorithm that is used in downsampling. Specify STRING values for the parameter. Valid values: vw: The Visvalingam-Whyatt algorithm. dp: The Douglas-Peucker algorithm. topologypreserving: The Douglas-Peucker algorithm that preserves the topology of trajectories. This parameter is valid only when the downsample_distance parameter is specified. Default value: topologypreserving.

Returned values

This function returns a sequence of the downsampled trajectory points sorted by time. The returned sequence is a string in the following format: [{\"x\":Longitude,\"y\":Latitude,\"t\":Time}, {\"x\":Longitude,\"y\":Latitude,\"t\":Time}, ...].

Examples

In the following examples, a table named gps_point is created and populated with sample data by executing the following statements:

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));

Query the trajectory of the specified vehicle within the specified time range in the gps_point table. The returned trajectory is generated by the points that are downsampled by using the Visvalingam-Whyatt algorithm based on the spatial threshold 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';

The following result is returned:

+----------------------------------------------------------------------------------------------------------------------+
|                                             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

This function sorts out-of-order points by time, aggregates the points into a trajectory, divides the trajectory into segments based on the specified temporal threshold, and then returns the coordinates and time of the start and end points of each segment.

The ST_TrajectoryProfile function must be used together with the GROUP BY clause to calculate the coordinates and time of the start and end points of each trajectory segment that is aggregated by a group of points.

The ST_TrajectoryProfile function is usually used together with functions for spatial relationship, such as ST_DWithinSphere. In this case, you can calculate the coordinates and time of the point that first enters a specified area and the point that last leaves a specified area in a trajectory segment.

Syntax

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

Parameters

Parameter	Description
geomColumnName	The geometry objects that you want to process by using this function. Specify points for this parameter. Note If the data of points is stored as point objects in the table, specify this parameter when you use the function.
xColumnName	The longitude column. Specify DOUBLE values for this parameter. This parameter must be specified together with the yColumnName parameter. Note If the data of points is stored as longitudes and latitudes in the table, specify this parameter when you use the function.
yColumnName	The latitude column. Specify DOUBLE values for this parameter. This parameter must be specified together with the xColumnName parameter. Note If the data of points is stored as longitudes and latitudes in the table, specify this parameter when you use the function.
timeColumnName	The time column. Points in the table are constructed in the increasing order of this column.
thresh	This parameter is optional. Unit: milliseconds. After points are sorted based on the time column, if the time interval between two adjacent points is equal to or smaller than the threshold, the two points are considered in the same trajectory segment. If the time interval between two adjacent points is larger than the threshold, the two points are considered in different trajectory segments. Default value: 10000.

Returned values

This function returns a JSON string in the following format:

{"Trajectory segment number":"{\"endY\":Latitude of the end point,\"endX\":Longitude of the end point,\"startY\":Latitude of the start point,\"startTime\":Time of the start point,\"startX\":Longitude of the start point,\"endTime\":Time of the end point}"}.

Examples

In the following examples, a table named test_traj is created and populated with sample data by executing the following statements:

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');

In each trajectory segment generated by the data in the test_traj table, query the coordinates and time of the point that first enters the specified area and the point that last leaves the specified area within the specified time range.

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;

Note

If a secondary index is used in the query, all columns that are used in the query, including the aggregation key, filter condition columns, and function parameter columns, must be added to the index as included columns. This way, the query does not need to scan the data in the base table. For example, in the preceding statement, the carid, p, and collect_time columns must be added to the secondary index as included columns.

The following result is returned:

+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 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}"}                                                                                                                                               |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+