It is a good solution. I modified it to only query the rasters intersecting the extent of my points, but bottom line, by reducing the number of rasters, it works. And it's not taking longer.<br>Do you think that the memory problem is coming from st_value() loading all the intersecting tiles in memory ?<br>
<br>Thanks,<br>Etienne<br><br><br><div class="gmail_quote">On Mon, Jan 17, 2011 at 4:44 PM, Paragon Corporation <span dir="ltr"><<a href="mailto:lr@pcorp.us" target="_blank">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">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">Other suggestion -- if you still run out of memory -- you may
want to do limit ranges and loops</font></span><span><font color="#0000ff" face="Arial" size="2"></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial" size="2"><span><div><font color="#000000" face="Times New Roman" size="3"> </font>
<div dir="ltr" align="left"><span><font color="#000000" face="Arial"><span style="font-family: courier new,monospace;">UPDATE
<table> </span></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"><font color="#000000"><span style="font-family: courier new,monospace;"></span></font></font></span><span><font color="#0000ff" face="Arial"><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"><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"><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><div dir="ltr" align="left"><span><font face="Arial"><span style="font-family: courier new,monospace;"></span><font color="#000000"> <font face="Courier New">st_value(rs.rast,
<table>.the_geom) > 0<span> AND rs.rid BETWEEN
1 and 100</span>;</font></font></font></span></div><span><font face="Arial"><font color="#000000"><font face="Courier New"></font></font></font></span></span></font></span></div>
<div><span><font color="#0000ff" face="Arial" size="2"><span><span><font face="Arial"><font color="#000000"></font></font></span></span></font></span> </div>
<div><span><font color="#0000ff" face="Arial"><span><span><font face="Arial"><font color="#000000"><font face="Courier New"><div>
<div dir="ltr" align="left"><span><font color="#000000" face="Arial" size="2"><span style="font-family: courier new,monospace;">UPDATE
<table> </span></font></span></div>
<div dir="ltr" align="left"><span><font color="#0000ff" face="Arial"><font color="#000000"><span style="font-family: courier new,monospace;"></span></font></font></span><span><font color="#0000ff" face="Arial"><font color="#000000" size="2"><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"><font color="#000000" size="2"><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"><font color="#000000" size="2"><span style="font-family: courier new,monospace;">WHERE
ST_Intersects(<table>.the_geom, rs.rast) AND
</span></font></font></span></div>
</div><div dir="ltr" align="left"><span><font face="Arial"><span style="font-family: courier new,monospace;"></span><font color="#000000"><font size="2"> <font face="Courier New">st_value(rs.rast,
<table>.the_geom) > 0<span> AND rs.rid BETWEEN
101 and 200</span>;</font></font></font></font></span></div>
<div dir="ltr" align="left"><span><font size="2"></font></span> </div>
<div dir="ltr" align="left"><span><span><font color="#0000ff" face="Arial" size="2">etc.
</font></span></span></div>
<div dir="ltr" align="left"><span><span><font color="#0000ff" face="Arial" size="2"></font></span></span> </div></font></font></font></span></span></font></span></div>
<div dir="ltr" align="left"><font size="2"><br style="font-family: courier new,monospace;"></font></div><br>
<div dir="ltr" lang="en-us" align="left">
<hr>
<font face="Tahoma" size="2"><div><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></div><b>Sent:</b> Monday, January 17, 2011 4:03
PM<div><div></div><div><br><b>To:</b> 'PostGIS Development Discussion'<br><b>Subject:</b> Re:
[postgis-devel] ST_Value out of memory<br></div></div></font><br></div><div><div></div><div>
<div></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'<br><b>Subject:</b> Re:
[postgis-devel] ST_Value out of memory<br></font><br></div>
<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" target="_blank">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>