def update_ts_run_name(pool, run_name, tms_id):
try:
ts = Timeseries(pool=pool)
ts.update_run_name(id_=tms_id, run_name=run_name)
except Exception as e:
traceback.print_exc()
print("Exception occurred while updating run name for tms_id {}.".format(tms_id))
def insert_timeseries(pool, timeseries, tms_id, end_date=None):
"""
Insert timeseries to curw_obs database
:param pool: database connection pool
:param timeseries: list of [time, value] lists
:param end_date: str: timestamp of the latest data
:param tms_id: str: curw_obs timeseries (hash) id
:return:
"""
new_timeseries = []
for t in [i for i in timeseries]:
if len(t) > 1:
# Insert EventId in front of timestamp, value list
t.insert(0, tms_id)
new_timeseries.append(t)
else:
print('Invalid timeseries data:: %s', t)
if end_date is None:
end_date = new_timeseries[-1][1]
try:
ts = Timeseries(pool=pool)
ts.insert_data(timeseries=new_timeseries, upsert=True)
ts.update_end_date(id_=tms_id, end_date=end_date)
except Exception as e:
traceback.print_exc()
print(
"Exception occurred while pushing timeseries for tms_id {} to curw_obs"
.format(tms_id))
def generate_curw_obs_hash_id(pool,
variable,
unit,
unit_type,
latitude,
longitude,
station_type=None,
station_name=None,
description=None,
append_description=False,
start_date=None):
"""
Generate corresponding curw_obs hash id for a given curw observational station
:param pool: databse connection pool
:param variable: str: e.g. "Precipitation"
:param unit: str: e.g. "mm"
:param unit_type: str: e.g. "Accumulative"
:param latitude: float: e.g. 6.865576
:param longitude: float: e.g. 79.958181
:param station_type: str: enum: 'CUrW_WeatherStation' | 'CUrW_WaterLevelGauge' | 'CUrW_CrossSection' | 'Irrigation_Department'
:param station_name: str: "Urumewella"
:param description: str: "A&T Communication Box, Texas Standard Rain Gauge"
:param append_description: bool:
:param start_date: str: e.g."2019-07-01 00:00:00" ; the timestamp of the very first entry of the timeseries
:return: new curw_obs hash id
"""
try:
lat = '%.6f' % float(latitude)
lon = '%.6f' % float(longitude)
meta_data = {
'unit': unit,
'unit_type': unit_type,
'latitude': lat,
'longitude': lon
}
if variable == "Waterlevel":
variable = "WaterLevel"
meta_data['variable'] = variable
if station_type == IRRIGATION_DEPARTMENT:
station_type = StationEnum.Irrigation_Department
elif variable == CURW_WEATHER_STATION:
station_type = StationEnum.CUrW_WeatherStation
elif variable == CURW_WATER_LEVEL_STATION:
station_type = StationEnum.CUrW_WaterLevelGauge
elif variable == CURW_CROSS_SECTION:
station_type = StationEnum.CUrW_CrossSection
else:
print(
"STATION_TYPE should be either Irrigation_Department or CUrW_WeatherStation or"
"CUrW_WaterLevelGauge or CUrW_CrossSection")
exit(1)
meta_data['station_type'] = StationEnum.getTypeString(station_type)
unit_id = get_unit_id(pool=pool,
unit=unit,
unit_type=UnitType.getType(unit_type))
if unit_id is None:
add_unit(pool=pool,
unit=unit,
unit_type=UnitType.getType(unit_type))
unit_id = get_unit_id(pool=pool,
unit=unit,
unit_type=UnitType.getType(unit_type))
variable_id = get_variable_id(pool=pool, variable=variable)
if variable_id is None:
add_variable(pool=pool, variable=variable)
variable_id = get_variable_id(pool=pool, variable=variable)
station_id = get_station_id(pool=pool,
latitude=lat,
longitude=lon,
station_type=station_type)
if station_id is None:
add_station(pool=pool,
name=station_name,
latitude=lat,
longitude=lon,
station_type=station_type)
station_id = get_station_id(pool=pool,
latitude=lat,
longitude=lon,
station_type=station_type)
if description:
update_description(pool=pool,
id_=station_id,
description=description,
append=False)
elif append_description:
if description:
update_description(pool=pool,
id_=station_id,
description=description,
append=True)
TS = Timeseries(pool=pool)
tms_id = TS.get_timeseries_id_if_exists(meta_data=meta_data)
meta_data['station_id'] = station_id
meta_data['variable_id'] = variable_id
meta_data['unit_id'] = unit_id
if tms_id is None:
tms_id = TS.generate_timeseries_id(meta_data=meta_data)
meta_data['tms_id'] = tms_id
TS.insert_run(run_meta=meta_data)
if start_date:
TS.update_start_date(id_=tms_id, start_date=start_date)
return tms_id
except Exception:
traceback.print_exc()
print(
"Exception occurred while inserting run entries to curw_obs run table and making hash mapping"
)
def insert_curw_obs_runs():
hash_mapping = [['old_hash_id', 'new_hash_id']]
try:
# pool = get_Pool(host=CURW_OBS_HOST, port=CURW_OBS_PORT, user=CURW_OBS_USERNAME, password=CURW_OBS_PASSWORD,
# db=CURW_OBS_DATABASE)
pool = get_Pool(host=HOST,
port=PORT,
user=USERNAME,
password=PASSWORD,
db=DATABASE)
curw_old_obs_entries = read_csv('all_curw_obs.csv')
for old_index in range(len(curw_old_obs_entries)):
meta_data = {}
old_hash_id = curw_old_obs_entries[old_index][0]
run_name = curw_old_obs_entries[old_index][1]
station_name = curw_old_obs_entries[old_index][4]
latitude = curw_old_obs_entries[old_index][5]
longitude = curw_old_obs_entries[old_index][6]
description = curw_old_obs_entries[old_index][7]
variable = curw_old_obs_entries[old_index][8]
unit = curw_old_obs_entries[old_index][9]
unit_type = curw_old_obs_entries[old_index][10]
meta_data['run_name'] = run_name
meta_data['variable'] = variable
meta_data['unit'] = unit
meta_data['unit_type'] = unit_type
meta_data['latitude'] = latitude
meta_data['longitude'] = longitude
if variable == "WaterLevel":
station_type = StationEnum.CUrW_WaterLevelGauge
else:
station_type = StationEnum.CUrW_WeatherStation
meta_data['station_type'] = StationEnum.getTypeString(station_type)
unit_id = get_unit_id(pool=pool,
unit=unit,
unit_type=UnitType.getType(unit_type))
if unit_id is None:
add_unit(pool=pool,
unit=unit,
unit_type=UnitType.getType(unit_type))
unit_id = get_unit_id(pool=pool,
unit=unit,
unit_type=UnitType.getType(unit_type))
variable_id = get_variable_id(pool=pool, variable=variable)
if variable_id is None:
add_variable(pool=pool, variable=variable)
variable_id = get_variable_id(pool=pool, variable=variable)
station_id = get_station_id(pool=pool,
latitude=latitude,
longitude=longitude,
station_type=station_type)
if station_id is None:
add_station(pool=pool,
name=station_name,
latitude=latitude,
longitude=longitude,
station_type=station_type,
description=description)
station_id = get_station_id(pool=pool,
latitude=latitude,
longitude=longitude,
station_type=station_type)
TS = Timeseries(pool=pool)
tms_id = TS.get_timeseries_id_if_exists(meta_data=meta_data)
meta_data['station_id'] = station_id
meta_data['variable_id'] = variable_id
meta_data['unit_id'] = unit_id
if tms_id is None:
tms_id = TS.generate_timeseries_id(meta_data=meta_data)
meta_data['tms_id'] = tms_id
TS.insert_run(run_meta=meta_data)
hash_mapping.append([old_hash_id, tms_id])
create_csv(file_name='curw_to_curw_obs_hash_id_mapping.csv',
data=hash_mapping)
except Exception:
traceback.print_exc()
print(
"Exception occurred while inserting run entries to curw_obs run table and making hash mapping"
)
finally:
destroy_Pool(pool=pool)
def generate_curw_obs_hash_id(pool,
variable,
unit,
unit_type,
latitude,
longitude,
run_name,
station_type=None,
station_name=None,
description=None,
append_description=True,
update_run_name=True,
start_date=None):
"""
Generate corresponding curw_obs hash id for a given curw observational station
:param pool: databse connection pool
:param variable: str: e.g. "Precipitation"
:param unit: str: e.g. "mm"
:param unit_type: str: e.g. "Accumulative"
:param latitude: float: e.g. 6.865576
:param longitude: float: e.g. 79.958181
:param run_name: str: e.g "A&T Labs"
:param station_type: str: enum: 'CUrW_WeatherStation' | 'CUrW_WaterLevelGauge'
:param station_name: str: "Urumewella"
:param description: str: "A&T Communication Box, Texas Standard Rain Gauge"
:param append_description: bool:
:param update_run_name: bool:
:param start_date: str: e.g."2019-07-01 00:00:00" ; the timestamp of the very first entry of the timeseries
:return: new curw_obs hash id
"""
if run_name not in ('A&T Labs', 'Leecom', 'CUrW IoT'):
print(
"This function is dedicated for generating curw_obs hash ids only for 'A&T Labs', 'Leecom', 'CUrW IoT' "
"weather stations")
exit(1)
try:
meta_data = {
'run_name': run_name,
'variable': variable,
'unit': unit,
'unit_type': unit_type,
'latitude': latitude,
'longitude': longitude
}
# run_name = run_name
# station_name = station_name
# latitude = latitude
# longitude = longitude
# description = description
# variable = variable
# unit = unit
# unit_type = unit_type
if variable == "Waterlevel":
variable = "WaterLevel"
if station_type and station_type in (CURW_WATER_LEVEL_STATION,
CURW_WEATHER_STATION):
station_type = StationEnum.getType(station_type)
else:
if variable == "WaterLevel":
station_type = StationEnum.CUrW_WaterLevelGauge
else:
station_type = StationEnum.CUrW_WeatherStation
meta_data['station_type'] = StationEnum.getTypeString(station_type)
unit_id = get_unit_id(pool=pool,
unit=unit,
unit_type=UnitType.getType(unit_type))
if unit_id is None:
add_unit(pool=pool,
unit=unit,
unit_type=UnitType.getType(unit_type))
unit_id = get_unit_id(pool=pool,
unit=unit,
unit_type=UnitType.getType(unit_type))
variable_id = get_variable_id(pool=pool, variable=variable)
if variable_id is None:
add_variable(pool=pool, variable=variable)
variable_id = get_variable_id(pool=pool, variable=variable)
station_id = get_station_id(pool=pool,
latitude=latitude,
longitude=longitude,
station_type=station_type)
if station_id is None:
add_station(pool=pool,
name=station_name,
latitude=latitude,
longitude=longitude,
station_type=station_type)
station_id = get_station_id(pool=pool,
latitude=latitude,
longitude=longitude,
station_type=station_type)
if description:
update_description(pool=pool,
id_=station_id,
description=description,
append=False)
elif append_description:
if description:
update_description(pool=pool,
id_=station_id,
description=description,
append=True)
TS = Timeseries(pool=pool)
tms_id = TS.get_timeseries_id_if_exists(meta_data=meta_data)
meta_data['station_id'] = station_id
meta_data['variable_id'] = variable_id
meta_data['unit_id'] = unit_id
if tms_id is None:
tms_id = TS.generate_timeseries_id(meta_data=meta_data)
meta_data['tms_id'] = tms_id
TS.insert_run(run_meta=meta_data)
if start_date:
TS.update_start_date(id_=tms_id, start_date=start_date)
if update_run_name:
TS.update_run_name(id_=tms_id, run_name=run_name)
return tms_id
except Exception:
traceback.print_exc()
print(
"Exception occurred while inserting run entries to curw_obs run table and making hash mapping"
)
if not isinstance(variables, list) or not len(variables) > 0:
print("Station's variable list is not valid.", variables)
continue
station_name = station['name']
latitude = station['station_meta'][2]
longitude = station['station_meta'][3]
units = station['units']
unit_types = station['unit_type']
description = station['description']
for variable, unit, unit_type in zip(variables, units, unit_types):
obs_hash_id = generate_curw_obs_hash_id(pool, variable=variable, unit=unit, unit_type=unit_type,
latitude=latitude, longitude=longitude, station_name=station_name, description=description)
TS = Timeseries(pool=pool)
prev_end_date = TS.get_end_date(obs_hash_id)
if prev_end_date is not None:
start_datetime = (prev_end_date - timedelta(minutes=30)).strftime(COMMON_DATE_FORMAT)
if variable == 'Precipitation':
try:
extract_n_push_precipitation(extract_adapter, station, start_datetime, end_datetime, pool, obs_hash_id)
except Exception as ex:
print("Error occured while pushing precipitation.", ex)
elif variable == 'Temperature':
try:
extract_n_push_temperature(extract_adapter, station, start_datetime, end_datetime, pool, obs_hash_id)
except Exception as ex:
print("Error occured while pushing temperature.", ex)
elif variable == 'WindSpeed':