[postgis-users] spatial update SQL

[Wood Brent]

--- Paul Ramsey <pramsey at refractions.net> wrote:

> This is going to sound "anti-database", but:

Not at all, anti SQL perhaps, but the DB does what is needed just fine. SQL
alone is not always the answer :-)

> 
> write a perl script that does

I plan to learn perl sometime this year, but I figure a shell script will do
about the same thing, if slower.

> 
> foreach cell
>    SQL to get average value
>    SQL to update
> 
> As long as your 20M record table is properly indexed, so that the 
> average value number comes back really fast, and you make good use of 
> prepared statements and statements handles, this can go extremely quickly.

Um, those sound like some of the perl things I need to learn :-)

> 
> I used this technique to collapse a 50M record table to a 3M record 
> table, doing a fair amount of analysis in each iteration (a little time 
> series analysis for each of the 3M new records). It took a shade under 
> an hour.
> 

So a simple shell script along the lines of the following should be reasonable?
(given that the geometry attrs and gid are indexed & the tables vacuum
analysed)

Any optimisations you can suggest? Speed is not a huge issue, as long as it's
not into days :-)

Thanks,

  Brent


#! ...

DB=<database>

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`
done