[postgis-devel] ST_Value out of memory

[Etienne Bellemare]

Actually it is working (when it doesn't run out of memory). And it is
described in update<http://www.postgresql.org/docs/8.4/static/sql-update.html>'s
doc. Do you think it could lead to errors or inconsistent results ? I'm
going to try it, just to see.

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.
Here's the explain :
Nested Loop  (cost=0.00..18997537.77 rows=543180 width=172)
  Join Filter: (_st_intersects(<table>.the_geom, rs.rast, 1, true) AND
(st_value(rs.rast, 1, <table>.the_geom) > 0::double precision))
  ->  Seq Scan on <raster> rs  (cost=0.00..15918.76 rows=763676 width=32)
  ->  Index Scan using <table>_the_geom_idx on <table>  (cost=0.00..21.57
rows=6 width=140)
        Index Cond: (<table>.the_geom && (rs.rast)::geometry)

Etienne

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

>  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/20110117/55d34625/attachment.html>