Debian Codegraph @

lib/db/sqlp/sql.html

raw
1 <!-- meta page description: SQL support in GRASS -->
2
3 <!-- this file is lib/db/sqlp/sql.html -->
4
5 Vector points, lines and areas usually have attribute data that are
6 stored in DBMS. The attributes are linked to each vector object using a
7 category number (attribute ID, usually the "cat" integer column). The
8 category numbers are stored both in the vector geometry and the
9 attribute table.
10 <p>
11 GRASS supports various RDBMS
12 (<a href="https://en.wikipedia.org/wiki/Relational_database_management_system">Relational
13 database management system</a>) and embedded databases. SQL
14 (<a href="https://en.wikipedia.org/wiki/Sql">Structured Query
15 Language</a>) queries are directly passed to the underlying database
16 system. The set of supported SQL commands depends on the RDMBS and
17 database driver selected.
18
19 <h2>Database drivers</h2>
20
21 The default database driver used by GRASS 8 is SQLite. GRASS
22 handles multiattribute vector data by default. The <em>db.*</em> set of
23 commands provides basic SQL support for attribute management, while the
24 <em>v.db.*</em> set of commands operates on vector maps.
25
26 <p>
27 Note: The list of available database drivers can vary in various binary
28 distributions of GRASS:
29 <p>
30 <table class="border">
31 <tr><td><a href="grass-sqlite.html">sqlite</a></td><td>Data storage in SQLite database files (default DB backend)</td>
32 <td><a href="https://sqlite.org/">https://sqlite.org/</a></td></tr>
33
34 <tr><td><a href="grass-dbf.html">dbf</a></td><td>Data storage in DBF files</td>
35 <td><a href="http://shapelib.maptools.org/dbf_api.html">http://shapelib.maptools.org/dbf_api.html</a></td></tr>
36
37 <tr><td><a href="grass-pg.html">pg</a></td><td>Data storage in PostgreSQL RDBMS</td>
38 <td><a href="https://postgresql.org/">https://postgresql.org/</a></td></tr>
39
40 <tr><td><a href="grass-mysql.html">mysql</a></td><td>Data storage in MySQL RDBMS</td>
41 <td><a href="https://www.mysql.com/">https://www.mysql.com/</a></td></tr>
42 <!--
43 <tr><td><a href="grass-mesql.html">mesql</a></td><td>Data are stored in MySQL embedded database</td>
44 <td><a href="https://www.mysql.com/">https://www.mysql.com/</a></td></tr>
45 -->
46 <tr><td><a href="grass-odbc.html">odbc</a></td><td>Data storage via UnixODBC (PostgreSQL, Oracle, etc.)</td>
47 <td><a href="https://www.unixodbc.org/">https://www.unixodbc.org/</a></td></tr>
48
49 <tr><td><a href="grass-ogr.html">ogr</a></td><td>Data storage in OGR files</td>
50 <td><a href="https://gdal.org">https://gdal.org/</a></td></tr>
51 </table>
52
53 <h2>NOTES</h2>
54
55 <h3>Database table name restrictions</h3>
56
57 <ul>
58 <li> No dots are allowed as SQL does not support '.' (dots) in table names.</li>
59 <li> Supported table name characters are only: <br>
60 <div class="code"><pre>
61 [A-Za-z][A-Za-z0-9_]*
62 </pre></div></li>
63 <li> A table name must start with a character, not a number.</li>
64 <li> Text-string matching requires the text part to be 'single quoted'.
65 When run from the command line multiple queries should be contained
66 in "double quotes". e.g.<br>
67 <div class="code"><pre>
68 d.vect map where="individual='juvenile' and area='beach'"
69 </pre></div></li>
70 <li> Attempts to use a reserved SQL word (depends on database backend) as
71 column or table name will cause a "SQL syntax error".</li>
72 <li> An error message such as &quot;<code>dbmi: Protocol
73 error</code>&quot; either indicates an invalid column name or an
74 unsupported column type (then the GRASS SQL parser needs to be
75 extended).</li>
76 <li> DBF column names are limited to 10 characters (DBF API definition).</li>
77 </ul>
78
79 <h3>Database table column types</h3>
80
81 The supported types of columns depend on the database backend. However, all backends
82 should support VARCHAR, INT, DOUBLE PRECISION and DATE.
83
84 <h2>EXAMPLES</h2>
85
86 <h3>Display of vector feature selected by attribute query</h3>
87 Display all vector points except for <i>LAMAR</i> valley
88 and <i>extensive trapping</i> (brackets are superfluous in this
89 example):
90
91 <div class="code"><pre>
92 g.region vector=schools_wake -p
93 d.mon wx0
94 d.vect roadsmajor
95
96 # all schools
97 d.vect schools_wake fcol=black icon=basic/diamond col=white size=13
98
99 # numerical selection: show schools with capacity of above 1000 kids:
100 d.vect schools_wake fcol=blue icon=basic/diamond col=white size=13 \
101 where="CAPACITYTO &gt; 1000"
102
103 # string selection: all schools outside of Raleigh
104 # along with higher level schools in Raleigh
105 d.vect schools_wake fcol=red icon=basic/diamond col=white size=13 \
106 where="ADDRCITY &lt;&gt; 'Raleigh' OR (ADDRCITY = 'Raleigh' AND GLEVEL = 'H')"
107 </pre></div>
108
109 <p>
110 Select all attributes from table where <i>CORECAPACI</i> column values are
111 smaller than 200 (children):
112
113 <div class="code"><pre>
114 # must be run from the mapset which contains the table
115 echo "SELECT * FROM schools_wake WHERE CORECAPACI &lt; 200" | db.select input=-
116 </pre></div>
117 <p>
118
119 <p>
120 Example of subquery expressions from a list (not supported for DBF driver):
121
122 <div class="code"><pre>
123 v.db.select schools_wake where="ADDRCITY IN ('Apex', 'Wendell')"
124 </pre></div>
125
126 <h3>Example of pattern matching</h3>
127
128 <div class="code"><pre>
129 # field contains string:
130 # for DBF driver:
131 v.extract schools_wake out=elementary_schools where="NAMELONG LIKE 'ELEM'"
132 # for SQLite driver:
133 v.extract schools_wake out=rivers_noce where="DES LIKE '%NOCE%'"
134 v.extract schools_wake out=elementary_schools where="NAMELONG LIKE '%ELEM%'"
135
136 # match exactly number of characters (here: 2), does not work for DBF driver:
137 v.db.select mysites where="id LIKE 'P__'"
138
139 #define wildcard:
140 v.db.select mysites where="id LIKE 'P%'"
141 </pre></div>
142
143 <h3>Example of null handling</h3>
144
145 <div class="code"><pre>
146 v.db.addcolumn map=roads col="nulltest int"
147 v.db.update map=roads col=nulltest value=1 where="cat &gt; 2"
148 d.vect roads where="nulltest is null"
149 v.db.update map=roads col=nulltest value=2 where="cat &lt;= 2"
150 </pre></div>
151
152 <h3>Update of attributes</h3>
153
154 Examples of complex expressions in updates (using <code>v.db.*</code>
155 modules):
156
157 <div class="code"><pre>
158 v.db.addcolumn map=roads column="exprtest double precision"
159 v.db.update map=roads column=exprtest value="cat/nulltest"
160 v.db.update map=roads column=exprtest value="cat/nulltest+cat" where="cat=1"
161
162 # using data from another column
163 v.db.update map=roads column=exprtest qcolumn="(cat*100.)/SHAPE_LEN."
164 </pre></div>
165
166 <p>
167 Examples of more complex expressions in updates (using <code>db.*</code>
168 modules):
169
170 <div class="code"><pre>
171 echo "UPDATE roads SET exprtest=null"
172 echo "UPDATE roads SET exprtest=cat/2" | db.execute
173 echo "UPDATE roads SET exprtest=cat/2+cat/3" | db.execute
174 echo "UPDATE roads SET exprtest=NULL WHERE cat&gt;2" | db.execute
175 echo "UPDATE roads SET exprtest=cat/3*(cat+1) WHERE exprtest IS NULL" | db.execute"
176 </pre></div>
177
178 <p>
179 Instead of creating and updating new columns with an expression, you
180 can use the expression directly in a command:
181
182 <div class="code"><pre>
183 d.vect roads where="(cat/3*(cat+1))&gt;8"
184 d.vect roads where="cat&gt;exprtest"
185 </pre></div>
186
187 <h3>Example of changing a SQL type (type casting)</h3>
188
189 <i>Note: not supported for <a href="grass-dbf.html">DBF driver</a>.</i>
190 <p>
191 North Carolina data set: convert string column to double precision:
192 <p>
193 <div class="code"><pre>
194 # first copy map into current mapset
195 g.copy vect=geodetic_pts,mygeodetic_pts
196 v.db.addcolumn mygeodetic_pts col="zval double precision"
197
198 # the 'z_value' col contains 'N/A' strings, not to be converted
199 v.db.update mygeodetic_pts col=zval \
200 qcol="CAST(z_value AS double precision)" \
201 where="z_value &lt;&gt; 'N/A'"
202 </pre></div>
203
204 <h3>Example of concatenation of fields</h3>
205
206 <i>Note: not supported for <a href="grass-dbf.html">DBF driver</a>.</i>
207
208 <div class="code"><pre>
209 v.db.update vectormap column=column3 qcolumn="column1 || column2"
210 </pre></div>
211
212 <h3>Example of conditions</h3>
213
214 Conditions (like if statements) are usually written as CASE statement in SQL:
215
216 <div class="code"><pre>
217 v.db.update vectormap column=species qcolumn="CASE WHEN col1 &gt;= 12 THEN cat else NULL end"
218
219 # a more complex example with nested conditions
220 v.db.update vectormap column=species qcolumn="CASE WHEN col1 &gt;= 1 THEN cat WHEN row = 13 then 0 ELSE NULL end"
221 </pre></div>
222
223 <h2>SEE ALSO</h2>
224
225 <em>
226 <a href="db.connect.html">db.connect</a>,
227 <a href="db.select.html">db.select</a>,
228 <a href="db.execute.html">db.execute</a>,
229 <a href="v.db.connect.html">v.db.connect</a>,
230 <a href="v.db.select.html">v.db.select</a>,
231 <a href="v.db.update.html">v.db.update</a>
232 </em>
233
234 <p>
235 <a href="databaseintro.html">Database management in GRASS</a>,
236 <a href="database.html">Help pages for database modules</a>
237
238 <h2>AUTHOR</h2>
239
240 Radim Blazek