[postgis-users] spatial update SQL

[David Blasby]

> > 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 goes through the celltable, row by row.  It updates the
celltable.value by executing the subquery, filling in
"celltable.the_geom" with the value FROM THE CURRENT ROW.

So, if celltable looks like this:

     the_geom
---------------------------
  <rectangle 1>
  <rectangle 2>

As above query goes through the table, and will execute these two sub-queries:

SELECT avg( point.value ) from point where <rectangle 1> &&
point.the_geom and contains(<rectangle 1>,point.the_geom);

SELECT avg( point.value ) from point where <rectangle 2> &&
point.the_geom and contains(<rectangle 2>,point.the_geom);

Its all magic!

dave