Hi, I'm using Grass 6.0 (I will use 6.2 soon, but I need to go further with 6.0 version until one month).
I would like to join a vector table with external attribute table using MySQL.
I created the MySQL Database, and did the v.dbconnect with MySQL drivers.
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.
Does some body has any suggestion ?
To resume my problem, I can say that I just try to do a join operation on vector attribute table, and that this operation seems to be a little bit difficult with Grass.
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.
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 ?
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.
Hi, I'm using Grass 6.0 (I will use 6.2 soon, but I need to go further with 6.0 version until one month).
I would like to join a vector table with external attribute table using MySQL.
I created the MySQL Database, and did the v.dbconnect with MySQL drivers.
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.
Does some body has any suggestion ?
To resume my problem, I can say that I just try to do a join operation on vector attribute table, and that this operation seems to be a little bit difficult with Grass.
well grass not see joined tables. the only way as I thing is to use SQL command CREATE TABLE nova AS
SELECT a.cat ..... FROM a INNER JOIN b ON .......;
nad reconnect vector to nova table (a.cat MUST be in SELECT section)
I use it very ofen but... on PgSQL. I'm not sure if CRATE TABLE AS works on mysql
On Wednesday 08 November 2006 12:33, Jarek Jasiewicz wrote:
Nicolas Devaux napisał(a):
> Hi, I'm using Grass 6.0 (I will use 6.2 soon, but I need to go further
> with 6.0 version until one month).
> I would like to join a vector table with external attribute table
> using MySQL.
> I created the MySQL Database, and did the v.dbconnect with MySQL drivers.
> 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.
>
> Does some body has any suggestion ?
>
> To resume my problem, I can say that I just try to do a join operation
> on vector attribute table, and that this operation seems to be a
> little bit difficult with Grass.
>
> Thanks
>
> --------------------------------------------------------
>
> _______________________________________________
> grassuser mailing list
> grassuser@grass.itc.it
> http://grass.itc.it/mailman/listinfo/grassuser
well grass not see joined tables. the only way as I thing is to use SQL
command CREATE TABLE nova AS
SELECT a.cat ..... FROM a INNER JOIN b ON .......;
nad reconnect vector to nova table (a.cat MUST be in SELECT section)
I use it very ofen but... on PgSQL. I'm not sure if CRATE TABLE AS works
on mysql
regards
Jarek
it should work.
--
Dylan Beaudette
Soils and Biogeochemistry Graduate Group
University of California at Davis
530.754.7341
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 ?
If GPS points and house data points do not exactly line up, you may have
to use the v.distance module to fill in attributes based on nearest
point. (it has a max distance parameter, so you can avoid unmatched data
mistakes)
I have very little idea about databases, but be aware that the MySQL
driver in GRASS 6.0 was rather weak and it has been substantially
rewritten & improved for GRASS 6.2.
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.
Ok but you are talking about a query. You are only issue a SQL instruction, so your results are temporarily and grass does not know nothing about it. As Jarek said, you must create a new table and then put your query’s results in it using a INSERT INTO new_table_name SELECT * FROM tbl_a JOIN tbl_b USING(key_name) or JOIN ON … …
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.
Right… as long the table doesn’t exist.
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 ?
The solution would be to work with views.
Grass sees views, connects to views and this is wonderful, but… more it would be if it could handle them correctly. I tried to link to views but quering on the attributes doesn’t work correctly.
Raffaele Morelli wrote:
> The solution would be to work with views.
Grass sees views, connects to views and this is wonderful, but... more it would be if it could handle them correctly. I tried to link to views but quering on the attributes doesn't work correctly.
I use views successfully quite often with the postgres driver. Could you be more specific about what does not work for you ?
The solution would be to work with views.
Grass sees views, connects to views and this is wonderful, but… more
it would be if it could handle them correctly. I tried to link to views
but quering on the attributes doesn’t work correctly.
I use views successfully quite often with the postgres driver. Could you
be more specific about what does not work for you ?
Moritz
With grass6.0 on a debian testing I connect succesfully to views.
The problem arise when trying to query the layer connected to the view, it doesn’t display (e.g. label) fields stored in the view or retrieve any information from there.
It seems just like “Layer for labels” and “Layer for query” text box in the Gis Manager doesn’t work properly with views, sometimes they simply ignore my SQL statements or retrieve incorrect values.
I tried to not use MySQL specific field type, switching to INT type all my SMALLINTs, TINYNTs, but still with no success.
2006/11/9, Moritz Lennert <mlennert@club.worldonline.be <mailto:mlennert@club.worldonline.be>>:
Raffaele Morelli wrote:
> The solution would be to work with views.
> Grass sees views, connects to views and this is wonderful, but...
more
> it would be if it could handle them correctly. I tried to link to
views
> but quering on the attributes doesn't work correctly.
I use views successfully quite often with the postgres driver. Could you
be more specific about what does not work for you ?
Moritz
With grass6.0 on a debian testing I connect succesfully to views.
The problem arise when trying to query the layer connected to the view, it doesn't display (e.g. label) fields stored in the view or retrieve any information from there.
It seems just like "Layer for labels" and "Layer for query" text box in the Gis Manager doesn't work properly with views, sometimes they simply ignore my SQL statements or retrieve incorrect values.
This might be an issue with layers, not with your view.
How do you use the layers ? Layers can be quite complex to understand and there has been much discussion on them.
Have you tried connecting to a view without changing layers, i.e.:
- take a map MyMap for which you have a table MapTable in your database
- create a view with "create view NewView as select * from MapTable"
- connect your map to this view with 'v.db.connect -o map=MyMap table=NewView key=KeyCol'
Do you have problems showing labels or querying the map ?
This might be an issue with layers, not with your view.
How do you use the layers ? Layers can be quite complex to understand
and there has been much discussion on them.
Have you tried connecting to a view without changing layers, i.e.:
take a map MyMap for which you have a table MapTable in your database
create a view with “create view NewView as select * from MapTable”
connect your map to this view with ’
Do you have problems showing labels or querying the map ?
(…!!!..) ok, this works. But now I have some questions about it.
“v.db.connect -o map=MyMap table=TABLE key=KeyCol driver=mysql database=grass layer=2”
this connects my map with a static table on layer 1
“v.db.connect -o map=MyMap table=NewView key=KeyCol driver=mysql database=grass layer=1”
this connects my map to the view
in this panorama, if I try to query (using gis manager) setting “Layer for labels” = 2 and indicating the col field in the view I don’t see anything. Why? Am I missing something?
2006/11/9, Moritz Lennert <mlennert@club.worldonline.be <mailto:mlennert@club.worldonline.be>>:
This might be an issue with layers, not with your view.
How do you use the layers ? Layers can be quite complex to understand
and there has been much discussion on them.
Have you tried connecting to a view without changing layers, i.e.:
- take a map MyMap for which you have a table MapTable in your database
- create a view with "create view NewView as select * from MapTable"
- connect your map to this view with '
Do you have problems showing labels or querying the map ?
(...!!!...) ok, this works. But now I have some questions about it.
"v.db.connect -o map=MyMap table=TABLE key=KeyCol driver=mysql database=grass layer=2"
this connects my map with a static table on layer 1
No, layer 2.
"v.db.connect -o map=MyMap table=NewView key=KeyCol driver=mysql database=grass layer=1"
this connects my map to the view
in this panorama, if I try to query (using gis manager) setting "Layer for labels" = 2 and indicating the col field in the view I don't see anything. Why? Am I missing something?
Probably you do not have any objects defined in layer 2.
see this thread to see if you don't have the same problem:
