<!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=927094521-17012011><FONT color=#0000ff
size=2 face=Arial>It is: <FONT color=#000000 size=3
face="Times New Roman"> Index Cond: (<table>.the_geom
&& (rs.rast)::geometry)</FONT><BR></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=927094521-17012011><FONT color=#0000ff
size=2 face=Arial>I suspect a memcpy is being done for each raster update.
Yes your example will work -- but I've found at least for most of my cases using
the FromList is easier to manage and tends to perform a bit better. It
varies though so worth a try both ways to see which performs
better.</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 4:41
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>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
style="BORDER-LEFT: rgb(204,204,204) 1px solid; MARGIN: 0pt 0pt 0pt 0.8ex; PADDING-LEFT: 1ex"
class=gmail_quote>
<DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>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 size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>So should
be </FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial><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 size=2 face=Arial><FONT
color=#000000><SPAN
style="FONT-FAMILY: courier new,monospace"></SPAN></FONT></FONT></SPAN><SPAN><FONT
color=#0000ff size=2 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 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><FONT color=#0000ff size=2 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 dir=ltr align=left><SPAN><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, <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 size=2 face=Tahoma><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 size=2
face=Arial>Etienne,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><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><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><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><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><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><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><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><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><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><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><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial>Hope that
helps,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial>Regina</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2 face=Arial><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 size=2
face=Arial></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN><FONT color=#0000ff size=2
face=Arial></FONT></SPAN> </DIV><BR>
<DIV dir=ltr lang=en-us align=left>
<HR>
<FONT size=2 face=Tahoma><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></BODY></HTML>