ST_IsRandomSampled samples records using a hash-based approach, returning true for records that fall within the specified sample rate and false for the rest. Use it in a WHERE clause to filter a table down to approximately sample_rate% of its rows.
Syntax
BOOLEAN ST_IsRandomSampled(RECORD tuple, INTEGER sample_rate)Parameters
| Parameter | Description |
|---|---|
tuple | The record properties used to compute the hash value. Use ROW() to combine one or more columns. Choose columns that are unique and evenly distributed to keep the actual sample size close to the expected proportion. |
sample_rate | The sample rate. Specifies the probability that a record is sampled. For example, 50 samples approximately 50% of records. |
How it works
The return value of this function is calculated based on the values of the tuple and sample_rate parameters.
When applied to all rows in a table, the tuple column (or column combination) must be unique. This ensures that the number of sampled records is as close as possible to sample_rate% of the total row count.
Note: If the tuple values are not unique across the table, the actual number of sampled rows may differ significantly from the expected count.Examples
Return a Boolean flag for each row, with approximately 50% returning true:
SELECT ST_IsRandomSampled(ROW(id), 50) FROM table;Calculate the area of a random 80% sample of geometries:
SELECT ST_Area(geom) FROM table WHERE ST_IsRandomSampled(ROW(geom), 80);Generate Mapbox Vector Tile (MVT) features from a 50% sample at zoom level 0:
SELECT ST_AsMVTGeom(geom, ST_Transform(ST_TileEnvelope(0,0,0),4326)) FROM table
WHERE ST_IsRandomSampled(ROW(geom), 50) AND geom&&ST_Transform(ST_TileEnvelope(0,0,0),4326));