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

Moritz Lennert wrote:

As a final (at least for today :wink: )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

On Wed, Aug 11, 2010 at 2:53 PM, Markus Metz
<markus.metz.giswork@googlemail.com> wrote:

Moritz Lennert wrote:

As a final (at least for today :wink: )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

Nikos Alexandris wrote:
[about v.distance and point-in-polygon]

Hmmm... "dmax=0.0": Is this _my_ problem perhaps? Instead of setting it
directly I was trying to estimate it first with "v.distance -pa" which meands
that I misunderstood the whole process :-/

The manual is updated accordingly for 7 and 6.5 (6.4 is locked for
immediate release), explaining the reason for dmax=0 for PiP.

Markus M

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:

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

Markus M:

> 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

Seeing the various timings I' ve got the impression that I did something
completely wrong.

Estimate for grass 6: >6 hours

Reading this means there was a "problem" after all, right?

> 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

Anyhow, the above timing is perfect! I "couting time right now" in grass64, in
grass70 (both before and after latest v.distance update). Let's see... :slight_smile:

Thank you, Nikos

So here you go using data from spearfish60 [1][2][3]

# in grass64 using v.what.vect (makes no difference, right?)
time v.what.vect --v vector=pareto_ref_points___pareto_ref_100m
qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_100m___ON___classification_V___classification_1_200m
column=gridcell_1 qcolumn=cat

Finding nearest lines...
Finding nearest areas...
26335 categories read from the map
26335 categories exist in the table
26335 categories read from the map exist in the table
26335 records updated
v.distance complete.

real 3m3.687s
user 2m38.910s
sys 0m2.400s

# in grass70 before the latest update
time v.distance --v from=pareto_ref_points___pareto_ref_100m
to=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_100m___ON___classification_V___classification_200m_1
column=gridcell_1 to_column=cat upload=to_attr dmax=0.0

Finding nearest feature...
Finding nearest areas...
26335 categories read from the map
26335 categories exist in the table
26335 categories read from the map exist in the table
26335 records updated
v.distance complete.

real 3m4.270s
user 2m43.140s
sys 0m2.870s

# in grass70 after the latest update ( I only did "make" within the
/vector/v.distance directory - correct?)

Finding nearest feature...
Finding nearest areas...
Update database...
26335 categories read from the map
26335 categories exist in the table
26335 categories read from the map exist in the table
26335 records updated
v.distance complete.

real 3m5.339s
user 2m42.780s
sys 0m3.040s

Hmmm... ? So, if use my 600000+ points maps, you can imagine why I report
timings >20h.

:-?

Nikos
---

[1] map #1
v.info -t pareto_ref_points___pareto_ref_100m

nodes=26335
points=26335
lines=0
boundaries=0
centroids=0
areas=0
islands=0
primitives=26335
map3d=0

[2] map #2
v.info -t pareto_...<Very Long Name>

nodes=26956
points=0
lines=0
boundaries=26955
centroids=6650
areas=6650
islands=1
primitives=33605
map3d=0

[3] The above maps where created by using data from spearfish60 as follows:

# enter spearfish60
grass64 /spearfish60/user1/

#
g.copy rast=landcover.30m,landcover.30m
g.region rast=landcover.30m -pa

# suppose the landcover.30m is the "source" map
# derive a "reference" high resolution dichotomic map (=100m)
r.mapcalc "rangeland.30m = if((landcover.30m == 51 || landcover.30m == 71 ||
landcover.30m == 81 || landcover.30m == 92), 2, null())"
r.grow input=rangeland.30m output=pareto_ref radius=1.1 metric=manhattan

# create the 100m maps (source + reference derived from the source)
g.region rast=landcover.30m res=100 -pa
r.mapcalc "landcover.100m = landcover.30m"
r.mapcalc "pareto_ref_100m = pareto_ref"
g.remove rast=pareto_ref

# create dichotomic rangeland maps supposedly being classification results
g.region rast=landcover.30m -pa
r.mapcalc "coi_1 = if((landcover.30m == 51 || landcover.30m == 71 ||
landcover.30m == 81 ), 2, null())"
r.mapcalc "coi_2 = if(( landcover.30m == 71 || landcover.30m == 92), 2,
null())"

