Simple Approach to Converting GRASS DB-backends

The current default database back-end used by the GRASS vector model is DBF (as of GRASS 6.5), however this is probably going to be changed (to SQLite) in GRASS 7. The DBF back-end works OK, however it tends to be very sensitive (i.e. breaks) when reserved words occur in column names or portions of a query. Complex UPDATE statements don't work, and just about anything more complex than a simple SELECT statement usually results in an error. Switching to the SQLite (or Postgresql, etc.) back-end solves most of these problems.

Currently GRASS uses a single SQLite (file-based) database per mapset-- convenient if you are interested in joining attribute tables between vectors; but not set-in-stone as the final approach that will be used by default in GRASS 7. Regardless, converting the back-end is a fairly simple matter. Finally, taking the time to convert to an SQLite or Postgresql back-end will undoubtably save you time and sanity if you ever find yourself working with vector+attribute data on a regular basis. Having access to a complete implementation of SQL can make extracting, summarizing, joining, and re-formatting (column names, types, etc.) tabular data much simpler than what is available in the DBF back-end. Also, there are several convenient graphical SQLite managers available, such as SQLite manager, SQLite data browser, and SQLite Admin.

Some strategies are illustrated though example below. Please read the inline comments, as these examples used in the wrong context could result in a loss of data.

• The simplest case involves a new mapset, where no vector data has been created or imported. A single command is all that is needed to convert from the default DBF back-end to SQLite:

db.connect driver=sqlite database='$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db'

• If you already have some vector data in a mapset, you can convert these files to a new back-end by changing the current database connection, and then copying the vector files. Be sure to backup your mapset before doing this!

# define junk label and mapset name
junk="_00000"
mapset=the_mapset

# reset database connection for this mapset
db.connect driver=sqlite database='$GISDBASE/$LOCATION_NAME/$MAPSET/sqlite.db'

# copy vectors, this will re-make their tables in sqlite file
# do by mapset for optimal safety
for x in `g.mlist type=vect mapset=${mapset}`
do
g.copy vect=$x,${x}${junk}
done

# remove originals
for x in `g.mlist type=vect mapset=${mapset} exclude="*${junk}"`
do
g.remove vect=$x
done

# remove junk from new names, matching the original names
for x in `g.mlist type=vect mapset=${mapset}`
do
new=`echo $x | sed s/${junk}//g`
g.rename vect=${x},${new}
done

• I have found a couple instances where I had been tinkering around with the database connection for individual vectors in a mapset, thus causing errors when attempting the above operation. Re-defining the database connection parameters for each vector solved this problem. Note that the below example is only useful (and safe) when the database connections were changed, but the original tables still exist in the default DBF back-end.

# ok, so you accidentally changed the back-end, but did not copy any of the attribute tables:
# just reset it back to the default for now:
db.connect driver=dbf database='$GISDBASE/$LOCATION_NAME/$MAPSET/dbf/'


# check to see that all of your maps have the expected database connections:
for x in `g.mlist type=vect mapset=${mapset}`; do v.db.connect -g $x  ; done

# if some of them are defined incorrectly, reset all of them
# BE CAREFUL!!!
for x in `g.mlist type=vect mapset=${mapset}`
do
v.db.connect -o map=$x table=$x database='$GISDBASE/$LOCATION_NAME/$MAPSET/dbf/' driver=dbf key=cat