[postgis-devel] ST_Value out of memory

[Paragon Corporation]

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  <http://ubuntuone.com/p/Yng/> of a problematic point table. 
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://gis4free.wordpress.com/> 
http://geohash.org/ezjqgrgzz0g

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-devel/attachments/20110117/650db0c9/attachment.html>