kinm/plotdata.py at main · Deltares-research/kinm · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
# -*- coding: utf-8 -*-
"""
Created on Fri Jan 29 17:38:11 2021

@author: hendrik_gt
"""
import json
import requests
import time
import datetime
import os
import matplotlib.pyplot as plt

# conda packages
import pandas as pd
import configparser

from kinm_helpders import establishconnection


local = False
if not local:
    dirname =  os.path.dirname(__file__)
    fc = os.path.join(dirname,'connection_kinm.txt')
else:
    dirname = r'D:\projecten\datamanagement\Nederland\waterkwaliteit\kinm_data'
    fc = os.path.join(dirname,'connection_local.txt')

session,engine = establishconnection(fc)

params = ('troebelheid','tp','ammonium','ph','ec','zuurstof','temperatuur','nitraat','regen','trp')
trailers = ('Meettrailer01_RD','Meettrailer02_RD')
for trailer in trailers:
    for parameter in params:
        strsql = """SELECT datetime,scalarvalue FROM timeseries.timeseriesvaluesandflags tsv
        join timeseries.timeseries ts on ts.timeserieskey = tsv.timeserieskey
        join timeseries.parameter p on p.parameterkey = ts.parameterkey
        join timeseries.location l on l.locationkey = ts.locationkey
        where l.name = '{t}' and p.name = '{p}' and datetime > to_timestamp('2021-02-2 00', 'YYYY-MM-DD HH24')
        order by datetime""".format(p=parameter,t=trailer)

        df = pd.read_sql(strsql,engine)
        df.plot(kind="line", x="datetime", y="scalarvalue",title=' '.join([parameter,'for trailer',trailer] ))
        plt.show()

session.close()
engine.dispose()

#

# strsql = """SELECT datetime, pumpvalue FROM timeseries.timeseriespumphistory tsv
# join timeseries.timeseries ts on ts.timeserieskey = tsv.timeserieskey
# join timeseries.parameter p on p.parameterkey = ts.parameterkey
# join timeseries.location l on l.locationkey = ts.locationkey
# where l.name in ('Meettrailer01_RD') and p.name = 'pump'
# order by datetime
# """
# df = pd.read_sql(strsql,engine)
# df.plot(kind="line", x="datetime", y="pumpvalue",title='pump indicator')
# plt.show()