ST_AsHMT-PolarDB(PolarDB)-阿里云帮助中心

Aggregates a set of trajectory objects into heat map tiles (HMT) for a specified geographic extent 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	Type	Default	Description
`trajectory_set`	`trajectory`	—	The trajectory column used by the aggregate function.
`extent`	`geometry`	—	The geographic range to aggregate. Only the bounding box is used. Compatible with `ST_TileEnvelope` and `ST_MakeEnvelope`.
`width`	`int4`	—	The width of the output matrix in pixels, corresponding to the number of columns.
`height`	`int4`	—	The height of the output matrix in pixels, corresponding to the number of rows.
`value`	`int4` or `float8`	`1`	The value to accumulate per cell. The sum of all matching values is calculated. Use `int4` for counts and `float8` for weighted metrics.
`point_mode`	`boolean`	`false`	When `true`, only trajectory points that fall exactly within each cell are counted, rather than all segments passing through.

Return value

Returns a bytea value containing a Protobuf-encoded binary matrix (HMT). Each cell in the matrix holds the accumulated value for that geographic area.

The 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;     // spatial reference identifier (SRID)
    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;
    }
}

Reading the matrix: The values array is row-major — elements are ordered row by row, left to right. The total number of elements equals rows × columns.

Type selection: The type field reflects the value parameter's type. Pass an int4 value to get an intMatrix (type INT32); pass a float8 value to get a doubleMatrix (type DOUBLE).

SRID field: The srid field stores the spatial reference identifier of the output matrix, inherited from the extent geometry.

Use ST_HMTAsArray to convert the returned binary value into a SQL array for further processing.

Examples

All examples use the same test table. Create it once before running the queries:

CREATE 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 density

Omit value to count the number of trajectory segments passing through each cell (default value = 1):

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

-- Result:
-- \x080010a0061880083284...

Sum an integer value

Pass an int4 column as value to sum that column's values per cell:

SELECT ST_AsHMT(
    traj,
    ST_MakeEnvelope(0, 0, 10, 10),
    1024,
    800,
    weight    -- int4 value column
)
FROM test_table;

-- Result:
-- \x080010a0061880...

Sum a computed float expression

Pass a float8 expression to accumulate weighted metrics per cell. The result uses a doubleMatrix:

SELECT ST_AsHMT(
    traj,
    ST_MakeEnvelope(0, 0, 10, 10),
    1024,
    800,
    weight / volume * 1.2    -- float8 expression
)
FROM test_table;

-- Result:
-- \x080110a0061880083a85...

Enable point mode

Set point_mode = true to count only trajectory points that fall within each cell, ignoring segments:

SELECT ST_AsHMT(
    traj,
    ST_MakeEnvelope(0, 0, 10, 10),
    1024,
    800,
    1::integer,  -- value
    true         -- point mode
)
FROM test_table;

-- Result:
-- \x080010a0061880083...

Filter rows with a WHERE clause

Apply a WHERE clause to aggregate only a subset of trajectories:

SELECT ST_AsHMT(
    traj,
    ST_MakeEnvelope(0, 0, 10, 10),
    1024,
    800
)
FROM test_table
WHERE num < 5;

-- Result:
-- \x080010a00618...