ST_HMTStats for calculating heatmap tile statistics for rendering - ApsaraDB RDS

Computes statistics on a heatmap tile for use during map rendering.

Syntax

record ST_HMTStats(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	Default	Description
hmt	bytea	—	The heatmap tile binary encoded in protobuf format. Generate this value using ST_AsHMT.
factor	integer	1	The sampling interval. A value of `n` means one in every `n` values is included in each statistical calculation. For example, `factor=4` samples every fourth value, reducing the count from 100 to 25.

Return values

Returns a record with the following output fields:

Field	Type	Description
min	float8	The minimum value.
max	float8	The maximum value.
mean	float8	The average value.
sum	float8	The sum of all sampled values.
count	float8	The number of values included in the calculation.
std	float8	The standard deviation.
median	float8	The median value.
mode	float8	The mode (most frequent value).
percentile	float8[]	An array of 100 percentile values.

Examples

The following examples use the same test table. Create it once, then run either query.

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 sampling (factor=1)

All 100 tile values are included in the calculation.

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

Expected output:

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

Example 2: Sparse sampling (factor=4)

Every fourth value is sampled, reducing the count to 25.

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

Expected output:

 min | max | mean | sum | count |        std         | median | mode |                  percentile
-----+-----+------+-----+-------+--------------------+--------+------+------------------------------------------------
   0 |   2 | 0.16 |   4 |    25 | 0.5425863986500215 |      0 |    0 | {0,0,...,0,0,2,2,2,2,2,2,2}