Calculate heatmap tile statistics using ST_HMTStats - PolarDB

Calculates statistical values of heatmap tiles for use during rendering.

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	Description
`hmt`	The heatmap tile binary in protobuf format.
`factor`	The sampling ratio, indicating the number of values used in each statistical calculation. For example, a `factor` of `4` indicates that four values are used in each statistical calculation. Defaults to `1`.

Return values

Returns a record with the following fields:

Field	Description
`min`	The minimum value.
`max`	The maximum value.
`mean`	The mean value.
`sum`	The sum of all values.
`count`	The total number of statistical values.
`std`	The standard deviation.
`median`	The median value.
`mode`	The mode.
`percentile`	An array of 100 percentile values.

Description

ST_HMTStats calculates statistical values of heatmap tiles for use during rendering. Generate heatmap tiles using the ST_AsHMT function before calling this function.

Examples

The following examples use a test table with 10 points. Each example calls ST_HMTStats with ST_AsHMT to compute tile statistics over a 10×10 grid bounded by (0,0) to (10,10).

Create the test 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;

Example 1: Default factor (factor=1)

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}

Example 2: Sparse sampling (factor=4)

With factor=4, the function samples every fourth data point, reducing count from 100 to 25.

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

Output:

 min | max | mean | sum | count | std                | median | mode | percentile
   0 |   2 | 0.16 |   4 |    25 | 0.5425863986500215 |      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,0,0,2,2,2,2,2,2,2}