[postgis-users] raster query

[Birgit Laggner]

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

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