-
-
Notifications
You must be signed in to change notification settings - Fork 302
/
sql.html
240 lines (187 loc) · 8.32 KB
/
sql.html
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
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
<!-- meta page description: SQL support in GRASS GIS -->
<!-- this file is lib/db/sqlp/sql.html -->
Vector points, lines and areas usually have attribute data that are
stored in DBMS. The attributes are linked to each vector object using a
category number (attribute ID, usually the "cat" integer column). The
category numbers are stored both in the vector geometry and the
attribute table.
<p>
GRASS GIS supports 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 default database driver used by GRASS GIS 8 is SQLite. GRASS GIS
handles multiattribute vector data by default. The <em>db.*</em> set of
commands provides basic SQL support for attribute management, while the
<em>v.db.*</em> set of commands operates on vector maps.
<p>
Note: The list of available database drivers can vary in various binary
distributions of GRASS GIS:
<p>
<table class="border">
<tr><td><a href="grass-sqlite.html">sqlite</a></td><td>Data storage in SQLite database files (default DB backend)</td>
<td><a href="http://sqlite.org/">http://sqlite.org/</a></td></tr>
<tr><td><a href="grass-dbf.html">dbf</a></td><td>Data storage in DBF files</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-pg.html">pg</a></td><td>Data storage in 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>Data storage in 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>Data are stored in 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>Data storage via UnixODBC (PostgreSQL, Oracle, etc.)</td>
<td><a href="http://www.unixodbc.org/">http://www.unixodbc.org/</a></td></tr>
<tr><td><a href="grass-ogr.html">ogr</a></td><td>Data storage in OGR files</td>
<td><a href="http://gdal.org">http://gdal.org/</a></td></tr>
</table>
<h2>NOTES</h2>
<h3>Database table name restrictions</h3>
<ul>
<li> No dots are allowed as SQL does not support '.' (dots) in table names.</li>
<li> Supported table name characters are only: <br>
<div class="code"><pre>
[A-Za-z][A-Za-z0-9_]*
</pre></div></li>
<li> A table name must start with a character, not a number.</li>
<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>
<li> Attempts to use a reserved SQL word (depends on database backend) as
column or table name will cause a "SQL syntax error".</li>
<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>
</ul>
<h3>Database table column types</h3>
The supported types of columns depend on the database backend. However, all backends
should support VARCHAR, INT, DOUBLE PRECISION and DATE.
<h2>EXAMPLES</h2>
<h3>Display of vector feature selected by attribute query</h3>
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>
g.region vector=schools_wake -p
d.mon wx0
d.vect roadsmajor
# all schools
d.vect schools_wake fcol=black icon=basic/diamond col=white size=13
# numerical selection: show schools with capacity of above 1000 kids:
d.vect schools_wake fcol=blue icon=basic/diamond col=white size=13 \
where="CAPACITYTO > 1000"
# string selection: all schools outside of Raleigh
# along with higher level schools in Raleigh
d.vect schools_wake fcol=red icon=basic/diamond col=white size=13 \
where="ADDRCITY <> 'Raleigh' OR (ADDRCITY = 'Raleigh' AND GLEVEL = 'H')"
</pre></div>
<p>
Select all attributes from table where <i>CORECAPACI</i> column values are
smaller than 200 (children):
<div class="code"><pre>
# must be run from the mapset which contains the table
echo "SELECT * FROM schools_wake WHERE CORECAPACI < 200" | db.select input=-
</pre></div>
<p>
<p>
Example of subquery expressions from a list (not supported for DBF driver):
<div class="code"><pre>
v.db.select schools_wake where="ADDRCITY IN ('Apex', 'Wendell')"
</pre></div>
<h3>Example of pattern matching</h3>
<div class="code"><pre>
# field contains string:
# for DBF driver:
v.extract schools_wake out=elementary_schools where="NAMELONG LIKE 'ELEM'"
# for SQLite driver:
v.extract schools_wake out=rivers_noce where="DES LIKE '%NOCE%'"
v.extract schools_wake out=elementary_schools where="NAMELONG LIKE '%ELEM%'"
# 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>
<h3>Example of null handling</h3>
<div class="code"><pre>
v.db.addcolumn 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>
<h3>Update of attributes</h3>
Examples of complex expressions in updates (using <tt>v.db.*</tt>
modules):
<div class="code"><pre>
v.db.addcolumn map=roads column="exprtest double precision"
v.db.update map=roads column=exprtest value="cat/nulltest"
v.db.update map=roads column=exprtest value="cat/nulltest+cat" where="cat=1"
# using data from another column
v.db.update map=roads column=exprtest qcolumn="(cat*100.)/SHAPE_LEN."
</pre></div>
<p>
Examples of more complex expressions in updates (using <tt>db.*</tt>
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>
<h3>Example of changing a SQL type (type casting)</h3>
<i>Note: not supported for <a href="grass-dbf.html">DBF driver</a>.</i>
<p>
North Carolina data set: convert string column to double precision:
<p>
<div class="code"><pre>
# first copy map into current mapset
g.copy vect=geodetic_pts,mygeodetic_pts
v.db.addcolumn 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>
<h3>Example of concatenation of fields</h3>
<i>Note: not supported for <a href="grass-dbf.html">DBF driver</a>.</i>
<div class="code"><pre>
v.db.update vectormap column=column3 qcolumn="column1 || column2"
</pre></div>
<h3>Example of conditions</h3>
Conditions (like if statements) are usually written as CASE statement in SQL:
<div class="code"><pre>
v.db.update vectormap column=species qcolumn="CASE WHEN col1 >= 12 THEN cat else NULL end"
# a more complex example with nested conditions
v.db.update vectormap column=species qcolumn="CASE WHEN col1 >= 1 THEN cat WHEN row = 13 then 0 ELSE NULL end"
</pre></div>
<h2>SEE ALSO</h2>
<em>
<a href="db.connect.html">db.connect</a>,
<a href="db.select.html">db.select</a>,
<a href="db.execute.html">db.execute</a>,
<a href="v.db.connect.html">v.db.connect</a>,
<a href="v.db.select.html">v.db.select</a>,
<a href="v.db.update.html">v.db.update</a>
</em>
<p>
<a href="databaseintro.html">Database management in GRASS GIS</a>,
<a href="database.html">Help pages for database modules</a>
<h2>AUTHOR</h2>
Radmin Blazek