[postgis-users] raster, stats conditioned to a set of values

[Pierre Racine]

Right! Actually you need a more complex expression returning not a boolean but just the right value:

'CASE WHEN [rast] < = 0 THEN 0 ELSE [rast] END'

Pierre

> -----Original Message-----
> From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-
> bounces at lists.osgeo.org] On Behalf Of juli g. pausas
> Sent: Wednesday, July 01, 2015 5:34 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] raster, stats conditioned to a set of values
> 
> Thanks for this.
> 
> But it didn't work for me.
> 
> 
> These queries work
> 
> 
> SELECT (ST_SummaryStats(ST_MapAlgebra(rast, 1, '16BSI'::text, '[rast]+1'),
> 1)).* FROM rastertmp.ndvitmp
> SELECT (ST_SummaryStats(ST_MapAlgebra(rast, 1, NULL, '[rast]+1'), 1)).*
> FROM rastertmp.ndvitmp
> 
> 
> But not when I use the condition [rast] > 0
> 
> SELECT (ST_SummaryStats(ST_MapAlgebra(rast, 1, NULL, '[rast] > 0'), 1)).*
> FROM rastertmp.ndvitmp
> 
> ERROR:  cannot cast type boolean to double precision
> LINE 1: SELECT ($1 > 0)::double precision
> 
> 
> Replacing NULL for '16BSI'::text, or '16BSI'::smallint, '16BSI'::double
> precision doen't solve the problem
> 
> 
> Any other suggestion?
> 
> Thanks
> 
> 
> 
> 
> 
> 
> 
> Juli
> --
> CIDE, CSIC  |  www.uv.es/jgpausas  |
> 
> 
> 
> On Tue, Jun 30, 2015 at 5:29 PM, Pierre Racine
> <Pierre.Racine at sbf.ulaval.ca> wrote:
> 
> 
> 	You can select pixels fulfilling an expression using the one-raster
> variant of ST_MapAlgebra
> 
> 	http://postgis.net/docs/RT_ST_MapAlgebra_expr.html
> 
> 	So just do something like:
> 
> 	ST_MapAlgebra(rast, '8BUI'::text, '[rast] > 0')
> 
> 	before computing stats.
> 
> 	To count the number of pixels of a certain value you can use
> ST_ValueCount().
> 
> 	Pierre
> 
> 
> 	> -----Original Message-----
> 	> From: postgis-users-bounces at lists.osgeo.org [mailto:postgis-
> users-
> 	> bounces at lists.osgeo.org] On Behalf Of juli g. pausas
> 	> Sent: Monday, June 29, 2015 12:49 PM
> 	> To: PostGIS Users Discussion
> 	> Subject: [postgis-users] raster, stats conditioned to a set of values
> 	>
> 	> Hi all
> 	>
> 	> I'm just starting to discover postgis, it is really useful.
> 	>
> 	> I have a raster file with different bands, that I have imported to
> postgres
> 	> (raster2pgsql, without the -R option, i.e., insite the database). I
> would like to
> 	> extract information from band 1, but filtering the data using only
> 	>
> 	> 1) pixels with positive values (in that band, band 1)
> 	>
> 	> 2) and pixels in which band 2 is equal to a given value, e.g. 1
> 	>
> 	>
> 	> The type of analysis I'd like to do is descriptive stats but also
> intersecting
> 	> with a vector map. If my raster is rastertmp.ndvitmp, two
> examples are:
> 	>
> 	>
> 	> SELECT (ST_SummaryStats(rast, 1)).* FROM rastertmp.ndvitmp
> WHERE rid
> 	> = 1
> 	>
> 	>
> 	>
> 	> SELECT p.region_cod, ST_ValueCount(ST_Clip(r.rast,1, p.geom,
> true)) AS res
> 	>   FROM gis_wd.wd_regiones AS p, rastertmp.ndvitmp AS r
> 	>   WHERE ST_Intersects(r.rast, p.geom)
> 	>   AND p.region_cod = 'PA1214';
> 	>
> 	>
> 	>
> 	> This works perfectly, but how can I compute the stats  only for
> pixels with
> 	> positive values and with a given value in another band?  The idea
> would be
> 	> something like:  WHERE ST_Values(rast, 1)>0 AND ST_Values(rast,
> 2)=1
> 	>
> 	>
> 	> Thanks for any help!
> 	>
> 	>
> 	> Juli
> 	> --
> 	> CIDE, CSIC  |  www.uv.es/jgpausas  |
> 	>
> 
> 
> 	_______________________________________________
> 	postgis-users mailing list
> 	postgis-users at lists.osgeo.org
> 	http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
> 
>