# suppose those maps come from data of low resolution (finally =200m)
g.region rast=landcover.30m res=200 -pa
r.grow input=coi_1 output=pareto_classification_200m_1 radius=1.1
metric=manhattan
r.grow input=coi_2 output=pareto_classification_200m_2 radius=1.1
metric=manhattan
g.mremove rast=coi_[12] -f

# below using the scripts that can be found at ticket
# pareto step 1
pareto_1_vectorise_rasters.py reference_raster=landcover.100m
reference_coi_rasters=pareto_ref_100m
classification_rasters=pareto_classification_200m_1,pareto_classification_200m_2

[...]
   Vectorised classifications maps for next step:
[ 'pareto_ref_points___pareto_ref_100m',
   'pareto_ref_coi_points___pareto_ref_100m',
   'pareto_classification_V___pareto_classification_1_200m',
   'pareto_classification_V___pareto_classification_2_200m']

# pareto step 2 (this also takes time!)
pareto_2_create_lowres_vector_grid.py highres=100 lowres=200 --v

# pareto step 3 (timing v.what.vect here!)
time v.what.vect --v vector=pareto_ref_points___pareto_ref_100m
qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_100m___ON___classification_V___classification_1_200m
column=gridcell_1 qcolumn=cat

All above steps repeated within grass70 before the update (should make no
difference, right?) and only the last script execution (pareto_3) skipped,
instead the v.distance command tested directly.

!!!
a. my script does not work in grass70
b. v.what.vect seems to be broken after latest update?

--> just test "v.distance"
!!!

time v.distance --v from=pareto_ref_points___pareto_ref_100m
to=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_100m___ON___classification_V___classification_200m_1
column=gridcell_1 to_column=cat upload=to_attr dmax=0.0

Nikos A:

> Hmmm... "dmax=0.0": Is this _my_ problem perhaps? Instead of setting it
> directly I was trying to estimate it first with "v.distance -pa" which
> meands that I misunderstood the whole process :-/

Moritz L:

dmax=0.0 means: only those features that are in the same place, i.e. in
the case of from=points and to=areas => only those points which fall
into areas.

All clear now!

>> So, using the combination of v.distance and db.select I cannot reproduce
>> your problem with 600,000 points, but maybe the number and nature of
>> polygons can also play a role...

(I am repeating your commands, will report in separate post)

> That's interesting. Maybe I have done once again something very messy(?).
> I use the 3rd script inside the attached file in ticket # 804 [1].
> Although this (old) script still executes so inefficiently a very large
> number of SQL statements, the problem is still only in v.what.vect (so
> in v.distance) before the SQL calls.

> The script counts several point maps (for example: 404347 points) that
> fall inside boxes (that compose a fishnet which I call cell-grid, for
> example: 1320 vector cells). One run with the above mentioned numbers
> takes more than 10h.

> The specific line(s) in the python script is:

> # carry low resolution grid-cell "CAT"s over to reference vector points
>
> grass.run_command('v.what.vect',\
> flags = '-v',\
> quiet = False,\
> vector = reference_points_map,\
> qvect = lowres_vector_grid,\
> column = gridcell_column,\
> qcolumn = "cat")

> Of course I checked the "problem" with the my data by testing only
> "v.what.vect" commands out and apart of my messy script.
>
> Equally, very slow are the trials I did with spearfish (random data). I
> can pass some of my data (off-list please) or let me find some time
> later or tomorrow to copy-paste from my history the exact commands of my
> test within spearfish60.

I just did a similar test with same points and a grid created by

v.mkgrid grid=35,40

(using same column cat_municip from previous test example)
time v.distance from=mypoints@sqlite to=mygrid upload=cat
column=cat_municip dmax=0.0

real 2m21.205s

Then testing the idea from the link Markus N added to your bug report:

Hmm... if I recall correctly, this is where I "stole" the how-to count points
in polygons in the past (when I was writing my "pareto" scripts).

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

