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 <[email protected]>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 <[email protected]>wrote: > >> On 9 July 2011 19:00, Chris English <[email protected]> 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 >> [email protected] >> 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.
_______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
