[GRASS-dev] Which are the strds 'columns'?

GRASS GRASS developer
1

Shouldn't the following work?

t.rast.univar lst where="mean > 10"
..
sqlite3.OperationalError: no such column: mean

'min', 'max' and 'start_time' work.

Where are the "columns"?
Isn't the whole of what t.rast.univar -e reports supported?

Thank you, Nikos

2

Hi Nikos,

the columns that you use in the where condition of temporal modules are those in the SQLite table that stores metadata about each map in the st(r)ds: min, max, start_time, end_time. AFAIK, there’s no metadata for the mean, i.e. no column in the SQLite table containing that info. Here there are some examples with t.rast.list [1] using where. Maybe the error message could be improved as to specify which columns are available.

If, from the output t.rast.univar (with or without -e) you want to get those maps where LST mean is higher than 10, then you would need a different procedure. What I did eventually, was import the .txt file into R, and there, make queries with sqldf package. There’s for sure other much better options that I would love to learn.

hth,
Vero

[1] https://grasswiki.osgeo.org/wiki/Temporal_data_processing#Listing_maps_and_selections

···

2017-10-21 14:34 GMT+02:00 Nikos Alexandris <nik@nikosalexandris.net>:

Shouldn’t the following work?

t.rast.univar lst where=“mean > 10”
…
sqlite3.OperationalError: no such column: mean

‘min’, ‘max’ and ‘start_time’ work.

Where are the “columns”?
Isn’t the whole of what t.rast.univar -e reports supported?

Thank you, Nikos

grass-dev mailing list
grass-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-dev

3

Hi,
the column option of:

`t.rast.list --help`

will show all columns that are available for time-stamped raster layer
SQL selection operations.

Best regards
Sören

2017-10-21 14:34 GMT+02:00 Nikos Alexandris <nik@nikosalexandris.net>:

Shouldn't the following work?

t.rast.univar lst where="mean > 10"
..
sqlite3.OperationalError: no such column: mean

'min', 'max' and 'start_time' work.

Where are the "columns"?
Isn't the whole of what t.rast.univar -e reports supported?

Thank you, Nikos

_______________________________________________
grass-dev mailing list
grass-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-dev

4

Thanks, Soeren :slight_smile:

···

2017-10-21 15:05 GMT+02:00 Sören Gebbert <soerengebbert@googlemail.com>:

Hi,
the column option of:

t.rast.list --help

will show all columns that are available for time-stamped raster layer
SQL selection operations.

Best regards
Sören

2017-10-21 14:34 GMT+02:00 Nikos Alexandris <nik@nikosalexandris.net>:

Shouldn’t the following work?

t.rast.univar lst where=“mean > 10”
…
sqlite3.OperationalError: no such column: mean

‘min’, ‘max’ and ‘start_time’ work.

Where are the “columns”?
Isn’t the whole of what t.rast.univar -e reports supported?

Thank you, Nikos

grass-dev mailing list
grass-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-dev

grass-dev mailing list
grass-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-dev

5

Awesome explanation, as usual!

Much appreciated.

Nikos

* Veronica Andreo <veroandreo@gmail.com> [2017-10-21 15:01:14 +0200]:

Hi Nikos,

the columns that you use in the where condition of temporal modules are
those in the SQLite table that stores metadata about each map in the
st(r)ds: min, max, start_time, end_time. AFAIK, there's no metadata for the
mean, i.e. no column in the SQLite table containing that info. Here there
are some examples with t.rast.list [1] using where. Maybe the error message
could be improved as to specify which columns are available.

If, from the output t.rast.univar (with or without -e) you want to get
those maps where LST mean is higher than 10, then you would need a
different procedure. What I did eventually, was import the .txt file into
R, and there, make queries with sqldf package. There's for sure other much
better options that I would love to learn.

hth,
Vero

[1]
https://grasswiki.osgeo.org/wiki/Temporal_data_processing#Listing_maps_and_selections

2017-10-21 14:34 GMT+02:00 Nikos Alexandris <nik@nikosalexandris.net>:

Shouldn't the following work?

t.rast.univar lst where="mean > 10"
..
sqlite3.OperationalError: no such column: mean

'min', 'max' and 'start_time' work.

Where are the "columns"?
Isn't the whole of what t.rast.univar -e reports supported?

Thank you, Nikos

_______________________________________________
grass-dev mailing list
grass-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-dev

--
Nikos Alexandris | Remote Sensing & Geomatics
GPG Key Fingerprint 6F9D4506F3CA28380974D31A9053534B693C4FB3

6

Maybe add this info (part of how Vero explained as well) in
t.rast.univar?

Danke Soeren, Nikos

* Sören Gebbert <soerengebbert@googlemail.com> [2017-10-21 15:05:24 +0200]:

Hi,
the column option of:

`t.rast.list --help`

will show all columns that are available for time-stamped raster layer
SQL selection operations.

Best regards
Sören

2017-10-21 14:34 GMT+02:00 Nikos Alexandris <nik@nikosalexandris.net>:

