Actually it is working (when it doesn't run out of memory). And it is described in <a href="http://www.postgresql.org/docs/8.4/static/sql-update.html">update</a>'s doc. Do you think it could lead to errors or inconsistent results ? I'm going to try it, just to see.<br>
<br>I'm not sure it is using the raster's index however (it looks like it's not) ? But I confirm this raster table is indexed.<br>Here's the explain :<br>Nested Loop (cost=0.00..18997537.77 rows=543180 width=172)<br>
Join Filter: (_st_intersects(<table>.the_geom, rs.rast, 1, true) AND (st_value(rs.rast, 1, <table>.the_geom) > 0::double precision))<br> -> Seq Scan on <raster> rs (cost=0.00..15918.76 rows=763676 width=32)<br>
-> Index Scan using <table>_the_geom_idx on <table> (cost=0.00..21.57 rows=6 width=140)<br> Index Cond: (<table>.the_geom && (rs.rast)::geometry)<br><br>Etienne<br><br><div class="gmail_quote">
On Mon, Jan 17, 2011 at 4:03 PM, Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us">lr@pcorp.us</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
<div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">Actually I don't think aliases are allowed in PostgreSQL for
the updating table (sorry have my sql server and postgresql syntaxes all
confused)</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">So should be </font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><font color="#000000"><span style="font-family: courier new,monospace;">UPDATE
<table> </span></font></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><font color="#000000"><span style="font-family: courier new,monospace;"></span></font></font></span><span><font color="#0000ff" face="Arial" size="2"><font color="#000000"><span style="font-family: courier new,monospace;"> SET h = z -
st_value(rs.rast, <table>.the_geom)</span></font></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><font color="#000000"><span style="font-family: courier new,monospace;">FROM <raster> AS
rs</span></font></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><font color="#000000"><span style="font-family: courier new,monospace;">WHERE
ST_Intersects(<table>.the_geom, rs.rast) AND
</span></font></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><font color="#000000"><span style="font-family: courier new,monospace;"></span> <font face="Courier New">st_value(rs.rast, <table>.the_geom) > 0;</font><br style="font-family: courier new,monospace;">
</font></font></span></div></span></div><br>
<div dir="ltr" lang="en-us" align="left">
<hr>
<font face="Tahoma" size="2"><b>From:</b>
<a href="mailto:postgis-devel-bounces@postgis.refractions.net" target="_blank">postgis-devel-bounces@postgis.refractions.net</a>
[mailto:<a href="mailto:postgis-devel-bounces@postgis.refractions.net" target="_blank">postgis-devel-bounces@postgis.refractions.net</a>] <b>On Behalf Of
</b>Paragon Corporation<br><b>Sent:</b> Monday, January 17, 2011 3:55
PM<br><b>To:</b> 'PostGIS Development Discussion'<div><div></div><div class="h5"><br><b>Subject:</b> Re:
[postgis-devel] ST_Value out of memory<br></div></div></font><br></div><div><div></div><div class="h5">
<div></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">Etienne,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">You might want to try this instead as I think it tends to
perform better than your subselect approach.</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><font color="#000000"><span style="font-family: courier new,monospace;">UPDATE <table> AS pt
</span></font></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><font color="#000000"><span style="font-family: courier new,monospace;"> SET h = z -
st_value(rs.rast, pt.the_geom)</span></font></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><font color="#000000"><span style="font-family: courier new,monospace;">FROM <raster> AS
rs</span></font></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><font color="#000000"><span style="font-family: courier new,monospace;">WHERE ST_Intersects(pt.the_geom,
rs.rast) AND </span></font></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><font color="#000000"><span style="font-family: courier new,monospace;"></span> <font face="Courier New">st_value(rs.rast, pt.the_geom) > 0;</font><br style="font-family: courier new,monospace;">
<br></font><i></i></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">I'm not absolutely sure but I think even though my above
example calls ST_Value twice it caches it so it should only execute
once.</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">also it goes without saying to make sure you have spatial
indexes on both your geometry and raster tables.</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">Hope that helps,</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2">Regina</font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><a href="http://www.postgis.us" target="_blank">http://www.postgis.us</a></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"></font></span> </div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"> </font></span></div><br>
<div dir="ltr" lang="en-us" align="left">
<hr>
<font face="Tahoma" size="2"><b>From:</b>
<a href="mailto:postgis-devel-bounces@postgis.refractions.net" target="_blank">postgis-devel-bounces@postgis.refractions.net</a>
[mailto:<a href="mailto:postgis-devel-bounces@postgis.refractions.net" target="_blank">postgis-devel-bounces@postgis.refractions.net</a>] <b>On Behalf Of
</b>Etienne Bellemare<br><b>Sent:</b> Monday, January 17, 2011 3:38
PM<br><b>To:</b> <a href="mailto:postgis-devel@postgis.refractions.net" target="_blank">postgis-devel@postgis.refractions.net</a><br><b>Subject:</b> Re:
[postgis-devel] ST_Value out of memory<br></font><br></div>
<div></div>Hi Jorge,<br><br><a href="http://ubuntuone.com/p/Yng/" target="_blank">Here's a dump
of a problematic point table. </a><br>As a complement, I'd like to post the
update query I'm using to fetch the values.<pre><font size="2"><span style="font-family: courier new,monospace;">UPDATE <table> AS pt </span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;">SET h = z - (SELECT value FROM</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> (SELECT st_value(rs.rast, pt.the_geom) AS value</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> FROM <raster> AS rs</span><br style="font-family: courier new,monospace;"><span style="font-family: courier new,monospace;"> WHERE st_intersects(pt.the_geom, rs.rast) ) AS foo</span><br style="font-family: courier new,monospace;">
<span style="font-family: courier new,monospace;"> WHERE value > 0); -- last line to avoid getting nodata values (-9999)</span></font><i><br></i><i></i></pre>Etienne<br><pre>On Mon, Jan 17, 2011 at 7:50 PM, Etienne Bellemare <<a href="http://postgis.refractions.net/mailman/listinfo/postgis-devel" target="_blank">etiennebr at gmail.com</a>> wrote:<br>
><i> Hi all,<br></i>><i><br></i>><i> I have points tables containing 1-2M (lidar) points and a raster containing<br>
</i>><i> 750k tiles of 100x100 pixels. So I'd like to get the value of the raster for<br></i>><i> each point coordinate. But I run out of memory. I was quite surprised to see<br></i>><i> it was actually possible to run out of memory with PostgeSQL as I thought it<br>
</i>><i> would switch to disk when the 3Gb RAM memory would fill (I've seen the<br></i>><i> process go as high as 1,7 Gb according to the task manager). But indeed it<br></i>><i> seems it's possible to run out of memory. The disk containing the db still<br>
</i>><i> have 900 Gb of free space. The solutions I've seen so far on the web look<br></i>><i> more like production database solutions. Mine is actually for research<br></i>><i> purpose : I'm the only one making transactions on it so I don't have many<br>
</i>><i> queries running at the same time. Furthermore, I'm not a db ninja, so<br></i>><i> messing around with all these settings... I wasn't sure to say the least.<br></i>><i><br></i>><i> I couldn't isolate a single factor to the bug. Of course, point table size<br>
</i>><i> looks like a factor, the biggest table succeding was 467Mb ( I have tables<br></i>><i> of size up to 600Mb). But some 200 Mb tables ran out of memory while other<br></i>><i> went OK. Other factor seems to be the tiles (low number of tiles<br>
</i>><i> intersecting the point table, more likely to not run out of memory.) So, I'm<br></i>><i> calling for some input on either tests to perform to isolate the bug factors<br></i>><i> or to avoid running out of memory.<br>
</i>><i><br></i>><i> Etienne<br></i>><i><br></i>><i> _______________________________________________<br></i>><i> postgis-devel mailing list<br></i>><i> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-devel" target="_blank">postgis-devel at postgis.refractions.net</a><br>
</i>><i> <a href="http://postgis.refractions.net/mailman/listinfo/postgis-devel" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-devel</a><br></i>><i><br></i>><i><br></i><br>Hi Etienne,<br>
<br>Could you please provide a dump of one table causing the error?<br>
<br>Best regards,<br><br>-- <br>Jorge Arévalo<br>Internet & Mobilty Division, DEIMOS<br><a href="http://postgis.refractions.net/mailman/listinfo/postgis-devel" target="_blank">jorge.arevalo at deimos-space.com</a><br>
<a href="http://mobility.grupodeimos.com/" target="_blank">http://mobility.grupodeimos.com/</a><br>
<a href="http://gis4free.wordpress.com/" target="_blank">http://gis4free.wordpress.com</a><br><a href="http://geohash.org/ezjqgrgzz0g" target="_blank">http://geohash.org/ezjqgrgzz0g</a><br></pre><br></div></div></div>
<br>_______________________________________________<br>
postgis-devel mailing list<br>
<a href="mailto:postgis-devel@postgis.refractions.net">postgis-devel@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-devel" target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-devel</a><br>
<br></blockquote></div><br>