One hypothesis I had was that since v.what.vect uses the upload=to_attr
option, thus making it necessary to query the to_map's attribute table,
this might create significant overhead in database connection, but when
using

time v.distance from=mypoints@sqlite to=mygrid upload=to_attr
column=cat_municip to_column=cat dmax=0.0

I get

real 2m13.741s

so no significant difference...

Well, there is some difference.

And final test with v.what.vect:

time v.what.vect mypoints col=cat_municip qvector=mygrid qcolumn=cat

real 2m9.377s

I'm pretty much at large about what causes your problem...

Me too :-p -- whenever there is free-time you could have a look in my timings
(some already posted, test using your commands is coming...)

> (
> Just a quick look: I did not set dmax=0.0 in my (v.distance) tests. Then
> again, in "v.what.vect" it is set by default to 0.0, right? Isn't this
> default dmax=0.0 passed (by default) to v.distance?
> )

Yes.

Good then.

Nikos

Moritz L:

As a final (at least for today :wink: )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

Moritz L:

As a follow-up:

The counting points in polygons algorithm I prefer at this stage is
(using municipal boundaries and hospitals in the NC data set with an
SQLite backend - DBF won't work):

g.copy hospitals,myhospitals
v.db.addcol myhospitals col="cat_municip int"
v.distance from=myhospitals@sqlite to=boundary_municp@PERMANENT
upload=cat column=cat_municip dmax=0.0
db.select sql="select cat_municip, count(*) from myhospitals group by
cat_municip"

If your hospital attribute table contains number of beds (nbeds), the
you could sum the number of beds as such:

db.select sql="select cat_municip, sum(nbeds) from myhospitals group by
cat_municip"

etc...

Using 6.5 to test a similar case to yours (I assume):

g.region vect=boundary_municp

v.random out=mypoints n=600000

v.db.addtable mypoints col="cat int, cat_municip int" (that's veeeeery
slow, probably because of 600000 update statements to the database in
the v.to.db call...)

It runs here... and takes for-ever (like my problem). Silly question but I
wonder if this has anything to do with my process/data?

Nikos

time v.distance from=mypoints@sqlite to=boundary_municp@PERMANENT
upload=cat column=cat_municip dmax=0.0

real 2m2.119s <= not so bad

db.select sql="select cat_municip, count(*) from mypoints group by
cat_municip"

So, using the combination of v.distance and db.select I cannot reproduce
your problem with 600,000 points, but maybe the number and nature of
polygons can also play a role...

Moritz L:

[...]

> Using 6.5 to test a similar case to yours (I assume):

The test below in grass64 gives...

> g.region vect=boundary_municp
> v.random out=mypoints n=600000
> v.db.addtable mypoints col="cat int, cat_municip int" (that's veeeeery
> slow, probably because of 600000 update statements to the database in
> the v.to.db call...)

Nikos A:

It runs here... and takes for-ever (like my problem). Silly question but I
wonder if this has anything to do with my process/data?

> time v.distance from=mypoints@sqlite to=boundary_municp@PERMANENT
> upload=cat column=cat_municip dmax=0.0

> real 2m2.119s <= not so bad

time v.distance from=mypoints@user1 to=boundary_municp@PERMANENT upload=cat
column=cat_municip dmax=0.0

575595 categories - no nearest feature found
600000 categories read from the map
600000 categories exist in the table
600000 categories read from the map exist in the table
600000 records updated
v.distance complete.

real 1m30.315s <= even faster with grass64 in my machine! :slight_smile:

Hmmm???

Maybe v.db.addtable is the crux? I have to check if and when v.db.addtable is
executed in my pareto scripts. Or is it irrelevant?

> db.select sql="select cat_municip, count(*) from mypoints group by
> cat_municip"
>
> So, using the combination of v.distance and db.select I cannot reproduce
> your problem with 600,000 points, but maybe the number and nature of
> polygons can also play a role...

...or v.db.addtable?

Nikos

Nikos Alexandris wrote:

So here you go using data from spearfish60 [1][2][3]

# in grass70 before the latest update
time v.distance --v from=pareto_ref_points___pareto_ref_100m
to=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_100m___ON___classification_V___classification_200m_1
column=gridcell_1 to_column=cat upload=to_attr dmax=0.0

Finding nearest feature...
Finding nearest areas...
26335 categories read from the map
26335 categories exist in the table
26335 categories read from the map exist in the table
26335 records updated
v.distance complete.

real 3m4.270s
user 2m43.140s
sys 0m2.870s

# in grass70 after the latest update ( I only did "make" within the
/vector/v.distance directory - correct?)

Correct. But there should not be and is no difference in speed because
v.distance is called both times with dmax=0.0.

Finding nearest feature...
Finding nearest areas...
Update database...
26335 categories read from the map
26335 categories exist in the table
26335 categories read from the map exist in the table
26335 records updated
v.distance complete.

real 3m5.339s
user 2m42.780s
sys 0m3.040s

[snip]

b. v.what.vect seems to be broken after latest update?

Bug in v.what.vect (trunk only) fixed in r43057

Markus M

On 12/08/10 06:56, Nikos Alexandris wrote:

So here you go using data from spearfish60 [1][2][3]

# in grass64 using v.what.vect (makes no difference, right?) time
v.what.vect --v vector=pareto_ref_points___pareto_ref_100m
qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_100m___ON___classification_V___classification_1_200m
column=gridcell_1 qcolumn=cat

[...]

real 3m3.687s user 2m38.910s sys 0m2.400s

# in grass70 before the latest update time v.distance --v

[...]

real 3m4.270s user 2m43.140s sys 0m2.870s

# in grass70 after the latest update ( I only did "make" within the
/vector/v.distance directory - correct?)

yes

real 3m5.339s user 2m42.780s sys 0m3.040s

[...]

AFAIU, MarkusM's change only affects dmax>0, so it's normal that you
don't see a difference.

[2] map #2 v.info -t pareto_...<Very Long Name>

nodes=26956 points=0 lines=0 boundaries=26955 centroids=6650
areas=6650 islands=1 primitives=33605 map3d=0

So, you have 6000+ areas times 26000+ points.

Hmmm... ? So, if use my 600000+ points maps, you can imagine why I
report timings>20h.

You mean that it's normal that it takes so long, or that it is not
normal, and that's why we can imagine that you report it ? :wink:

Here's an attempt with a 100x100 grid (=10000 areas) and the 600000
points randomly created:

v.mkgrid mygrid n=100,100 --o
time v.distance from=mypoints@sqlite to=mygrid upload=to_attr
column=cat_municip to_column=cat dmax=0.0

real 2m22.505s

Moritz

On 12/08/10 07:51, Nikos Alexandris wrote:

Moritz L:

v.db.addtable mypoints col="cat int, cat_municip int" (that's veeeeery
slow, probably because of 600000 update statements to the database in
the v.to.db call...)

It runs here... and takes for-ever (like my problem). Silly question but I
wonder if this has anything to do with my process/data?

