From: Philipp Spitzer <philipp@spitzer.priv.at>
Date: Wed, 22 Aug 2018 21:10:58 +0000 (+0200)
Subject: Implement grouping of openweathermapdata.
X-Git-Url: https://git.toastfreeware.priv.at/chrisu/seepark.git/commitdiff_plain/4e2a6e018609488515cc81942a46254e33f06a7c?ds=sidebyside

Implement grouping of openweathermapdata.
---

diff --git a/web/seepark_web.py b/web/seepark_web.py
index cbe55f8..42bbf30 100644
--- a/web/seepark_web.py
+++ b/web/seepark_web.py
@@ -72,8 +72,8 @@ def select_sensordata(sensor_id, sensor_type, begin, end):
     return query.all()
 
 
-def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
-    # determine resolution (interval in seconds for data points)
+def calc_grouping_resolution(begin, end):
+    """How many data points should be between the timestamps begin and end?"""
     # copied from munin/master/_bin/munin-cgi-graph.in
     resolutions = dict(
         day   =   300,
@@ -91,6 +91,12 @@ def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
         resolution = resolutions['month']
     else:
         resolution = resolutions['year']
+    return resolution
+
+
+def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
+    # determine resolution (interval in seconds for data points)
+    resolution = calc_grouping_resolution(begin, end)
 
     # Let the database do the grouping. Example in SQL (MySQL):
     # select to_seconds(datetime) DIV (60*60*24) as interval_id, min(datetime), max(datetime), min(temp), avg(temp), max(temp), count(temp) from openweathermap group by interval_id order by interval_id;
@@ -108,34 +114,29 @@ def select_sensordata_grouped(sensor_id, sensor_type, begin, end):
     return query.all()
 
 
-def select_openweatherdata(cityid, begin, end, mode):
+def select_openweatherdata(cityid, begin, end):
     query = OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)
     if begin is not None:
         query = query.filter(OpenWeatherMap.datetime >= begin)
     if end is not None:
         query = query.filter(OpenWeatherMap.datetime <= end)
-    if mode == 'consolidated' and begin is not None and end is not None:
-        # copied from munin/master/_bin/munin-cgi-graph.in
-        # interval in seconds for data points
-        resolutions = dict(
-            day   =   300,
-            week  =  1800,
-            month =  7200,
-            year  = 86400,
-        )
-        duration = (end - begin).total_seconds()
-        day = 60 * 60 * 24
-        if duration < day:
-            resolution = resolutions['day']
-        elif duration < 7 * day:
-            resolution = resolutions['week']
-        elif duration < 31 * day:
-            resolution = resolutions['month']
-        else:
-            resolution = resolutions['year']
-        # TODO: filter out samples from 'result'
-        # something like 
-        # select to_seconds(datetime) DIV (60*60*24) as interval_id, min(datetime), max(datetime), min(temp), avg(temp), max(temp), count(temp) from openweathermap group by interval_id order by interval_id;
+    return query.all()
+
+
+def select_openweatherdata_grouped(cityid, begin, end):
+    # determine resolution (interval in seconds for data points)
+    resolution = calc_grouping_resolution(begin, end)
+
+    # Let the database do the grouping. Example in SQL (MySQL):
+    # select to_seconds(datetime) DIV (60*60*24) as interval_id, min(datetime), max(datetime), min(temp), avg(temp), max(temp), count(temp) from openweathermap group by interval_id order by interval_id;
+    query = db.session.query(func.to_seconds(OpenWeatherMap.datetime).op('div')(resolution).label('g'),
+            func.min(OpenWeatherMap.datetime).label('datetime'),
+            func.avg(OpenWeatherMap.temp).label('temp'),
+            OpenWeatherMap.cityid)
+    OpenWeatherMap.query.filter(OpenWeatherMap.cityid == cityid)
+    query = query.filter(Sensors.timestamp >= begin)
+    query = query.filter(Sensors.timestamp <= end)
+    query = query.group_by('g', OpenWeatherMap.cityid)
     return query.all()
 
 
@@ -188,7 +189,14 @@ def openweathermapdata(cityid):
     mode = request.args.get('mode', 'full')
     format = request.args.get('format', 'default')
 
-    result = select_openweatherdata(cityid, begin, end, mode)
+    if mode == 'full':
+        result = select_openweatherdata(cityid, begin, end)
+    elif mode == 'consolidated':
+        if begin is None or end is None:
+            abort(Response('begin and end have to be set for mode==consolidated', 400))
+        result = select_openweatherdata_grouped(cityid, begin, end)
+    else:
+        abort(Response('unknown value for mode', 400))
 
     if format == 'c3':
         return convert_to_c3(result, 'cityid', 'datetime', 'temp')