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,
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 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"
)