import sqlite3
import matplotlib.pyplot as plt
from math import cos, pi

# 1
requete_sql = "SELECT W.Tmin FROM (Weather AS W JOIN Station AS S ON W.StationId=S.StationID) WHERE S.CallSign='JFK' AND W.Tmin IS NOT NULL;"
#StationId='94789'

# 2
c = sqlite3.connect("noaa2012.db3")
cur = c.cursor()
l = cur.execute(requete_sql)
y=[]
y1=[]
y=list(l)
y1=[y[k][0] for k in range(len(y))]

plt.figure("Température minimale quotidienne à JFK")
plt.plot(y1,c='blue',linewidth=0.5,label="Données brutes")
plt.xlabel("Année 2012")
plt.ylabel("Température")
plt.legend()
plt.show()

# 3
def moyenne(liste):
    M = len(liste)
    somme = 0
    for k in liste:
        somme += k
    moy = somme/M
    return moy

def moyenneGlissante(liste,N):
    m = []
    for k in range(len(liste)-N+1):
        m.append(moyenne(liste[k:k+N]))
    return m

# 4
x = []
z = []
t = []
x = list(range(len(y)))
N = 30
z = moyenneGlissante(y1,N)
t = list(range(int(N/2),len(z)+int(N/2)))

# 5
plt.figure("Température minimale quotidienne à JFK")
plt.plot(y1,c='blue',linewidth=0.5,label="Données brutes")
plt.plot(t,z,c='red',label="Données lissées sur {} jours".format(N))
plt.xlabel("Année 2012")
plt.ylabel("Température")
plt.legend()
plt.show()

# 6 7 8
requete_sql_2 = "SELECT weather.Tavg, station.Latitude, station.Longitude FROM weather JOIN station ON weather.StationId=station.StationId WHERE weather.Date='20120516' AND station.latitude>25 AND station.Latitude<50 AND weather.Tavg IS NOT Null;"
l=cur.execute(requete_sql_2)
z=list(l)
x=[l[1] for l in z]
y=[l[2]*cos(37.5/180*pi) for l in z]
temp=[l[0] for l in z]

plt.figure("Tempéraure Moyenne relevée le {}".format("16/05/2012"))
plt.axes().set_aspect(1)
plt.scatter(y,x,c=temp,cmap='jet')
plt.colorbar()
plt.show()