[GRASS-user] v.db.join question

Johannes_Radinger · February 12, 2013, 3:15pm

Hi,

I try to join a vector attribute table (points, sqlite-db) with
another sqlite table that is already imported to GRASS.
Both tables share some columns and have a column called AB_ID as
unique identifier (key to join).

I opened the sqlite database with the SQLite Manager (Firefox) and can
successfully perform a simple query:
SELECT * FROM Vector_1, Table_2 WHERE Vector_1.AB_ID=Table_2.AB_ID

however when I want to join the tables in GRASS using v.db.join the
new columns (*_other) are empty for all rows:
v.db.join -a map="Vector_1" column="AB_ID" otable="Table_2" ocolumn="AB_ID"

As some of the columns have the same names in both tables I used the 'a'-flag.

What I am doing wrong? I am working with GRASS65 (rev 55016) on Ubuntu 12.04.

/Johannes

Johannes_Radinger · February 14, 2013, 2:29pm

Hi,

does anyone have an idea what I should test to get my join working?

/johannes

On Tue, Feb 12, 2013 at 4:15 PM, Johannes Radinger
<johannesradinger@gmail.com> wrote:

Hi,

I try to join a vector attribute table (points, sqlite-db) with
another sqlite table that is already imported to GRASS.
Both tables share some columns and have a column called AB_ID as
unique identifier (key to join).

I opened the sqlite database with the SQLite Manager (Firefox) and can
successfully perform a simple query:
SELECT * FROM Vector_1, Table_2 WHERE Vector_1.AB_ID=Table_2.AB_ID

however when I want to join the tables in GRASS using v.db.join the
new columns (*_other) are empty for all rows:
v.db.join -a map="Vector_1" column="AB_ID" otable="Table_2" ocolumn="AB_ID"

As some of the columns have the same names in both tables I used the 'a'-flag.

What I am doing wrong? I am working with GRASS65 (rev 55016) on Ubuntu 12.04.

/Johannes

Johannes_Radinger · February 15, 2013, 11:04am

Hi all,

here some update...

On Thu, Feb 14, 2013 at 3:29 PM, Johannes Radinger
<johannesradinger@gmail.com> wrote:

Hi,

does anyone have an idea what I should test to get my join working?

/johannes

On Tue, Feb 12, 2013 at 4:15 PM, Johannes Radinger
<johannesradinger@gmail.com> wrote:

Hi,

I try to join a vector attribute table (points, sqlite-db) with
another sqlite table that is already imported to GRASS.
Both tables share some columns and have a column called AB_ID as
unique identifier (key to join).

I opened the sqlite database with the SQLite Manager (Firefox) and can
successfully perform a simple query:
SELECT * FROM Vector_1, Table_2 WHERE Vector_1.AB_ID=Table_2.AB_ID

however when I want to join the tables in GRASS using v.db.join the
new columns (*_other) are empty for all rows:
v.db.join -a map="Vector_1" column="AB_ID" otable="Table_2" ocolumn="AB_ID"

I know checked the code of v.db.join and the important JOIN-line seems to be:

echo "UPDATE $maptable SET $col=(SELECT $col
FROM $GIS_OPT_OTABLE WHERE
$GIS_OPT_OTABLE.$GIS_OPT_OCOLUMN=$maptable.$GIS_OPT_COLUMN);" | \

So I tried that SQL command to select also only one column in my SQlite Manager:

SELECT colX FROM Table_2 WHERE Vector_1.AB_ID=Table_2.AB_ID which is
basically what the script does?!
but here I fail again...(table_2 has similar columns like Vector_1)

The SQL query that is working for that case is:
SELECT table_2.colX FROM Vector_1,Table_2 WHERE Vector_1.AB_ID=Table_2.AB_ID

or translated to the v.db.join script something like:
UPDATE $maptable SET $col=(SELECT $GIS_OPT_OTABLE.$col
FROM $GIS_OPT_OTABLE, $maptable WHERE
$GIS_OPT_OTABLE.$GIS_OPT_OCOLUMN=$maptable.$GIS_OPT_COLUMN);

Is there something wrong how I am handling v.db.join or is there a bug
like described above?
Maybe it has something to do with the case that both tables have
similar columns...

Any GRASS-SQL expert out there?

/Johannes

As some of the columns have the same names in both tables I used the 'a'-flag.

What I am doing wrong? I am working with GRASS65 (rev 55016) on Ubuntu 12.04.

/Johannes

