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

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

<!-- this file is lib/db/sqlp/sql.html -->

Vector points, lines and areas usually have attribute data that are
stored in DBMS. The attributes are linked to each vector object using a
category number (attribute ID, usually the "cat" integer column). The
category numbers are stored both in the vector geometry and the
attribute table.
<p>
GRASS GIS supports 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 default database driver used by GRASS GIS 8 is SQLite. GRASS GIS 
handles multiattribute vector data by default. The <em>db.*</em> set of
commands  provides basic SQL support for attribute management, while the
<em>v.db.*</em> set of commands operates on vector maps.

<p>
Note: The list of available database drivers can vary in various binary
distributions of GRASS GIS:
<p>
<table class="border">
<tr><td><a href="grass-sqlite.html">sqlite</a></td><td>Data storage in SQLite database files (default DB backend)</td>
<td><a href="http://sqlite.org/">http://sqlite.org/</a></td></tr>

<tr><td><a href="grass-dbf.html">dbf</a></td><td>Data storage 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-pg.html">pg</a></td><td>Data storage in 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>Data storage in 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>Data are stored in 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>Data storage via UnixODBC (PostgreSQL, Oracle, etc.)</td>
<td><a href="http://www.unixodbc.org/">http://www.unixodbc.org/</a></td></tr>

<tr><td><a href="grass-ogr.html">ogr</a></td><td>Data storage in OGR files</td>
<td><a href="http://gdal.org">http://gdal.org/</a></td></tr>
</table>

<h2>NOTES</h2>

<h3>Database table name restrictions</h3>

<ul>
<li> No dots are allowed as SQL does not support '.' (dots) in table names.</li>
<li> Supported table name characters are only: <br>
<div class="code"><pre>
[A-Za-z][A-Za-z0-9_]*
</pre></div></li>
<li> A table name must start with a character, not a number.</li>
<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>
<li> Attempts to use a reserved SQL word (depends on database backend) as 
     column or table name will cause a "SQL syntax error".</li>
<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>
<li> DBF column names are limited to 10 characters (DBF API definition).</li>
</ul>

<h3>Database table column types</h3>

The supported types of columns depend on the database backend. However, all backends
should support VARCHAR, INT, DOUBLE PRECISION and DATE.

<h2>EXAMPLES</h2>

<h3>Display of vector feature selected by attribute query</h3>
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>
g.region vector=schools_wake -p
d.mon wx0
d.vect roadsmajor

# all schools
d.vect schools_wake fcol=black icon=basic/diamond col=white size=13

# numerical selection: show schools with capacity of above 1000 kids:
d.vect schools_wake fcol=blue icon=basic/diamond col=white size=13 \
    where="CAPACITYTO &gt; 1000"

# string selection: all schools outside of Raleigh 
#   along with higher level schools in Raleigh
d.vect schools_wake fcol=red icon=basic/diamond col=white size=13 \
    where="ADDRCITY &lt;&gt; 'Raleigh' OR (ADDRCITY = 'Raleigh' AND GLEVEL = 'H')"
</pre></div>

<p>
Select all attributes from table where <i>CORECAPACI</i> column values are
smaller than 200 (children):

<div class="code"><pre>
# must be run from the mapset which contains the table
echo "SELECT * FROM schools_wake WHERE CORECAPACI &lt; 200" | db.select input=-
</pre></div>
<p>

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

<div class="code"><pre>
v.db.select schools_wake where="ADDRCITY IN ('Apex', 'Wendell')"
</pre></div>

<h3>Example of pattern matching</h3>

<div class="code"><pre>
# field contains string:
#  for DBF driver:
v.extract schools_wake out=elementary_schools where="NAMELONG LIKE 'ELEM'"
#  for SQLite driver:
v.extract schools_wake out=rivers_noce where="DES LIKE '%NOCE%'"
v.extract schools_wake out=elementary_schools where="NAMELONG LIKE '%ELEM%'"

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

<h3>Example of null handling</h3>

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

<h3>Update of attributes</h3>

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

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

# using data from another column
v.db.update map=roads column=exprtest qcolumn="(cat*100.)/SHAPE_LEN."
</pre></div>

<p>
Examples of more 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>

<h3>Example of changing a SQL type (type casting)</h3>

<i>Note: not supported for <a href="grass-dbf.html">DBF driver</a>.</i>
<p>
North Carolina data set: convert string column to double precision:
<p>
<div class="code"><pre>
# first 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>

<h3>Example of concatenation of fields</h3>

<i>Note: not supported for <a href="grass-dbf.html">DBF driver</a>.</i>

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

<h3>Example of conditions</h3>

Conditions (like if statements) are usually written as CASE statement in SQL:

<div class="code"><pre>
v.db.update vectormap column=species qcolumn="CASE WHEN col1 &gt;= 12 THEN cat else NULL end"

# a more complex example with nested conditions
v.db.update vectormap column=species qcolumn="CASE WHEN col1 &gt;= 1 THEN cat WHEN row = 13 then 0 ELSE NULL end"
</pre></div>

<h2>SEE ALSO</h2>

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

<h2>AUTHOR</h2>

Radmin Blazek