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

Markus Neteler neteler at osgeo.org
Wed Nov 26 12:09:57 EST 2008


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.

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

# 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

Nice exercise :)
TODO: fix v.to.db for layer>1.

Cheers
Markus


More information about the grass-user mailing list