[postgis-users] spatial update SQL

[Wood Brent]

Hi,

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