[GRASS-user] Re: Connect two tables in vector data

[Moritz Lennert]

On 11/10/11 10:26, Sebastian Schubert wrote:
>
> On Tuesday 11 October 2011 01:17:15 Richard Chirgwin wrote:
>> On 11/10/11 4:02 PM, grass-user-request at lists.osgeo.org wrote:
>>> Hi,
>>>
>>> using 6.4.1, I imported a vector shape file which includes a
>>> tables. This table got a "cat" column by grass (which I don't
>>> need, I think) and already has a "key" column. In addition, I have
>>> got another table which also has a "key" column. Using both "key"
>>> columns, I want to combine both tables. How can I do that? I was
>>> able connect, as a second layer, the additional table to the
>>> vector data using the "key" column but then as far as I can see it
>>> is not really connected to the first table.
>>>
>>> I guess this is a standard procedure but I was not able to find the
>>> solution.
>>>
>>> Thanks
>>> Sebastian
>>
>> Sebastian -
>>
>> I think you're asking to connect two tables to the same map?
>
> Yes, I think so.
>
>> The two tables need to refer to the same features, and you use
>> v.db.connect
>>
>> Note that you can specify a different name for the key column using
>> the key= parameter.
>
> I think, this is what I tried. Here is what I did:
>
> I imported the map baubloecke.shp. It already includes data (which is
> then called table "baubloecke":
>
> GRASS 6.4.1 (Soldner):~>  v.db.connect -p baubloecke
> Vector map<baubloecke at Berlin>  is connected by:
> layer<1>  table<baubloecke>  in database
> </home/schubert/grassdata/Soldner/Berlin/dbf/>  through driver<dbf>  with
> key<cat>
>
> Ok, it is connected with key "cat" which has been introduced by grass, I
> think. The table "baubloecke" includes the column I want, as well as the
> table "bbdb":
>
> GRASS 6.4.1 (Soldner):~>  db.columns baubloecke
> cat
> ELEMNAME
> KLASSENNAM
> SCHLUESSEL
> GRASS 6.4.1 (Soldner):~>  db.columns bbdb
> SCHLUESSEL
> BLOCKART
> FLALLE
> NUTZ
> FISNUTZUNG
> TYP
> TYPKLAR
> VG
> STSTRTYP
> STSTRNAME
>
> I connected bbdb to the map:
>
> GRASS 6.4.1 (Soldner):~>  v.db.connect -o map=baubloecke table=bbdb
> key=SCHLUESSEL layer=2
> The table<bbdb>  is now part of vector map<baubloecke>  and may be
> deleted
> or overwritten by GRASS modules
> Select privileges were granted on the table
> GRASS 6.4.1 (Soldner):~>  v.db.connect -p baubloecke
> Vector map<baubloecke at Berlin>  is connected by:
> layer<1>  table<baubloecke>  in database
> </home/schubert/grassdata/Soldner/Berlin/dbf/>  through driver<dbf>  with
> key<cat>
> layer<2>  table<bbdb>  in database
> </home/schubert/grassdata/Soldner/Berlin/dbf/>  through driver<dbf>  with
> key<SCHLUESSEL>
>
> I tried to verify the connection using the wxpython gui. I opened the
> map there (tried both layer 1 and 2). Using the "Query raster/vector
> map(s)" tool, I was able to only get information from the layer 1 or the
> original table when clicking on a polygon.
>
> Ok, maybe table 1 has to be connected also via the key "SCHLUESSEL" so I
> tried:
>
> GRASS 6.4.1 (Soldner):~>  v.db.connect -o map=baubloecke
> table=baubloecke key=SCHLUESSEL layer=1
> ERROR: Data type of key column must be integer
> GRASS 6.4.1 (Soldner):~>  v.db.connect -o map=baubloecke
> table=baubloecke key=3 layer=1
> ERROR: Missing column<3>  in table<baubloecke>
>
> I do not understand this... Apparently, grass does not accept the key
> SCHLUESSEL so I tried the number of the column (?) 3. No success,
> either.

You are mixing up two different things here:

1) A vector map in GRASS can be (but does not have to be) connceted to 
an attribute table. Any object in the vector map can have a category 
value (sort of an identification number). If the map is connected to a 
table, this table has to contain a column which contains these category 
values. By default, this column is called 'cat' (but you can give it any 
arbitrary name). The information about which table a map is connected to 
and which column in this table contains the relevant category values is 
managed with v.db.connect.

2) You can connect a map to different tables by using layers, but this 
is not what is needed here and is quite complex to grasp, so I'll leave 
it out here.

3) What you are trying to do is not to connect the map to your second 
table (bbdb), but rather to link the table bbdb to the table baubloecke. 
In GRASS, the classical way to do this is to a) import bbdb into the 
database (which you seem to have done, but we have no info on how) using 
db.in.ogr and b) join the two tables using v.db.join. However, this last 
command does not suppport the dbf backend for attribute table handling. 
You, therefore, have to use another backend, such as the SQLite backend 
(see more info here: 
http://grass.osgeo.org/grass64/manuals/html64_user/databaseintro.html).

So, in summary, no need to use v.db.connect, but rather, use the SQLite 
backend and use db.in.ogr and v.db.join.

Moritz