GRASS logo

Note: A new GRASS GIS stable version has been released: GRASS GIS 7, available here. And see the new manual page here

SQL support in GRASS GIS

GRASS can use various RDBMS (Relational database management system) and embedded databases. SQL (Structured Query Language) queries are directly passed to the underlying database system. The set of supported SQL commands depends on the RDMBS and database driver selected.

Database drivers

The list of available drivers can vary in various binary distributions of GRASS.

dbfDBF files. Data are stored in DBF files (GRASS 6 default DB backend).
sqliteSQLite embedded database.
pgPostgreSQL RDBMS.
mesqlMySQL embedded database.
odbcUnixODBC. (PostgreSQL, Oracle, etc.)



Display all vector points except for LAMAR valley and extensive trapping (brackets are superfluous in this example):
d.vect trapping_sites_points fcol=black icon=basic/diamond col=white size=13 \
    where="valley <> 'LAMAR' OR (valley = 'LAMAR' AND description = 'extensive trapping')"

Select all attributes from table where str1 column values are not 'No Name':

echo "SELECT * FROM archsites WHERE str1 <> 'No Name'" |

Example of subquery expressions from a list (does not work for DBF driver): mysites where="id IN ('P04', 'P05')"

Example of pattern matching:

# field contains string:
#  for DBF driver:
v.extract rivers out=rivers_noce where="DES LIKE 'NOCE'"
#  for SQLite driver:
v.extract rivers out=rivers_noce where="DES LIKE '%NOCE%'"

# match exactly number of characters (here: 2), does not work for DBF driver: mysites where="id LIKE 'P__'"

#define wildcard: mysites where="id LIKE 'P%'"

Example of null handling:

v.db.addcol map=roads col="nulltest int"
v.db.update map=roads col=nulltest value=1 where="cat > 2"
d.vect roads where="nulltest is null"
v.db.update map=roads col=nulltest value=2 where="cat <= 2"

Examples of complex expressions in updates (using v.db.* modules):

v.db.addcol map=roads col="exprtest double precision"
v.db.update map=roads col=exprtest value=cat/nulltest
v.db.update map=roads col=exprtest value=cat/nulltest+cat where=cat=1

Examples of complex expressions in updates (using db.* modules):

echo "UPDATE roads SET exprtest=null"
echo "UPDATE roads SET exprtest=cat/2" | db.execute
echo "UPDATE roads SET exprtest=cat/2+cat/3" | db.execute
echo "UPDATE roads SET exprtest=NULL WHERE cat>2" | db.execute
echo "UPDATE roads SET exprtest=cat/3*(cat+1) WHERE exprtest IS NULL" | db.execute"

Instead of creating and updating new columns with an expression, you can use the expression directly in a command:

d.vect roads where="(cat/3*(cat+1))>8"
d.vect roads where="cat>exprtest"

Example of changing a SQL type (type casting, does not work for DBF driver):

# North Carolina data set: convert string column to double precision
#  copy map into current mapset
g.copy vect=geodetic_pts,mygeodetic_pts
v.db.addcol mygeodetic_pts col="zval double precision"

# the 'z_value' col contains 'N/A' strings, not to be converted
v.db.update mygeodetic_pts col=zval \
            qcol="CAST(z_value AS double precision)" \
            where="z_value <> 'N/A'"

Example of concatenating fields (does not work for DBF driver):

v.db.update vectormap column=column3 qcolumn="column1 || column2"

SEE ALSO, db.execute,, v.db.update

Database management in GRASS GIS, Help pages for database modules

Last changed: $Date: 2014-02-15 14:27:05 -0800 (Sat, 15 Feb 2014) $

Main index - database index - full index

© 2008-2014 GRASS Development Team