v.distance -a with upload=to_attr and table= option: problems in table creation and value upload

[mlennert]

When trying to create a distance matrix and upload that to a table, I've stumbled upon what seems to be a very old bug in v.distance:

1) The table cannot be created because of a missing TO_ATTR: case in the switch statement in vector/v.distance/main.c, line 790.

2) Even when such a TO_ATTR: case is added, the column in the new table is filled with NULL values. I have not been able, yet, to figure out where this problem comes from.

[mlennert]

Replying to mlennert:

When trying to create a distance matrix and upload that to a table, I've stumbled upon what seems to be a very old bug in v.distance: 1) The table cannot be created because of a missing TO_ATTR: case in the switch statement in vector/v.distance/main.c, line 790. 2) Even when such a TO_ATTR: case is added, the column in the new table is filled with NULL values. I have not been able, yet, to figure out where this problem comes from.

How to reproduce bug in NC demo data set:

g.copy vect=hospitals@PERMANENT,myhosp
v.distance from=myhosp to=myhosp upload=cat,dist,to_attr to_column=CANCER column=to_cat,dist,to_cancer table=distance_cancer

The attached patch solves 1) & 2) for dbf and postgresql backends, but not 2) for SQLite. When using an SQLite backend, the trouble seems to come from the fact that the same database and table are accessed twice. Maybe some playing around with db_close_database_shutdown_driver might help, but I haven't had the opportunity to play around with that. Anybody have any hints about this ?

In any case I find the attached patch useful for the backends where it works. It's against devel6, but should be straightforward to port to trunk.

Moritz

[mlennert]

patch allowing upload of to_attr data to newly created table

[mlennert]

Replying to mlennert:

When using an SQLite backend, the trouble seems to come from the fact that the same database and table are accessed twice. Maybe some playing around with db_close_database_shutdown_driver might help, but I haven't had the opportunity to play around with that. Anybody have any hints about this ?

I can now confirm that this is a database locking issue. When doing

g.copy vect=myhosp,myhosp2
cp $GISDBASE/$MAPSET/sqlite.db ~/
v.db.connect -o myhosp2 driver=sqlite database=~/sqlite.db key=cat table=myhosp2
v.distance from=myhosp to=myhosp2 upload=cat,dist,to_attr to_column=CANCER column=to_cat,dist,to_cancer table=distance_cancer

it works, i.e distance_cancer contains values in the to_cancer column.

I would imagine that this kind of sqlite database locking will be a problem in other modules as well, so this probably needs a wider solution than just within this module. Anyone out there with enough experience with SQLite to attack this ? This is very important if SQLite is to become the default db backend...

Moritz