[postgis-users] spatial update SQL

[David Blasby]

Ethan,

update celltable set celltable.value = 
(
SELECT avg( point.value ) from point where celltable.the_geom &&
point.the_geom and contains(celltable.the_geom,point.the_geom)
) ;

This query is going to do the same thing as your program.  Do an
"explain" and you'll see the query plan is very similiar to what your
program does (i.e. does a sequential scan of the cell table, executing
the sub-query for each row).

(If there's no points inside a cell, avg() will return NULL)

I do this kind of processing a lot - I hate having to write external
programs when a single SQL statement will suffice.

dave


On Tue, 31 Aug 2004 10:13:03 -0600, Ethan Alpert
<ealpert at digitalglobe.com> wrote:
> 
> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>