grass-dbf.html 5.87 KB
Newer Older
xuebingbing's avatar
xuebingbing committed
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
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>GRASS GIS Manual: DBF DATABASE DRIVER</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link rel="stylesheet" href="grassdocs.css" type="text/css">
</head>
<body bgcolor="white">
<div id="container">

<a href="index.html"><img src="grass_logo.png" alt="GRASS logo"></a>
<hr class="header">
<h2>DBF DATABASE DRIVER</h2>
<div class="toc">
<h4 class="toc">Table of contents</h4>
<ul class="toc">
    <li class="toc"><a href="#defining-the-dbf-driver" class="toc">Defining the DBF driver</a></li>
    <li class="toc"><a href="#creating-a-dbf-table" class="toc">Creating a DBF table</a></li>
    <li class="toc"><a href="#supported-sql-commands-by-dbf-driver" class="toc">Supported SQL commands by DBF driver</a></li>
    <li class="toc"><a href="#operators-available-in-conditions" class="toc">Operators available in conditions</a></li>
    <li class="toc"><a href="#limitations-of-the-dbf-driver" class="toc">LIMITATIONS OF THE DBF DRIVER</a></li>
    <li class="toc"><a href="#error-messages" class="toc">ERROR MESSAGES</a></li>
    <li class="toc"><a href="#see-also" class="toc">SEE ALSO</a></li>
</ul>
</div>
<!-- meta page description: DBF DATABASE DRIVER -->

The DBF driver is a file based attribute table driver.

<h2><a name="defining-the-dbf-driver">Defining the DBF driver</a></h2>

The DBF driver is a file based driver, in theory no user interaction is
required. However, if the settings should be set back from a different
driver to the DBF driver, the following step is required:

<div class="code"><pre>
# keep single quotes:
db.connect driver=dbf database='$GISDBASE/$LOCATION_NAME/$MAPSET/dbf/'
db.connect -p
</pre></div>

The dbf/ subdirectory in the mapset must exist or must be created by the user.


<h2><a name="creating-a-dbf-table">Creating a DBF table</a></h2>

DBF tables are created by GRASS when generating a vector map
with attributes and having defined the DBF as attribute driver.
<p>
If a DBF table has to be created manually, <a href="db.execute.html">db.execute</a>
can be used or a spreadsheet application. Also <a href="db.copy.html">db.copy</a>
is sometimes useful as well as <a href="db.in.ogr.html">db.in.ogr</a> to import external
tables.


<h2><a name="supported-sql-commands-by-dbf-driver">Supported SQL commands by DBF driver</a></h2>

<div class="code"><pre>
  ALTER TABLE table ADD [COLUMN] columndef
  ALTER TABLE table DROP COLUMN colname
  CREATE TABLE table ( columndefs )
  DROP TABLE table
  SELECT columns FROM table
  SELECT columns FROM table WHERE condition
  SELECT columns FROM table ORDER BY column
  DELETE FROM table
  DELETE FROM table WHERE condition
  INSERT INTO table VALUES (value1[,value2,...])
  INSERT INTO table ( column1[,column2,...] ) VALUES (value1[,value2,...])
  UPDATE table SET assignment1[,assignment2,...]
  UPDATE table SET assignment1[,assignment2,...] WHERE condition
</pre></div>


<h2><a name="operators-available-in-conditions">Operators available in conditions</a></h2>

<div class="code"><pre>
  "="  : equal
  "&lt;"  : smaller than
  "&lt;=" : smaller/equal than
  "&gt;"  : larger than
  "&gt;=" : larger/equal than
  "&lt;&gt;" : not equal
  "~"  : Substring matching  (non-standard SQL)
  "%"  : Substring matching  (limited functionality)
</pre></div>

<p>
Arithmetic expressions using constants and field values are allowed 
in condition clauses and in the RHS of assignments.
<br>
Usual precedence rules and bracketing (using '(' and ')') are supported. 
<br>
Type conversion is performed if necessary (experimental). 

<p>
Conditions allow boolean expressions using the AND, OR and NOT operators, 
with the usual precedence rules. 

<p>
NULLs can be tested by 'colname IS NULL' in conditions. The negation is
'colname NOT NULL'.

<p>
Sorting: Empty fields in a character column are sorted to the end.


<h2><a name="limitations-of-the-dbf-driver">LIMITATIONS OF THE DBF DRIVER</a></h2>

The DBF driver supports only a <b>few SQL statements</b> since the DBF tables are
intended for simple table storage. DBF column names are limited to 10 characters
(as defined in the DBF specifications). For example,

<ul>
<li> aggregate functions (sum, count, min, max,...) are <b>not</b> supported 
in SELECT clauses;</li>
<li> mathematic functions (sin, cos, exp, log,...) are <b>not</b> supported 
in expressions;</li>
<li> SQL query with IN are <b>not</b> supported.</li>
</ul>

<h2><a name="error-messages">ERROR MESSAGES</a></h2>

An error message such as:

<div class="code"><pre>
DBMI-DBF driver error:
SQL parser error: syntax error, unexpected NAME processing 'IN'..
</pre></div>
indicates that an unsupported SQL statement (here, 'IN') was used. The only
solution is to switch the DBMI backend to a real SQL engine (SQLite, 
PostgreSQL, MySQL etc.). See <a href="sql.html">SQL support in GRASS GIS</a>.

<p>
An error message such as:

<div class="code"><pre>
DBMI-DBF driver error:
SQL parser error: syntax error, unexpected DESC, expecting NAME processing 'DESC'
</pre></div>

indicates that a column name corresponds to a reserved SQL word (here: 'DESC').
A different column name should be used. If this happens during import with
<em>v.in.ogr</em>, the <em>cnames</em> parameter can be used to assign different
column names on the fly.

<h2><a name="see-also">SEE ALSO</a></h2>

<em>
<a href="db.connect.html">db.connect</a>,
<a href="sql.html">SQL support in GRASS GIS</a><br>
<a href="http://shapelib.maptools.org/dbf_api.html">DBF Specifications</a> (Shapelib)
</em>

<!--
<p>
<i>Last changed: $Date$</i>
--><hr class="header">
<p>
<a href="index.html">Main index</a> |
<a href="topics.html">Topics index</a> |
<a href="keywords.html">Keywords index</a> |
<a href="graphical_index.html">Graphical index</a> |
<a href="full_index.html">Full index</a>
</p>
<p>
xuebingbing's avatar
xuebingbing committed
167
&copy; 2003-2020
xuebingbing's avatar
xuebingbing committed
168
<a href="http://grass.osgeo.org">GRASS Development Team</a>,
xuebingbing's avatar
xuebingbing committed
169
GRASS GIS 7.8.5 Reference Manual
xuebingbing's avatar
xuebingbing committed
170 171 172 173 174
</p>

</div>
</body>
</html>