[GRASS-dev] About v.distance, v.what.vect (wrt "count points within...").

[Moritz Lennert]

As a final (at least for today ;-) )follow-up, just for the record:

On 11/08/10 13:42, Moritz Lennert wrote:
> Then testing the idea from the link Markus N added to your bug report:
>
> time v.db.update mygrid col=count value="(SELECT count(*) from mypoints
> WHERE mygrid.cat=mypoints.cat_municip group by cat_municip)"
>
> real 5m28.312s

And to show the magic of database indices:

time echo "create index mypoints_cat_municip on mypoints (cat_municip)" 
| db.execute  && time v.db.update mygrid col=count value="(SELECT 
count(*) from mypoints WHERE mygrid.cat=mypoints.cat_municip group by 
cat_municip)"

real	0m10.113s
user	0m6.320s
sys	0m1.300s

real	0m0.668s
user	0m0.544s
sys	0m0.124s

And, very interestingly, the difference of behaviour of the different db 
backends (previous examples were all with SQLite, the following is with 
PostgreSQL):

time echo "create index mypoints_cat_municip on mypoints_pg 
(cat_municip)" | db.execute  && time v.db.update mygrid_pg col=count 
value="(SELECT count(*) from mypoints_pg WHERE 
mygrid_pg.cat=mypoints_pg.cat_municip group by cat_municip)"

real	0m2.905s
user	0m0.012s
sys	0m0.004s

real	0m7.948s
user	0m0.228s
sys	0m0.128s

So, SQLite takes lot's of time creating the index and then is very fast 
for the update, and the opposite is true for PostgreSQL. Don't know if 
that's anything we can do something about in GRASS...

Moritz