DBF limits column names to 10 chars; you end up with "ADMIN_CLAS" twice
in the above list. Either change DB column names or user the v.in.ogr
cnames= option to rename columns to something unique. (hint, cut & paste
above list with modification). Maybe changing DB to SQLite or something
helps too?
Brad Douglas wrote:
> Is there an easy fix for this? I'm trying to import a shapefile and
> I'm not terribly familiar with this part of the GRASS architecture.
>
> GRASS 6.3.cvs (hamilton):~/hamilton > v.in.ogr dsn=. output=roads
> layer=17
> Projection of input dataset and current location appear to match.
> Proceeding with import...
> Layer: 17
> DBMI-DBF driver error:
> Column 'ADMIN_CLAS' already exists (duplicate name)
> Cannot create table.
> Error in db_execute_immediate()
>
> ERROR: Cannot create table: create table roads (cat integer, PREFIX
> varchar
> ( 2 ), NAME varchar ( 30 ), TYPE varchar ( 4 ), SUFFIX varchar
> ( 2
> ), FCC varchar ( 3 ), FIPS varchar ( 11 ), ID integer,
> FULL_NAME varchar ( 40 ), ADMIN_CLAS varchar ( 40 ), CATEGORY
> integer, ST_CNTY_FI varchar ( 11 ), RD_CLASS varchar ( 4 ),
> RTE_NUM1
> varchar
> ( 4 ), RTE_NUM2 varchar ( 4 ), ADMIN_CLAS integer, AREA double
> precision, LEN double precision)
> GRASS 6.3.cvs (hamilton):~/hamilton >
DBF limits column names to 10 chars; you end up with "ADMIN_CLAS" twice
in the above list. Either change DB column names or user the v.in.ogr
cnames= option to rename columns to something unique. (hint, cut & paste
above list with modification). Maybe changing DB to SQLite or something
helps too?
Ahh! Thanks!
However, this does present a usability issue. This is a problem many,
people will encounter. I was trying to import a standard USGS product.
How about when v.in.ogr encounters this, offer a suggestion to fix,
rather than simply erroring out? We could almost cut and paste your
answer above. At least something a little less terse than, "ERROR:
Cannot create table: [SQL statement]". That describes nothing about the
problem encountered (duplicate columns)[1].
What do you think?
[1] Yes, I do [now] realize that upon close inspection and decent
knowledge of SQL92, one can readily solve the problem.
--
Brad Douglas <rez touchofmadness com> KB8UYR
Address: 37.493,-121.924 / WGS84 National Map Corps #TNMC-3785
On Mon, Sep 04, 2006 at 03:54:07AM -0700, Brad Douglas wrote:
On Sun, 2006-09-03 at 19:59 +1200, Hamish wrote:
> Brad Douglas wrote:
> > Is there an easy fix for this? I'm trying to import a shapefile and
> > I'm not terribly familiar with this part of the GRASS architecture.
> >
> > GRASS 6.3.cvs (hamilton):~/hamilton > v.in.ogr dsn=. output=roads
> > layer=17
> > Projection of input dataset and current location appear to match.
> > Proceeding with import...
> > Layer: 17
> > DBMI-DBF driver error:
> > Column 'ADMIN_CLAS' already exists (duplicate name)
> > Cannot create table.
> > Error in db_execute_immediate()
> >
> > ERROR: Cannot create table: create table roads (cat integer, PREFIX
> > varchar
> > ( 2 ), NAME varchar ( 30 ), TYPE varchar ( 4 ), SUFFIX varchar
> > ( 2
> > ), FCC varchar ( 3 ), FIPS varchar ( 11 ), ID integer,
> > FULL_NAME varchar ( 40 ), ADMIN_CLAS varchar ( 40 ), CATEGORY
> > integer, ST_CNTY_FI varchar ( 11 ), RD_CLASS varchar ( 4 ),
> > RTE_NUM1
> > varchar
> > ( 4 ), RTE_NUM2 varchar ( 4 ), ADMIN_CLAS integer, AREA double
> > precision, LEN double precision)
> > GRASS 6.3.cvs (hamilton):~/hamilton >
>
>
> DBF limits column names to 10 chars; you end up with "ADMIN_CLAS" twice
> in the above list. Either change DB column names or user the v.in.ogr
> cnames= option to rename columns to something unique. (hint, cut & paste
> above list with modification). Maybe changing DB to SQLite or something
> helps too?
Ahh! Thanks!
However, this does present a usability issue. This is a problem many,
people will encounter. I was trying to import a standard USGS product.
How about when v.in.ogr encounters this, offer a suggestion to fix,
rather than simply erroring out? We could almost cut and paste your
answer above. At least something a little less terse than, "ERROR:
Cannot create table: [SQL statement]". That describes nothing about the
problem encountered (duplicate columns)[1].
What do you think?
Such a message would be very useful.
[1] Yes, I do [now] realize that upon close inspection and decent
knowledge of SQL92, one can readily solve the problem.
Another option is to change the last char of the offending
col(s) to an incrementing number:
DBF limits column names to 10 chars; you end up with "ADMIN_CLAS" twice
in the above list. Either change DB column names or user the v.in.ogr
cnames= option to rename columns to something unique. (hint, cut & paste
above list with modification). Maybe changing DB to SQLite or something
helps too?
Ahh! Thanks!
However, this does present a usability issue. This is a problem many,
people will encounter. I was trying to import a standard USGS product.
I wanted to ask about it as I had exactly the same problem just few days ago -
I believe I was importing USGS roads too.
How about when v.in.ogr encounters this, offer a suggestion to fix,
rather than simply erroring out? We could almost cut and paste your
answer above. At least something a little less terse than, "ERROR:
Cannot create table: [SQL statement]". That describes nothing about the
problem encountered (duplicate columns)[1].
it actually says that, but it does not explain that the duplicate column is due
to the 10chars limit so I was rather confused too.
so a suggestion for a solution
would be helpful. If DBF does not work directly with a standard USGS product
it is one more reason to change the default DBMS in future (GRASS7?)
Helena
What do you think?
[1] Yes, I do [now] realize that upon close inspection and decent
knowledge of SQL92, one can readily solve the problem.
--
Brad Douglas <rez touchofmadness com> KB8UYR
Address: 37.493,-121.924 / WGS84 National Map Corps #TNMC-3785
Hamish is right. And you are importing a shapefile, correct? So how
could a column name longer than 10 chars be in the dbf shipped with
that shapefile?
Michael
__________________________________________
Michael Barton, Professor of Anthropology
School of Human Evolution & Social Change
Center for Social Dynamics & Complexity
Arizona State University
On Mon, 2006-09-04 at 18:09 +0200, Maciej Sieczka wrote:
Hamish wrote:
> Brad Douglas wrote:
>> Is there an easy fix for this? I'm trying to import a shapefile and
>> I'm not terribly familiar with this part of the GRASS architecture.
>>
>> GRASS 6.3.cvs (hamilton):~/hamilton > v.in.ogr dsn=. output=roads
>> layer=17
>> Projection of input dataset and current location appear to match.
>> Proceeding with import...
>> Layer: 17
>> DBMI-DBF driver error:
>> Column 'ADMIN_CLAS' already exists (duplicate name)
>> Cannot create table.
>> Error in db_execute_immediate()
>>
>> ERROR: Cannot create table: create table roads (cat integer, PREFIX
>> varchar
>> ( 2 ), NAME varchar ( 30 ), TYPE varchar ( 4 ), SUFFIX varchar
>> ( 2
>> ), FCC varchar ( 3 ), FIPS varchar ( 11 ), ID integer,
>> FULL_NAME varchar ( 40 ), ADMIN_CLAS varchar ( 40 ), CATEGORY
>> integer, ST_CNTY_FI varchar ( 11 ), RD_CLASS varchar ( 4 ),
>> RTE_NUM1
>> varchar
>> ( 4 ), RTE_NUM2 varchar ( 4 ), ADMIN_CLAS integer, AREA double
>> precision, LEN double precision)
>> GRASS 6.3.cvs (hamilton):~/hamilton >
> DBF limits column names to 10 chars;
Brad,
Just curious:
Hamish is right. And you are importing a shapefile, correct? So how
could a column name longer than 10 chars be in the dbf shipped with
that shapefile?
After looking at the DBF, it appears that they've extracted from another
source to a shapefile. During the extraction, it truncates the column
names at 10 characters, leaving duplicate column names. It's *really*
bad practice on their part.
I have confirmed this with a new dataset.
--
Brad Douglas <rez touchofmadness com> KB8UYR
Address: 37.493,-121.924 / WGS84 National Map Corps #TNMC-3785
> However, this does present a usability issue. This is a problem
> many, people will encounter. I was trying to import a standard USGS
> product.
>
> How about when v.in.ogr encounters this, offer a suggestion to fix,
> rather than simply erroring out? We could almost cut and paste your
> answer above. At least something a little less terse than, "ERROR:
> Cannot create table: [SQL statement]". That describes nothing about
> the problem encountered (duplicate columns)[1].
The error message talks about duplicate column names already. The issue
is mentioned in one of the SQL or DBF help pages already (?), but I have
added a note to the v.in.ogr help page as well (+6.2).
Markus:
Another option is to change the last char of the offending
col(s) to an incrementing number:
ADMIN_CLAS
ADMIN_CLA1
...
maybe rare, but what happens if you have more than 10 similar column
names? need to code for ADMIN_CL10, ADMIN_CL11, ..., ADMIN_C100,
As William's points out there, all vector attributes are stored in one
file per mapset. This is problematic for:
- backups or easy transfer of a single map to another system
- disk error etc would trash all vectors in mapset, not just the one map
- with many vector maps in the same mapset 32bit users will rapidly hit
the 2gb file size limit.
Is per-map $MAPSET/vector/$MAPNAME/sqlite.db (or similar) possible?
As William's points out there, all vector attributes are stored in one
file per mapset. This is problematic for:
- backups or easy transfer of a single map to another system
- disk error etc would trash all vectors in mapset, not just the one map
- with many vector maps in the same mapset 32bit users will rapidly hit
the 2gb file size limit.
Isn't there a better alternative? This does not sound good at all.
Helena
Is per-map $MAPSET/vector/$MAPNAME/sqlite.db (or similar) possible?
As William's points out there, all vector attributes are stored in one
file per mapset. This is problematic for:
- backups or easy transfer of a single map to another system
- disk error etc would trash all vectors in mapset, not just the one map
- with many vector maps in the same mapset 32bit users will rapidly hit
the 2gb file size limit.
Is per-map $MAPSET/vector/$MAPNAME/sqlite.db (or similar) possible?
This no more limiting than we have now, but with a more robust database and
SQL.
These issues are the same if we use PostgreSQL or MySQL too.
I'm assuming that there is a way to export a table from an SQLite database
and import it into another one. If so, they people who wanted to do joins
could make a database where it is possible.
Michael
__________________________________________
Michael Barton, Professor of Anthropology
School of Human Evolution & Social Change
Center for Social Dynamics & Complexity
Arizona State University
As William's points out there, all vector attributes are stored in one
file per mapset. This is problematic for:
- backups or easy transfer of a single map to another system
- disk error etc would trash all vectors in mapset, not just the one map
- with many vector maps in the same mapset 32bit users will rapidly hit
the 2gb file size limit.
Is per-map $MAPSET/vector/$MAPNAME/sqlite.db (or similar) possible?
Yes. This is discussed in my vector TODO, maybe you can look at FreeGIS.org
.2
Chapter 1.2 Attributes.
The problem with db per map is that you cannot do joins from tables in
more maps and you cannot use db.* commands without database option.
