ST_trajAttrsMeanMax

Syntax

SETOF record ST_trajAttrsMeanMax(trajectory traj, cstring attr_field_name, out interval duration, out float8 max);

Parameters

Description

The MEAN-MAX algorithm slides a window across the trajectory timeline. For each window size, it computes the average of the attribute field values within that time range, then returns the maximum of those averages.

The function returns one row per window size, with each row containing:

• duration: the window size as an interval

• max: the maximum average value (float8) for that window size

Constraints:

• Supported field types: integer and float only.

• Null values in the attribute field are not supported.

Examples

The following examples use a trajectory with four points and a velocity attribute field with values [120.0, 130.0, 140.0, 120.0].

Example 1: Return results as a composite record

With traj AS (
    Select ST_makeTrajectory('STPOINT', 'LINESTRING(1 1, 6 6, 9 8, 10 12)'::geometry,
    ARRAY['2010-01-01 11:30'::timestamp, '2010-01-01 12:30', '2010-01-01 13:30', '2010-01-01 14:30'],
    '{"leafcount":4, "attributes":{"velocity": {"type": "float", "length": 8,"nullable": true,"value": [120.0, 130.0, 140.0, 120.0]}, "power": {"type": "float", "length": 4,"nullable": true,"value": [120.0, 130.0, 140.0, 120.0]}}}') a)
Select st_trajAttrsMeanMax(a, 'velocity') from traj;

Output:

 st_trajattrsmeanmax
---------------------
 ("@ 1 hour",135)
 ("@ 2 hours",130)
 ("@ 3 hours",127.5)
(3 rows)

Example 2: Expand results into separate columns

With traj AS (
    Select ST_makeTrajectory('STPOINT', 'LINESTRING(1 1, 6 6, 9 8, 10 12)'::geometry,
    ARRAY['2010-01-01 11:30'::timestamp, '2010-01-01 12:30', '2010-01-01 13:30', '2010-01-01 14:30'],
    '{"leafcount":4, "attributes":{"velocity": {"type": "float", "length": 8,"nullable": true,"value": [120.0, 130.0, 140.0, 120.0]}, "power": {"type": "float", "length": 4,"nullable": true,"value": [120.0, 130.0, 140.0, 120.0]}}}') a)
Select (st_trajAttrsMeanMax(a, 'velocity')).* from traj;

Output:

 duration |  max
----------+-------
 01:00:00 |   135
 02:00:00 |   130
 03:00:00 | 127.5
(3 rows)

Use .* to expand the composite result into duration and max columns for easier downstream processing.