More progress still:<div><br></div><div><div>CREATE OR REPLACE FUNCTION extract_xcoord_ycoord_text_1(rast raster)</div><div> RETURNS text AS</div><div>$BODY$</div><div>Declare</div><div> i int;</div><div> j int;</div><div>
xcoord text;</div><div>ycoord text;</div><div>text text= '''' ;</div><div><br></div><div>Begin</div><div>for i in 1..10 loop</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>for j in 1..10 loop</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>select ST_world2rastercoordx(rast, i, j) as xcoord INTO text;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>select St_world2rastercoordy(rast, i, j) as ycoord INTO text;</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span></div><div><span class="Apple-tab-span" style="white-space:pre"> </span>End loop;</div><div><span class="Apple-tab-span" style="white-space:pre"> </span>End loop;</div>
<div><span class="Apple-tab-span" style="white-space:pre"> </span>return text;</div><div>end;</div><div>$BODY$</div><div> LANGUAGE plpgsql VOLATILE</div><div><br></div><div>extract_xcoord_ycoord_test_1</div><div>1 "3377172"</div>
<div><br></div><div>which turns out to be the same as:</div><div><br></div><div><div>Select ST_world2rastercoordy(rast,1,6)</div><div>from rbike_all;</div></div><div><br></div><div><u>st_world2rastercoordy</u></div>3377172 (as Int)</div>
<div><br></div><div>and just one result so perhaps, as raster is stored as single row I need to</div><div>set Return as Table or something.</div><div><br></div><div>Chris<br><div class="gmail_quote">On Sat, Jul 9, 2011 at 2:29 PM, Chris English <span dir="ltr"><<a href="mailto:emailchrisenglish@gmail.com">emailchrisenglish@gmail.com</a>></span> wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"><div>Some progress:</div><div>extract_as_text_5(rast raster) </div><div>--snip</div><div>v_param raster;</div><div class="im">
<div><br></div><div>Begin</div><div>for i in 1..2048 loop</div></div><div class="im"><div><span style="white-space:pre-wrap"> </span>for j in 1..1536 loop</div>
</div><div><span style="white-space:pre-wrap"> </span>select ST_world2rastercoordx(v_param, i, j) as xcoord,</div><div><span style="white-space:pre-wrap"> </span>St_world2rastercoordy(v_param, i, j) as ycoord,</div>
<div>-snip</div><div><div><div>Select extract_as_text_5(rast)</div><div>from rbike_all;</div></div><div><br></div><div>ERROR: query has no destination for result data</div><div>HINT: If you want to discard the results of a SELECT, use PERFORM instead.</div>
<div>CONTEXT: PL/pgSQL function "extract_as_text_5" line 9 at SQL statement</div></div><div><br></div><div>I thought the ST_xxx( ) as name was providing destination(s). And perhaps you</div><div>did Not mean to replace rast with v_param inside the loops.</div>
<div><br></div><div>and hoped to get:</div><div> xcoord ycoord R B G</div><div> large # large# 1 255 255</div><div>and etc.</div><div><br></div><div>I see its a name collision, but ...Results so far </div>
<div><div></div><div class="h5">
<br><div class="gmail_quote">On Sat, Jul 9, 2011 at 1:44 PM, Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com" target="_blank">nicolas.ribot@gmail.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div><div></div><div>On 9 July 2011 19:00, Chris English <<a href="mailto:emailchrisenglish@gmail.com" target="_blank">emailchrisenglish@gmail.com</a>> wrote:<br>
> Hi,<br>
> I am trying to iterate over a raster, extract World2RasterCoordX/Y and Band<br>
> Values using the following function:<br>
> Create or replace function extract_as_text(rast raster) returns text as<br>
> $$<br>
> Declare<br>
> i int;<br>
> j int;<br>
> rast raster;<br>
> Begin<br>
> for i in 1..2048 loop --dim x<br>
> for j in 1..1536 loop -- dim y<br>
> select ST_world2rastercoordx(rast, i, j) as xcoord,<br>
> St_world2rastercoordy(rast, i, j) as ycoord,<br>
> ST_value(rast,1, i, j) as R,<br>
> ST_value(rast, 2, i, j) as G,<br>
> ST_value(rast, 3, i, j) as B<br>
> from rbike_all;<br>
> End loop;<br>
> End loop;<br>
> return xcoord,ycoord,R,G,B; <- this part is probably not right either -<br>
> should perhaps just say text<br>
> end;<br>
> $$<br>
> Language 'plpgsql'<br>
> Query returned successfully with no result in 15 ms.<br>
> Select extract_as_text(rast)<br>
> from rbike_all;<br>
> ERROR: column reference "rast" is ambiguous<br>
> LINE 1: select ST_world2rastercoordx(rast, i, j) as xcoord,<br>
> ^<br>
> DETAIL: It could refer to either a PL/pgSQL variable or a table column.<br>
> QUERY: select ST_world2rastercoordx(rast, i, j) as xcoord,<br>
> St_world2rastercoordy(rast, i, j) as ycoord,<br>
> ST_value(rast,1, i, j) as R,<br>
> ST_value(rast, 2, i, j) as G,<br>
> ST_value(rast, 3, i, j) as B<br>
> from rbike_all<br>
> CONTEXT: PL/pgSQL function "extract_as_text" line 9 at SQL statement<br>
><br>
> ********** Error **********<br>
> ERROR: column reference "rast" is ambiguous<br>
> SQL state: 42702<br>
> Detail: It could refer to either a PL/pgSQL variable or a table column.<br>
> Context: PL/pgSQL function "extract_as_text" line 9 at SQL statement<br>
> Removing rast from Declare above results in the same error.<br>
> Any pointers greatly appreciated.<br>
> Chris<br>
><br>
</div></div>Hi Chris:<br>
<br>
Both the input parameter and the declared parameter are called "rast".<br>
This causes the error I think.<br>
Rename the input parameter to something like p_rast and the declared<br>
parameter to v_param.<br>
<br>
That should fix the problem.<br>
<br>
Nicolas<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net" target="_blank">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
</blockquote></div><br><br clear="all"><br></div></div><div><div></div><div class="h5">-- <br>He doesn't fully understand what he thinks he knows about the problem.<br>
</div></div></blockquote></div><br><br clear="all"><br>-- <br>He doesn't fully understand what he thinks he knows about the problem.<br>
</div>