def match_wgn(self, prog): if Weather_wgn_cli.select().count() > 0: self.emit_progress(prog, "Matching wgn to weather stations...") with project_base.db.atomic(): for row in Weather_sta_cli.select(): id = closest_lat_lon(project_base.db, "weather_wgn_cli", row.lat, row.lon) row.wgn = id row.save()
def match_to_weather_stations(self, start_prog, total_prog):
if Weather_wgn_cli.select().count() > 0:
with project_base.db.atomic():
query = Weather_sta_cli.select()
records = query.count()
i = 1
for row in query:
if self.__abort: return
prog = round(i * total_prog / records) + start_prog
i += 1
if row.lat is not None and row.lon is not None:
id = closest_lat_lon(project_base.db, "weather_wgn_cli", row.lat, row.lon)
self.emit_progress(prog, "Updating weather station with generator {i}/{total}...".format(i=i, total=records))
row.wgn_id = id
row.save()
def create_weather_stations(
self, start_prog, total_prog
): # total_prog is the total progress percentage available for this method
if self.__abort: return
stations = []
query = Weather_wgn_cli.select()
records = query.count()
i = 1
for row in query:
if self.__abort: return
lat = row.lat
lon = row.lon
#name = "w{lat}{lon}".format(lat=abs(round(lat*1000)), lon=abs(round(lon*1000)))
name = weather_sta_name(lat, lon)
prog = round(i * total_prog / records) + start_prog
# self.emit_progress(prog, "Creating weather station {name}...".format(name=name))
try:
existing = Weather_sta_cli.get(Weather_sta_cli.name == name)
except Weather_sta_cli.DoesNotExist:
station = {
"name": name,
"hmd": None,
"pcp": None,
"slr": None,
"tmp": None,
"wnd": None,
"wnd_dir": None,
"atmo_dep": None,
"lat": lat,
"lon": lon,
"wgn": row.id
}
stations.append(station)
i += 1
db_lib.bulk_insert(project_base.db, Weather_sta_cli, stations)
def add_wgn_stations_db(self, start_prog, total_prog):
if self.__abort: return
conn = sqlite3.connect(self.wgn_database)
conn.row_factory = sqlite3.Row
monthly_table = "{}_mon".format(self.wgn_table)
if not db_lib.exists_table(conn, self.wgn_table):
raise ValueError("Table {table} does not exist in {file}.".format(
table=self.wgn_table, file=self.wgn_database))
if not db_lib.exists_table(conn, monthly_table):
raise ValueError("Table {table} does not exist in {file}.".format(
table=monthly_table, file=self.wgn_database))
if Rout_unit_con.select().count() > 0:
coords = Rout_unit_con.select(
fn.Min(Rout_unit_con.lat).alias("min_lat"),
fn.Max(Rout_unit_con.lat).alias("max_lat"),
fn.Min(Rout_unit_con.lon).alias("min_lon"),
fn.Max(Rout_unit_con.lon).alias("max_lon")).get()
query = "select * from {table_name} where lat between ? and ? and lon between ? and ? order by name".format(
table_name=self.wgn_table)
tol = 0.5
cursor = conn.cursor().execute(
query, (coords.min_lat - tol, coords.max_lat + tol,
coords.min_lon - tol, coords.max_lon + tol))
elif Chandeg_con.select().count() > 0:
coords = Chandeg_con.select(
fn.Min(Chandeg_con.lat).alias("min_lat"),
fn.Max(Chandeg_con.lat).alias("max_lat"),
fn.Min(Chandeg_con.lon).alias("min_lon"),
fn.Max(Chandeg_con.lon).alias("max_lon")).get()
query = "select * from {table_name} where lat between ? and ? and lon between ? and ? order by name".format(
table_name=self.wgn_table)
tol = 0.5
cursor = conn.cursor().execute(
query, (coords.min_lat - tol, coords.max_lat + tol,
coords.min_lon - tol, coords.max_lon + tol))
else:
query = "select * from {table_name} order by name".format(
table_name=self.wgn_table)
cursor = conn.cursor().execute(query)
wgns = []
ids = []
data = cursor.fetchall()
records = len(data)
#print(records)
i = 1
print('\t - Preparing weather generator')
for row in data:
if self.__abort: return
try:
existing = Weather_wgn_cli.get(
Weather_wgn_cli.name == row['name'])
except Weather_wgn_cli.DoesNotExist:
prog = round(i * (total_prog / 2) / records) + start_prog
# self.emit_progress(prog, "Preparing weather generator {name}...".format(name=row['name']))
i += 1
ids.append(row['id'])
wgn = {
"id": row['id'],
"name": row['name'],
"lat": row['lat'],
"lon": row['lon'],
"elev": row['elev'],
"rain_yrs": row['rain_yrs']
}
wgns.append(wgn)
prog = start_prog if records < 1 else round(i * (total_prog / 2) /
records) + start_prog
# self.emit_progress(prog, "Inserting {total} weather generators...".format(total=len(ids)))
db_lib.bulk_insert(project_base.db, Weather_wgn_cli, wgns)
# Chunk the id array so we don't hit the SQLite parameter limit!
max_length = 999
id_chunks = [
ids[i:i + max_length] for i in range(0, len(ids), max_length)
]
i = 1
start_prog = start_prog + (total_prog / 2)
mon_count_query = "select count(*) from {table_name}".format(
table_name=monthly_table)
total_mon_rows = conn.cursor().execute(mon_count_query).fetchone()[0]
current_total = 0
for chunk in id_chunks:
monthly_values = []
mon_query = "select * from {table_name} where wgn_id in ({ids})".format(
table_name=monthly_table, ids=",".join('?' * len(chunk)))
mon_cursor = conn.cursor().execute(mon_query, chunk)
mon_data = mon_cursor.fetchall()
mon_records = len(mon_data)
i = 1
for row in mon_data:
if self.__abort: return
if i == 1 or (i % 12 == 0):
prog = round(i *
(total_prog / 2) / mon_records) + start_prog
# self.emit_progress(prog, "Preparing monthly values {i}/{total}...".format(i=i, total=mon_records))
i += 1
mon = {
"weather_wgn_cli": row['wgn_id'],
"month": row['month'],
"tmp_max_ave": row['tmp_max_ave'],
"tmp_min_ave": row['tmp_min_ave'],
"tmp_max_sd": row['tmp_max_sd'],
"tmp_min_sd": row['tmp_min_sd'],
"pcp_ave": row['pcp_ave'],
"pcp_sd": row['pcp_sd'],
"pcp_skew": row['pcp_skew'],
"wet_dry": row['wet_dry'],
"wet_wet": row['wet_wet'],
"pcp_days": row['pcp_days'],
"pcp_hhr": row['pcp_hhr'],
"slr_ave": row['slr_ave'],
"dew_ave": row['dew_ave'],
"wnd_ave": row['wnd_ave']
}
monthly_values.append(mon)
prog = round(i * (total_prog / 2) / mon_records) + start_prog
current_total = current_total + mon_records
# self.emit_progress(prog, "Inserting monthly values {rec}/{total}...".format(rec=current_total, total=total_mon_rows))
db_lib.bulk_insert(project_base.db, Weather_wgn_cli_mon,
monthly_values)
def delete_existing(self):
Weather_wgn_cli_mon.delete().execute()
Weather_wgn_cli.delete().execute()