[postgis-devel] ST_Value out of memory

Mon Jan 17 13:53:21 PST 2011

Ah never mind what I said I see what you are saying about docs didn't
realize the alias feature was supported in PostgreSQL.  Learn something
every day.  Guess it doesn't hurt to be confused -- you may come up with
some new way of doing things buy copying from what you do in another system
:)

  _____  

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:49 PM
To: 'PostGIS Development Discussion'
Subject: Re: [postgis-devel] ST_Value out of memory

It is:      Index Cond: (<table>.the_geom && (rs.rast)::geometry)

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.

  _____  

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 4:41 PM
To: PostGIS Development Discussion
Subject: Re: [postgis-devel] ST_Value out of memory

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://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/9e64e164/attachment.html>