mlennert · February 15, 2013, 11:18am

On 14/02/13 15:29, Johannes Radinger wrote:

Hi,

does anyone have an idea what I should test to get my join working?

I think this comes from the -a flag (which only exists in 65, not in 64 nor 7). In the code I see:

     elif [ "$GIS_FLAG_A" -eq 1 ] ; then
         col="${col}_other"
     fi

and then

     echo "UPDATE $maptable SET $col=(SELECT $col
             FROM $GIS_OPT_OTABLE WHERE $GIS_OPT_OTABLE.$GIS_OPT_OCOLUMN=$maptable.$GIS_OPT_COLUMN);" | \
       db.execute database="$database" driver="$driver"

So the new column in the vector map table is set to the value of that column in the joined table, but both are called by the variable $col which with the flag -a contains the string '_other' which does not exist in the original table. In other words, the query becomes something like this:

UPDATE $maptable SET yourcolum_other = (SELECT yourcolumn_other FROM $GIS_OPT_OTABLE ....

but in the $GIS_OPT_OTABLE the column is yourcolumn, not yourcolumn_other.

Changing the script like this might work (untested):

===================================================================
--- v.db.join (révision 53809)
+++ v.db.join (copie de travail)
@@ -101,7 +101,9 @@
exit 1
fi

-maptable=`v.db.connect -gl map="$GIS_OPT_MAP" fs="|" layer="$GIS_OPT_LAYER" | cut -d'|' -f2`
+maptablef [ "$GIS_FLAG_A" -eq 1 ] ; then
+ col="${col}_other"
+ fi=`v.db.connect -gl map="$GIS_OPT_MAP" fs="|" layer="$GIS_OPT_LAYER" | cut -d'|' -f2`

  if [ -z "$maptable" ] ; then
     g.message 'There is no table connected to this map! Cannot join any column.'
@@ -127,18 +129,18 @@
      if [ "$GIS_FLAG_C" -eq 1 ] && [ "$col" = "$GIS_OPT_OCOLUMN" ] ; then
    continue
      elif [ "$GIS_FLAG_A" -eq 1 ] ; then
- col="${col}_other"
+ col_new="${col}_other"
      fi

v.db.addcol "$GIS_OPT_MAP" layer="$GIS_OPT_LAYER" \
- col="$col `echo $COLTYPES | cut -d' ' -f$i | tr -s '_' ' '`"
+ col_new="$col `echo $COLTYPES | cut -d' ' -f$i | tr -s '_' ' '`"

      if [ $? -ne 0 ] ; then
    g.message -e "Column creation failed. Cannot continue."
    exit 1
      fi

- echo "UPDATE $maptable SET $col=(SELECT $col
+ echo "UPDATE $maptable SET ${col_new}=(SELECT $col
FROM $GIS_OPT_OTABLE WHERE $GIS_OPT_OTABLE.$GIS_OPT_OCOLUMN=$maptable.$GIS_OPT_COLUMN);" | \
db.execute database="$database" driver="$driver"

Moritz

On Tue, Feb 12, 2013 at 4:15 PM, Johannes Radinger
<johannesradinger@gmail.com> wrote:

Hi,

I try to join a vector attribute table (points, sqlite-db) with
another sqlite table that is already imported to GRASS.
Both tables share some columns and have a column called AB_ID as
unique identifier (key to join).

I opened the sqlite database with the SQLite Manager (Firefox) and can
successfully perform a simple query:
SELECT * FROM Vector_1, Table_2 WHERE Vector_1.AB_ID=Table_2.AB_ID

however when I want to join the tables in GRASS using v.db.join the
new columns (*_other) are empty for all rows:
v.db.join -a map="Vector_1" column="AB_ID" otable="Table_2" ocolumn="AB_ID"

As some of the columns have the same names in both tables I used the 'a'-flag.

What I am doing wrong? I am working with GRASS65 (rev 55016) on Ubuntu 12.04.

/Johannes

_______________________________________________
grass-user mailing list
grass-user@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/grass-user

neteler · March 3, 2013, 10:33am

On Fri, Feb 15, 2013 at 12:18 PM, Moritz Lennert
<mlennert@club.worldonline.be> wrote:

On 14/02/13 15:29, Johannes Radinger wrote:

does anyone have an idea what I should test to get my join working?

I think this comes from the -a flag (which only exists in 65, not in 64 nor 7)

Please file a ticket if anything needs to be done here.

thanks
Markus