I think the slow part is reading the entire map in v.to.db (cf read_lines() function in vector/v.to.db/lines.c. Another slow-down might be from the fact that v.db.connect automatically creates an index on the cat column and inserting values into a an indexed table might slow down the procedure, but from very superficial testing, I think the bottleneck is in reading the features. No idea how to speed this up, and no time to look any deeper into it.

For such large tables, you might be better off working manually, i.e.:

- create a table in your db backend with just the cat column:
echo "create table mypoints (cat int)" | db.execute

- export all category values into a text file, making sure that you only have each category value once:

v.category mypoints option=print layer=1 | sort | uniq > cat.csv

- use the bulk import function of your db backend to import these cats into your table (COPY or \copy in PostgreSQL, .import in SQLite):

echo "COPY mypoints FROM '/home/user/cat.csv'" | db.execute

[this will only work in PostgreSQL if your db server is on the same machine as the file cat.csv - if this does not work, or if you use SQLite, you have to use respectively \copy or .import in the command line interfaces to the DB backends]

- connect your map to this table:
v.db.connect mypoints table=mypoint key=cat

- add any other columns you need in the table:
v.db.addcol mypoints col="cat_to int"

Means a bit more typing, but is way faster ! Maybe v.db.addtable could be rewritten to follow this path...

Moritz

Nikos A:

(MM & ML, I really appreciate the time you spend on this)

[...]

AFAIU, MarkusM's change only affects dmax>0, so it's normal that you
don't see a difference.

Yep, it's clear.

> [2] map #2 v.info -t pareto_...<Very Long Name>

> nodes=26956 points=0 lines=0 boundaries=26955 centroids=6650
> areas=6650 islands=1 primitives=33605 map3d=0

So, you have 6000+ areas times 26000+ points.

Well, this is just a "spearfish" small example. Worst case beforehand here is:
areas=2376 _vs_ points=593273 (in a region of rows:1201, cols:550,
cells:660550 -- but this does not make any difference though)

> Hmmm... ? So, if use my 600000+ points maps, you can imagine why I
> report timings>20h.

You mean that it's normal that it takes so long, or that it is not
normal, and that's why we can imagine that you report it ? :wink:

It was a (very) wild guess, smthn like "3m5.339s for 23.000, how much for 600K
thinking that time-consumation does _not_ increase linearly, but more
aggressive).

Here's an attempt with a 100x100 grid (=10000 areas) and the 600000
points randomly created:

v.mkgrid mygrid n=100,100 --o

Can someone confirm that even this takes (a bit) long (or is it only my box)?

time v.distance from=mypoints@sqlite to=mygrid upload=to_attr
column=cat_municip to_column=cat dmax=0.0

real 2m22.505s

GRASS 6.4.0svn (nc_spm_08):~ > real 1m42.860s [ :slight_smile: | :frowning: ]

(shrug)

Don't know what's wrong here!? To make the long story short following my
scripts:

1. pareto_1 converts all pixels of Landsat7 samples to (centroids ->) points.
Yes, res=30m, many pixels (e.g. the 593273 above). Includes only: g.region,
r.to.vect, v.db.dropcol.

2. pareto_2 loops over the point(s) maps and creates "low-resolution" vector
grid (using v.mkgrid, e.g. 2376 boxes that correspond to a raster with
res=500m). Includes only: g.region, v.in.region, v.select, g.remove, v.mkgrid

3. pareto_3 just counts points in polygons. Includes: v.db.addcol, v.what.vect
(this is the time-killer), v.support, v.db.addcol, v.db.update (also
inefficiently executed but not the real problem)

(...the rest of pareto_? is another story...)

I will give another go with the spearfish60 dataset but this time keeping the
high-resolution to 30m and the low resolution to 500m. In detail, all of the
commands that have to be executed are:

--%<---
grass64 /spearfish60/user1/

g.copy rast=landcover.30m,landcover.30m

g.region rast=landcover.30m -pa # cells: 294978

r.mapcalc "rangeland.30m = if((landcover.30m == 51 || landcover.30m == 71 ||
landcover.30m == 81 || landcover.30m == 92), 2, null())"

r.grow input=rangeland.30m output=pareto_ref_30m radius=1.1 metric=manhattan

r.mapcalc "coi_1 = if((landcover.30m == 51 || landcover.30m == 71 ||
landcover.30m == 81 ), 2, null())"

r.mapcalc "coi_2 = if(( landcover.30m == 71 || landcover.30m == 92), 2,
null())"

g.region rast=landcover.30m res=500 -pa

r.grow input=coi_1 output=pareto_classification_500m_1 radius=1.1
metric=manhattan

r.grow input=coi_2 output=pareto_classification_500m_2 radius=1.1
metric=manhattan

g.mremove rast=coi_[12] -f

pareto_1_vectorise_rasters.py reference_raster=landcover.30m
reference_coi_rasters=pareto_ref_30m
classification_rasters=pareto_classification_500m_1,pareto_classification_500m_2

pareto_2_create_lowres_vector_grid.py highres=30 lowres=500 --v

