From GRASS-Wiki
Revision as of 11:15, 13 January 2017 by Mankoff (talk | contribs) (example for YMD from DOY.)

Jump to: navigation, search

SQL support in GRASS GIS

GRASS GIS uses SQL for vector attribute management.

Analysing SQL syntax errors/table import or copy problems

Sometimes it happens that db.*, v.db.* or other SQL related commands fail. For example, with

 ERROR:  syntax error at or near "Offset"
 LINE 1: ...eatureDet varchar(254), Measure double precision, Offset dou...
 WARNING: Unable to create table <hydro_pointEvent>
 WARNING: Copy table failed

How to fix that? Check out for

  • column name used more than one time;
  • column name has reserved SQL word (depends on DB backend, see SQL Reserved Words Checker to quickly find out if a name is reserved)


If your data should have values as character with leading zero (000123) but they are lacking (123) then you can run

 alter table TABLE add column NEW_COLUMN text;
 update TABLE set NEW_COLUMN=substr('000000'||OLD_COLUMN, length('000000'||OLD_COLUMN)-5, 6);


Combining columns for date calculations

An example command that builds a YMD string from a column that contains the Day of Year (DOY) is:

 db.execute sql="UPDATE <DB> SET YMD=date('2015-01-01', +CAST(DOY AS text) || ' days');"