[postgis-users] Newbie ST_World2RasterCoordX, ST_World2RasterCoordY Band Values nested loop to iterate over raster

[Nicolas Ribot]

On 9 July 2011 19:00, Chris English <emailchrisenglish at gmail.com> wrote:
> Hi,
> I am trying to iterate over a raster, extract World2RasterCoordX/Y and Band
> Values using the following function:
> Create or replace function extract_as_text(rast raster) returns text as
> $$
> Declare
>  i int;
>  j int;
> rast raster;
> Begin
> for i in 1..2048 loop  --dim x
> for j in 1..1536 loop -- dim y
> select ST_world2rastercoordx(rast, i, j) as xcoord,
> St_world2rastercoordy(rast, i, j) as ycoord,
> ST_value(rast,1, i, j) as R,
> ST_value(rast, 2, i, j) as G,
> ST_value(rast, 3, i, j) as B
> from rbike_all;
> End loop;
> End loop;
> return xcoord,ycoord,R,G,B; <- this part is probably not right either -
> should perhaps just say text
> end;
> $$
> Language 'plpgsql'
> Query returned successfully with no result in 15 ms.
> Select extract_as_text(rast)
> from rbike_all;
> ERROR:  column reference "rast" is ambiguous
> LINE 1: select ST_world2rastercoordx(rast, i, j) as xcoord,
>                                      ^
> DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
> QUERY:  select ST_world2rastercoordx(rast, i, j) as xcoord,
> St_world2rastercoordy(rast, i, j) as ycoord,
> ST_value(rast,1, i, j) as R,
> ST_value(rast, 2, i, j) as G,
> ST_value(rast, 3, i, j) as B
> from rbike_all
> CONTEXT:  PL/pgSQL function "extract_as_text" line 9 at SQL statement
>
> ********** Error **********
> ERROR: column reference "rast" is ambiguous
> SQL state: 42702
> Detail: It could refer to either a PL/pgSQL variable or a table column.
> Context: PL/pgSQL function "extract_as_text" line 9 at SQL statement
> Removing rast from Declare above  results in the same error.
> Any pointers greatly appreciated.
> Chris
>
Hi Chris:

Both the input parameter and the declared parameter are called "rast".
This causes the error I think.
Rename the input parameter to something like p_rast and the declared
parameter to v_param.

That should fix the problem.

Nicolas