[postgis-users] spatial update SQL

[David Blasby]

> MAX=`psql $DB -q -t -c "select max(gid) from table;"`
> COUNTER=`psql $DB -q -t -c "select min(gid) from table;"`
> 
> while [ $COUNTER -le $MAX ] ; do
>  AVG=`psql $DB -q -t -c "select avg(depth)
>                          from bathy_points b, cells c
>                          where c.gid=$COUNTER
>                            and b.the_geom && c.the_geom;"`
> 
>  psql -q -t -c "update cells
>                 set depth $AVG
>                 where gid = $COUNTER ;"
> 
>  COUNTER=`expr $COUNTER + 1`

Wouldnt it be easier just to do:

update cells set depth =
(
       select avg(depth)
        FROM bathy_points 
                  WHERE bathy_points.the_geom && cells.the_geom
);

Sub-queries are your friend!

dave