sql(1) - f14

1sql(1)                        Grass User's Manual                       sql(1)
2
3
4

SQL support in GRASS GIS

6       GRASS  can  use  various RDBMS and embedded databases.  SQL queries are
7       directly passed to the underlying database system.   The  set  of  sup‐
8       ported SQL commands depends on the RDMBS and driver selected.
9

Drivers

11       The  list of available drivers can vary in various binary distributions
12       of GRASS.
13
14            |  dbf     |  DBF  files.  Data  are  stored  in  DBF  files.    |
15       http://shapelib.maptools.org/dbf_api.html
16            | sqlite  | SQLite embedded database.  | http://sqlite.org/
17            | pg | PostgreSQL RDBMS.  | http://postgresql.org/
18            | mysql   | MySQL RDBMS.  | http://mysql.org/
19            | mesql   | MySQL embedded database.  | http://mysql.org/
20            |    odbc    |    UnixODBC.    (PostgreSQL,   Oracle,   etc.)    |
21       http://www.unixodbc.org/
22

NOTES

24                      SQL does not support '.' (dots) in table names.
25
26                      Supported table name characters are only:
27                     [A-Za-z][A-Za-z0-9_]*
28
29                      A table name must start with a character, not a number.
30
31                      Text-string matching requires the text part to be  'sin‐
32                     gle  quoted'.   When  run  from the command line multiple
33                     queries should be contained in "double quotes". e.g.
34
35                     d.vect map where="individual='juvenile' and area='beach'"
36
37
38                      An error message such as "dbmi: Protocol  error"  either
39                     indicates an invalid column name or an unsupported column
40                     type (then the GRASS SQL parser needs to be extended).
41
42                      DBF column names are limited to 10 characters  (DBF  API
43                     definition)
44

EXAMPLES

46       Display  all  vector points except for LAMAR valley and extensive trap‐
47       ping (brackets are superfluous in this example):
48       d.vect trapping_sites_points  fcol=black  icon=basic/diamond  col=white
49       size=13 \
50           where="valley  <>  'LAMAR'  OR  (valley = 'LAMAR' AND description =
51       'extensive trapping')"
52
53
54       Select all attributes from table where str1 column values are  not  'No
55       Name':
56       echo "SELECT * FROM archsites WHERE str1 <> 'No Name'" | db.select
57
58
59       Example  of  subquery  expressions  from  a list (does not work for DBF
60       driver):
61       v.db.select mysites where="id IN ('P04', 'P05')"
62
63
64       Example of pattern matching:
65       # match exactly number of characters (here: 2), does not work  for  DBF
66       driver:
67       v.db.select mysites where="id LIKE 'P__'"
68       #define wildcard:
69       v.db.select mysites where="id LIKE 'P%'"
70
71
72       Example of null handling:
73       v.db.addcol map=roads col="nulltest int"
74       v.db.update map=roads col=nulltest value=1 where="cat > 2"
75       d.vect roads where="nulltest is null"
76       v.db.update map=roads col=nulltest value=2 where="cat <= 2"
77
78
79       Examples of complex expressions in updates (using v.db.* modules):
80       v.db.addcol map=roads col="exprtest double precision"
81       v.db.update map=roads col=exprtest value=cat/nulltest
82       v.db.update map=roads col=exprtest value=cat/nulltest+cat where=cat=1
83
84
85       Examples of complex expressions in updates (using db.* modules):
86       echo "UPDATE roads SET exprtest=null"
87       echo "UPDATE roads SET exprtest=cat/2" | db.execute
88       echo "UPDATE roads SET exprtest=cat/2+cat/3" | db.execute
89       echo "UPDATE roads SET exprtest=NULL WHERE cat>2" | db.execute
90       echo "UPDATE roads SET exprtest=cat/3*(cat+1) WHERE exprtest IS NULL" |
91       db.execute"
92
93
94       Instead of creating and updating new columns with  an  expression,  you
95       can use the expression directly in a command:
96       d.vect roads where="(cat/3*(cat+1))>8"
97       d.vect roads where="cat>exprtest"
98
99

SQL support in GRASS GIS

Drivers

NOTES

EXAMPLES

SEE ALSO