def test_tablenameschema_exists(self):
"""
This test controls if the table exist in the specified schema.
The query returns True or False.
True=Tablename exists in the schema
False=Tablename does'nt exist in the schema
"""
for layer in layersData:
"""
Subtest only available on python 3.4
"""
with self.subTest(tablename=layersData[layer]['tablename'],schema_hectare=layersData[layer]['schema_hectare']):
sql_query = get_exists_table_query(tbname=layersData[layer]['tablename'], schema=layersData[layer]['schema_hectare'])
query = query_geographic_database(sql_query).fetchone()
#print("schema_hectare:"+layersData[layer]['schema_hectare'] + ", tablename:" + layersData[layer]['tablename'] + " => result:" + str(query[0]))
self.assertTrue(bool(query[0]) == True)
if layersData[layer]['data_aggregated'] == True:
with self.subTest(tablename=layersData[layer]['tablename']+"_nuts",schema_scalelvl=layersData[layer]['schema_scalelvl']):
sql_query = get_exists_table_query(tbname=layersData[layer]['tablename']+"_nuts", schema=layersData[layer]['schema_scalelvl'])
query = query_geographic_database(sql_query).fetchone()
#print("schema_scalelvl:"+layersData[layer]['schema_scalelvl'] + ", tablename:" + layersData[layer]['tablename']+"_nuts => result:" + str(query[0]))
self.assertTrue(bool(query[0]) == True)
with self.subTest(tablename=layersData[layer]['tablename']+"_lau",schema_scalelvl=layersData[layer]['schema_scalelvl']):
sql_query = get_exists_table_query(tbname=layersData[layer]['tablename']+"_lau", schema=layersData[layer]['schema_scalelvl'])
query = query_geographic_database(sql_query).fetchone()
#print("schema_scalelvl:"+layersData[layer]['schema_scalelvl'] + ", tablename:" + layersData[layer]['tablename']+"_lau => result:" + str(query[0]))
self.assertTrue(bool(query[0]) == True)
def getEnergyMixNutsLau(nuts):
sql_query = "WITH energy_total as (SELECT sum(electricity_generation) as value FROM " + ELECRICITY_MIX + " WHERE nuts0_code IN ("+nuts+") )" + \
"SELECT DISTINCT energy_carrier, SUM(electricity_generation * 100 /energy_total.value) FROM " + ELECRICITY_MIX + " ,energy_total WHERE nuts0_code IN ("+nuts+") GROUP BY energy_carrier ORDER BY energy_carrier ASC" ;
query = model.query_geographic_database(sql_query)
labels = []
data = []
backgroundColor = []
for c, l in enumerate(query):
labels.append(l[0])
data.append(helper.roundValue(l[1]))
backgroundColor.append(helper.getGenerationMixColor(l[0]))
datasets = {
'data' : data,
'label': '%',
'backgroundColor': backgroundColor
}
result = {
'labels':labels,
'datasets':datasets
}
return result
def test_aggregateddata_statlvl_nutslau(self):
"""
This function test if aggregated data are in the database.
The test runs over all indicators and check data in with schema_scalelvl (the schema) and the tablename (tablename of the dataset).
For the tablename, "_lau" and "_nuts" are added are added at the end of the tablename.
At nuts level, every stat level are tested (column name: stat_levl_), from 0 to 3.
0=NUTS 0
1=NUTS 1
2=NUTS 2
3=NUTS 3
The query return the count of rows available in the table.
Result:
Query returns 0 => No data avalaible for the specific scale
Query return >= 1 => Data are available in the specific scale
"""
for layer in layersData:
if layersData[layer]['data_aggregated'] == True:
sql_querie = "select count(*) from " + layersData[layer][
'schema_scalelvl'] + "." + layersData[layer]['tablename']
sql_querie_lau = sql_querie + "_lau limit 100"
with self.subTest(
stat_levl_='lau',
tablename=layersData[layer]['schema_scalelvl'] + "." +
layersData[layer]['tablename'] + "_lau"):
try:
query = query_geographic_database(
sql_querie_lau).fetchone()
self.assertTrue(int(query[0]) >= 1)
except Exception as e:
self.assertTrue(False)
for i in range(0, 4):
sql_querie_nuts = sql_querie + "_nuts where stat_levl_ = " + str(
i)
with self.subTest(
stat_levl_=i,
tablename=layersData[layer]['schema_scalelvl'] +
"." + layersData[layer]['tablename'] + "_nuts"):
try:
query = query_geographic_database(
sql_querie_nuts).fetchone()
self.assertTrue(int(query[0]) >= 1)
except Exception as e:
self.assertTrue(False)
def test_aggregateddata_statlvl_nutslau(self):
"""
This function test if aggregated data are in the database.
The test runs over all indicators and check data in with schema_scalelvl (the schema) and the tablename (tablename of the dataset).
For the tablename, "_lau" and "_nuts" are added are added at the end of the tablename.
At nuts level, every stat level are tested (column name: stat_levl_), from 0 to 3.
0=NUTS 0
1=NUTS 1
2=NUTS 2
3=NUTS 3
The query return the count of rows available in the table.
Result:
Query returns 0 => No data avalaible for the specific scale
Query return >= 1 => Data are available in the specific scale
"""
for layer in layersData:
if layersData[layer]['data_aggregated'] == True:
sql_querie = "select count(*) from "+layersData[layer]['schema_scalelvl']+"."+layersData[layer]['tablename']
sql_querie_lau = sql_querie + "_lau limit 100"
#print(sql_querie_lau)
with self.subTest(stat_levl_='lau',tablename=layersData[layer]['schema_scalelvl']+"."+layersData[layer]['tablename']+"_lau"):
try:
query = query_geographic_database(sql_querie_lau).fetchone()
self.assertTrue(int(query[0]) >= 1)
except Exception as e:
#print(e)
self.assertTrue(False)
for i in range(0,4):
sql_querie_nuts = sql_querie + "_nuts where stat_levl_ = "+str(i)
#print(sql_querie_nuts)
with self.subTest(stat_levl_=i,tablename=layersData[layer]['schema_scalelvl']+"."+layersData[layer]['tablename']+"_nuts"):
try:
query = query_geographic_database(sql_querie_nuts).fetchone()
self.assertTrue(int(query[0]) >= 1)
except Exception as e:
#print(e)
self.assertTrue(False)
def test_tablenameschema_exists(self):
"""
This test controls if the table exist in the specified schema.
The query returns True or False.
True=Tablename exists in the schema
False=Tablename does'nt exist in the schema
"""
for layer in layersData:
"""
Subtest only available on python 3.4
"""
with self.subTest(
tablename=layersData[layer]['tablename'],
schema_hectare=layersData[layer]['schema_hectare']):
sql_query = get_exists_table_query(
tbname=layersData[layer]['tablename'],
schema=layersData[layer]['schema_hectare'])
query = query_geographic_database(sql_query).fetchone()
self.assertTrue(bool(query[0]) == True)
if layersData[layer]['data_aggregated'] == True:
with self.subTest(
tablename=layersData[layer]['tablename'] + "_nuts",
schema_scalelvl=layersData[layer]['schema_scalelvl']):
sql_query = get_exists_table_query(
tbname=layersData[layer]['tablename'] + "_nuts",
schema=layersData[layer]['schema_scalelvl'])
query = query_geographic_database(sql_query).fetchone()
self.assertTrue(bool(query[0]) == True)
with self.subTest(
tablename=layersData[layer]['tablename'] + "_lau",
schema_scalelvl=layersData[layer]['schema_scalelvl']):
sql_query = get_exists_table_query(
tbname=layersData[layer]['tablename'] + "_lau",
schema=layersData[layer]['schema_scalelvl'])
query = query_geographic_database(sql_query).fetchone()
self.assertTrue(bool(query[0]) == True)
def duration_curve_hectares(
year, geometry): #/heat-load-profile/duration-curve/hectares
# Get the query
sql_query = createQueryDataDCHectares(year=year, geometry=geometry)
query = model.query_geographic_database(sql_query)
listAllValues = []
for n, q in enumerate(query):
listAllValues.append({'X': n + 1, 'Y': q[0]})
listAllValues = listAllValues[0:len(listAllValues) - 1:10]
return listAllValues
def heatloadprofile_nuts_lau(year, month, day, nuts, nuts_level): #/heat-load-profile/nuts-lau
request_type=''
# Check the type of the query
if month != 0 and day != 0:
request_type = 'day'
elif month != 0:
request_type = 'month'
else:
request_type = 'year'
# Get the data
query = createQueryDataLPNutsLau(year=year, month=month, day=day, nuts=nuts,request_type=request_type, nuts_level=nuts_level)
# Construction of the query
# Execution of the query
#query = db.session.execute(sql_query)
res = model.query_geographic_database(query)
# Storing the results only if there is data
output = []
for c, q in enumerate(res):
if q[0]:
data={}
if request_type == 'year':
data = {
'year': year,'month': q[4],'granularity': 'month','unit': 'kW','min': round(q[0], constants.NUMBER_DECIMAL_DATA),
'max': round(q[1], constants.NUMBER_DECIMAL_DATA),'average': round(q[2], constants.NUMBER_DECIMAL_DATA)
}
elif request_type == 'month':
data = {
'year': year,'month': month,'day': q[4],'granularity': 'day','unit': 'kW','min': round(q[0], constants.NUMBER_DECIMAL_DATA),
'max': round(q[1], constants.NUMBER_DECIMAL_DATA),
'average': round(q[2], constants.NUMBER_DECIMAL_DATA)
}
elif request_type == 'day':
data = {
'year': year,'month': month,'day': day,'hour_of_day': q[4],'granularity': 'hour',
'unit': 'kW','value': round(q[3], constants.NUMBER_DECIMAL_DATA)
}
output.append(data)
else:
output = []
return {
"values": output
}
def duration_curve_hectares(year, geometry): #/heat-load-profile/duration-curve/hectares
# Get the query
sql_query = createQueryDataDCHectares(year=year, geometry=geometry)
query = model.query_geographic_database(sql_query)
listAllValues = []
for n, q in enumerate(query):
listAllValues.append({
'X':n+1,
'Y':q[0]
})
listAllValues = listAllValues[0:len(listAllValues)-1:10]
return listAllValues
def duration_curve_nuts_lau(
year, nuts,
nuts_level): #/heat-load-profile/duration-curve/nuts-lau
# Get the query
sql_query = createQueryDataDCNutsLau(year=year,
nuts=nuts,
nuts_level=nuts_level)
# Execution of the query
#query = db.session.execute(sql_query)
query = model.query_geographic_database(sql_query)
# Store query results in a list
listAllValues = []
points = []
for n, q in enumerate(query):
#listAllValues.append(q[0])
points.append({'X': n + 1, 'Y': float(q[0])})
newList = points[0:len(points) - 1:10]
return newList
def duration_curve_nuts_lau(year, nuts, nuts_level): #/heat-load-profile/duration-curve/nuts-lau
# Get the query
sql_query = createQueryDataDCNutsLau(year=year, nuts=nuts, nuts_level=nuts_level)
# Execution of the query
#query = db.session.execute(sql_query)
query = model.query_geographic_database(sql_query)
# Store query results in a list
listAllValues = []
points = []
for n, q in enumerate(query):
#listAllValues.append(q[0])
points.append({
'X':n+1,
'Y':float(q[0])
})
newList = points[0:len(points)-1:10]
return newList
def getEnergyMixNutsLau(nuts):
sql_query = "WITH energy_total as (SELECT sum(electricity_generation) as value FROM " + ELECRICITY_MIX + " WHERE nuts0_code IN ("+nuts+") )" + \
"SELECT DISTINCT energy_carrier, SUM(electricity_generation * 100 /energy_total.value) FROM " + ELECRICITY_MIX + " ,energy_total WHERE nuts0_code IN ("+nuts+") GROUP BY energy_carrier ORDER BY energy_carrier ASC"
query = model.query_geographic_database(sql_query)
labels = []
data = []
backgroundColor = []
for c, l in enumerate(query):
labels.append(l[0])
data.append(helper.roundValue(l[1]))
backgroundColor.append(helper.getGenerationMixColor(l[0]))
datasets = {
'data': data,
'label': '%',
'backgroundColor': backgroundColor
}
result = {'labels': labels, 'datasets': datasets}
return result
def heatloadprofile_hectares(year, month, day,
geometry): #/heat-load-profile/hectares
# Check the type of the query
if month != 0 and day != 0:
by = 'byDay'
elif month != 0:
by = 'byMonth'
else:
by = 'byYear'
# Get the data
queryData = createQueryDataLPHectares(year=year,
month=month,
day=day,
geometry=geometry)
# Construction of the query
sql_query = queryData[by]['with'] + queryData[by]['select']
# Execution of the query
#query = db.session.execute(sql_query)
query = model.query_geographic_database(sql_query)
# Storing the results only if there is data
output = []
if by == 'byYear':
for c, q in enumerate(query):
if q[0]:
output.append({
'year':
year,
'month':
q[3],
'granularity':
'month',
'unit':
'kW',
'min':
round(q[0], constants.NUMBER_DECIMAL_DATA),
'max':
round(q[1], constants.NUMBER_DECIMAL_DATA),
'average':
round(q[2], constants.NUMBER_DECIMAL_DATA)
})
else:
output = []
elif by == 'byMonth':
for c, q in enumerate(query):
if q[0]:
output.append({
'year':
year,
'month':
month,
'day':
q[3],
'granularity':
'day',
'unit':
'kW',
'min':
round(q[0], constants.NUMBER_DECIMAL_DATA),
'max':
round(q[1], constants.NUMBER_DECIMAL_DATA),
'average':
round(q[2], constants.NUMBER_DECIMAL_DATA)
})
else:
output = []
else:
for c, q in enumerate(query):
if q[0]:
output.append({
'year':
year,
'month':
month,
'day':
day,
'hour_of_day':
q[1],
'granularity':
'hour',
'unit':
'kW',
'value':
round(q[0], constants.NUMBER_DECIMAL_DATA)
})
else:
output = []
return {"values": output}
def heatloadprofile_nuts_lau(year, month, day, nuts,
nuts_level): #/heat-load-profile/nuts-lau
request_type = ''
# Check the type of the query
if month != 0 and day != 0:
request_type = 'day'
elif month != 0:
request_type = 'month'
else:
request_type = 'year'
# Get the data
query = createQueryDataLPNutsLau(year=year,
month=month,
day=day,
nuts=nuts,
request_type=request_type,
nuts_level=nuts_level)
# Construction of the query
# Execution of the query
#query = db.session.execute(sql_query)
res = model.query_geographic_database(query)
# Storing the results only if there is data
output = []
for c, q in enumerate(res):
if q[0]:
data = {}
if request_type == 'year':
data = {
'year': year,
'month': q[4],
'granularity': 'month',
'unit': 'kW',
'min': str(round(q[0], constants.NUMBER_DECIMAL_DATA)),
'max': str(round(q[1], constants.NUMBER_DECIMAL_DATA)),
'average':
str(round(q[2], constants.NUMBER_DECIMAL_DATA))
}
elif request_type == 'month':
data = {
'year': year,
'month': month,
'day': q[4],
'granularity': 'day',
'unit': 'kW',
'min': str(round(q[0], constants.NUMBER_DECIMAL_DATA)),
'max': str(round(q[1], constants.NUMBER_DECIMAL_DATA)),
'average':
str(round(q[2], constants.NUMBER_DECIMAL_DATA))
}
elif request_type == 'day':
data = {
'year': year,
'month': month,
'day': day,
'hour_of_day': q[4],
'granularity': 'hour',
'unit': 'kW',
'value': str(round(q[3],
constants.NUMBER_DECIMAL_DATA))
}
output.append(data)
else:
output = []
return {"values": output}
def heatloadprofile_hectares(year, month, day, geometry): #/heat-load-profile/hectares
# Check the type of the query
if month != 0 and day != 0:
by = 'byDay'
elif month != 0:
by = 'byMonth'
else:
by = 'byYear'
# Get the data
queryData = createQueryDataLPHectares(year=year, month=month, day=day, geometry=geometry)
# Construction of the query
sql_query = queryData[by]['with'] + queryData[by]['select']
# Execution of the query
#query = db.session.execute(sql_query)
query = model.query_geographic_database(sql_query)
# Storing the results only if there is data
output = []
if by == 'byYear':
for c, q in enumerate(query):
if q[0]:
output.append({
'year': year,
'month': q[3],
'granularity': 'month',
'unit': 'kW',
'min': round(q[0], constants.NUMBER_DECIMAL_DATA),
'max': round(q[1], constants.NUMBER_DECIMAL_DATA),
'average': round(q[2], constants.NUMBER_DECIMAL_DATA)
})
else:
output = []
elif by == 'byMonth':
for c, q in enumerate(query):
if q[0]:
output.append({
'year': year,
'month': month,
'day': q[3],
'granularity': 'day',
'unit': 'kW',
'min': round(q[0], constants.NUMBER_DECIMAL_DATA),
'max': round(q[1], constants.NUMBER_DECIMAL_DATA),
'average': round(q[2], constants.NUMBER_DECIMAL_DATA)
})
else:
output = []
else:
for c, q in enumerate(query):
if q[0]:
output.append({
'year': year,
'month': month,
'day': day,
'hour_of_day': q[1],
'granularity': 'hour',
'unit': 'kW',
'value': round(q[0], constants.NUMBER_DECIMAL_DATA)
})
else:
output = []
return {
"values": output
}