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

[Chris English]

More progress still:

CREATE OR REPLACE FUNCTION extract_xcoord_ycoord_text_1(rast raster)
  RETURNS text AS
$BODY$
Declare
 i int;
 j int;
xcoord text;
ycoord text;
text text= '''' ;

Begin
for i in 1..10 loop
for j in 1..10 loop
select ST_world2rastercoordx(rast, i, j) as xcoord INTO text;
select St_world2rastercoordy(rast, i, j) as ycoord INTO text;
 End loop;
End loop;
return text;
end;
$BODY$
  LANGUAGE plpgsql VOLATILE

extract_xcoord_ycoord_test_1
1  "3377172"

which turns out to be the same as:

Select ST_world2rastercoordy(rast,1,6)
from rbike_all;

*st_world2rastercoordy*
3377172 (as Int)

and just one result so perhaps, as raster is stored as single row I need to
set Return as Table or something.

Chris
On Sat, Jul 9, 2011 at 2:29 PM, Chris English
<emailchrisenglish at gmail.com>wrote:

> Some progress:
> extract_as_text_5(rast raster)
> --snip
> v_param raster;
>
> Begin
> for i in 1..2048 loop
> for j in 1..1536 loop
>  select ST_world2rastercoordx(v_param, i, j) as xcoord,
> St_world2rastercoordy(v_param, i, j) as ycoord,
> -snip
> Select extract_as_text_5(rast)
> from rbike_all;
>
> ERROR:  query has no destination for result data
> HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
> CONTEXT:  PL/pgSQL function "extract_as_text_5" line 9 at SQL statement
>
> I thought the   ST_xxx( ) as name was providing destination(s).  And
> perhaps you
> did Not mean to replace rast with v_param inside the loops.
>
> and hoped to get:
>      xcoord        ycoord      R    B    G
>  large #            large#       1   255  255
> and etc.
>
> I see its a name collision, but ...Results so far
>
> On Sat, Jul 9, 2011 at 1:44 PM, Nicolas Ribot <nicolas.ribot at gmail.com>wrote:
>
>> 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
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
>
>
>
> --
> He doesn't fully understand what he thinks he knows about the problem.
>



-- 
He doesn't fully understand what he thinks he knows about the problem.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110710/59c827b9/attachment.html>