[GRASS-user] How to copy vector columns between databases

[Moritz Lennert]

On 26/11/08 18:09, Markus Neteler wrote:
> On Tue, Nov 25, 2008 at 4:29 PM,  <peter.loewe at gmx.de> wrote:
>> Hi,
>>
>> I have a vector layer FOO which is linked to two tables in layers 1 and 2.
>> The categories for each vector element are different in layer 1 and (e.g. a certain area may have the cat value "51" in layer 1 and a cat value of "42" in layer 2).
>> Let's assume that layer one has a VARCHAR column containing the names of the region (e.g. database_layer_1: 51,"Wolfenstein" database_layer_2: 42 )
>>
>> If a new VARCHAR column is added to layer 2 by v.db.adcol,
>> how can the the names from layer 1 be copied into it?
>>
>> [Goal: database_layer_1: 51,"Wolfenstein" database_layer_2: 42,"Wolfenstein" ]
>>
>> Unfortunately, v.db.update seems only to work within one layer.
>>
>> An UPDATE/SELECT SQL-statement will not be possible unless a table can be produced which holds the categories for both database layers for each geometry element.
>>
>> How can this be solved ?
> 
> g.copy vect=roads,myroads --o
> v.info -c myroads
> v.db.addtable myroads table=extratab layer=2 columns="cat
> integer,label varchar(100)"
> v.db.select myroads layer=2
> cat|label
> 
> -> Uffa: this should be set!! Bug in v.to.db :(
> ... ok, I have made some fixes in SVN (r34494 and r34495) but they don't
> seem to be sufficient. See related mail on grass-dev.

IIUC, the result is actually correct since myroads does not contain any 
categories in layer 2 (see my response on grass-dev).

But you got us on the right track: there actually is a way to copy cats 
of one layer to another layer (e.g. here from layer 1 to layer 2):

v.db.addcol FOO layer=2 col='cat1 int'
v.to.db FOO layer=2 col=cat1 option=query qlayer=1 qcol=cat

Only if any of the two layers do not have a table attached do you need 
the v.db.addtable above, and to make that work you actually need 
categories in both layers (which isn't the case in Markus' example)...

> 
> Now you need a join operation (db.execute). Note that this isn't
> supported with DBF driver:
> 
> # I use SQLIte here:
> v.db.connect -p myroads
> Vector map <myroads at sqlite> is connected by:
> layer <1> table <myroads> in database
> </home/neteler/grassdata/spearfish60/sqlite/sqlite.db> through driver
> <sqlite> with key <cat>
> layer <2> table <extratab> in database
> </home/neteler/grassdata/spearfish60/sqlite/sqlite.db> through driver
> <sqlite> with key <cat>
> 
> # bug workaround:
> db.droptable extratab -f
> db.copy from_table=myroads to_table=extratab
> v.db.select myroads layer=2
> cat|label
> 0|no data
> 1|interstate
> 2|primary highway, hard surface
> 3|secondary highway, hard surface
> 4|light-duty road, improved surface
> 5|unimproved road

This "works" because v.db.select selects all tuples in the table 
irrespective of whether these tuples have any corresponding features in 
the layer.

> 
> # simulate almost empty table (note that I fixed v.db.dropcol right
> now for layer=2):
> v.db.dropcol myroads layer=2 column=label
> v.info -c myroads layer=2
> 
> # add new column with desired name:
> v.db.addcol myroads layer=2 col="names varchar(100)"
> v.info -c myroads layer=2
> Displaying column types/names for database connection of layer 2:
> INTEGER|cat
> CHARACTER|names
> 
> v.db.select myroads layer=2
> cat|names
> 0|
> 1|
> 2|
> 3|
> 4|
> 5|
> 
> # now JOIN the names into:
> echo "UPDATE extratab SET names=(SELECT label FROM myroads WHERE
> extratab.cat=myroads.cat);" | db.execute
> 
> # voilà:
> v.db.select myroads layer=2
> cat|names
> 0|no data
> 1|interstate
> 2|primary highway, hard surface
> 3|secondary highway, hard surface
> 4|light-duty road, improved surface
> 5|unimproved road

Try clicking on any of the roads : do you actually see these results in 
layer 2 ?

> TODO: fix v.to.db for layer>1.

It works AFAIU, you just need to use the query option.

Moritz