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

[Moritz Lennert]

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.

Moritz