[postgis-devel] ST_Value out of memory

[Etienne Bellemare]

Hi Jorge,

Here's a dump of a problematic point table. <http://ubuntuone.com/p/Yng/>
As a complement, I'd like to post the update query I'm using to fetch the
values.

UPDATE <table> AS pt
SET h = z - (SELECT value FROM
		(SELECT st_value(rs.rast, pt.the_geom) AS value
		 FROM <raster> AS rs
		 WHERE st_intersects(pt.the_geom, rs.rast) ) AS foo
	     WHERE value > 0); -- last line to avoid getting nodata values (-9999)*
***

Etienne

On Mon, Jan 17, 2011 at 7:50 PM, Etienne Bellemare <etiennebr at
gmail.com <http://postgis.refractions.net/mailman/listinfo/postgis-devel>>
wrote:
>* Hi all,
*>*
*>* I have points tables containing 1-2M (lidar) points and a raster containing
*>* 750k tiles of 100x100 pixels. So I'd like to get the value of the raster for
*>* each point coordinate. But I run out of memory. I was quite surprised to see
*>* it was actually possible to run out of memory with PostgeSQL as I thought it
*>* would switch to disk when the 3Gb RAM memory would fill (I've seen the
*>* process go as high as 1,7 Gb according to the task manager). But indeed it
*>* seems it's possible to run out of memory. The disk containing the db still
*>* have 900 Gb of free space.  The solutions I've seen so far on the web look
*>* more like production database solutions. Mine is actually for research
*>* purpose : I'm the only one making transactions on it so I don't have many
*>* queries running at the same time. Furthermore, I'm not a db ninja, so
*>* messing around with all these settings... I wasn't sure to say the least.
*>*
*>* I couldn't isolate a single factor to the bug. Of course, point table size
*>* looks like a factor, the biggest table succeding was 467Mb ( I have tables
*>* of size up to 600Mb). But some 200 Mb tables ran out of memory while other
*>* went OK. Other factor seems to be the tiles (low number of tiles
*>* intersecting the point table, more likely to not run out of memory.) So, I'm
*>* calling for some input on either tests to perform to isolate the bug factors
*>* or to avoid running out of memory.
*>*
*>* Etienne
*>*
*>* _______________________________________________
*>* postgis-devel mailing list
*>* postgis-devel at postgis.refractions.net
<http://postgis.refractions.net/mailman/listinfo/postgis-devel>
*>* http://postgis.refractions.net/mailman/listinfo/postgis-devel
*>*
*>*
*
Hi Etienne,

Could you please provide a dump of one table causing the error?

Best regards,

-- 
Jorge Arévalo
Internet & Mobilty Division, DEIMOS
jorge.arevalo at deimos-space.com
<http://postgis.refractions.net/mailman/listinfo/postgis-devel>
http://mobility.grupodeimos.com/
http://gis4free.wordpress.com
http://geohash.org/ezjqgrgzz0g
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20110117/203d3cdd/attachment.html>