[postgis-users] raster query

[Birgit Laggner]

I am glad I could help :-)

Regards, Birgit

Am 04.11.2016 um 15:45 schrieb 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/postgis-users

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