Shouldn't the following work?

t.rast.univar lst where="mean > 10"
..
sqlite3.OperationalError: no such column: mean

'min', 'max' and 'start_time' work.

Where are the "columns"?
Isn't the whole of what t.rast.univar -e reports supported?

Thank you, Nikos

_______________________________________________
grass-dev mailing list
grass-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-dev

--
Nikos Alexandris | Remote Sensing & Geomatics
GPG Key Fingerprint 6F9D4506F3CA28380974D31A9053534B693C4FB3

7

Hi,
to do it coherently: all t.* modules with a where option should
mention this. Feel free to perform the necessary manpage updates. :slight_smile:

Best regards
Sören

2017-10-21 17:05 GMT+02:00 Nikos Alexandris <nik@nikosalexandris.net>:

Maybe add this info (part of how Vero explained as well) in
t.rast.univar?

Danke Soeren, Nikos

* Sören Gebbert <soerengebbert@googlemail.com> [2017-10-21 15:05:24 +0200]:

Hi,
the column option of:

`t.rast.list --help`

will show all columns that are available for time-stamped raster layer
SQL selection operations.

Best regards
Sören

2017-10-21 14:34 GMT+02:00 Nikos Alexandris <nik@nikosalexandris.net>:

Shouldn't the following work?

t.rast.univar lst where="mean > 10"
..
sqlite3.OperationalError: no such column: mean

'min', 'max' and 'start_time' work.

Where are the "columns"?
Isn't the whole of what t.rast.univar -e reports supported?

Thank you, Nikos

_______________________________________________
grass-dev mailing list
grass-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-dev

--
Nikos Alexandris | Remote Sensing & Geomatics
GPG Key Fingerprint 6F9D4506F3CA28380974D31A9053534B693C4FB3

8

Hi,

I added a note in the wiki [0] and I will add similar note(s) in [1] because, as Soeren said, it is common to all t.* modules with where option. I will think how to add some note in each manual with a where option.

cheers,
Vero

[0] https://grasswiki.osgeo.org/wiki/Temporal_data_processing#Listing_maps_and_selections
[1] https://trac.osgeo.org/grass/browser/grass/trunk/temporal/temporalintro.html

···

2017-10-21 21:15 GMT+02:00 Sören Gebbert <soerengebbert@googlemail.com>:

Hi,
to do it coherently: all t.* modules with a where option should
mention this. Feel free to perform the necessary manpage updates. :slight_smile:

Best regards
Sören

2017-10-21 17:05 GMT+02:00 Nikos Alexandris <nik@nikosalexandris.net>:

Maybe add this info (part of how Vero explained as well) in
t.rast.univar?

Danke Soeren, Nikos

Hi,
the column option of:

t.rast.list --help

will show all columns that are available for time-stamped raster layer
SQL selection operations.

Best regards
Sören

2017-10-21 14:34 GMT+02:00 Nikos Alexandris <nik@nikosalexandris.net>:

Shouldn’t the following work?

t.rast.univar lst where=“mean > 10”
…
sqlite3.OperationalError: no such column: mean

‘min’, ‘max’ and ‘start_time’ work.

Where are the “columns”?
Isn’t the whole of what t.rast.univar -e reports supported?

Thank you, Nikos

grass-dev mailing list
grass-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-dev

–
Nikos Alexandris | Remote Sensing & Geomatics
GPG Key Fingerprint 6F9D4506F3CA28380974D31A9053534B693C4FB3

grass-dev mailing list
grass-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-dev

9

* Veronica Andreo <veroandreo@gmail.com> [2017-10-22 13:18:09 +0200]:

I added a note in the wiki [0] and I will add similar note(s) in [1]
because, as Soeren said, it is common to all t.* modules with where option.
I will think how to add some note in each manual with a where option.

[0]
https://grasswiki.osgeo.org/wiki/Temporal_data_processing#Listing_maps_and_selections
[1]
https://trac.osgeo.org/grass/browser/grass/trunk/temporal/temporalintro.html

(Sorry for bottom-posting, want to break the discussion to a new
"thread")

It is, and was, clear that the `t.rast.list` module does exactly
what it is named for. It lists the raster maps of an strds and their
properties.

On the other hand, the skepsis on t.rast.univar to be able to report
maps with a where SQL query that accepts mean values as a "column" is of
interest.

Univariate statistics are computed and reported. Is it really
such a computational overhead to keep that "mean", or any other of the
descriptive statistics, and re-loop over the maps to filter the output?

I feel it's worth it to avoid the extra post-processing step, outside of
GRASS' environment. May I open an enhancement request?

Thank you, Nikos

10

[snip]

(Sorry for bottom-posting, want to break the discussion to a new
"thread")

It is, and was, clear that the `t.rast.list` module does exactly
what it is named for. It lists the raster maps of an strds and their
properties.

On the other hand, the skepsis on t.rast.univar to be able to report
maps with a where SQL query that accepts mean values as a "column" is of
interest.

