Geografic-Information-System/lib/db/sqlp/sql.html

<!-- meta page description: SQL support in GRASS GIS -->

GRASS can use 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 list of available database drivers can vary in various binary
distributions of GRASS:<br><br>

<table class="border">
<tr><td><a href="grass-dbf.html">dbf</a></td><td>DBF files. Data are stored 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-sqlite.html">sqlite</a></td><td>SQLite embedded database (GRASS 7 default DB backend).</td>
<td><a href="http://sqlite.org/">http://sqlite.org/</a></td></tr>

<tr><td><a href="grass-pg.html">pg</a></td><td>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>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>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>UnixODBC. (PostgreSQL, Oracle, etc.)</td>
<td><a href="http://www.unixodbc.org/">http://www.unixodbc.org/</a></td></tr>
</table>

<h2>NOTES</h2>

<ul>
<li> SQL does not support '.' (dots) in table names.
<li> Supported table name characters are only: <br>
<div class="code"><pre>[A-Za-z][A-Za-z0-9_]*</pre></div>
<li> A table name must start with a character, not a number.
<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> An error message such as &quot;<tt>dbmi: Protocol
     error</tt>&quot; either indicates an invalid column name or an
     unsupported column type (then the GRASS SQL parser needs to be
     extended).
<li> DBF column names are limited to 10 characters (DBF API definition)
</ul>


<h2>EXAMPLES</h2>

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>
d.vect trapping_sites_points fcol=black icon=basic/diamond col=white size=13 \
    where="valley &lt;&gt; 'LAMAR' OR (valley = 'LAMAR' AND description = 'extensive trapping')"
</pre></div>

<p>
Select all attributes from table where <i>str1</i> column values are not 'No
Name':

<div class="code"><pre>
echo "SELECT * FROM archsites WHERE str1 &lt;&gt; 'No Name'" | db.select
</pre></div>
<p>

<p>Example of subquery expressions from a list (does not work
for DBF driver):

<div class="code"><pre>
v.db.select mysites where="id IN ('P04', 'P05')"
</pre></div>

<p>Example of pattern matching:

<div class="code"><pre>

# field contains string:
#  for DBF driver:
v.extract rivers out=rivers_noce where="DES LIKE 'NOCE'"
#  for SQLite driver:
v.extract rivers out=rivers_noce where="DES LIKE '%NOCE%'"

# 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>

<p>Example of null handling:

<div class="code"><pre>
v.db.addcolumn map=roads col="nulltest int"
v.db.update map=roads col=nulltest value=1 where="cat &gt; 2"
d.vect roads where="nulltest is null"
v.db.update map=roads col=nulltest value=2 where="cat &lt;= 2"
</pre></div>

<p>Examples of complex expressions in updates (using <tt>v.db.*</tt>
modules):

<div class="code"><pre>
v.db.addcolumn map=roads col="exprtest double precision"
v.db.update map=roads col=exprtest value=cat/nulltest
v.db.update map=roads col=exprtest value=cat/nulltest+cat where=cat=1
</pre></div>

<p>Examples of 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&gt;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))&gt;8"
d.vect roads where="cat&gt;exprtest"
</pre></div>

<p>Example of changing a SQL type (type casting, does not work
for DBF driver):

<div class="code"><pre>
# North Carolina data set: convert string column to double precision
#  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 &lt;&gt; 'N/A'"
</pre></div>

<p>Example of concatenating fields (does not work for DBF
driver):

<div class="code"><pre>
v.db.update vectormap column=column3 qcolumn="column1 || column2"
</pre></div>

<h2>SEE ALSO</h2>

<em>
<a href="db.select.html">db.select</a>,
<a href="db.execute.html">db.execute</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>

<p>
<i>Last changed: $Date$</i>