[GRASS-user] v.db.join question

[Moritz Lennert]

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 at 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 at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/grass-user