# here would go the "pareto_3" script, running many times v.what.vect
depending on the number of "area-maps" and naturally the "poin-maps". However,
one test-command is enough to demonstrate:

time v.what.vect --v vector=pareto_ref_points___pareto_ref_30m
qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_30m___ON___classification_V___classification_1_500m
column=gridcell_1 qcolumn=cat
-->%---

Now this is crawling and I am waiting [*]. Didn't wanna let you waiting too
long to know that this keeps me waiting - so I've hit the Send button :-p

Nikos
---
[*] You Waited Too Long, Casey Bill Weldon (1936)

On 12/08/10 12:40, Nikos Alexandris wrote:

time v.what.vect --v vector=pareto_ref_points___pareto_ref_30m
qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_30m___ON___classification_V___classification_1_500m
column=gridcell_1 qcolumn=cat
-->%---

Now this is crawling and I am waiting [*]. Didn't wanna let you waiting too
long to know that this keeps me waiting - so I've hit the Send button :-p

Found the culprit !

Actually the problem is in v.db.dropcol which for the SQLite driver also drops all indices (it creates a new table without the dropped column and populates with a select from the old table). This means that there is no index on the cat column which makes v.distance really slow.

I'm not sure about indices in the dbf driver, but would think that there are none...

So, a quick solution for you would be to recreate the index after the v.db.dropcol (all this is happening in your pareto_1 script):

echo "CREATE UNIQUE INDEX any_name_for_index on yourtable ( cat )" | db.execute

