[postgis-devel] ST_Value out of memory

Tue Jan 18 10:32:50 PST 2011

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.
Do you think that the memory problem is coming from st_value() loading all
the intersecting tiles in memory ?

Thanks,
Etienne

On Mon, Jan 17, 2011 at 4:44 PM, Paragon Corporation <lr at pcorp.us> wrote:

>  Etienne,
>
> Other suggestion -- if you still run out of memory -- you may want to do
> limit ranges and loops
>
> UPDATE <table>
>     SET h = z - st_value(rs.rast, <table>.the_geom)
> FROM  <raster> AS rs
> WHERE ST_Intersects(<table>.the_geom, rs.rast) AND
>     st_value(rs.rast, <table>.the_geom) > 0 AND rs.rid BETWEEN 1 and 100;
>
>  UPDATE <table>
>     SET h = z - st_value(rs.rast, <table>.the_geom)
> FROM  <raster> AS rs
> WHERE ST_Intersects(<table>.the_geom, rs.rast) AND
>     st_value(rs.rast, <table>.the_geom) > 0 AND rs.rid BETWEEN 101 and 200
> ;
>
> etc.
>
>
>
>  ------------------------------
> *From:* postgis-devel-bounces at postgis.refractions.net [mailto:
> postgis-devel-bounces at postgis.refractions.net] *On Behalf Of *Paragon
> Corporation
> *Sent:* Monday, January 17, 2011 4:03 PM
>
> *To:* 'PostGIS Development Discussion'
> *Subject:* Re: [postgis-devel] ST_Value out of memory
>
>  Actually I don't think aliases are allowed in PostgreSQL for the updating
> table (sorry have my sql server and postgresql syntaxes all confused)
>
> So should be
>
>  UPDATE <table>
>     SET h = z - st_value(rs.rast, <table>.the_geom)
> FROM  <raster> AS rs
> WHERE ST_Intersects(<table>.the_geom, rs.rast) AND
>     st_value(rs.rast, <table>.the_geom) > 0;
>
>  ------------------------------
> *From:* postgis-devel-bounces at postgis.refractions.net [mailto:
> postgis-devel-bounces at postgis.refractions.net] *On Behalf Of *Paragon
> Corporation
> *Sent:* Monday, January 17, 2011 3:55 PM
> *To:* 'PostGIS Development Discussion'
> *Subject:* Re: [postgis-devel] ST_Value out of memory
>
>  Etienne,
>
> You might want to try this instead as I think it tends to perform better
> than your subselect approach.
>
> UPDATE <table> AS pt
>     SET h = z - st_value(rs.rast, pt.the_geom)
> FROM  <raster> AS rs
> WHERE ST_Intersects(pt.the_geom, rs.rast) AND
>     st_value(rs.rast, pt.the_geom) > 0;
>
> **
> 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.
>
> also it goes without saying to make sure you have spatial indexes on both
> your geometry and raster tables.
>
> Hope that helps,
> Regina
> http://www.postgis.us
>
>
>
>  ------------------------------
> *From:* postgis-devel-bounces at postgis.refractions.net [mailto:
> postgis-devel-bounces at postgis.refractions.net] *On Behalf Of *Etienne
> Bellemare
> *Sent:* Monday, January 17, 2011 3:38 PM
> *To:* postgis-devel at postgis.refractions.net
> *Subject:* Re: [postgis-devel] ST_Value out of memory
>
> 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
>
>
>
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-devel
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20110118/05933d91/attachment.html>