sql(1grass)

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

SQL support in GRASS GIS

6       Vector  points,  lines  and  areas usually have attribute data that are
7       stored in DBMS. The attributes are linked to each vector object using a
8       category  number  (attribute ID, usually the "cat" integer column). The
9       category numbers are  stored  both  in  the  vector  geometry  and  the
10       attribute table.
11
12       GRASS  GIS  supports various RDBMS (Relational database management sys‐
13       tem) and embedded databases. SQL (Structured  Query  Language)  queries
14       are  directly passed to the underlying database system. The set of sup‐
15       ported SQL commands depends on the RDMBS and database driver selected.
16

Database drivers

18       The default database driver used by GRASS GIS 7 is  SQLite.  GRASS  GIS
19       handles multiattribute vector data by default. The db.* set of commands
20       provides basic SQL support for attribute management, while  the  v.db.*
21       set of commands operates on vector maps.
22
23       Note: The list of available database drivers can vary in various binary
24       distributions of GRASS GIS:
25
26       sqlite                                                       Data storage in SQLite database files (default DB backend)   http://sqlite.org/
27
28       dbf                                                          Data storage in DBF files                                    http://shapelib.maptools.org/dbf_api.html
29
30       pg                                                           Data storage in PostgreSQL RDBMS                             http://postgresql.org/
31
32       mysql                                                        Data storage in MySQL RDBMS                                  http://mysql.org/
33
34       odbc                                                         Data storage via UnixODBC (PostgreSQL, Oracle, etc.)         http://www.unixodbc.org/
35
36       ogr                                                          Data storage in OGR files                                    http://gdal.org/
37
38

NOTES

40   Database table name restrictions
41           ·   No dots are allowed as SQL does not support ’.’ (dots) in table
42               names.
43
44           ·   Supported table name characters are only:
45               [A-Za-z][A-Za-z0-9_]*
46
47           ·   A table name must start with a character, not a number.
48
49           ·   Text-string  matching  requires  the  text  part  to be ’single
50               quoted’.  When run  from  the  command  line  multiple  queries
51               should be contained in "double quotes". e.g.
52               d.vect map where="individual=’juvenile’ and area=’beach’"
53
54           ·   Attempts  to use a reserved SQL word (depends on database back‐
55               end) as column or table name will cause a "SQL syntax error".
56
57           ·   An error message such as "dbmi: Protocol  error"  either  indi‐
58               cates  an  invalid  column  name  or an unsupported column type
59               (then the GRASS SQL parser needs to be extended).
60
61           ·   DBF column names are limited to 10 characters (DBF API  defini‐
62               tion).
63
64   Database table column types
65       The supported types of columns depend on the database backend. However,
66       all backends should support VARCHAR, INT, DOUBLE PRECISION and DATE.
67

EXAMPLES

69   Display of vector feature selected by attribute query
70       Display all vector points except for LAMAR valley and  extensive  trap‐
71       ping (brackets are superfluous in this example):
72       g.region vector=schools_wake -p
73       d.mon wx0
74       d.vect roadsmajor
75       # all schools
76       d.vect schools_wake fcol=black icon=basic/diamond col=white size=13
77       # numerical selection: show schools with capacity of above 1000 kids:
78       d.vect schools_wake fcol=blue icon=basic/diamond col=white size=13 \
79           where="CAPACITYTO > 1000"
80       # string selection: all schools outside of Raleigh
81       #   along with higher level schools in Raleigh
82       d.vect schools_wake fcol=red icon=basic/diamond col=white size=13 \
83           where="ADDRCITY <> ’Raleigh’ OR (ADDRCITY = ’Raleigh’ AND GLEVEL = ’H’)"
84
85       Select  all  attributes  from  table where CORECAPACI column values are
86       smaller than 200 (children):
87       # must be run from the mapset which contains the table
88       echo "SELECT * FROM schools_wake WHERE CORECAPACI < 200" | db.select input=-
89
90       Example of subquery expressions from a  list  (not  supported  for  DBF
91       driver):
92       v.db.select schools_wake where="ADDRCITY IN (’Apex’, ’Wendell’)"
93
94   Example of pattern matching
95       # field contains string:
96       #  for DBF driver:
97       v.extract schools_wake out=elementary_schools where="NAMELONG LIKE ’ELEM’"
98       #  for SQLite driver:
99       v.extract schools_wake out=rivers_noce where="DES LIKE ’%NOCE%’"
100       v.extract schools_wake out=elementary_schools where="NAMELONG LIKE ’%ELEM%’"
101       # match exactly number of characters (here: 2), does not work for DBF driver:
102       v.db.select mysites where="id LIKE ’P__’"
103       #define wildcard:
104       v.db.select mysites where="id LIKE ’P%’"
105
106   Example of null handling
107       v.db.addcolumn map=roads col="nulltest int"
108       v.db.update map=roads col=nulltest value=1 where="cat > 2"
109       d.vect roads where="nulltest is null"
110       v.db.update map=roads col=nulltest value=2 where="cat <= 2"
111
112   Update of attributes
113       Examples of complex expressions in updates (using v.db.*  modules):
114       v.db.addcolumn map=roads column="exprtest double precision"
115       v.db.update map=roads column=exprtest value="cat/nulltest"
116       v.db.update map=roads column=exprtest value="cat/nulltest+cat" where="cat=1"
117       # using data from another column
118       v.db.update map=roads column=exprtest qcolumn="(cat*100.)/SHAPE_LEN."
119
120       Examples of more complex expressions in updates (using db.*  modules):
121       echo "UPDATE roads SET exprtest=null"
122       echo "UPDATE roads SET exprtest=cat/2" | db.execute
123       echo "UPDATE roads SET exprtest=cat/2+cat/3" | db.execute
124       echo "UPDATE roads SET exprtest=NULL WHERE cat>2" | db.execute
125       echo "UPDATE roads SET exprtest=cat/3*(cat+1) WHERE exprtest IS NULL" | db.execute"
126
127       Instead  of  creating  and updating new columns with an expression, you
128       can use the expression directly in a command:
129       d.vect roads where="(cat/3*(cat+1))>8"
130       d.vect roads where="cat>exprtest"
131
132   Example of changing a SQL type (type casting)
133       Note: not supported for DBF driver.
134
135       North Carolina data set: convert string column to double precision:
136
137       # first copy map into current mapset
138       g.copy vect=geodetic_pts,mygeodetic_pts
139       v.db.addcolumn mygeodetic_pts col="zval double precision"
140       # the ’z_value’ col contains ’N/A’ strings, not to be converted
141       v.db.update mygeodetic_pts col=zval \
142                   qcol="CAST(z_value AS double precision)" \
143                   where="z_value <> ’N/A’"
144
145   Example of concatenation of fields
146       Note: not supported for DBF driver.
147       v.db.update vectormap column=column3 qcolumn="column1 || column2"
148
149   Example of conditions
150       Conditions (like if statements) are usually written as  CASE  statement
151       in SQL:
152       v.db.update vectormap column=species qcolumn="CASE WHEN col1 >= 12 THEN cat else NULL end"
153       # a more complex example with nested conditions
154       v.db.update vectormap column=species qcolumn="CASE WHEN col1 >= 1 THEN cat WHEN row = 13 then 0 ELSE NULL end"
155

SQL support in GRASS GIS

Database drivers

NOTES

EXAMPLES

SEE ALSO