(don't know how to do this in a python script...)

Another (dirty) solution is to copy your map after the v.db.dropcol:

g.copy vect=pareto_ref_points___pareto_ref_30m,test

This automatically creates the index in test, and so

time v.what.vect --v vector=test
qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_30m___ON___classification_V___classification_1_500m
column=gridcell_1 qcolumn=cat

should be fast again...

I don't have time right now, but I'll look into adding the index creation into v.db.dropcol, if you confirm that this is the problem.

Moritz

Start from scratch:

I've put a new module in grass-addons, called v.vect.stats. It counts
points in polygons, fairly fast, but, as the name implies, can do a
bit more. Optionally, it calculates statistics for areas based on the
attributes of all points falling into an area. Currently supported
methods are sum, average, median, mode, min, min_cat, max, max_cat,
range, stddev, variance, diversity. The meaning of min_cat and max_cat
is the category value corresponding to the observed minimum/maximum
value, e.g. the hospital with the largest number of beds and not the
largest number of beds.

The module is meant as the companion to v.rast.stats, update area
attributes from raster: v.vect.stats, update area attributes from
vector

Have fun,

Markus M

PS: No entry on wiki because I am not sure what will happen with the
module. If it's regarded nonsense, it will be removed again. OTOH, it
might end up in trunk.

Guys, you are amazing. No other comments!

Nikos A:

> time v.what.vect --v vector=pareto_ref_points___pareto_ref_30m
> qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_30m_
> __ON___classification_V___classification_1_500m column=gridcell_1
> qcolumn=cat
> -->%---

> Now this is crawling and I am waiting [*]. Didn't wanna let you waiting
> too long to know that this keeps me waiting - so I've hit the Send
> button :-p

Moritz Lennert wrote:

Found the culprit !

:smiley:

Actually the problem is in v.db.dropcol which for the SQLite driver also
drops all indices (it creates a new table without the dropped column and
populates with a select from the old table). This means that there is no
index on the cat column which makes v.distance really slow.

I'm not sure about indices in the dbf driver, but would think that there
are none...

So, a quick solution for you would be to recreate the index after the
v.db.dropcol (all this is happening in your pareto_1 script):

echo "CREATE UNIQUE INDEX any_name_for_index on yourtable ( cat )" |
db.execute

A-ha! Here you go... so it's my fault!

(don't know how to do this in a python script...)

No worries, I'll fix that, allthough I'm (still) rather slow with Python (yet,
I will not be forever).

Another (dirty) solution is to copy your map after the v.db.dropcol:

g.copy vect=pareto_ref_points___pareto_ref_30m,test

This automatically creates the index in test, and so

time v.what.vect --v vector=test
qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_30m___
ON___classification_V___classification_1_500m column=gridcell_1 qcolumn=cat

should be fast again...

I don't have time right now, but I'll look into adding the index
creation into v.db.dropcol, if you confirm that this is the problem.

I am breaking the never-ending process... who cares what "time ..." will
return. It is still running!!

Nikos

Markus M:

Start from scratch:

I've put a new module in grass-addons, called v.vect.stats. It counts
points in polygons, fairly fast, but, as the name implies, can do a
bit more. Optionally, it calculates statistics for areas based on the
attributes of all points falling into an area. Currently supported
methods are sum, average, median, mode, min, min_cat, max, max_cat,
range, stddev, variance, diversity. The meaning of min_cat and max_cat
is the category value corresponding to the observed minimum/maximum
value, e.g. the hospital with the largest number of beds and not the
largest number of beds.

The module is meant as the companion to v.rast.stats, update area
attributes from raster: v.vect.stats, update area attributes from
vector

Have fun,

Markus M

PS: No entry on wiki because I am not sure what will happen with the
module. If it's regarded nonsense, it will be removed again. OTOH, it
might end up in trunk.

Crazy! I'll take my time then and try to report to both of you ASAP (=within
the next days I guess).

For the records

I am breaking the never-ending process... who cares what "time ..." will
return. It is still running!!

time v.what.vect --v vector=pareto_ref_points___pareto_ref_30m
qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_30m___ON___classification_V___classification_500m_1
column=gridcell_1 qcolumn=cat
Finding nearest lines...
Finding nearest areas...
^C
                                                                                                                   
real 317m9.500s
user 0m12.230s
sys 0m10.120s

Nikos A:

For the records

> I am breaking the never-ending process... who cares what "time ..." will
> return. It is still running!!

time v.what.vect --v vector=pareto_ref_points___pareto_ref_30m
qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_30m___
ON___classification_V___classification_500m_1 column=gridcell_1 qcolumn=cat
Finding nearest lines...
Finding nearest areas...
^C

real 317m9.500s
user 0m12.230s
sys 0m10.120s

Skipping the v.db.dropcol step I get:

time v.what.vect --v vector=pareto_ref_points___pareto_ref_30m
qvect=pareto_grid___OVER___ref_coi_points___pareto_grid___OVER___ref_30m___ON___classification_V___classification_500m_1
column=gridcell_1 qcolumn=cat
Finding nearest lines...
Finding nearest areas...
292317 categories read from the map
292317 categories exist in the table
292317 categories read from the map exist in the table
292317 records updated
v.distance complete.

real 0m47.338s <~ !^1000
user 0m23.100s
sys 0m16.510s

I am about to jump in to the lake [1] thinking how many nights I've spend on
this. No comments please. In the end of the day(s, weeks, months) reaching a
solution was my wish so I should be happy. I just need some time to accept
it... :-? Maybe file a ticket for it as well?

Moritz, MarkusM, thanks once again :slight_smile:
(will try within the next days the v.vect.stats).

[1] 7.814375, 48.012126

On 12/08/10 16:50, Moritz Lennert wrote:

I don't have time right now, but I'll look into adding the index
creation into v.db.dropcol, if you confirm that this is the problem.

Committed to grass6_devbranch as rev 43068.

Candidate for 6.4.1.

For grass7, I think the change should be:

--- v.db.dropcolumn.py 2010-08-12 18:48:03.000000000 +0200
+++ /home/mlennert/v.db.dropcolumn.py 2010-08-12 18:47:29.000000000 +0200
@@ -102,6 +102,7 @@
             "DROP TABLE ${table}",
             "CREATE TABLE ${table}(${coldef})",
             "INSERT INTO ${table} SELECT ${colnames} FROM ${table}_backup",
+ "CREATE UNIQUE INDEX ${table}_cat ON ${table} ( ${keycol} )"
             "DROP TABLE ${table}_backup",
             "COMMIT"
             ]

But I'll have to update my svn tree first and see if it works, before committing anything. If anyone wants to go ahead, please feel free.

Moritz