Simple Approach to Converting GRASS DB-backends

Submitted by dylan on Sat, 2009-05-23 21:32.

 
Premise:
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
    done

Is this done on the command line?

Hi

How are you implementing these commands. Is this all done via the command line in GRASS, or are use using a programing language?

implementation

Hi Kurt,

These examples are a mixture of the bash scripting language (common shell environment on most unix-like operating systems) and GRASS commands. You can try these out on a unix-like OS by starting GRASS from the command line.

Implementation

Hi,

I tried it but didn't seem to get any joy, unless the new sglite databases are hidden some where. The dbf databases are still there. Running the script on the command line didn't indicate any errors. So I'm not sure what to make of it.

I tried importing a shape file into a new location and map set, after switching the database driver from dbf to sqlite, but that gave an error message.

I used:
v.in.ogr -o -e dsn=/Users/kurtsprings/grassdata/NED/03604870/03604870.shp output=steam_water min_area=0.0001 snap=-1 --overwrite

The message I get is:

Over-riding projection check
DBMI-SQLite driver error:
Unable to open database: unable to open database file
Unable to open database by driver

Unable open database by driver

Layer: 03604870

Any thoughts?

Kurt

Re: Implementation

Hi Kurt,

The conversion is a three step process-- 1) switch the default back-end, 2) make a copy of all vector maps, 3) remove the originals.

The first thing I would do is to make sure that all of your vector maps have correct database connections. You can loop over a list of maps, and print their database definitions with the following code:

# check for multiple database connection types
for x in `g.mlist type=vect mapset=${mapset}`; do v.db.connect -g $x  ; done | awk '{print $2, $5}'
...
# looks like there is a mixture of database connections
# in this location/mapset
valleys_grown sqlite
wp sqlite
wp1 sqlite
wp_i sqlite
wp_range sqlite
az_100km_grid dbf

Next you will need to make sure that those connections are correct. If they are not correct, and you know that all vector maps are connected to DBF files, then you can use something like the following code:

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 ; done

If you are still having problems, I would recommend asking on the GRASS mailing list.

GRASS 7 uses SQLite as default

Small note: in GRASS 7 the default DB backend is SQLite since last year. There are plans to add support for per-map-SQLite-DB as alternative to the current per-mapset-SQLite-DB.

corrections

Thanks for the correction Markus. I should have done some more homework before posting that!