[postgis-users] spatial update SQL

[Wood Brent]

--- David Blasby <dblasby at gmail.com> wrote:

> 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)


In my case the cells are all rectangular & adjacent so contains is not
necessary, the && operator uses the bounding box which is the same extent as my
feature. I assume this will speed things up.

What I can't see is how the subquery "knows" which cell to work on. I looked at
this approach & figured that the subquery was not limited to just the single
cell to be updated. Is my understanding wrong & the subquery will only use the
current cell from the parent query automagically? 


Brent Wood