PolarDB:ST_AsHMT

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

Choosing a variant:

• Count geometries (no value argument): use the first variant with default value=1 and point_mode=false.

• Sum an int4 column (e.g., counts, quantities): use the int4 value variants. If the sum may exceed 2,147,483,647, use float8 value instead to avoid overflow.

• Sum a float8 column (e.g., metrics, ratios): use the float8 value variants.

• Advanced aggregation (min, max, avg, or point-mode via JSON): use the cstring config variants.

Return values

Returns a protobuf-based binary data matrix (bytea). The proto schema is:

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

Matrix layout:

• type is determined by the input value parameter: INT32 for int4, DOUBLE for float8.

• rows and columns correspond to the height and width parameters.

• Values are stored row by row, in ascending order on the X axis and descending order on the Y axis, which matches standard image coordinate conventions.

To convert the return value to an array representation, use ST_HMTAsArray.

Parameters

Usage notes

SRID mismatch behavior: If the spatial reference of geometry_set differs from the spatial reference of extent, the function applies the spatial reference of extent to the data. To confirm that both use the same SRID before calling the function, run ST_SRID() on a sample geometry and compare it with the SRID embedded in extent.

int4 overflow: When using int4 value to calculate sums, values are bounded by the int32 range (–2,147,483,648 to 2,147,483,647). For data where the sum may exceed this range, pass a float8 column or expression instead.

Examples

All examples use a test table created with:

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

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

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

SELECT ST_AsHMT(geom,
    ST_MakeEnvelope(0, 0, 10, 10, 4326),  -- extent
    1024,                                  -- width
    800,                                   -- height
    weight / volume * 1.2                  -- float8 expression
)
FROM test_table;
---------
\x080110a0061880083a85...

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

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

SELECT ST_AsHMT(the_geom,
    ST_MakeEnvelope(0, 0, 10, 10, 4326),  -- extent
    256,                                   -- width, in pixels
    256,                                   -- height
    weight,
    '{"type":"avg"}'::cstring)
FROM test_table;
---------
\x080010a00618...

SELECT ST_AsHMT(the_geom,
    ST_MakeEnvelope(0, 0, 10, 10, 4326),  -- extent
    256,                                   -- width, in pixels
    256,                                   -- height
    weight,
    '{"type":"min", "point_mode":true}'::cstring)
FROM test_table;
---------
\x080010a00618...

What's next

• ST_HMTAsArray — convert an HMT binary value to an array representation