I am trying to follow the tutorial posted here. When I get to this part "Extraction of seasonal mean temperature" and run this code:

t.rast.extract \
  input=temperature_mean_1950_2011_seasonal \
  output=temperature_mean_1950_2011_spring \
  where="start_time = date_trunc('year', start_time \
         + interval '2 months')" \
expression="temperature_mean_1950_2011_seasonal/100.0" \

I get the following error:

ERROR: Unable to get map ids from register table
Traceback (most recent call last):
File "/usr/local/grass-7.0.svn/scripts/t.rast.extract", line 91, in <module>
File "/usr/local/grass-7.0.svn/scripts/t.rast.extract", line 85, in main
base, nprocs, register_null)
File "/usr/local/grass-7.0.svn/etc/python/grass/temporal/extract.py", line 95, in extract_dataset
rows = sp.get_registered_maps("id", where, "start_time", dbif)
File "/usr/local/grass-7.0.svn/etc/python/grass/temporal/abstract_space_time_dataset.py", line 905, in get_registered_maps
sqlite3.OperationalError: near "'2 months'": syntax error

I am using the data specified in the presentation (mean temperatures dataset downloaded from http://eca.knmi.nl/download/ensembles/data/Grid_0.25deg_reg/).

Please help.

Thank you, Bogdan Rosca

  • 1
    Which SQL database backend do you use?
    – markusN
    Mar 30 '13 at 8:30

The date_trunc works in PostgreSQL:

# SELECT date_trunc('year', timestamp '2001-11-16 20:38:40' + interval '2 months');
 2002-01-01 00:00:00
(1 row)

While in SQLite it gives the error you get:

> SELECT date_trunc('year', timestamp '2001-11-16 20:38:40' + interval '2 months');
Error: near "'2001-11-16 20:38:40'": syntax error

However, in SQLite there is a function datetime function which does the job:

> SELECT datetime('2001-11-16 20:38:40', '2 months', 'start of year');
2002-01-01 00:00:00

The current version of presentation from geostat-course provides a different command:

t.rast.extract \
    input=temperature_mean_1950_2011_seasonal \
    output=temperature_mean_1950_2011_spring \
    where="start_time = datetime(start_time, \
    ’start of year’, ’2 month’)" \

This command relies on SQLite backend, not PostgreSQL. And the function call in temporal SQL where is actually different from the examples above (and possibly from the previous version) and returns March:

> SELECT datetime('2001-11-16 20:38:40', 'start of year', '2 month');
2001-03-01 00:00:00

The datetime parameters are evaluated after each other, so 'start of year' rounds to nearest smaller year (similarly to data_trunc('year', ...) and nect parameter '2 month' (or '+2 months') adds two month resulting in March of year given in first parameter.

Note that the default backend of GRASS GIS temporal framework is SQLite (SQLite3), the PostgreSQL backend is an alternative. The connection to backend is handled by t.connect module.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.