Note: This document is for an older version of GRASS GIS that will be discontinued soon. You should upgrade, and read the current manual page.
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.
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/ |
[A-Za-z][A-Za-z0-9_]*
d.vect map where="individual='juvenile' and area='beach'"
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"
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"
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'"
v.db.update vectormap column=column3 qcolumn="column1 || column2"
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"
Database management in GRASS GIS, Help pages for database modules
Available at: SQL support in GRASS GIS source code (history)
Latest change: Monday Feb 27 13:45:25 2023 in commit: a82501dc85294207e8e58437c3ec7f1056465ed4
Main index | Topics index | Keywords index | Graphical index | Full index
© 2003-2024 GRASS Development Team, GRASS GIS 8.3.3dev Reference Manual