[postgis-users] Dynamic parameters to ST_MapAlgebra

[Hugues François]

Hi,

I think the problem comes from the use of a raster variable where ST_MapAlgebra is waiting for the name of a raster column. One way to avoid that is to use varchar variables to tell from which schema, table and name of the raster column you want to use. It should look like something like that:

CREATE OR REPLACE FUNCTION scale_sds_plpgsql(schema varchar, table varchar, rastcol varchar, gain float8, offs float8 DEFAULT 0)
RETURNS table(rast raster) AS
$$
    DECLARE
        query  text;
    BEGIN
        query := 'SELECT ST_MapAlgebra('||rastcol || ', 1, ''32BF'', ''([rast] * '||gain ||') + '||offs ||''')
	FROM '||schema||'.'||table||';';
RETURN query
 EXECUTE query;

RETURN;
    END;
    $$ LANGUAGE plpgsql IMMUTABLE;
 
HTH
Regards,

Hugues.

-----Message d'origine-----
De : postgis-users-bounces at lists.osgeo.org [mailto:postgis-users-bounces at lists.osgeo.org] De la part de Guillaume Drolet
Envoyé : mercredi 10 décembre 2014 15:41
À : postgis-users at lists.osgeo.org
Objet : [postgis-users] Dynamic parameters to ST_MapAlgebra

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.
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users