[postgis-users] Slow query ST_SummaryStatsAgg over tiles

[Pierre Racine]

From the ST_SummaryStatsAgg () doc: "By default will sample all pixels. To get faster response, set sample_percent to value between 0 and 1"

Pierre

> -----Original Message-----
> From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf
> Of matteo rivola
> Sent: Wednesday, August 10, 2016 11:31 AM
> To: postgis-users at lists.osgeo.org
> Subject: [postgis-users] Slow query ST_SummaryStatsAgg over tiles
> 
> Dear all,
> I'm using postigis version 2.2.1, on PostgreSQL 9.2.0.
> 
> I loaded SRTM DEM geotiff with raster2pgsql.
> I create a query in order to retrieve the global statistics of the tiles, over only
> Europe zone.
> 
> The query is:
> 
> SELECT ((stats).min) as value from (SELECT
> ST_SummaryStatsAgg(ST_Clip(<DB_table>.rast,ST_GeomFromText(<wkt>,ST_SRI
> D(<DB_table>.rast)),true),1,true) as stats from <DB_table>) as stats;
> 
> where DB_table is the table in which are the rasters and wkt is equal to
> 'POLYGON((-16 74,39 74,39 33,-16 33,-16 74))'.
> 
> The query works fine but it takes a lot of time.
> 
> Is there a way to speed up the query?
> 
> Cheers,
> Matteo