[postgis-users] spatial update SQL

[Ethan Alpert]

You'll need to use the contains function unless you are sure your cells
are rectangular with the edges parallel to their respective axis. The
spatial operator && only usings the bounding box of the the geometry.

-e

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Obe,
Regina DNDMIS
Sent: Tuesday, August 31, 2004 10:18 AM
To: 'PostGIS Users Discussion'
Subject: RE: [postgis-users] spatial update SQL


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
_______________________________________________
postgis-users mailing list postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users