[GRASS-user] How to JOIN a table to vector layer using MySQL

[Moritz Lennert]

Nicolas Devaux wrote:
> Raffaele Morelli a écrit :
>> Hi
>> I use to work with grass vectors with attributes stored in a MySQL db.
>> But it's not very clear when you say
>>
>>
>>     I did the Join operation with Mysql and it works perfectly. But,
>>     when I
>>     come back in Grass, it is not possible to see the result within the
>>     attribute table... Joined attributes are not in the table. It
>>     seems that
>>     grass is not able to see results of the joined operation made in
>>     Mysql.
>>
>>
>> What kind of join are you talking about?
>> Do you mean a db view resulting from a two (or more) table join?
>>
>> Vectors have categories which identify polygons. You must "link" those 
>> values with the corresponding key on your table, so when calling 
>> db.connect you must specify a valid database name, valid table name 
>> and finally a valid key on your table.
>> Valid means that polygons id's (categories) need a value stored in the 
>> table to link to.
>>
>> Try to create a dummy table containing a field with the same value 
>> your categories have, and link it to the vector using that field.
>>
>> Hope this helps
>>
> Thanks for this answer, but it did not really help me.
> I know what is a table join, and as I say, I made the join (using Cat as 
> common fields of both tables to join), but what I want is to see result 
> within Grass on the attribute table of my vector layer.
> 
> Exemple : I have a GPS points vector layer call GPS which locate houses. 
> Attributes table contain only categories number. I use MySql to import 
> another table containing points informations (point id and house's owner 
> names). I make the table join within Mysql and it works (I have in the 
> attribute table of GPS points cover the name of house's owners. Now, I 
> want to be able to use this information within Grass, and when I look at 
> the attribute table, I don't have owner names, I have only cat number as 
> in the attributre table before the join operation made in MySql. I think 
> that I miss something... I would like to transform the join operation 
> results as permanent information within my points cover attribute table....
> Perhaps I should make the join table operation directly in Grass instead 
> of doing it in MySql ? If is is the solution, how can I do, what is the 
> command ?
> 
> I hope this is more clear...


Unfortunately not: you still don't explain what the result of your 
"join" is: is it a new table, a view or are you joining in an SQL query ?

In order to make the join visible from GRASS you have to follow these steps:

- within mysql (or using db.execute from within GRASS - the result is 
the same) create a view (or a table) containing the join.
- use v.db.connect -o to link your map to this view/table using the key= 
parameter to tell GRASS which column in your joined view/table contains 
the category numbers.

Moritz