[postgis-users] spatial update SQL

[Ethan Alpert]

Presumably there is some unique column (primary key) in the cell, I'll
use 'id' in my example. I'm also assuming you have created a spatial
index for both your tables. I'm sure there's a way to do all you want in
one query using self joins but I can't think of how right now. Here's my
perl example 9 lines!


#!/usr/bin/perl 
use DBI;
my
$dbh=DBI->connect("DBI:Pg:dbname=<yourdb>;host="<yourhost>","<youruser>"
,"<yourpasswd>") or die "Could not connect to <yourdb>\n";
my @row = $dbh->selectcol_arrayref("select id from celltable");
my $statement = "update celltable set celltable.value = avg(select
point.value from point where celltable.the_geom && point.the_geom and
contains(celltable.the_geom,point.the_geom)) where celltable.id=?";
my $sth = $dbh->prepare($statment);
foreach my $id (@row) {
    $sth->execute($id);
}





-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Wood
Brent
Sent: Monday, August 30, 2004 9: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