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

[Markus Metz]

Moritz Lennert wrote:
> 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
>
Thanks a lot! This index was missing here on my sqlite tables. To
throw in another timing with 600 000 random points:

time v.distance from=randpoints_600K from_layer=1 to=boundary_municp
to_layer=1 to_type=area upload=cat column=to_cat
(no dmax, a nearest area was found for each point)

real	3m26.308s
user	1m49.736s
sys	1m35.067s

same with dmax=0.0
558294 categories - no nearest feature found <-- expected

real	1m24.819s
user	0m42.511s
sys	0m41.384s

tuned v.distance in trunk r43042

Markus M


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