v.in.ogr does not handle columns named 2D_lenght and 3D_lenght

[wenzeslaus]

When the attribute table of the imported map contains numbers at the beginning of the map/layer name (e.g. 2D_lenght), G7:v.in.ogr replaces the number with x.

When there are two columns which differ only by this first character (e.g. 2D_lenght and 3D_lenght), both names will result in xD_lenght:

Column name <2D_lenght> renamed to <xD_lenght>
Column name <3D_lenght> renamed to <xD_lenght>

This of course causes an error:

DBMI-SQLite driver error:
Error in sqlite3_prepare():
duplicate column name: xD_lenght
DBMI-SQLite driver error:
Error in sqlite3_prepare():
duplicate column name: xD_lenght
ERROR: Unable to create table: 'create table mytable (cat integer, ..., xD_lenght double precision, xD_lenght double precision, ...)'

(I don't know why the lines at the beginning are repeated.)

This replacing can actually happen also for map/layer names itself, probably for both G7:v.in.ogr and G7:r.in.gdal, when filenames (or whatever) begging with the number.

The workaround is to specify all column names manually using cnames parameter, or output parameter in case of layers. However, more comfortable (especially in case of columns) would be to have some more robust replacing method active by default, e.g. prefix for columns with invalid character at the beginning instead of replacing.

The other part of the story is that in GUI, you cannot specify cnames, perhaps some table for column names can be done but automatic working (robust) way would be beneficial for both GUI and scripts.

By the way, shouldn't be cnames renamed to column_names according to new GRASS reading friendly standards?

[hamish]

Hi,

IIUC, column names starting with a number are not SQL compliant so won't work in grass. e.g. if a column was called '3e' (or perhaps just '3') should the sql select statement treat it like a number or a column? there's the same common problem with sql reserved keywords used as column names + v.in.ogr, with the same solution: either continue to ban them, or go through all the code and add quoting to every place it needs it.

we've had similar discussions about allowing vector maps to use non-sql compliant names as they used to in grass 5 and older. a solution there is to either cleanse them for the table names and adjust db.connect/dbln as needed, or again spend a bunch of time finding everywhere that needs quoting.

in the 2D, 3D, -> xD case, perhaps the code should insert the "x" instead of replacing (if it's flagged for starting with a number), other non-compatible chars would still need to be x'd though. And remember that dbf limits to 10 char column names, so if adding an "x" at the beginning you need to check if it is needed to remove one from the end. (even if dbf isn't the default anymore it still needs to be supported)

regards, Hamish

[martinl]

Still relevant?

[mmetz]

Replying to martinl:

Still relevant?

Yes.

Column names not starting with a letter are still not SQL compliant. Alternative column names can be provided with the columns option. Maybe the mechanism of automatically renaming columns needs to be removed because 1) it is not working, 2) the user does not know if and how columns have been renamed. Better have the user provide new column names if need be.

[sbl]

Quoting of column names would probably be another way of addressing this. See also: #3071