[postgis-users] spatial update SQL

[Paul Ramsey]

Try this as it stands and see how fast it runs. You'll be spawning psql 
and reconnecting a lot, which will cost you some speed. This is where 
using perl/python/php and real connection handles would save you some 
time. Also if you do you your update inside a transaction and batch your 
commits every 100-1000 statements, you will speed things up a bit 
(although it is possible you could get a deadlock, selecting from the 
table while you are updating it, since the spatial index locks are table 
locks, so maybe one at a time is better, best to try and see). Oh, on 
the perl/python/php front, I found that opening two connections, one for 
reading and one for writing, was a nice performance booster, rather than 
running all my statements on the same connection.

Wood Brent wrote:

> 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
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users