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

GRASS GRASS user
1

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 ?

Regards,
Peter

--
Dr. Peter Löwe
<peter.loewe@gmx.de>

Sensationsangebot nur bis 30.11: GMX FreeDSL - Telefonanschluss + DSL
für nur 16,37 Euro/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a

2

On 25/11/08 16:29, peter.loewe@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 ?

At this stage, the only way I can see to solve this problem is using v.build option=cdump. This gives you something like this:

---------- CATEGORY INDEX DUMP: Number of layers: 2 ---------------------------- ----------
Layer 1 number of unique cats: 6 number of cats: 6 number of types: 1
-------------------------------------------------------------------------------- ----------
             type | count
                1 | 6
  category | type | line/area
         1 | 1 | 1
         2 | 1 | 2
         3 | 1 | 3
         4 | 1 | 4
         5 | 1 | 5
         6 | 1 | 6
-------------------------------------------------------------------------------- ----------
Layer 2 number of unique cats: 6 number of cats: 6 number of types: 1
-------------------------------------------------------------------------------- ----------
             type | count
                1 | 6
  category | type | line/area
         1 | 1 | 1
         3 | 1 | 2
         5 | 1 | 3
         7 | 1 | 4
         9 | 1 | 5
        11 | 1 | 6
--------------------------------

So, you see that the line/area id can be used to find the correspondance between the category values in both layers (i.e. in this case cat 3 in layer 1 is the same object as cat 5 in layer 2.

Unfortunately, there is no script friendly output from v.build (ToDo...), but still it should be possible to wrap this into a script with some python or awk magic. The easiest would probably be to use a real SQL backend (i.e. not dbf) and to add a column with the line id to each feature (i.e. 'v.db.update col=lineid value=$line where="cat=$cat"' where $line and $cat are the variables of the script read from the v.build ouput...

If you come up with a script, I think that this would be very useful. One thing I have been confronted with before was the desire to create a second layer with exactly the same category values (which didn't start at 1 and which had no regular step as they were statistical id codes of municipalities) which currently isn't possible.

But adding a clayer option to v.db.update might be another solution.

Moritz

3

Moritz,

thanks for your response. I didn't think of the option to use v.build yet and will try to set up an awk-script eventually.

One thing I have been confronted with before was the desire to create a
second layer with exactly the same category values (which didn't start
at 1 and which had no regular step as they were statistical id codes of
municipalities) which currently isn't possible.

That's where I also started...

Thanks,
Peter

-------- Original-Nachricht --------

Datum: Wed, 26 Nov 2008 14:34:17 +0100
Von: Moritz Lennert <mlennert@club.worldonline.be>
An: peter.loewe@gmx.de
CC: grass-user@lists.osgeo.org
Betreff: Re: [GRASS-user] How to copy vector columns between databases

On 25/11/08 16:29, peter.loewe@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 ?

At this stage, the only way I can see to solve this problem is using
v.build option=cdump. This gives you something like this:

---------- CATEGORY INDEX DUMP: Number of layers: 2
---------------------------- ----------
Layer 1 number of unique cats: 6 number of cats: 6
number of types: 1
--------------------------------------------------------------------------------
----------
             type | count
                1 | 6
  category | type | line/area
         1 | 1 | 1
         2 | 1 | 2
         3 | 1 | 3
         4 | 1 | 4
         5 | 1 | 5
         6 | 1 | 6
--------------------------------------------------------------------------------
----------
Layer 2 number of unique cats: 6 number of cats: 6
number of types: 1
--------------------------------------------------------------------------------
----------
             type | count
                1 | 6
  category | type | line/area
         1 | 1 | 1
         3 | 1 | 2
         5 | 1 | 3
         7 | 1 | 4
         9 | 1 | 5
        11 | 1 | 6
--------------------------------

So, you see that the line/area id can be used to find the correspondance
between the category values in both layers (i.e. in this case cat 3 in
layer 1 is the same object as cat 5 in layer 2.

Unfortunately, there is no script friendly output from v.build
(ToDo...), but still it should be possible to wrap this into a script
with some python or awk magic. The easiest would probably be to use a
real SQL backend (i.e. not dbf) and to add a column with the line id to
each feature (i.e. 'v.db.update col=lineid value=$line where="cat=$cat"'
where $line and $cat are the variables of the script read from the
v.build ouput...

If you come up with a script, I think that this would be very useful.
One thing I have been confronted with before was the desire to create a
second layer with exactly the same category values (which didn't start
at 1 and which had no regular step as they were statistical id codes of
municipalities) which currently isn't possible.

But adding a clayer option to v.db.update might be another solution.

Moritz

--
Dr. Peter Löwe
<peter.loewe@gmx.de>

Psssst! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger

4

On Tue, Nov 25, 2008 at 4:29 PM, <peter.loewe@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 :frowning:
... 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@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 :slight_smile:
TODO: fix v.to.db for layer>1.

Cheers
Markus

5

On 26/11/08 18:09, Markus Neteler wrote:

On Tue, Nov 25, 2008 at 4:29 PM, <peter.loewe@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 :frowning:
... 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@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

6

(back from grass-dev)

On Thu, Nov 27, 2008 at 10:06 AM, Moritz Lennert
<mlennert@club.worldonline.be> wrote:
...

And to respond to Peter's original query, i.e:

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?

it should actually be (assuming that the varchar attribute in layer 1 is
called 'name':

v.db.addcol FOO layer=2 col='name varchar(Size)'
v.to.db myroads layer=2 option=query col=name qlayer=1 qcolumn=name

Now a FAQ:

http://grass.osgeo.org/wiki/Vector_map_attribute_transfer_between_connected_tables

Markus

7

Excellent. Thank you Markus!
Another proof how the community benefits when simple minds address stuff they can't figure out by themselves,

Simple minded,
Peter
-------- Original-Nachricht --------

Datum: Thu, 27 Nov 2008 11:39:14 +0100
Von: "Markus Neteler" <neteler@osgeo.org>
An: peter.loewe@gmx.de
CC: "GRASS user list" <grass-user@lists.osgeo.org>
Betreff: Re: [GRASS-user] How to copy vector columns between databases

(back from grass-dev)

On Thu, Nov 27, 2008 at 10:06 AM, Moritz Lennert
<mlennert@club.worldonline.be> wrote:
...
> And to respond to Peter's original query, i.e:
>
>> 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?
>
>
> it should actually be (assuming that the varchar attribute in layer 1 is
> called 'name':
>
> v.db.addcol FOO layer=2 col='name varchar(Size)'
> v.to.db myroads layer=2 option=query col=name qlayer=1 qcolumn=name

Now a FAQ:

http://grass.osgeo.org/wiki/Vector_map_attribute_transfer_between_connected_tables

Markus

--
Dr. Peter Löwe
<peter.loewe@gmx.de>

Sensationsangebot nur bis 30.11: GMX FreeDSL - Telefonanschluss + DSL
für nur 16,37 Euro/mtl.!* http://dsl.gmx.de/?ac=OM.AD.PD003K11308T4569a