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

[Kirk Wythers]

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.
> 

Thanks Moritz,

I just want to make sure I understand your point…  

I want to attach the "MEAN" from the table "ew4km_tawc" added to the vector map "ew4kmg" where ew4kmg_Id is the same as ew4km_tawc_ID, so you are saying that my join needs to be:

v.db.join ew4kmg col=Id otable=ew4km_tawc ocol=ID?


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/grass-user/attachments/20120124/24fc4a80/attachment-0001.html