<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi Stephen,<br>
<br>
here my proposed solution (untested!!)<br>
<br>
DO $$<br>
DECLARE r record; doy date;<br>
BEGIN<br>
<br>
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<br>
<br>
doy := r.obs_date;<br>
<br>
EXECUTE<br>
'INSERT INTO accum_risk (obs_date, rast)<br>
SELECT '||doy||',<br>
ST_Union(ST_MapAlgebra(a.rast, b.rast,<br>
''CASE WHEN [rast2]='||quote_literal(doy)||'
THEN [rast1] ELSE 0 END'',<br>
''64BF'',''INTERSECTION''),''SUM'')<br>
FROM daily_risk AS a, doy_raster AS b<br>
WHERE a.obs_date BETWEEN '||quote_literal(doy)||'::date - ''60
days''::interval AND<br>
'||quote_literal(doy);<br>
<br>
END LOOP;<br>
<br>
END $$;<br>
<br>
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.<br>
As already mentioned, the code is not tested (because I don't have
similar datasets available)...<br>
<br>
Regards,<br>
<br>
Birgit<br>
<br>
<br>
<div class="moz-cite-prefix">Am 02.11.2016 um 21:51 schrieb Stephen
Crawford:<br>
</div>
<blockquote cite="mid:4b5de97c-950e-0394-f737-9990578e473f@psu.edu"
type="cite">
<meta http-equiv="content-type" content="text/html; charset=utf-8">
Hi All,<br>
<br>
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:<br>
<br>
INSERT INTO accum_risk (obs_date, rast)<br>
SELECT '1979-07-15', ST_Union(rast,'SUM')<br>
FROM daily_risk<br>
WHERE obs_date BETWEEN '1979-05-16' AND '1979-07-15';<br>
<br>
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:<br>
<br>
FOR EACH doy_cell IN doy_raster<br>
SELECT ST_Union(rast,'SUM')<br>
FROM daily_risk<br>
WHERE obs_date BETWEEN doy-60 AND doy;<br>
<br>
Any help is greatly appreciated.<br>
<br>
Thanks,<br>
Steve<br>
<pre class="moz-signature" cols="72">--
Stephen Crawford
Center for Environmental Informatics
The Pennsylvania State University
<a moz-do-not-send="true" class="moz-txt-link-abbreviated" href="mailto:src176@psu.edu">src176@psu.edu</a>
</pre>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a>
<a class="moz-txt-link-freetext" href="http://lists.osgeo.org/mailman/listinfo/postgis-users">http://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</body>
</html>