Ubuntu Manpage: sql - SQL support introduction

NAME

       sql - SQL support introduction

       SQL support introduction

SQL support in GRASS GIS

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

Drivers

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

            |  dbf     | DBF files. Data are stored in DBF files (GRASS 6 default DB backend).  |
       http://shapelib.maptools.org/dbf_api.html
            | sqlite  | SQLite embedded database.  | http://sqlite.org/
            | pg | PostgreSQL RDBMS.  | http://postgresql.org/
            | mysql   | MySQL RDBMS.  | http://mysql.org/
            | mesql   | MySQL embedded database.  | http://mysql.org/
            | odbc    | UnixODBC. (PostgreSQL, Oracle, etc.)  | http://www.unixodbc.org/

NOTES

                      SQL does not support '.' (dots) in table names.

                      Supported table name characters are only:
                     [A-Za-z][A-Za-z0-9_]*

                      A table name must start with a character, not a number.

                      Text-string matching requires the text part to be  'single  quoted'.   When
                     run  from  the  command line multiple queries should be contained in "double
                     quotes". e.g.

                     d.vect map where="individual='juvenile' and area='beach'"

                      An error message such as "dbmi: Protocol error" either indicates an invalid
                     column  name  or an unsupported column type (then the GRASS SQL parser needs
                     to be extended).

                      DBF column names are limited to 10 characters (DBF API definition)

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 subquery expressions from a list (does not work for DBF driver):
       v.db.select 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:
       v.db.select mysites where="id LIKE 'P__'"
       #define wildcard:
       v.db.select 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"

NAME

SQL support in GRASS GIS

Drivers

NOTES

EXAMPLES

SEE ALSO