1

I have a raster that is tiled and I am looking to produce an average raster with a 3x3 window. I have tried three methods. The first method uses the standard ST_Mean4ma. An average raster is produced, however I can see the tiles in the result. This method took almost an hour to process. The second method I tried is a custom function that basically strips ST_Mean4ma to its base but obviously misses something as the tiles are even more visible - null values around the tile edges are produced. This method only took 23 mins. The third method I tried looked to ST_Union the raster first however it errored out with a memory allocation message.

How can I get a complete raster without tile edge effects and in a timely manner?

--TEST 1
CREATE TABLE p839.image_average AS
SELECT ST_MapAlgebra(ARRAY[ROW(rast, 1)]::rastbandarg[], 'st_mean4ma(double precision[][][], integer[][], text[])'::regprocedure, '32BF', null, null, 1,1
            ) as rast
    FROM p839.image_normal;
CREATE INDEX idx_image_average_rast ON p839.image_average USING GIST (ST_ConvexHull(rast));
--TIME = 51:40
--ARTIFACT TILES PRESENT = TRUE

--TEST 2
CREATE TABLE p839.image_average AS
SELECT ST_MapAlgebra(ARRAY[ROW(rast, 1)]::rastbandarg[], 'ImageAnalysis_Average(float[][][], text, text[])'::regprocedure, '32BF', null, null, 1,1
            ) as rast
    FROM p839.image_normal;
CREATE INDEX idx_image_average_rast ON p839.image_average USING GIST (ST_ConvexHull(rast));
--TIME = 23:53
--ARTIFACT TILES PRESENT = TRUE

--TEST 3
CREATE TABLE p839.image_average AS
SELECT ST_MapAlgebra(ARRAY[ROW(ST_Union(rast), 1)]::rastbandarg[], 'ImageAnalysis_Average(float[][][], text, text[])'::regprocedure, '32BF', null, null, 1,1
            ) as rast
    FROM p839.image_normal;
CREATE INDEX idx_image_average_rast ON p839.image_average USING GIST (ST_ConvexHull(rast));
--TIME = MEMORY ALLOCATION ERROR
--ARTIFACT TILES PRESENT = N/A


-- THE CUSTOM IMAGE AVERAGE FUNCTION
CREATE OR REPLACE FUNCTION imageanalysis_average(
    IN matrix double precision[],
    IN nodatamode text,
    VARIADIC args text[])
  RETURNS double precision AS
$BODY$
    DECLARE
    _matrix float[][];
        sum float;
    BEGIN
    _matrix := matrix;
        sum := 0;
        FOR x in array_lower(matrix, 1)..array_upper(matrix, 1) LOOP
            FOR y in array_lower(matrix, 2)..array_upper(matrix, 2) LOOP
                sum := sum + _matrix[1][x][y];
            END LOOP;
        END LOOP;
        RETURN (sum*1.0/(array_upper(matrix,1)*array_upper(matrix,2) ))::float ;
    END;
    $BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 100;

TEST 1: TEST 1 TEST 2: TEST 2

Anyone have anything on this?

  • I don't use postgre so can't help much, but you should first be sure that the original tiles themselves are "seamless"; i.e. show no tiling effects. If they don't, my guess would be that your 3x3 kernel is not overlapping at the edges. I.e. at the boundaries, the kernel is only 1x3 or 3x1 (or 1x1 in the corners). That would create a tiling effect. Maybe won't fix your problem, just an idea. Easy to check using the corner values--if they are exactly the same after tiling, that (likely) indicates no averaging took place. – Jon Nov 15 '18 at 22:32
  • the original tiles are seamless. – D_C Nov 16 '18 at 18:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.