[postgis-users] spatial update SQL

[Paul Ramsey]

This is going to sound "anti-database", but:

write a perl script that does

foreach cell
   SQL to get average value
   SQL to update

As long as your 20M record table is properly indexed, so that the 
average value number comes back really fast, and you make good use of 
prepared statements and statements handles, this can go extremely quickly.

I used this technique to collapse a 50M record table to a 3M record 
table, doing a fair amount of analysis in each iteration (a little time 
series analysis for each of the 3M new records). It took a shade under 
an hour.

Trying to do it in one SQL statement might prove counter-productive.

Paul

Wood Brent wrote:
> 
> Hopefully someone can help with a SQL how-to.
> 
> 
> I have two tables with geometries. One has 150,000 square cells as polygons as
> well as an empty depth attribute. The other table has 20,000,000 point depths,
> derived from a global topography dataset.
> 
> I want to update the depth attribute for each cell record with the average
> value of all the depth points lying within each cell.
> 
> I'm sure there should be a way to do this in a single pass, but I can't see it.
> 
> I can extract the cell.gid & avg(point.depth) to a text file or another table
> with a simple sql, then use this to run the update, but this seems unecessary
> overhead.
> 
> Given the numbers of records in each table, I'm also interested in any ways to
> improve the performance of the query, apart from adding more memory :-)
> 
> 
> Any suggestions appreciated.
> 
>  Brent Wood
> 
> 
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users