Generate heat map tiles from trajectory objects with ST_AsHMT - PolarDB

Converts a set of trajectory objects into heat map tiles (HMT) based on a specified geographic range and resolution.

Syntax

bytea ST_AsHMT(trajectory trajectory_set, geometry extent, int4 width, int4 height, int4 value default 1, boolean point_mode default false);
bytea ST_AsHMT(trajectory trajectory_set, geometry extent, int4 width, int4 height, int4 value);
bytea ST_AsHMT(trajectory trajectory_set, geometry extent, int4 width, int4 height, int4 value, boolean point_mode);
bytea ST_AsHMT(trajectory trajectory_set, geometry extent, int4 width, int4 height, float8 value);
bytea ST_AsHMT(trajectory trajectory_set, geometry extent, int4 width, int4 height, float8 value, boolean point_mode);

Parameters

Parameter	Description
`trajectory_set`	The trajectory column used by the aggregate function.
`extent`	The geographic range to query. Only the bounding box is used. Compatible with `ST_TileEnvelope`.
`width`	The width of the output matrix in pixels. Corresponds to the number of columns in the result.
`height`	The height of the output matrix in pixels. Corresponds to the number of rows in the result.
`value`	The column to aggregate. The function computes the sum of values across all trajectory points that fall within each cell. Defaults to `1` (count mode). Accepts `int4` or `float8`.
`point_mode`	Specifies whether to enable point mode. In point mode, only trajectory points are evaluated — trajectory segments between points are ignored. Defaults to `false`.

Return value

Returns a bytea value containing a Protobuf-encoded matrix. Each cell in the matrix holds the aggregated value for trajectory data within that geographic cell.

Protobuf schema

syntax = "proto2";
option optimize_for = LITE_RUNTIME;

message HMT {
    required Type type = 1;       // data value type
    required uint32 rows = 2;     // rows of matrix
    required uint32 columns = 3;  // columns of matrix
    required uint32 srid = 4;     // columns of matrix
    required float  xmin = 5;     // xmin
    required float  ymin = 6;     // ymin
    required float  xmax = 7;     // xmax
    required float  ymax = 8;     // ymax

    oneof matrix {
        intMatrix intValues = 10;
        doubleMatrix doubleValues = 11;
    }

    message intMatrix {
        repeated sint32 values = 12 [packed = true];
    }

    message doubleMatrix {
        repeated double values = 13 [packed = true];
    }

    enum Type {
        INT32 = 0;
        DOUBLE = 1;
    }
}

Schema notes

The type field is determined by the value parameter type: int4 → INT32, float8 → DOUBLE. Use INT32 for counts and DOUBLE for weighted metrics.
Matrix values are stored as a flat array organized row by row.
To convert the returned bytea value into arrays, use ST_HMTAsArray.

Examples

The following examples use a test table with 100 trajectory objects, each with an integer weight column and a floating-point volume column.

-- Create the test table
CREATE TABLE test_table AS
SELECT i AS num,
       st_maketrajectory('STPOINT'::leaftype,
           st_MakeLine(ST_Point(i::numeric/10, i::numeric/10),
                       ST_Point((i+10)::numeric/10, (i+10)::numeric/10)),
           '[2010-01-01 14:30, 2010-01-01 15:30)'::tsrange,
           NULL) AS traj,
       i*100::int4 AS weight,
       i*i*i::float8 AS volume
FROM generate_series(1, 100) i;

Count trajectory occurrences

Omit the value parameter to count how many trajectory objects pass through each cell.

SELECT ST_AsHMT(traj,
    ST_MakeEnvelope(0, 0, 10, 10),  -- extent
    1024,                            -- width, in pixels
    800                              -- height
)
FROM test_table;
---------
\x080010a0061880083284...

Aggregate an integer column

Pass a column name as value to sum its values across all trajectories in each cell.

SELECT ST_AsHMT(traj,
    ST_MakeEnvelope(0, 0, 10, 10),  -- extent
    1024,                            -- width
    800,                             -- height
    weight                           -- value column
)
FROM test_table;
---------
\x080010a0061880...

Aggregate a computed expression

Pass an expression as value to aggregate derived metrics. The result uses DOUBLE encoding.

SELECT ST_AsHMT(traj,
    ST_MakeEnvelope(0, 0, 10, 10),  -- extent
    1024,                            -- width
    800,                             -- height
    weight / volume * 1.2            -- computed value
)
FROM test_table;
---------
\x080110a0061880083a85...

Enable point mode

Use point mode to include only trajectory points, not the interpolated segments between them.

SELECT ST_AsHMT(traj,
    ST_MakeEnvelope(0, 0, 10, 10),  -- extent
    1024,                            -- width, in pixels
    800,                             -- height
    1::integer,                      -- value
    true                             -- point mode
)
FROM test_table;
---------
\x080010a0061880083...

Filter rows before aggregation

Use a WHERE clause to restrict which trajectory objects are included.

SELECT ST_AsHMT(traj,
    ST_MakeEnvelope(0, 0, 10, 10),  -- extent
    1024,                            -- width, in pixels
    800                              -- height
)
FROM test_table
WHERE num < 5;
---------
\x080010a00618...