I have time series data from several weather stations located in a specific area. The readings include a timestamp, the humidity and the temperature. The resolution of the data is quite high, about 6 measurements every second. The recorded data are provided as CSV files (one CSV for each day for each weather station).

My question is how is it possible to perform a query like this: Find a station (or let's say an area) where the temperature is higher than 20°C for more than 6 hours during the last 3 months.

Additionally, it would be meaningful to create some graphs in a web application. From my knowledge it's non trivial to pass such a big amount of data into a javascript library (e.g. highcharts,...) and then visualize them.

I would appreciate any hints or examples.

  • 1
    $\begingroup$ This seems to come down to specific syntax (or equivalent) in particular software and so there could be dozens of answers depending on what is holding or looking at the file. You have a generic question, but I can't envisage a generic answer. Indeed, the question is ambiguous on whether you insist on a sustained spell lasting at least 6 hours or would be content with a total of 6 hours within the last 3 months. $\endgroup$ – Nick Cox Dec 13 '18 at 17:12
  • $\begingroup$ Hi Nick, my question is generic as i am not experienced with such data. About the query, yes it should be the duration. For example the result in such query should be a period, or date/time range or multiple date/time ranges. $\endgroup$ – jorgeb Dec 13 '18 at 17:30
  • 1
    $\begingroup$ That's cleared that up. But for example I could give suggestions in my favourite software if you used it, yet that would be off-topic here. And similarly for anybody else's favourite software. I am not even clear that pseudocode would help you. Either way, you need someone else to think this is answerable and then to answer it. $\endgroup$ – Nick Cox Dec 13 '18 at 17:43

For data, that not fits into single server machine I can recommend to use a distributed database like HBase and OpenTSDB on the top (Cassandra+KairosDB alternatively). Then you can put your measures there.

For the query Find a station (or let's say an area) where the temperature is higher than 20°C for more than 6 hours during the last 3 months. you can use aggregations: min value. The time frame for the aggregation is 6h period. If the min within the period is higher than 20 then it satisfies your requirement. You need to "slide" your time window by a resolution (step) to check all "candidates" periods of time.

Query for min value for single time frame is running on the OpenTSDB. For making sequence of queries you need to write your own simple aplpication.

| cite | improve this answer | |

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