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
|
<h2>DESCRIPTION</h2>
<em>v.db.update</em> assigns a new value to a column in the attribute
table connected to a given map. The <em>value</em> parameter allows
updating with a literal value. Alternatively, with the <em>qcol</em>
parameter values can be copied from another column in the table or be
the result of a combination or transformation of other columns.
<h2>NOTES</h2>
<em>v.db.update</em> is just a front-end to <em>db.execute</em> to
allow easier usage.
<p>For complex SQL UPDATE statements, <em>db.execute</em> should be used.
<h2>EXAMPLES</h2>
<h3>Replacing of NULL values</h3>
In this example, selectively display lakes without (blue) and with
NULL (red) are shown to find out which type is undefined. In the
original map there are lakes missing FTYPE attribute which are
wetlands along streams. These NULL attributes are replaced with the
landuse type WETLAND:
<div class="code"><pre>
g.copy vect=lakes,mylakes
v.db.select mylakes
v.db.select mylakes where="FTYPE IS NULL"
# display the lakes, show undefined FTYPE lakes in red
g.region vector=mylakes
d.mon wx0
d.vect mylakes where="FTYPE NOT NULL" type=area col=blue
d.vect mylakes where="FTYPE IS NULL" type=area col=red
# replace NULL with FTYPE WETLAND
v.db.update mylakes col=FTYPE value=WETLAND \
where="FTYPE IS NULL"
v.db.select mylakes
</pre></div>
<h3>Updating of columns with on the fly calculation</h3>
Spearfish example: adding new column, copying values from another table
column with on the fly calculation:
<div class="code"><pre>
g.copy vect=fields,myfields
v.db.addcolumn myfields col="polynum integer"
v.db.update myfields col=polynum qcol="cat*2"
v.db.select myfields
</pre></div>
<h3>Type casting</h3>
Type cast (type conversion) of strings to double precision
(unsupported by DBF driver):
<div class="code"><pre>
g.copy vect=geodetic_pts,mygeodetic_pts
v.db.update mygeodetic_pts col=zval qcol="CAST(z_value AS double precision)" \
where="z_value <> 'N/A'"
</pre></div>
<h3>Updating of columns with on the fly calculation (SQLite extended functions)</h3>
Note: this requires SQLite extended functions. For details see the GRASS GIS Wiki
(compilation of <a href="https://grasswiki.osgeo.org/wiki/Build_SQLite_extension_on_Linux">libsqlitefunctions.so</a>
and <a href="https://grasswiki.osgeo.org/wiki/Build_SQLite_extension_on_windows">libsqlitefunctions.dll</a>).
<p>
North Carolina data set example: adding new column, copying values from
another table column with on the fly calculation:
<div class="code"><pre>
g.copy vect=precip_30ynormals,myprecip_30ynormals
v.db.addcolumn myprecip_30ynormals column="logjuly double precision"
v.db.update myprecip_30ynormals column="logjuly" query_column="log(jul)" \
sqliteextra=$HOME/sqlite_extensions/libsqlitefunctions.so
v.db.select myprecip_30ynormals columns=jul,logjuly
jul|logjuly
132.842|4.88916045210132
127|4.84418708645859
124.206|4.82194147751127
104.648|4.65060233738593
98.298|4.58800368106618
...
</pre></div>
<h2>SEE ALSO</h2>
<em>
<a href="db.execute.html">db.execute</a>,
<a href="v.db.addcolumn.html">v.db.addcolumn</a>,
<a href="v.db.addtable.html">v.db.addtable</a>,
<a href="v.db.connect.html">v.db.connect</a>,
<a href="v.db.droptable.html">v.db.droptable</a>,
<a href="v.db.join.html">v.db.join</a>,
<a href="v.db.select.html">v.db.select</a>
</em>
<p>
<em>
<a href="sql.html">GRASS SQL interface</a>
</em>
<h2>AUTHOR</h2>
Moritz Lennert (mlennert@club.worldonline.be)
|
