GSP
Quick Navigator

Search Site

Unix VPS
A - Starter
B - Basic
C - Preferred
D - Commercial
MPS - Dedicated
Previous VPSs
* Sign Up! *

Support
Contact Us
Online Help
Handbooks
Domain Status
Man Pages

FAQ
Virtual Servers
Pricing
Billing
Technical

Network
Facilities
Connectivity
Topology Map

Miscellaneous
Server Agreement
Year 2038
Credits
 

USA Flag

 

 

Man Pages
sql(1) GRASS GIS User's Manual sql(1)

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.

GRASS GIS supports various RDBMS (Relational database management system) and embedded databases. SQL (Structured Query Language) queries are directly passed to the underlying database system. The set of supported SQL commands depends on the RDMBS and database driver selected.

The default database driver used by GRASS GIS 7 is SQLite. GRASS GIS handles multiattribute vector data by default. The db.* set of commands provides basic SQL support for attribute management, while the v.db.* set of commands operates on vector maps.

Note: The list of available database drivers can vary in various binary distributions of GRASS GIS:

sqlite Data storage in SQLite database files (default DB backend) http://sqlite.org/
dbf Data storage in DBF files http://shapelib.maptools.org/dbf_api.html
pg Data storage in PostgreSQL RDBMS http://postgresql.org/
mysql Data storage in MySQL RDBMS http://mysql.org/
odbc Data storage via UnixODBC (PostgreSQL, Oracle, etc.) http://www.unixodbc.org/
ogr Data storage in OGR files http://gdal.org/

  • No dots are allowed as SQL does not support ’.’ (dots) in table names.
  • Supported table name characters are only:
    
    [A-Za-z][A-Za-z0-9_]*
    
        
  • A table name must start with a character, not a number.
  • 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.
    
    d.vect map where="individual=’juvenile’ and area=’beach’"
    
        
  • Attempts to use a reserved SQL word (depends on database backend) as column or table name will cause a "SQL syntax error".
  • An error message such as "dbmi: Protocol error" either indicates an invalid column name or an unsupported column type (then the GRASS SQL parser needs to be extended).
  • DBF column names are limited to 10 characters (DBF API definition).

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

Display all vector points except for LAMAR valley and extensive trapping (brackets are superfluous in this example):

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 > 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 <> ’Raleigh’ OR (ADDRCITY = ’Raleigh’ AND GLEVEL = ’H’)"

Select all attributes from table where CORECAPACI column values are smaller than 200 (children):


# must be run from the mapset which contains the table
echo "SELECT * FROM schools_wake WHERE CORECAPACI < 200" | db.select input=-

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


v.db.select schools_wake where="ADDRCITY IN (’Apex’, ’Wendell’)"


# 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%’"


v.db.addcolumn map=roads col="nulltest int"
v.db.update map=roads col=nulltest value=1 where="cat > 2"
d.vect roads where="nulltest is null"
v.db.update map=roads col=nulltest value=2 where="cat <= 2"

Examples of complex expressions in updates (using v.db.* modules):

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

Examples of more complex expressions in updates (using db.* modules):


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>2" | db.execute
echo "UPDATE roads SET exprtest=cat/3*(cat+1) WHERE exprtest IS NULL" | db.execute"

Instead of creating and updating new columns with an expression, you can use the expression directly in a command:


d.vect roads where="(cat/3*(cat+1))>8"
d.vect roads where="cat>exprtest"

Note: not supported for DBF driver.

North Carolina data set: convert string column to double precision:


# 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 <> ’N/A’"

Note: not supported for DBF driver.

v.db.update vectormap column=column3 qcolumn="column1 || column2"

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

v.db.update vectormap column=species qcolumn="CASE WHEN col1 >= 12 THEN cat else NULL end"
# a more complex example with nested conditions
v.db.update vectormap column=species qcolumn="CASE WHEN col1 >= 1 THEN cat WHEN row = 13 then 0 ELSE NULL end"

db.connect, db.select, db.execute, v.db.connect, v.db.select, v.db.update

Database management in GRASS GIS, Help pages for database modules

Main index | Topics index | Keywords index | Graphical index | Full index

© 2003-2021 GRASS Development Team, GRASS GIS 7.8.6 Reference Manual

GRASS 7.8.6

Search for    or go to Top of page |  Section 1 |  Main Index

Powered by GSP Visit the GSP FreeBSD Man Page Interface.
Output converted with ManDoc.