DESCRIPTION

SQL support for GRASS.

Supported is a limited set of SQL functions and operators.

FUNCTIONS

  ALTER TABLE table ADD [COLUMN] columndef
  CREATE TABLE table ( columndefs )
  DROP TABLE table
  SELECT columns FROM table
  SELECT columns FROM table WHERE condition
  DELETE FROM table
  DELETE FROM table WHERE condition
  INSERT INTO table VALUES (value1[,value2,...])
  INSERT INTO table ( column1[,column2,...] ) VALUES (value1[,value2,...])
  UPDATE table SET assignment1[,assignment2,...]
  UPDATE table SET assignment1[,assignment2,...] WHERE condition

OPERATORS

  "="  : equal
  "<"  : smaller than
  "<=" : smaller/equal than
  ">"  : larger than
  ">=" : larger/equal than
  "<>" : not equal
  "~"  : Substring matching

Arithmetic expressions using constants and field values are allowed in condition clauses and in the LHS of assignments. Usual precedence rules and bracketing (using '(' and ')') are supported. Type conversion is performed if necessary (experimental).

Aggregate functions (sum, count, min, max,...) are NOT currently supported in SELECT clauses.

Mathematic functions (sin, cos, exp, log,...) are NOT currently supported in expressions.

Conditions allow boolean expressions using the AND, OR and NOT operators, with the usual precedence rules.

NULLs can be tested by 'colname IS NULL' in conditions. The negation is 'colname NOT NULL'.

NOTES

EXAMPLES

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'" | db.select

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'
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"

You actually don't always have to create/update columns with expressions, but you can use them directly in other commands:

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

SEE ALSO

db.execute, db.select, db.tables, d.vect
SQL reference links

Last changed: $Date: 2005/09/05 08:58:31 $



Help Index