<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>
<META name=GENERATOR content="MSHTML 8.00.7600.16700"></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial>Etienne,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial>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 class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial><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 class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial><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 class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial><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 class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial><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 class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial><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 class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial>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 class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial>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 class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial>Hope that helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial><A
href="http://www.postgis.us">http://www.postgis.us</A></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=230584920-17012011><FONT color=#0000ff
size=2 face=Arial> </DIV></FONT></SPAN><BR>
<DIV dir=ltr lang=en-us class=OutlookMessageHeader align=left>
<HR tabIndex=-1>
<FONT size=2 face=Tahoma><B>From:</B>
postgis-devel-bounces@postgis.refractions.net
[mailto:postgis-devel-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Etienne Bellemare<BR><B>Sent:</B> Monday, January 17, 2011 3:38
PM<BR><B>To:</B> postgis-devel@postgis.refractions.net<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/">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">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">postgis-devel at postgis.refractions.net</A><BR>
</I>><I> <A href="http://postgis.refractions.net/mailman/listinfo/postgis-devel">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">jorge.arevalo at deimos-space.com</A><BR><A href="http://mobility.grupodeimos.com/">http://mobility.grupodeimos.com/</A><BR>
<A href="http://gis4free.wordpress.com/">http://gis4free.wordpress.com</A><BR><A href="http://geohash.org/ezjqgrgzz0g">http://geohash.org/ezjqgrgzz0g</A><BR></PRE><BR></BODY></HTML>