SQL: Difference between revisions
Jump to navigation
Jump to search
(+Analysing SQL syntax errors/table import or copy problems) |
(+SpatiaLite) |
||
| (4 intermediate revisions by 3 users not shown) | |||
| Line 1: | Line 1: | ||
== SQL support in GRASS GIS == | == SQL support in GRASS GIS == | ||
GRASS | GRASS GIS uses SQL for vector attribute management. | ||
* {{cmd|sql}} Overview of SQL in GRASS | * {{cmd|sql}} Overview of SQL in GRASS | ||
| Line 7: | Line 7: | ||
* Using [[Openoffice.org with SQL Databases]] | * Using [[Openoffice.org with SQL Databases]] | ||
* [[SQL rename columns]] | * [[SQL rename columns]] | ||
* [[SpatiaLite]] | |||
=== Analysing SQL syntax errors/table import or copy problems === | === Analysing SQL syntax errors/table import or copy problems === | ||
| Line 21: | Line 21: | ||
* column name used more than one time; | * column name used more than one time; | ||
* column name has reserved SQL word (depends on DB backend, see [http://www.petefreitag.com/tools/sql_reserved_words_checker/ SQL Reserved Words Checker] to quickly find out if a name is reserved) | * column name has reserved SQL word (depends on DB backend, see [http://www.petefreitag.com/tools/sql_reserved_words_checker/ SQL Reserved Words Checker] to quickly find out if a name is reserved) | ||
=== Formatting === | |||
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); | |||
=== Examples === | |||
==== 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');" | |||
[[Category:FAQ]] | [[Category:FAQ]] | ||
Latest revision as of 10:11, 2 February 2017
SQL support in GRASS GIS
GRASS GIS uses SQL for vector attribute management.
- sql Overview of SQL in GRASS
- databaseintro - Database management
- Using Openoffice.org with SQL Databases
- SQL rename columns
- SpatiaLite
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)
Formatting
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);
Examples
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');"