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

[Moritz Lennert]

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