1v.db.select(1) GRASS GIS User's Manual v.db.select(1)
2
6 v.db.select - Prints vector map attributes.
7
9 vector, attribute table, database, SQL, export
10
12 v.db.select
13 v.db.select --help
14 v.db.select [-rcef] map=name [layer=string] [col‐
15 umns=name[,name,...]] [where=sql_query] [group=string] for‐
16 mat=string [separator=character] [vertical_separator=character]
17 [null_value=string] [file=name] [--overwrite] [--help] [--ver‐
18 bose] [--quiet] [--ui]
19 Flags:
21 -r
22 Print minimal region extent of selected vector features instead of
23 attributes
24 -c
26 Do not include column names in output
27 -e
29 Escape newline and backslash characters
30 -f
32 Exclude attributes not linked to features
33 --overwrite
35 Allow output files to overwrite existing files
36 --help
38 Print usage summary
39 --verbose
41 Verbose module output
42 --quiet
44 Quiet module output
45 --ui
47 Force launching GUI dialog
48 Parameters:
50 map=name [required]
51 Name of vector map
52 Or data source for direct OGR access
53 layer=string
55 Layer number or name
56 Vector features can have category values in different layers. This
57 number determines which layer to use. When used with direct OGR ac‐
58 cess this is the layer name.
59 Default: 1
60 columns=name[,name,...]
62 Name of attribute column(s)
63 where=sql_query
65 WHERE conditions of SQL statement without ’where’ keyword
66 Example: income < 1000 and population >= 10000
67 group=string
69 GROUP BY conditions of SQL statement without ’group by’ keyword
70 format=string [required]
72 Output format
73 Options: plain, csv, json, vertical
74 Default: plain
75 plain: Configurable plain text output
76 csv: CSV (Comma Separated Values)
77 json: JSON (JavaScript Object Notation)
78 vertical: Plain text vertical output (instead of horizontal)
79 separator=character
81 Field separator
82 Special characters: pipe, comma, space, tab, newline
83 vertical_separator=character
85 Output vertical record separator
86 Special characters: pipe, comma, space, tab, newline
87 null_value=string
89 String representing NULL value
90 file=name
92 Name for output file (if omitted or "-" output to stdout)
93
95 v.db.select prints attributes of a vector map from one or several user
96 selected attribute table columns.
97 Output formats
99 Four different formats can be used depending on the circumstances using
100 the format option: plain text, CSV, JSON, and vertical plain text.
101 Plain text
103 The plain text is the default output which is most suitable for reading
104 by humans, e.g., when working in the command line or obtaining specific
105 values from the attribute table using the v.db.select GUI dialog.
106 The individual fields (attribute values) are separated by a pipe (|)
108 which can be customized using the separator option. The records (rows)
109 are separated by newlines.
110 Example with a pipe as a separator (the default):
112 cat|road_name|multilane|year|length
113 1|NC-50|no|2001|4825.369405
114 2|NC-50|no|2002|14392.589058
115 3|NC-98|no|2003|3212.981242
116 4|NC-50|no|2004|13391.907552
117 When escaping is enabled, the following characters in the fields are
118 escaped: backslash (\\), carriage return (\r), line feed (\n), tabula‐
119 tor (\t), form feed (\f), and backslash (\b).
120 No quoting or escaping is performed by default, so if these characters
122 are in the output, they look just like the separators. This is usually
123 not a problem for humans looking at the output to get a general idea
124 about query result or attribute table content.
125 Consequently, this format is not recommended for computers, e.g., for
127 reading attribute data in Python scripts. It works for further parsing
128 in limited cases when the values don’t contain separators or when the
129 separators are set to one of the escaped characters.
130 CSV
132 CSV (comma-separated values) has many variations. This module by de‐
133 fault produces CSV with comma (,) as the field separator (delimiter).
134 All text fields (based on the type) are quoted with double quotes. Dou‐
135 ble quotes in fields are represented as two double quotes. Newline
136 characters in the fields are present as-is in the output. Header is in‐
137 cluded by default containing column names.
138 All full CSV parsers such as the ones in LibreOffice or Python are able
140 to parse this format when configured to the above specification.
141 Example with default settings:
143 cat,road_name,multilane,year,length
144 1,"NC-50","no",2001,4825.369405
145 2,"NC-50","no",2002,14392.589058
146 3,"NC-98","no",2003,3212.981242
147 4,"NC-50","no",2004,13391.907552
148 If desired, the separator can be customized and escaping can be enabled
150 with the same characters being escaped as for the plain text. Notably,
151 newlines and tabs are escaped, double quotes are not, and the separator
152 is not escaped either (unless it is a tab). However, the format is
153 guaranteed only for the commonly used separators such as comma, semi‐
154 colon, pipe, and tab.
155 Note that using multi-character separator is allowed, but not recom‐
157 mended as it is not generally supported by CSV readers.
158 CSV is the recommended format for further use in another analytical ap‐
160 plications, especially for use with spreadsheet applications. For
161 scripting, it is advantageous when tabular data is needed (rather than
162 key-value pairs).
163 JSON
165 JSON (JavaScript Object Notation) format is produced according to the
166 specification so it is readily readable by JSON parsers. The standard
167 JSON escapes are performed (backslash, carriage return, line feed, tab‐
168 ulator, form feed, backslash, and double quote) for string values.
169 Numbers in the database such as integers and doubles are represented as
170 numbers, while texts (TEXT, VARCHAR, etc.) and dates in the database
171 are represented as strings in JSON. NULL values in database are repre‐
172 sented as JSON null. Indentation and newlines in the output are mini‐
173 mal and not guaranteed.
174 Records which are the result of the query are stored under key records
176 as an array (list) of objects (collections of key-value pairs). The
177 keys for attributes are lowercase or uppercase depending on how the
178 columns were defined in the database.
179 Example with added indentation (note that booleans are not directly
181 supported; here, an attribute is a string with value no):
182 {
183 "records": [
184 {
185 "cat": 1,
186 "road_name": "NC-50",
187 "multilane": "no",
188 "year": 2001,
189 "length": 4825.369405
190 },
191 {
192 "cat": 2,
193 "road_name": "NC-50",
194 "multilane": "no",
195 "year": 2002,
196 "length": 14392.589058
197 }
198 ]
199 }
200 JSON is the recommended format for reading the data in Python and for
202 any uses and environments where convenient access to individual values
203 is desired and JSON parser is available.
204 Vertical plain text
206 In the vertical plain text format, each value is on a single line and
207 is preceded by the name of the attribute (column) which is separated by
208 separator. The individual records can be separated by the vertical sep‐
209 arator (vertical_separator option).
210 Example with (horizontal) separator = and vertical separator newline:
212 cat=1
213 road_name=NC-50
214 multilane=no
215 year=2001
216 length=4825.369405
217 cat=2
218 road_name=NC-50
219 multilane=no
220 year=2002
221 length=14392.589058
222 Newline is automatically added after a vertical separator unless it is
223 a newline which allows for separating the records, e.g., by multiple
224 dashes. The escaping (-e) need to should be enabled in case the output
225 is meant for reading by a computer rather than just as a data overview
226 for humans. Escaping will ensure that values with newlines will be con‐
227 tained to a single line. This format is for special uses in scripting,
228 for example, in combination with columns option set to one column only
229 and escaping (-e) and no column names flags (-c). It is also advanta‐
230 geous when you need implement the parsing yourself.
231
233 • CSV and JSON were added in version 8.0 as new primary formats
234 for further consumption by scripts and other applications.
235 • Escaping of plain and vertical formats was extended from just
237 backslash and newlines to all escapes from JSON except for dou‐
238 ble quote character.
239
241 All examples are based on the North Carolina sample dataset.
242 Select and show entire table
244 v.db.select map=roadsmajor
245 cat|MAJORRDS_|ROAD_NAME|MULTILANE|PROPYEAR|OBJECTID|SHAPE_LEN
246 1|1|NC-50|no|0|1|4825.369405
247 2|2|NC-50|no|0|2|14392.589058
248 3|3|NC-98|no|0|3|3212.981242
249 4|4|NC-50|no|0|4|13391.907552
250 ...
251 Select and show single column from table
253 Note: multiple columns can be specified as comma separated list.
254 v.db.select map=roadsmajor column=ROAD_NAME
255 NC-50
256 NC-50
257 NC-98
258 NC-50
259 NC-98
260 ...
261 Print region extent of selected vector features
263 v.db.select -r map=roadsmajor where="ROAD_NAME = ’NC-98’"
264 n=248425.389891
265 s=245640.640081
266 w=635906.517653
267 e=661979.801880
268 Select empty vector features (no data entries)
270 v.db.select geonames_wake where="ALTERNATEN IS NULL"
271 cat|GEONAMEID|NAME|ASCIINAME|ALTERNATEN|FEATURECLA|FEATURECOD|...
272 8|4498303|West Raleigh|West Raleigh||P|PPL|US||NC|338759|123|...
273 14|4459467|Cary|Cary||P|PPL|US||NC|103945|146|152|America/Iqaluit|...
274 31|4452808|Apex|Apex||P|PPL|US||NC|30873|167|134|America/Iqaluit|...
275 ...
276 Select not empty vector features (no data entries)
278 v.db.select geonames_wake where="ALTERNATEN IS NOT NULL"
279 cat|GEONAMEID|NAME|ASCIINAME|ALTERNATEN|FEATURECLA|FEATURECOD|...
280 9|4487042|Raleigh|Raleigh|Raleigh,...
281 31299|4487056|Raleigh-Durham Airport|Raleigh-Durham Airport|...
282 ...
283 Select features with distinct road names
285 v.db.select map=roadsmajor columns=ROAD_NAME group=ROAD_NAME
286 ROAD_NAME
287 I-40
288 I-440
289 I-540
290 NC-231
291 NC-39
292 NC-42
293 ...
294 It is also possible to combine with where option
295 v.db.select map=roadsmajor columns=ROAD_NAME,MULTILANE group=ROAD_NAME where=’ROAD_NAME is not null’
296 ROAD_NAME|MULTILANE
297 I-40|yes
298 I-440|yes
299 I-540|yes
300 NC-231|no
301 NC-39|no
302 NC-42|no
303 NC-50|no
304 NC-54|no
305 NC-55|no
306 NC-96|no
307 NC-97|no
308 NC-98|no
309 US-1|
310 US-401|no
311 US-64|yes
312 US-70|yes
313 It can also use more columns in group option
314 v.db.select map=roadsmajor columns=ROAD_NAME,MULTILANE group=ROAD_NAME,MULTILANE where=’ROAD_NAME is not null’
315 ROAD_NAME|MULTILANE
316 I-40|yes
317 I-440|yes
318 I-540|yes
319 NC-231|no
320 NC-39|no
321 NC-42|no
322 NC-50|no
323 NC-54|no
324 NC-55|no
325 NC-96|no
326 NC-97|no
327 NC-98|no
328 US-1|
329 US-1|yes
330 US-401|no
331 US-401|yes
332 US-64|yes
333 US-70|yes
334 Read results in Python
336 The json package in the standard Python library can load a JSON string
337 obtained as output from the v.db.select module through the read_command
338 function:
339 import json
340 import grass.script as gs
341 text = gs.read_command("v.db.select", map="roadsmajor", format="json")
342 data = json.loads(text)
343 for row in data["records"]:
344 print(row["ROAD_NAME"])
345
347 db.select
348 GRASS SQL interface
350
352 Radim Blazek, ITC-Irst, Trento, Italy
353 Minimal region extent added by Martin Landa, FBK-irst (formerly
354 ITC-irst), Trento, Italy
355 Group option added by Luca Delucchi, Fondazione Edmund Mach, Trento,
356 Italy
357 Huidae Cho (JSON output, escaping and features-only flags)
358 Vaclav Petras (true CSV output, format option and documentation)
359
361 Available at: v.db.select source code (history)
362 Accessed: Saturday Jan 21 21:15:56 2023
364 Main index | Vector index | Topics index | Keywords index | Graphical
366 index | Full index
367 © 2003-2023 GRASS Development Team, GRASS GIS 8.2.1 Reference Manual
369GRASS 8.2.1 v.db.select(1)