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

[Markus Metz]

On Wed, Aug 11, 2010 at 2:53 PM, Markus Metz
<markus.metz.giswork at googlemail.com> wrote:
> 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

Estimate for grass 6: >6 hours
>
> 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
>>
>