[postgis-users] raster query

[Stephen Crawford]

I had to make just a few minor adjustments, only about casting types 
which you couldn't have know about anyway.  Worked well.  Many Thanks.

On 11/3/2016 8:20 AM, Birgit Laggner wrote:
> Hi Stephen,
>
> here my proposed solution (untested!!)
>
> DO $$
> DECLARE r record; doy date;
> BEGIN
>
> FOR r IN (SELECT DISTINCT ST_Value(rast,x,y) AS obs_date FROM 
> doy_raster, generate_series(1,(SELECT ST_Width(rast) FROM doy_raster)) 
> AS x, generate_series(1,(SELECT ST_Height(rast) FROM doy_raster)) AS 
> y) LOOP
>
>     doy := r.obs_date;
>
>     EXECUTE
>     'INSERT INTO accum_risk (obs_date, rast)
>      SELECT '||doy||',
>             ST_Union(ST_MapAlgebra(a.rast, b.rast,
>                      ''CASE WHEN [rast2]='||quote_literal(doy)||' THEN 
> [rast1] ELSE 0 END'',
>                      ''64BF'',''INTERSECTION''),''SUM'')
>      FROM daily_risk AS a, doy_raster AS b
>      WHERE a.obs_date BETWEEN '||quote_literal(doy)||'::date - ''60 
> days''::interval AND
>                               '||quote_literal(doy);
>
> END LOOP;
>
> END $$;
>
> The idea is to loop over the all existing observation dates from 
> doy_raster and use ST_MapAlgebra to limit the raster cells from 
> daily_risk raster to those intersecting with raster cells from 
> doy_raster with the corresponding observation date for the current loop.
> As already mentioned, the code is not tested (because I don't have 
> similar datasets available)...
>
> Regards,
>
> Birgit
>
>
> Am 02.11.2016 um 21:51 schrieb Stephen Crawford:
>> Hi All,
>>
>> I am hoping somebody can help me with a query.  I have a table of 
>> rasters where each record is date ("obs_date") and raster containing 
>> a risk value of 0  or 1.  My easy, successful query to accumulate the 
>> risk values over the previous 60 days is:
>>
>> INSERT INTO accum_risk (obs_date, rast)
>> SELECT '1979-07-15', ST_Union(rast,'SUM')
>> FROM daily_risk
>> WHERE obs_date BETWEEN '1979-05-16' AND '1979-07-15';
>>
>> My final goal--for which I am asking help--is similar to the above 
>> query, but it will reference another raster table.  This table has 
>> for each grid cell a value for the day of year (DOY) from which the 
>> 60 day accumulation should be made. Conceptually:
>>
>> FOR EACH doy_cell IN doy_raster
>> SELECT ST_Union(rast,'SUM')
>> FROM daily_risk
>> WHERE obs_date BETWEEN doy-60 AND doy;
>>
>> Any help is greatly appreciated.
>>
>> Thanks,
>> Steve
>> -- 
>> Stephen Crawford
>> Center for Environmental Informatics
>> The Pennsylvania State University
>> src176 at psu.edu
>>
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> http://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Stephen Crawford
Center for Environmental Informatics
The Pennsylvania State University
src176 at psu.edu
814.865.9905

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20161104/cd5a7ae1/attachment.html>