GRASS 5.7: Attribute management with connections to DBMS

README: GRASS 5.7 is currently under development. In case the examples described here do not work properly, you are kindly invited to send us further examples and/or code bugfixes/enhancements.

To get debug information, set variable (set to 0 for debug silence) to 1..5:

g.gisenv set="DEBUG=3"

Attribute management introduction
[UP]

The attribute management was completely changed in GRASS 5.7 to achieve multi-attribute capabilities managed within external databases. The former dig_cats/ files are not used any more and all vector attributes are stored in external database. Connection with database is done through DBMI library (DataBase Management Interface) with its integrated drivers. At time of this writing following DBMI drivers for attribute storage are available:

DBF file (default)
ODBC connected RDBMS (e.g. Oracle, mySQL, PostgreSQL etc)
PostgreSQL database
mySQL database

The connection of a vector map to an attribute table (except for SHAPE) is internally defined in a dbln file (an ASCII file in the vector map directory). This file is created when importing/converting a map into GRASS 5.7 or can be generated with 'v.db.connect'. The current connection for a map can be printed with

v.db.connect -p vectmap

Managing the default settings
[UP]

Per default vector map attributes are stored in DBF tables. This default definition can be modified with:

v.database

Possible settings are (examples):

DBF: driver=dbf
database='$GISDBASE/$LOCATION_NAME/$MAPSET/dbf'
ODBC: driver=odbc
database=grass57test
PostgreSQL: driver=pg
database='host=pgserver.itc.it,dbname=grass57test,user=name'
mySQL: driver=mysql
database=?

The db.* modules are independent from the v.* modules and just a tool collection to modify attribute tables.
The connection settings for the db.* modules (Driver and Database) can be defined/modified with 'db.connect'. To set it to the same settings as the v.* modules use per default, run:

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

Attributes stored in DBF file
[UP]

Example: SHAPE file with attributes in DBF file which was registered with 'v.external' (see here).

You can verify the connection of a vector map to a table:

v.db.connect -p markveggy.shp

which should print the database connection to the related DBF file.

The attribute column names and types can be printed with:

v.db.connect -c vectmap

in case the vector map is connected to an attribute table.

If you want to use the db.* commands, you first have to connect them to the DBF database:

db.connect driver=dbf database='$GISDBASE/$LOCATION_NAME/$MAPSET/dbf'
db.tables -p

... which prints the available tables.

Attributes stored in external database (PostgreSQL) connected through ODBC
[UP]

Possible communication between GRASS 5.7 and PostgreSQL database for attribute management (ODBC can connect to other RDBMS as well):

GRASS module <-> <--> ODBC Interface <--> RDBMS

GRASS DBMI driver unixODBC ODBC driver PostgreSQL

Oracle

...

Example: Connection of SHAPE file with attributes in PostgreSQL table which get's connected through ODBC to GRASS 5.7. The name of the PostgreSQL database is 'grass57test' which contains the imported DBF file as table 'markveggy'.

Some steps are required:

Configure ODBC driver for selected database (manually or with 'ODBCConfig'). ODBC drivers are defined in /etc/odbcinst.ini. Here is example:

 [PostgreSQL]
 Description     = ODBC for PostgreSQL
 Driver          = /usr/lib/libodbcpsql.so
 Setup           = /usr/lib/libodbcpsqlS.so
 FileUsage       = 1

Create DSN (data source name). The DSN is used as database name in db.* modules. Then DSN must be defined in $HOME/.odbc.ini (for this user only) or in /etc/odbc.ini for (for all users) [watch out for the database name which appears twice and also for the PostgreSQL protocol version]. Omit blanks at the beginning of lines:

 [grass57test]
 Description             = PostgreSQL
 Driver                  = PostgreSQL
 Trace                   = No
 TraceFile               =
 Database                = grass57test
 Servername              = localhost
 UserName                = neteler
 Password                =
 Port                    = 5432
 Protocol                = 7.2
 ReadOnly                = No
 RowVersioning           = No
 ShowSystemTables        = No
 ShowOidColumn           = No
 FakeOidIndex            = No
 ConnSettings            =

http://www.unixodbc.org/odbcinst.html

http://www.unixodbc.org/doc/UserManual/

To find out about your PostgreSQL protocol, run
cat /var/lib/pgsql/data/PG_VERSION
or
psql -V

Now create a new database if not yet existing:

db.createdb driver=odbc database=grass57test

Now store the table 'markveggy.dbf' (in current directory) into PostgreSQL through ODBC:

db.connect driver=odbc database=grass57test
db.copy from_driver=dbf from_database=./ from_table=markveggy \
 to_driver=odbc to_database=grass57test to_table=markveggy

