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 : [email protected]
[mailto:[email protected]] De la part de Guillaume Drolet
Envoyé : mercredi 10 décembre 2014 15:41
À : [email protected]
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
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
