Use ST_HMTStats to calculate statistical values for heatmap tiles - PolarDB

Computes statistical values from a heatmap tile (HMT) binary for use in rendering pipelines. Call this function after generating a heatmap tile with ST_AsHMT to determine the value range before mapping colors.

Syntax

record ST_HMTAsArray(bytea hmt, integer factor default 1, out float8 min, out float8 max, out float8 mean, out float8 sum , out float8 count , out float8 std, out float8 median, out float8 mode, out float8[] percentile);

Parameters

Parameter	Type	Description
`hmt`	`bytea`	The heatmap tile binary encoded in protobuf format. Generate it with `ST_AsHMT`.
`factor`	`integer`	The sampling ratio, indicating the number of values used in each statistical calculation. Default: `1`. For example, a factor of 4 indicates that four values are used in each statistical calculation.

Return values

The function returns a record with the following fields:

Field	Type	Description
`min`	`float8`	Minimum value.
`max`	`float8`	Maximum value.
`mean`	`float8`	Arithmetic mean.
`sum`	`float8`	Sum of values.
`count`	`float8`	The total number of statistical values.
`std`	`float8`	Standard deviation.
`median`	`float8`	Median value.
`mode`	`float8`	Most frequently occurring value.
`percentile`	`float8[]`	Array of 100 percentile values.

Examples

Basic usage (factor=1)

Compute full statistics on a heatmap tile generated from 10 points. All 100 tile values are included in the calculation.

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_HMTstats(ST_AsHMT(the_geom,
    ST_MakeEnvelope(0, 0, 10, 10),
    10,
    10))).*
FROM test_table;

Output:

 min | max | mean | sum | count | std | median | mode | percentile
   0 |   2 | 0.19 |  19 |   100 | 0.5778408085277467 |      0 |    0 | {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,0,0,0,0,0,0,0,1,2,2,2,2,2,2,2,2}

Using factor for faster sampling (factor=4)

Set factor=4 to use four values in each statistical calculation. The count drops from 100 to 25.

SELECT (ST_HMTstats(ST_AsHMT(the_geom,
    ST_MakeEnvelope(0, 0, 10, 10),
    10,
    10),4)).*
FROM test_table;