Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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" \
  base=temp_mean_spring

I get the following error:

ERROR: Unable to get map ids from register table
   <temperature_mean_1950_2011_seasonal_PERMANENT_raster_register>
Traceback (most recent call last):
File "/usr/local/grass-7.0.svn/scripts/t.rast.extract", line 91, in <module>
main()
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
dbif.cursor.execute(sql)
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

share|improve this question
1  
Which SQL database backend do you use? –  markusN Mar 30 '13 at 8:30

1 Answer 1

The date_trunc works in PostgreSQL:

# SELECT date_trunc('year', timestamp '2001-11-16 20:38:40' + interval '2 months');
     date_trunc      
---------------------
 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’)" \
    expression="temperature_mean_1950_2011_seasonal/100.0"
    base=temp_mean_spring

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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