ST_trajAttrsMeanMax

Syntax

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

Parameters

Output columns

How it works

The MEAN-MAX algorithm evaluates the attribute field using every possible window size — from a window that covers one time step up to a window that covers the entire trajectory. For each window size, it slides the window across the trajectory, computes the average of the attribute field for the points within each window position, and records the highest average it finds. The function returns one row per window size, each containing that window's duration and its corresponding maximum average.

Constraints:

• Supports integer and float attribute fields only.

• The attribute field values cannot be null.

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] recorded at one-hour intervals.

Example 1: Return results as a composite type

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)

The result has three rows — one for each window size that fits the trajectory. Reading the output:

• @ 1 hour, 135: With a 1-hour window, the function slides over pairs of adjacent points — (120.0, 130.0), (130.0, 140.0), (140.0, 120.0) — with averages 125, 135, and 130. The highest is 135.

• @ 2 hours, 130: With a 2-hour window, the function slides over triples — (120.0, 130.0, 140.0) and (130.0, 140.0, 120.0) — with averages 130 and 130. The highest is 130.

• @ 3 hours, 127.5: With a 3-hour window covering all four points, there is only one position: average of (120.0, 130.0, 140.0, 120.0) = 127.5.

Example 2: Return results as separate columns

Use .* to expand the composite output into individual duration and max 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)