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!
Best,
Kirk
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
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?
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.
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