But t.rast.univar is designed to compute the mean values. IMHO, you
can't expect to have a selection option of a value that should be
computed after selection?
The beauty of GRASS GIS is its Unix-like design. Many dedicated
modules that show their strength when combined with other Unix tools.
How about using AWK?

Univariate statistics are computed and reported. Is it really
such a computational overhead to keep that "mean", or any other of the
descriptive statistics, and re-loop over the maps to filter the output?

The temporal database has no "mean" column, so the current where
option can not be used.
However, its really simple to improve that. Feel free to implement the
following steps into GRASS, to avoid extra post-processing by adding
new statistical metadata columns:

1. Patch the raster library to compute and store the mean value on the
fly while writing the raster rows and write tests for validation. It
already stores min and max, so this shouldn't be too difficult. In
addition, you can compute many other statistical values on the fly
like median and so on and add them to the raster map metadata. Be
aware of backward compatibility for raster map layers that do not have
the new metadata.
2. Patch r.info to show the new statistical metadata and write tests
to validate it. Be aware of backward compatibility.
3. Patch the temporal database SQL scripts to add the new statistical
columns to raster layers and space time raster datasets (STRDS) e.g.:
min_mean, max_mean, min_median, max_median, ... .
4. Patch the temporal framework and include the new statistical
columns in the space-time classes for raster layers and STRDS and
write tests for validation. Make sure your improvements are compatible
with older GRASS GIS temporal database versions.
5. Patch the temporal modules to make use the new statistical columns
(where queries, t.info ...) and write tests for validation. Be aware
of backward compatibility.
6. Patch r.univar and t.rast.univar to compute only the remaining
statistical values, write tests and update the documentation.
7. Update the documentation about raster map layers and the temporal
framework using the new statistical metadata for raster maps and STRDS

This is a nice little GRASS development project. :slight_smile:

I feel it's worth it to avoid the extra post-processing step, outside of
GRASS' environment. May I open an enhancement request?

Yes, feel free to open an enhancement request. The workflow howto
implement this is clear i think.

Best regards
Sören

Thank you, Nikos

11

On Sun, Oct 22, 2017 at 9:47 PM, Sören Gebbert <soerengebbert@googlemail.com> wrote:

[snip]

(Sorry for bottom-posting, want to break the discussion to a new
“thread”)

It is, and was, clear that the t.rast.list module does exactly
what it is named for. It lists the raster maps of an strds and their
properties.

On the other hand, the skepsis on t.rast.univar to be able to report
maps with a where SQL query that accepts mean values as a “column” is of
interest.

But t.rast.univar is designed to compute the mean values. IMHO, you
can’t expect to have a selection option of a value that should be
computed after selection?
The beauty of GRASS GIS is its Unix-like design. Many dedicated
modules that show their strength when combined with other Unix tools.
How about using AWK?

Univariate statistics are computed and reported. Is it really
such a computational overhead to keep that “mean”, or any other of the
descriptive statistics, and re-loop over the maps to filter the output?

The temporal database has no “mean” column, so the current where
option can not be used.
However, its really simple to improve that. Feel free to implement the
following steps into GRASS, to avoid extra post-processing by adding
new statistical metadata columns:

  1. Patch the raster library to compute and store the mean value on the
    fly while writing the raster rows and write tests for validation. It
    already stores min and max, so this shouldn’t be too difficult. In
    addition, you can compute many other statistical values on the fly
    like median and so on and add them to the raster map metadata. Be
    aware of backward compatibility for raster map layers that do not have
    the new metadata.
  2. Patch r.info to show the new statistical metadata and write tests
    to validate it. Be aware of backward compatibility.

Adding

  • standard deviation

  • number of non-NULL cells

  • handling of maps that are all NULL

  • compatibility between 32 bit and 64 bit versions

  • compatibility between little endian and big endian systems

Having these statistics instantly available with r.info could substantially speed up time series processing. Technically this is possible.

Markus M

  1. Patch the temporal database SQL scripts to add the new statistical
    columns to raster layers and space time raster datasets (STRDS) e.g.:
    min_mean, max_mean, min_median, max_median, … .
  2. Patch the temporal framework and include the new statistical
    columns in the space-time classes for raster layers and STRDS and
    write tests for validation. Make sure your improvements are compatible
    with older GRASS GIS temporal database versions.
  3. Patch the temporal modules to make use the new statistical columns
    (where queries, t.info …) and write tests for validation. Be aware
    of backward compatibility.
  4. Patch r.univar and t.rast.univar to compute only the remaining
    statistical values, write tests and update the documentation.
  5. Update the documentation about raster map layers and the temporal
    framework using the new statistical metadata for raster maps and STRDS

This is a nice little GRASS development project. :slight_smile:

I feel it’s worth it to avoid the extra post-processing step, outside of
GRASS’ environment. May I open an enhancement request?

Yes, feel free to open an enhancement request. The workflow howto
implement this is clear i think.

Best regards
Sören

Thank you, Nikos

grass-dev mailing list
grass-dev@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/grass-dev