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

[juli g. pausas]

Hi
I found a way to discard the negative values at least when computing
ValueCount:

SELECT region_cod, (res).*
FROM
  (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'
   ) AS foo WHERE (res).value  > 0;


Although this is not useful for computing the mean and SD, ie. I still do
not know how to perform ST_SummaryStats excluding negative values in the
raster. One possibility could perhaps be to reclassify negative values to
NULL (assuming that NULL values are not considered in ST_SummaryStats,
which I'm not sure), but my attempts were unsuccessful.

But my main problem is that I would like to do this (e.g., the query
above), but only for the pixels in which Band2 = 0.  Any idea? any clue?

Thanks


Juli
--
*CIDE, CSIC*  |  www.uv.es/jgpausas  |


On Wed, Jul 1, 2015 at 11:34 AM, juli g. pausas <juli.g.pausas at uv.es> wrote:

> 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
>>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20150701/4adb1669/attachment.html>