1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
|
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>SQL support in GRASS GIS</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="grassdocs.css" type="text/css">
</head>
<!-- This file is lib/db/sqlp/description.html -->
<body bgcolor="white">
<img src="grass_logo.png" alt="GRASS logo"><hr align=center size=6 noshade>
<!-- meta page description: SQL support introduction -->
<h2>SQL support in GRASS GIS</h2>
GRASS can use various RDBMS
(<a href="http://en.wikipedia.org/wiki/Relational_database_management_system">Relational
database management system</a>) and embedded databases. SQL
(<a href="http://en.wikipedia.org/wiki/Sql">Structured Query
Language</a>) queries are directly passed to the underlying database
system. The set of supported SQL commands depends on the RDMBS and
database driver selected.
<h2>Database drivers</h2>
The list of available drivers can vary in various binary
distributions of GRASS.
<p>
<table border=1 >
<tr><td><a href="grass-dbf.html">dbf</a></td><td>DBF files. Data are stored in DBF files (GRASS 6 default DB backend).</td>
<td><a href="http://shapelib.maptools.org/dbf_api.html">http://shapelib.maptools.org/dbf_api.html</a></td></tr>
<tr><td><a href="grass-sqlite.html">sqlite</a></td><td>SQLite embedded database.</td>
<td><a href="http://sqlite.org/">http://sqlite.org/</a></td></tr>
<tr><td><a href="grass-pg.html">pg</a></td><td>PostgreSQL RDBMS.</td>
<td><a href="http://postgresql.org/">http://postgresql.org/</a></td></tr>
<tr><td><a href="grass-mysql.html">mysql</a></td><td>MySQL RDBMS.</td>
<td><a href="http://mysql.org/">http://mysql.org/</a></td></tr>
<tr><td><a href="grass-mesql.html">mesql</a></td><td>MySQL embedded database.</td>
<td><a href="http://mysql.org/">http://mysql.org/</a></td></tr>
<tr><td><a href="grass-odbc.html">odbc</a></td><td>UnixODBC. (PostgreSQL, Oracle, etc.)</td>
<td><a href="http://www.unixodbc.org/">http://www.unixodbc.org/</a></td></tr>
</table>
<h2>NOTES</h2>
<ul>
<li> SQL does not support '.' (dots) in table names.
<li> Supported table name characters are only: <br>
<div class="code"><pre>[A-Za-z][A-Za-z0-9_]*</pre></div>
<li> A table name must start with a character, not a number.
<li> 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.<br>
<div class="code"><pre>
d.vect map where="individual='juvenile' and area='beach'"
</pre></div>
<li> An error message such as "<tt>dbmi: Protocol
error</tt>" either indicates an invalid column name or an
unsupported column type (then the GRASS SQL parser needs to be
extended).</li>
<li> DBF column names are limited to 10 characters (DBF API definition).</li>
<li> Attempts to use a reserved SQL word (depends on database backend) as
column or table name will cause a "SQL syntax error".</li>
</ul>
<h2>EXAMPLES</h2>
Display all vector points except for <i>LAMAR</i> valley
and <i>extensive trapping</i> (brackets are superfluous in this
example):
<div class="code"><pre>
d.vect trapping_sites_points fcol=black icon=basic/diamond col=white size=13 \
where="valley <> 'LAMAR' OR (valley = 'LAMAR' AND description = 'extensive trapping')"
</pre></div>
<p>
Select all attributes from table where <i>str1</i> column values are not 'No
Name':
<div class="code"><pre>
echo "SELECT * FROM archsites WHERE str1 <> 'No Name'" | db.select
</pre></div>
<p>
<p><b>Example</b> of subquery expressions from a list (does not work for DBF driver):
<div class="code"><pre>
v.db.select mysites where="id IN ('P04', 'P05')"
</pre></div>
<p><b>Example</b> of pattern matching:
<div class="code"><pre>
# 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%'"
</pre></div>
<p><b>Example</b> of null handling:
<div class="code"><pre>
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"
</pre></div>
<p><b>Examples</b> of complex expressions in updates (using v.db.* modules):
<div class="code"><pre>
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
</pre></div>
<p><b>Examples</b> of complex expressions in updates (using db.* modules):
<div class="code"><pre>
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"
</pre></div>
<p>
Instead of creating and updating new columns with an expression,
you can use the expression directly in a command:
<div class="code"><pre>
d.vect roads where="(cat/3*(cat+1))>8"
d.vect roads where="cat>exprtest"
</pre></div>
<p><b>Example</b> of changing a SQL type (type casting, does not work for DBF driver):
<div class="code"><pre>
# 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'"
</pre></div>
<p><b>Example</b> of concatenating fields (does not work for DBF driver):
<div class="code"><pre>
v.db.update vectormap column=column3 qcolumn="column1 || column2"
</pre></div>
<h2>SEE ALSO</h2>
<em>
<a href="db.select.html">db.select</a>,
<a href="db.execute.html">db.execute</a>,
<a href="v.db.select.html">v.db.select</a>,
<a href="v.db.update.html">v.db.update</a>
</em>
<p>
<em>
<a href="databaseintro.html">Database management in GRASS GIS</a>,
<a href="database.html">Help pages for database modules</a>
</em>
<p>
<i>Last changed: $Date: 2014-02-15 23:27:05 +0100 (Sat, 15 Feb 2014) $</i>
<hr>
<br>
<a href="index.html">Main index</a> -
<a href="database.html">database index</a> -
<a href="full_index.html">full index</a>
<p>© 2008-2014 <a href="http://grass.osgeo.org">GRASS Development Team</a>
</body>
</html>
|
