Use ST_AsHMT to convert geometry objects to heat map tiles - PolarDB

ST_AsHMT aggregates a set of geometry objects into a heat map tile (HMT) — a protobuf-encoded binary data matrix — based on a specified geographic extent and grid resolution.

Syntax

bytea ST_AsHMT(geometry geometry_set, geometry extent, int4 width, int4 height, int4 value default 1, boolean point_mode default false);
bytea ST_AsHMT(geometry geometry_set, geometry extent, int4 width, int4 height, int4 value);
bytea ST_AsHMT(geometry geometry_set, geometry extent, int4 width, int4 height, int4 value, boolean point_mode);
bytea ST_AsHMT(geometry geometry_set, geometry extent, int4 width, int4 height, float8 value);
bytea ST_AsHMT(geometry geometry_set, geometry extent, int4 width, int4 height, float8 value, boolean point_mode);
bytea ST_AsHMT(geometry geometry_set, geometry extent, int4 width, int4 height, int4 value, cstring config);
bytea ST_AsHMT(geometry geometry_set, geometry extent, int4 width, int4 height, float8 value, cstring config);

Parameters

Parameter	Type	Default	Description
`geometry_set`	geometry	—	The geometry column to aggregate.
`extent`	geometry	—	The geographic extent of the tile. Only the bounding box is used. Works with ST_TileEnvelope.
`width`	int4	—	Grid width in pixels. Maps to the `columns` field in the returned matrix.
`height`	int4	—	Grid height in pixels. Maps to the `rows` field in the returned matrix.
`value`	int4 or float8	`1`	The value to accumulate per geometry. Summed across all geometries in each grid cell. Use `float8` to avoid integer overflow when summing large datasets (int32 range: -2,147,483,648 to 2,147,483,647).
`point_mode`	boolean	`false`	When `true`, only points within the mesh are counted.
`config`	cstring	—	A JSON string with advanced aggregation options. See Config options.

Return value

Returns bytea — a protobuf-encoded binary data matrix (proto2 format).

syntax = "proto2";
option optimize_for = LITE_RUNTIME;

message HMT {
    required Type type = 1;       // INT32 or DOUBLE, determined by the value parameter type
    required uint32 rows = 2;     // grid height (matches the height parameter)
    required uint32 columns = 3;  // grid width (matches the width parameter)
    required uint32 srid = 4;
    required float  xmin = 5;
    required float  ymin = 6;
    required float  xmax = 7;
    required float  ymax = 8;

    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;
    }
}

Matrix layout:

Values are stored row by row.
X values increase left to right (ascending).
Y values increase bottom to top (descending in array order), which aligns with standard image coordinate systems.

To convert an HMT result to a SQL array, use ST_HMTAsArray.

Usage notes

If the spatial reference of the data is inconsistent with the spatial reference of the input range, the spatial reference of the input range is used for the data.

Config options

Pass a JSON string to the config parameter to control aggregation behavior.

Option	Type	Default	Description
`type`	string	`sum`	Aggregation method. Valid values: `sum`, `min`, `max`, `avg`.
`point_mod`	boolean	`false`	Specifies whether to use the point mode.

Examples

All examples use the same test table:

CREATE TABLE test_table AS
SELECT i                                                                   AS num,
       ST_SetSrid(st_makepoint((i-0.5)::numeric, (i-0.5)::numeric), 4326) AS geom,
       i * 100::int4                                                       AS weight,
       i * i * i::float8                                                   AS volume
FROM generate_series(1, 10) i;

Count points

The simplest form — count how many points fall in each grid cell. value defaults to 1.

SELECT ST_AsHMT(
    geom,
    ST_MakeEnvelope(0, 0, 10, 10, 4326),  -- extent
    1024,                                   -- width (columns)
    800                                     -- height (rows)
)
FROM test_table;

Sum a value column

Pass a column as value to sum it across each cell.

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

Sum a computed expression

value accepts any expression. This example sums a computed ratio as a float8.

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

Point mode

Use point_mode => true to count only points within each cell.

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

Filter rows with WHERE

Apply a WHERE clause to limit which geometries are included in the aggregation.

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

Use the config parameter for avg and min

Pass config as a JSON string cast to cstring to use aggregation modes other than sum.

-- Average aggregation
SELECT ST_AsHMT(
    geom,
    ST_MakeEnvelope(0, 0, 10, 10, 4326),
    256,
    256,
    weight,
    '{"type":"avg"}'::cstring
)
FROM test_table;

-- Min aggregation with point mode
SELECT ST_AsHMT(
    geom,
    ST_MakeEnvelope(0, 0, 10, 10, 4326),
    256,
    256,
    weight,
    '{"type":"min","point_mode":true}'::cstring
)
FROM test_table;

Decode the result with ST_HMTAsArray

The raw bytea return value is opaque. Use ST_HMTAsArray to inspect the matrix contents as a SQL array.

SELECT ST_HMTAsArray(
    ST_AsHMT(
        geom,
        ST_MakeEnvelope(0, 0, 10, 10, 4326),
        10,   -- small grid for readability
        10,
        weight
    )
)
FROM test_table;

What's next

ST_HMTAsArray — convert an HMT result to a SQL array