[postgis-users] Summarystats (Raster, Polygon)

[Pierre Racine]

There are two ways to do that:

1) Load multiple overviews of the raster with the -l OVERVIEW_FACTOR raster2pgsql option and query the overview instead of the full resolution raster. (You can also create the overviews in SQL with a complex mixture of ST_Union() and ST_Rescale() call. A plpgsql ST_CreateOverview() would be very welcome if you have some spare time. See objective 20 and 21 in http://trac.osgeo.org/postgis/wiki/WKTRaster/SpecificationWorking03)

2) If you look at rtpostgis.sql you will find an undocumented ST_ApproxSummaryStats() function which accept a sample_percent argument reducing the number of pixels summary stats are computed on. Not sure this one speedup things...  To try!

Pierre

> -----Original Message-----
> From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-
> bounces at lists.osgeo.org] On Behalf Of Duncan Golicher
> Sent: Friday, November 16, 2012 11:20 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Summarystats (Raster, Polygon)
> 
> Can I ask Pierre his opinion on applying nearest neighbour resampling
> within a query in order to speed things up when precision is not the
> top priority, but many polygons or buffers have to be used?
> 
> Would this work, and if so what would the query look like?
> 
> Duncan
> 
> 
> On Fri, Nov 16, 2012 at 10:09 AM, Duncan Golicher <dgolicher at gmail.com>
> wrote:
> > Hello Andreas,
> >
> > Comparisons with Arc are always interesting to have.
> >
> > Can I ask how the speed of running the queries compares? We have been
> > implementing some similar queries but found them to run rather more
> > slowly than would be ideal. Pierre has suggested that this is due to
> > using the pgsql version of st_clip rather than the new C version, but
> > I have not yet managed to update to that as it does not seem to be
> > part of the nightly build yet.
> >
> > I have also found these sort of queries to be VERY sensitive to tile
> > size, with speed optimising at a mid range size making it quite tricky
> > to find the best tile size, especially as it will also depend on the
> > size of the polygons that are being used.  It would be interesting to
> > know what your experience has been.
> >
> > Duncan
> >
> > On Fri, Nov 16, 2012 at 5:41 AM, Andreas Forø Tollefsen
> > <andreasft at gmail.com> wrote:
> >> Hi Pierre and thanks for your reply.
> >>
> >> My nodata values are well defined and are excluded in the summarystats
> >> operation.
> >> I also tried inverting the order of (geom, rast) without this changing the
> >> results.
> >>
> >> Could this be explained by the possibility that Postgis and ArcGIS handels
> >> pixels at the border of the polygon differently? i.e. that one includes
> >> pixels intersecting, while the other only includes pixels if the centroid of
> >> the pixel intersects?
> >>
> >> Andreas
> >>
> >>
> >> 2012/11/15 Pierre Racine <Pierre.Racine at sbf.ulaval.ca>
> >>>
> >>> > Any suggestions why the results are not the same?
> >>>
> >>> Are your nodata values well defined?
> >>>
> >>> Might also be, depending on the version you have, the order in which you
> >>> pass the geometry and the raster to the ST_Intersects() function. Try
> >>> inverting them.
> >>>
> >>> > Any improvements to the query maybe?
> >>>
> >>> Not really.
> >>>
> >>> Pierre
> >>>
> >>> _______________________________________________
> >>> postgis-users mailing list
> >>> postgis-users at lists.osgeo.org
> >>> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >>
> >>
> >>
> >> _______________________________________________
> >> postgis-users mailing list
> >> postgis-users at lists.osgeo.org
> >> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> >>
> >
> >
> >
> > --
> > Dr Duncan Golicher
> > Investigador Titular,
> > El Colegio de la Frontera Sur, Chiapas,Mexico
> > Mexico tel +52 1 967 137 94 20
> > Skype name duncangolicher
> >
> > Publications: http://www.mendeley.com/profiles/duncan-golicher
> >
> > Senior lecturer, Bournemouth University, UK
> > Centre for Conservation Ecology & Environmental Change
> > School of Applied Sciences
> > Christchurch House rm C218a
> > Bournemouth University
> > Fern Barrow
> > Poole (Dorset) BH12 5BB UK
> > Tel. +44 (0)1202 961682
> >
> > For list of publications see Researcher ID:
> > http://www.researcherid.com/rid/B-4240-2009
> >
> > dgolicher at bournemouth.ac.uk
> > dgoliche at ecosur.mx
> >
> > Researcher ID:
> > http://www.researcherid.com/rid/B-4240-2009
> 
> 
> 
> --
> Dr Duncan Golicher
> Investigador Titular,
> El Colegio de la Frontera Sur, Chiapas,Mexico
> Mexico tel +52 1 967 137 94 20
> Skype name duncangolicher
> 
> Publications: http://www.mendeley.com/profiles/duncan-golicher
> 
> Senior lecturer, Bournemouth University, UK
> Centre for Conservation Ecology & Environmental Change
> School of Applied Sciences
> Christchurch House rm C218a
> Bournemouth University
> Fern Barrow
> Poole (Dorset) BH12 5BB UK
> Tel. +44 (0)1202 961682
> 
> For list of publications see Researcher ID:
> http://www.researcherid.com/rid/B-4240-2009
> 
> dgolicher at bournemouth.ac.uk
> dgoliche at ecosur.mx
> 
> Researcher ID:
> http://www.researcherid.com/rid/B-4240-2009
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users