[GRASS-user] v.db.join question

[Johannes Radinger]

Hi all,

here some update...

On Thu, Feb 14, 2013 at 3:29 PM, Johannes Radinger
<johannesradinger at 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 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"

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