Slow query ST_SummaryStatsAgg over tiles

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Slow query ST_SummaryStatsAgg over tiles

matteo rivola
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_SRID(<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

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: Slow query ST_SummaryStatsAgg over tiles

Pierre Racine-2
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:[hidden email]] On Behalf
> Of matteo rivola
> Sent: Wednesday, August 10, 2016 11:31 AM
> To: [hidden email]
> 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
_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users