Simple Approach to Converting GRASS DB-backends
May 23, 2009 metroadminThe 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:
- 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!
- 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.