raster query

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

raster query

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
[hidden email]

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: raster query

Birgit Laggner-2
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
[hidden email]


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: raster query

Stephen Crawford
Thanks I will give it try this afternoon.

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
[hidden email]


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Stephen Crawford
Center for Environmental Informatics
The Pennsylvania State University
[hidden email]
814.865.9905

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: raster query

Stephen Crawford
In reply to this post by Birgit Laggner-2
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
[hidden email]


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Stephen Crawford
Center for Environmental Informatics
The Pennsylvania State University
[hidden email]
814.865.9905

_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: raster query

Birgit Laggner-2
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
[hidden email]


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users

-- 
Stephen Crawford
Center for Environmental Informatics
The Pennsylvania State University
[hidden email]
814.865.9905


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
http://lists.osgeo.org/mailman/listinfo/postgis-users