[postgis-users] Summarystats (Raster, Polygon)

Fri Nov 16 09:06:55 PST 2012

I don't know if this is what you are referring to but another way to speed up things (and get fast approximate results) without having to store multiple resolutions is to tile everything very small (10x10) and precompute and store all the stats generated by ST_SummaryStats in extra columns for each tile. You can also add the centroid of the tile in an extra geometry column and then you can just query just for the intersecting centroids and compute the aggregate stats yourself. This would be equivalent to if the raster type would store the stats internally. But we choose not to do it because we can store them as extra columns (we are in a database!).

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:43 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] Summarystats (Raster, Polygon)
> 
> Thanks Pierre, really helpful advice. So, what would be the best way
> in your book to calculate summary stats and cell counts over a
> graticule? That could also be a useful way to go and more accurate
> than nearest neighbour.
> 
> Duncan
> 
> On Fri, Nov 16, 2012 at 10:35 AM, Pierre Racine
> <Pierre.Racine at sbf.ulaval.ca> wrote:
> > 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
> > _______________________________________________
> > 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users