All Products
Search

This Product

    Lindorm:Aggregate functions

    Document Center

    Lindorm:Aggregate functions

    Last Updated:Mar 28, 2026

    Lindorm GanosBase provides spatio-temporal aggregate functions that sort out-of-order points by time, assemble them into trajectories, and compute trajectory metrics. Use these functions for large-scale spatio-temporal workloads such as Internet of Vehicles (IoV), GIS, and Internet of Things (IoT) applications that require trajectory analysis.

    Prerequisites

    Functions

    FunctionDescription
    ST_Length_RowsSorts out-of-order points by time, aggregates them into a trajectory, and returns the spherical length of the trajectory in meters.
    ST_ResampleSorts out-of-order points by time, downsamples the points based on temporal and spatial thresholds, and returns the downsampled trajectory as a JSON string.
    ST_TrajectoryProfileSorts out-of-order points by time, aggregates them into a trajectory, segments the trajectory by a temporal threshold, and returns the coordinates and timestamps of each segment's start and end points.

    ST_Length_Rows

    Sorts out-of-order points by time, aggregates them into a trajectory, and returns the spherical length of the trajectory in meters.

    Syntax

    double ST_Length_Rows(point column)

    Parameters

    ParameterDescription
    point columnThe geometry column to process. Must contain POINT objects.

    Usage notes

    • Use ST_Length_Rows with a GROUP BY clause to compute trajectory lengths grouped by a column (for example, by vehicle ID).

    • Correct ordering depends on the primary key sort order. Define composite primary keys that include both an entity identifier (such as vehicle ID) and a timestamp column so that rows are stored and scanned in the correct travel order. If the primary key does not guarantee correct ordering, the returned length may be incorrect.

    Examples

    The following examples use the test_len table, created and populated as follows:

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

    The primary key (carid, collect_time) ensures that points are stored in ascending time order per vehicle, so the trajectory is assembled correctly.

    Example 1: Compute the spherical length of the trajectory for car1.

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

    Result:

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

    Example 2: Compute the spherical length for each vehicle, grouped by vehicle ID.

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

    Result:

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

    ST_Resample

    Sorts out-of-order points by time and downsamples them based on temporal and spatial thresholds. Returns the downsampled trajectory as a JSON string.

    Syntax

    Variant 1: Point column as input.

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

    Variant 2: Separate longitude and latitude columns as input.

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

    Parameters

    ParameterDescription
    geomColumnNameThe POINT geometry column. Use this parameter when point data is stored as geometry objects (Variant 1).
    xColumnNameThe longitude column (DOUBLE). Must be paired with yColumnName. Use this parameter when point data is stored as separate longitude and latitude values (Variant 2).
    yColumnNameThe latitude column (DOUBLE). Must be paired with xColumnName (Variant 2).
    timeColumnNameThe time column used to sort points in ascending order. Supports TIME, TIMESTAMP, and LONG data types. If LONG, values must be in milliseconds.
    configDownsampling configuration in JSON format (STRING). Format: {"parameter": value, ...}. See the table below for available parameters.

    Downsampling parameters (`config`)

    ParameterTypeDefaultDescription
    downsample_timeINT or LONG-1 (disabled)Temporal downsampling threshold in milliseconds. Adjacent points in the output are separated by more than this interval.
    downsample_distanceINT, LONG, FLOAT, or DOUBLE-1.0 (disabled)Spatial downsampling threshold. The geometry of a trajectory is simplified by using the specified algorithm based on this parameter value. The unit of this parameter is the same as that of coordinates.
    simplifierSTRINGtopologypreservingSimplification algorithm. Valid values: vw (Visvalingam-Whyatt), dp (Douglas-Peucker), topologypreserving (Douglas-Peucker with topology preservation; valid only when downsample_distance is specified).

    Returned value

    A time-sorted JSON array of downsampled trajectory points:

    [{"x": <longitude>, "y": <latitude>, "t": <time>}, ...]

    Usage notes

    • Apply filter conditions to select trajectory points for a specific entity or time range. ST_Resample operates on the filtered result set, not the full table.

    • ST_Resample cannot be used with a GROUP BY clause.

    Example

    The following example uses the gps_points table, created and populated as follows:

    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 account 001 within a 24-hour window and downsample it using the Visvalingam-Whyatt algorithm with a spatial threshold of 0.0001:

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

    Result:

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

    Sorts out-of-order points by time, assembles them into a trajectory, segments the trajectory based on a temporal gap threshold, and returns the coordinates and timestamps of each segment's start and end points.

    ST_TrajectoryProfile is typically combined with spatial relationship functions such as ST_DWithinSphere to find when a moving entity first enters and last leaves a defined area within each trajectory segment.

    Syntax

    Variant 1: Point column as input.

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

    Variant 2: Separate longitude and latitude columns as input.

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

    Parameters

    ParameterDescription
    geomColumnNameThe POINT geometry column. Use this parameter when point data is stored as geometry objects (Variant 1).
    xColumnNameThe longitude column (DOUBLE). Must be paired with yColumnName. Use this parameter when point data is stored as separate longitude and latitude values (Variant 2).
    yColumnNameThe latitude column (DOUBLE). Must be paired with xColumnName (Variant 2).
    timeColumnNameThe time column. Points are sorted in ascending order of this column.
    thresh(Optional) The maximum time gap in milliseconds between two adjacent points that belong to the same trajectory segment. If the gap between two points exceeds this threshold, they are placed in separate segments. Default: 10000 (10 seconds).

    Returned value

    A JSON object where each key is a segment index (0-based) and each value is a JSON string containing the segment's boundary coordinates and timestamps:

    {
  "0": "{\"startX\": <longitude>, \"startY\": <latitude>, \"startTime\": <epoch ms>, \"endX\": <longitude>, \"endY\": <latitude>, \"endTime\": <epoch ms>}",
  "1": "...",
  ...
}

    Usage notes

    • ST_TrajectoryProfile must be used with a GROUP BY clause to aggregate points into trajectories by entity (for example, by vehicle ID).

    • When a secondary index is used, add all columns referenced in the query—including the GROUP BY key, filter condition columns, and function parameter columns—as included columns in the index. This avoids scanning the base table. For example, if the query groups by carid and passes p and collect_time to the function, include all three columns in the index.

    Example

    The following example uses the test_traj table, created and populated as follows:

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

    For each vehicle, find the first entry and last exit point within a 100-meter radius of POINT (111.40217 35.616386), for points collected between 2023-09-12 00:09:07 and 2023-09-20 00:09:07. Use a segment gap threshold of 30,000 ms (30 seconds):

    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

    When a secondary index is used in the query, add carid, p, and collect_time as included columns in the index so that the query does not scan the base table.

    Result:

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

    car1 has two trajectory segments within the search area (segment 0 and segment 1). car2 has one point in the area, which forms a single segment (segment 0) where the start and end coordinates and timestamps are identical.