All Products
Search
Document Center

PolarDB:ST_AsHMT

Last Updated:Apr 23, 2024

This function converts a set of geometry objects into heat map tiles (HMT) based on a specified range and 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);

Return values

A protobuf-based binary data matrix is returned. The following example shows a proto file.

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

  • The value of the type parameter is specified by the input parameters, and can be of the int32 or double type. The int32 type is used to calculate information such as quantity, and the double type is used to calculate information such as metrics.

  • The rows parameter indicates the number of rows in the matrix, and the columns parameter indicates the number of columns in the matrix.

  • The values in the matrix are arrays of a specified data type and organized in rows.

  • In the matrix, values are in ascending order in the X direction and in descending order in the Y direction. This facilitates the conversion into images.

  • You can use the ST_HMTAsArray function to convert the return values into an array representation. For more information about the ST_HMTAsArray function, see ST_HMTAsArray.

Parameters

Parameter

Description

geometry_set

The geometry column field.

extent

The geographic range. Only the bounding box is used. It can be used in conjunction with the ST_TileEnvelope function.

width

The mesh width, which corresponds to the columns parameter in the result.

height

The mesh height, which corresponds to the rows parameter in the result.

value

The statistic value. The value is summed in the process.

point_mode

Specifies whether to use the point mode. If the point mode is used, only the points within the mesh are counted.

config

The JSON string that indicates the style of the heatmap.

Usage notes

This function converts a set of geometry objects into HMTs based on a specified range and resolution.

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.

The config parameter indicates the calculation method in the JSON strings. The following table describes the options that you can configure for the parameter.

Option

Description

Type

Default value

Remarks

type

The type of the aggregation.

string

sum

Valid values:

  • sum

  • min

  • max

  • avg

point_mod

Specifies whether to use the point mode.

boolean

false

-

Note

If you use the int4 type to calculate a sum value, invalid values may be returned due to the valid values of the int32 type that range from -2147483648 to 2147483647. We recommend that you use the float8 type.

Examples

-- create table
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;

-- count quantity
SELECT ST_AsHMT(geom, --geometry type
    ST_MakeEnvelope(0, 0, 10, 10, 4326), -- Extent
    1024,        -- Width, in pixel
    800        -- height
)
FROM test_table;

---------
\x080010a0061880083284...

-- count value
SELECT ST_AsHMT(geom, --geometry type
    ST_MakeEnvelope(0, 0, 10, 10, 4326), -- Extent
    1024,        -- Width
    800,       -- height
    weight     -- value column
)
FROM test_table;
---------
\x080010a0061880...

-- complex count
SELECT ST_AsHMT(geom, --geometry type
    ST_MakeEnvelope(0, 0, 10, 10, 4326), -- Extent
    1024,        -- Width
    800,        -- height
    weight / volume * 1.2     -- complex value
)
FROM test_table;
---------
\x080110a0061880083a85...

-- point mode
SELECT ST_AsHMT(geom, --geometry type
    ST_MakeEnvelope(0, 0, 10, 10, 4326), -- Extent
    1024,        -- Width, in pixel
    800,        -- height,
    1::integer,  -- value
    true        -- point mode
)
FROM test_table;
---------
\x080010a0061880083...

-- where clause
SELECT ST_AsHMT(geom, --geometry type
    ST_MakeEnvelope(0, 0, 10, 10, 4326), -- Extent
    1024,        -- Width, in pixel
    800        -- height
)
FROM test_table
WHERE num <5;
---------
\x080010a00618...

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

-- Use min, point mode 
SELECT ST_AsHMT(the_geom,
    ST_MakeEnvelope(0, 0, 10, 10, 4326), -- Extent
    256,        -- Width, in pixel
    256        -- height
    weight, 
    '{"type":"min", "point_mode":true}'::cstring)
FROM test_table;
---------
\x080010a00618...