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

[Nikos Alexandris]

Moritz L:

> As a final (at least for today ;-) )follow-up, just for the record:
 
On 11/08/10 13:42, Moritz L:

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

so, this is an "extra" step? i.e. not done by grass' modules.
 
> 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...

This is very interesting, indeed.
Nikos