[postgis-users] spatial update SQL

[Obe, Regina DND\MIS]

I think a perl script would be much faster to run as Paul mentioned, but if
you were inclined to do it all in SQL if for nothing but curiosity sake (you
would probably run out of memory), I think the statement would look
something like this

UPDATE cells
SET depth = avg(bathy_points.depth)
FROM bathy_points
WHERE bathy_points.the_geom && cells.the_geom ;

or partition by gid with something like

UPDATE cells
SET depth = avg(bathy_points.depth)
FROM bathy_points
WHERE bathy_points.the_geom && cells.the_geom and cells.gid between 1 and 2;







-----Original Message-----
From: Wood Brent [mailto:pcreso at pcreso.com]
Sent: Monday, August 30, 2004 11:44 PM
To: PostGIS Users Discussion
Subject: [postgis-users] spatial update SQL



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


_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users