時空彙總函式 - 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}"}                                                                                                                                               |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+