Next link map to attribute table:

v.db.connect map=markveggy.shp table=markveggy key=VEGCNP_ID \
             database=grass57test driver=odbc
v.db.connect -p

Finally a test: Here we should see the table columns (if the ODBC connection works):
```
db.tables -p
db.columns table=markveggy
```
Now the table name 'markveggy' should appear.
Doesn't work? Check with 'isql <databasename>' if the ODBC-PostgreSQL connection is really established.

Note that you can also connect mySQL, Oracle etc. through ODBC to GRASS 5.7. You can also check the vector map itself concerning a current link to a table:

v.db.connect -p markveggy.shp

which should print the database connection through ODBC to the defined RDBMS.

Attributes stored in PostgreSQL database table directly connected
[UP]

Example: Connection of SHAPE file with attributes in PostgreSQL database table directly connected to GRASS 5.7.

If not yet done, create a new database if not yet existing ('-h host' defines the PostgreSQL server host if not localhost). Here we assume that PostgreSQL runs on machine 'pgserver':

db.connect driver=pg database="host=pgserver,dbname=grass57test,user=neteler"
createdb -h pgserver grass57test

Now store the table 'markveggy.dbf' into PostgreSQL:

db.copy from_driver=dbf from_database=./ from_table=markveggy \
 to_driver=pg to_database="host=pgserver,dbname=grass57test,user=neteler" \
 to_table=markveggy

This will open a password dialog for the PostgreSQL database.

Next we link the vector geometry to the attributes table:

v.db.connect map=markveggy.shp table=markveggy key=VEGCNP_ID \
             database='pgserver,dbname=grass57test,user=neteler' driver=pg

Connect GRASS 5.7 to the table:

db.tables -p
db.describe -c markveggy

If, depending on the security settings of the PG database 'grass57test' a password is required, a pop-up dialog window will ask you (alternatively you could supply ',password=secretword' in the 'database' parameter of 'v.db.connect' and 'db.connect', which is not recommended for security reasons). This should connect the map to the table. 'db.describe' will print table name and columns of the table 'markveggy' which is stored as PostgreSQL table.

Doesn't work? Check with 'psql -l' if the PostgreSQL connection generally established. You can also check the vector map itself concerning a current link to a table:

v.db.connect -p markveggy.shp

which should print the database connection to PostgreSQL.

Attributes stored in mySQL database table
[UP]

Example: SHAPE file with attributes in mySQL database table using mySQL driver.

TODO (will be similar to PostgreSQL).

Changing table column types with StarOffice
[UP]

Sometimes the ID field in a database table (e.g. .dbf) is of a wrong type. It must be converted to Integer (be sure that the ID values are in the range of Integer, float IDs are not supported). To change the type of a table column you can use StarOffice. Start it without parameters, then:

File -> New -> Database
                 [Properties]
                   * Type -> dBase
                     Directory -> [Browse, select directory]
                   * Tables -> check if the table is visible
                     -> OK button

Now you reach the Database tool which provides "Forms", "Reports", "Queries" and "Tables". To change column types,

-> Double click "Tables": then you should see your table as icon
   -> right mouse button on the table icon
      -> select "Table Design" from the right mouse button menu

Then select the ID column and change it's type to "Number" (alias Integer). Save the table. Now GRASS 5.7 can use the ID column as it is of Integer type.

NOTE: Since April 2004 'ALTER TABLE table ADD COLUMN col type' should work, so that 'db.execute' will do the same job to add a column.

Changing table column types with OpenOffice
[UP]

Sometimes the ID field in a database table (e.g. .dbf) is of a wrong type. It must be converted to Integer (be sure that the ID values are in the range of Integer, float IDs are not supported). To change the type of a table column you can use OpenOffice. Start it without parameters, then:

Tools -> Data Sources
            -> New Data Source
                  [General] 
                    * Database type: dBase
                    * Data source URL [Browser: ...]
                          -> select directory
                  [Tables] 
                    * check if the table is visible 
                          -> OK button
View -> Data
             * Sources
                     -> select your data source (probably "Data source 1")
             * Tables
                   * your table 
                        * click right mouse button on table
                             -> Edit table

Select the column and change the type to "Number" (alias Integer). Save the table. Now GRASS 5.7 can use the ID column as it is of Integer type.
NOTE: Since April 2004 'ALTER TABLE table ADD COLUMN col type' should work, so that 'db.execute' will do the same job to add a column.

Further Links (related software, SQL reference etc).

GRASS module <->	<-->	ODBC Interface	<-->	RDBMS
GRASS	DBMI driver	unixODBC	ODBC driver	PostgreSQL
				Oracle
				...