[postgis-users] Dynamic parameters to ST_MapAlgebra

[Paragon Corporation]

Oops sorry that's what I get for not being able to test or not having a
debugger in my head.  I forgot to get rid of the Execute sql; So should be

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

    END;
    $$ LANGUAGE plpgsql IMMUTABLE; 

Hope that helps,
Regina

 

-----Original Message-----
From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Paragon
Corporation
Sent: Wednesday, December 10, 2014 3:34 PM
To: 'PostGIS Users Discussion'
Subject: Re: [postgis-users] Dynamic parameters to ST_MapAlgebra

Sorry quote_literal wasn't right function to use -- just cast to text and
even that is not necessary but more robust since it wouldn't rely on
postgres autocasting numbers to text for you.



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

        EXECUTE sql;
    END;
    $$ LANGUAGE plpgsql IMMUTABLE; 

-----Original Message-----
From: Paragon Corporation [mailto:lr at pcorp.us]
Sent: Wednesday, December 10, 2014 3:31 PM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] Dynamic parameters to ST_MapAlgebra

Guillaume,

If the expression approach is faster, is there some reason you aren't just
doing


    WITH scaling_params AS (
        SELECT gain::text As gain, off_set::text as off_set
        FROM meta
        WHERE product = 'MCD12Q2' AND sds = 'EVI'
    )
    SELECT ST_MapAlgebra(rast, 1, '32BF', '([rast] * ' || gain || ') + ' ||
off_set ) AS rast
    FROM evi, scaling_params;

Or is that too hard-coded for you?

The downside I see of using plpgsql is it has to copy each raster again into
the your new function so it uses at least twice the amount of memory of the
above. 

Now regarding the one that Hugues gave you, you have to run it differently
from how you were calling your others, otherwise it would take forever since
for every raster row you have it would apply map algebra to all the rows in
your table.  So how did you call his?


If you wanted to use the syntax you have I would rewrite your plpgsql as
follows (and not use dynamic sql) - you don't need dynamic sql to do this
just call ST_MapAlgebra directly.

    CREATE OR REPLACE FUNCTION scale_sds_plpgsql(rast raster, gain float8,
offs float8 DEFAULT 0)
    RETURNS raster AS $$
    BEGIN
        RETURN ST_MapAlgebra(rast, 1, '32BF', '([rast] * ' ||
            quote_literal(gain) || ') + ' || quote_literal(offs) );

        EXECUTE sql;
    END;
    $$ LANGUAGE plpgsql IMMUTABLE; 




Hope that helps,
Regina
http://www.postgis.us
http://postgis.net 

-----Original Message-----
From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Guillaume Drolet
Sent: Wednesday, December 10, 2014 2:56 PM
To: postgis-users at lists.osgeo.org
Subject: Re: [postgis-users] Dynamic parameters to ST_MapAlgebra

Hugues: thank a lot for your fix.

The function now runs without error... but it's been running for almost an
hour now :(

Compared to the 30-ish seconds it takes using the hard-coded query, and the
some 120 seconds using the PLV8 version, I cannot think of using the PLPGSQL
version in its current formulation. 

Does anyone else has a possibly faster suggestion, more in the ballpark of
the two other methods?

Thanks,

G



--
View this message in context:
http://postgis.17.x6.nabble.com/Dynamic-parameters-to-ST-MapAlgebra-tp500746
8p5007470.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


_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users