[postgis-users] Dynamic parameters to ST_MapAlgebra

[Guillaume Drolet]

Hi,

I'm trying to find the best way to dynamically apply scaling factors to a
raster. I've tried different approaches using ST_MapAlgebra, both callback
and expression versions. The fastest method so far is this one but it
doesn't allow for dynamic parameters:

    SELECT ST_MapAlgebra(rast, 1, '32BF', '([rast] * 0.01) + 0') AS rast,
    FROM evi;

Compared to using the following function, the query above is ten times
faster:

    CREATE OR REPLACE FUNCTION public.scale_sds_plv8(value double
precision[][][], 
	pos integer[][], VARIADIC userargs text[])
    RETURNS double precision AS
    $$
      var g = Number(userargs[0]);
      var o = Number(userargs[1]);
      
      return (value * g + o);
   
    $$ LANGUAGE plv8 IMMUTABLE;

    WITH scaling_params AS (
	SELECT gain, off_set
	FROM meta
	WHERE product = 'MCD12Q2' AND sds = 'EVI'
    ) 
    SELECT ST_MapAlgebra(rast, 1, 'scale_sds_plv8(double
            precision[], integer[], text[])'::regprocedure, '32BF', 'FIRST',
NULL::raster, 0, 0, 
            VARIADIC ARRAY[gain, off_set]::text[]) AS rast 
    FROM evi, scaling_params 

I want to take advantage of the speed of the first method above but be able
to pass parameters dynamically instead of hard-coding them in the query. To
this aim I tried this approach:

    CREATE OR REPLACE FUNCTION scale_sds_plpgsql(rast raster, gain float8,
offs float8 DEFAULT 0)
    RETURNS raster AS $$
    DECLARE
        sql text;
    BEGIN
        sql := 'SELECT ST_MapAlgebra(' || rast || 
            ', 1, ''32BF'', ''([rast] * ' ||
            gain || ') + ' || offs || ''')';

        EXECUTE sql;
    END;
    $$ LANGUAGE plpgsql IMMUTABLE;

    SELECT scale_sds_plpgsql(rast, 0.01, 0.0)
    FROM evi;

Running this last query, I get a synthax error which I don't understand:  

ERREUR: erreur de syntaxe sur ou près de «
F400000000000407FC05E10B2668C203F41A0BD86AA1F7C2....
CONTEXT:  fonction PL/pgsql scale_rast(raster,double precision,double
precision), ligne 12 à instruction EXECUTE
********** Error **********

I'm sure there are experienced folks on this list who will have some clues
to solve this or who will come up with a better approach. 

Thanks a lot for your time.

G











--
View this message in context: http://postgis.17.x6.nabble.com/Dynamic-parameters-to-ST-MapAlgebra-tp5007468.html
Sent from the PostGIS - User mailing list archive at Nabble.com.