[GRASS-user] sqlite table join problem "duplicate column name"

[Moritz Lennert]

On 25/01/12 01:10, Kirk Wythers wrote:
>
> On Jan 24, 2012, at 1:29 PM, Moritz Lennert wrote:
>
>> On 24/01/12 19:49, Kirk Wythers wrote:
>>> I am trying to do a 'join' of a column from one table, to the attribute table of another vector. However, I am getting an error about a problem adding another column due to a duplicate column name.
>>>
>>> Snip of commands are below:
>>>
>>> GRASS 6.4.1 (latlon):~>   v.info -c ew4kmg
>>> Displaying column types/names for database connection of layer 1:
>>> INTEGER|cat
>>> INTEGER|Id
>>> DOUBLE PRECISION|XMIN
>>> DOUBLE PRECISION|XMAX
>>> DOUBLE PRECISION|YMIN
>>> DOUBLE PRECISION|YMAX
>>>
>>> GRASS 6.4.1 (latlon):~>   db.describe -c ew4km_tawc
>>> ncols: 13
>>> nrows: 265090
>>> Column 1: ID:INTEGER:20
>>> Column 2: COUNT:INTEGER:20
>>> Column 3: AREA:DOUBLE PRECISION:20
>>> Column 4: MIN:INTEGER:20
>>> Column 5: MAX:INTEGER:20
>>> Column 6: RANGE:INTEGER:20
>>> Column 7: MEAN:DOUBLE PRECISION:20
>>> Column 8: STD:DOUBLE PRECISION:20
>>> Column 9: SUM:DOUBLE PRECISION:20
>>> Column 10: VARIETY:INTEGER:20
>>> Column 11: MAJORITY:INTEGER:20
>>> Column 12: MINORITY:INTEGER:20
>>> Column 13: MEDIAN:INTEGER:20
>>>
>>> GRASS 6.4.1 (latlon):~>   v.db.select ew4kmg | head -7
>>> cat|Id|XMIN|XMAX|YMIN|YMAX
>>> 1|128|-1793092|-1789092|-1142894|-1138894
>>> 2|129|-1793092|-1789092|-1138894|-1134894
>>> 3|130|-1793092|-1789092|-1134894|-1130894
>>> 4|826|-1789092|-1785092|-1150894|-1146894
>>> 5|827|-1789092|-1785092|-1146894|-1142894
>>> 6|828|-1789092|-1785092|-1142894|-1138894
>>>
>>> GRASS 6.4.1 (latlon):~>   db.select ew4km_tawc | head -7
>>> ID|COUNT|AREA|MIN|MAX|RANGE|MEAN|STD|SUM|VARIETY|MAJORITY|MINORITY|MEDIAN
>>> 127|349|314100|2163|2850|687|2199.68|131.842|767687|36|2163|2185|2163
>>> 128|7906|7115400|932|2850|1918|2182.34|534.089|17253600|252|2762|1245|2202
>>> 129|10528|9475200|864|2762|1898|1733.46|877.929|18249900|3|864|1568|1568
>>> 130|15421|13878900|539|2850|2311|1403.27|806.156|21639900|80|864|539|864
>>> 131|17822|16039800|864|2762|1898|1024.77|111.876|18263400|3|1096|2762|1096
>>> 132|17689|15920100|864|1096|232|1076.65|64.1395|19044900|2|1096|864|1096
>>>
>>> So I am trying to perform the join the MEAN column in the table ew4km_tawc on the Id column in the map ew4kmg. Here is the output:
>>>
>>> GRASS 6.4.1 (latlon):~>   v.db.join map=ew4kmg column=Id otable=ew4km_tawc ocolumn=MEAN
>>> DBMI-SQLite driver error:
>>> Error in sqlite3_prepare():
>>> duplicate column name: ID
>>>
>>> ERROR: Error while executing: 'ALTER TABLE ew4kmg ADD COLUMN ID INTEGER
>>>         '
>>> ERROR: Cannot continue (problem adding column).
>>> ERROR: Cannot continue.
>>>
>>> I am confused since it seems to me, that there has to be a duplicate column name (in this case 'ID', in order for the join to know what to join on… Any suggestions would be much appreciated!
>>
>> No, there has to be two columns containing the join criterium, but these do not have to have the same column name. As you can see from the error, they actually shouldn't have the same name (note: the module should probably just prefix each column with the name of the original table to avoid such issues...but then you run into column name length issues.).
>>
>> You v.db.join is incorrect as well, as your ocolumn is ID, not MEAN. the column and ocolumn options designate the respective columns in the two tables that contain the join criterium. All of the other columns of otable will be joined to the table of your vector map.
>>
>>
>
>
> I reworked the join statement to:
>
> v.db.join ew4kmg col=id otable=ew4km_tawc ocol=ID
>
> and got the error about duplicate column names. After that I tired renameing the Id column in the map ew4kmg to "label" and re-ran with:
>
> v.db.join ew4kmg col=label otable=ew4km_tawc ocol=ID
>
> a couple sqlite processes have been running at 100% for several hours now. Does this seem normal? The join table has ~265,000 rows.

No, even though your table is large, this should not take as long. Could 
you try to test how long this takes doing the join in SQL directly 
within sqlite to see whether this is a GRASS problem ? Also you could 
add indexes to the two join columns (label and ID), this would probably 
speed up things considerably.

Moritz