def site_alerts(curr_trig, ts, release_data_ts):
site_id = curr_trig['site_id'].values[0]
query = "SELECT site_id, stat.trigger_id, trigger_source, alert_level FROM "
query += " (SELECT * FROM alert_status "
query += " WHERE ts_last_retrigger >= '%s' " % (ts - timedelta(1))
query += " ) as stat "
query += "INNER JOIN "
query += " (SELECT trigger_id, site_id, trigger_source, alert_level FROM "
query += " (SELECT * FROM operational_triggers "
query += " WHERE site_id = %s " % site_id
query += " ) as op "
query += " INNER JOIN "
query += " (SELECT trigger_sym_id, trigger_source, alert_level FROM "
query += " operational_trigger_symbols AS trig_sym "
query += " INNER JOIN "
query += " trigger_hierarchies AS trig "
query += " ON trig.source_id = trig_sym.source_id "
query += " ) as sym "
query += " ON op.trigger_sym_id = sym.trigger_sym_id "
query += " ) as sub "
query += "ON stat.trigger_id = sub.trigger_id"
sent_alert = qdb.get_db_dataframe(query)
query = "SELECT * FROM alert_status"
query += " WHERE trigger_id in (%s)" %(','.join(map(lambda x: str(x), \
set(curr_trig['trigger_id'].values))))
written = qdb.get_db_dataframe(query)
site_curr_trig = curr_trig[~curr_trig.trigger_id.isin(written.trigger_id)]
site_curr_trig = site_curr_trig.sort_values('alert_level', ascending=False)
site_curr_trig = site_curr_trig.drop_duplicates('trigger_source')
if len(site_curr_trig) == 0:
qdb.print_out('no new trigger for site_id %s' % site_id)
return
if len(sent_alert) == 0:
pass
elif max(site_curr_trig.alert_level) <= max(sent_alert.alert_level):
if max(sent_alert.alert_level) > 1 or \
(max(site_curr_trig.alert_level) == 1 and \
'surficial' not in site_curr_trig['trigger_source'].values):
qdb.print_out('no higher trigger')
return
site_curr_trig = site_curr_trig[site_curr_trig.trigger_source ==
'surficial']
else:
site_curr_trig = site_curr_trig[
site_curr_trig.alert_level > max(sent_alert.alert_level)]
alert_status = site_curr_trig[['ts_last_retrigger', 'trigger_id']]
alert_status = alert_status.rename(columns={'ts': 'ts_last_retrigger'})
alert_status['ts_set'] = datetime.now()
qdb.push_db_dataframe(alert_status, 'alert_status', index=False)
def get_monitoring_type(site_id, end):
"""Type of monitoring: 'event' or 'routine'. Extended monitoring is tagged
as 'routine' since it requires surficial data once a day. For simplicity,
sites not under monitoring (event, extended, routine) are tagged 'routine'.
Args:
site_id (int): ID of each site.
end (datetime): Current public alert timestamp.
Returns:
str: 'event' or 'routine'.
"""
query = "SELECT alert_type FROM "
query += " (SELECT * FROM public_alerts "
query += " WHERE site_id = %s " % site_id
query += " AND ((ts_updated <= '%s' " % end
query += " AND ts_updated >= '%s') " % (end - timedelta(hours=0.5))
query += " OR (ts_updated >= '%s' " % end
query += " AND ts <= '%s')) " % end
query += " ORDER BY ts DESC LIMIT 1 "
query += " ) AS pub "
query += "INNER JOIN "
query += " public_alert_symbols AS sym "
query += "ON pub.pub_sym_id = sym.pub_sym_id"
monitoring_type = qdb.get_db_dataframe(query)['alert_type'].values[0]
return monitoring_type
def get_operational_trigger(site_id, start_monitor, end):
"""Dataframe containing alert level on each operational trigger
from start of monitoring.
Args:
site_id (dataframe): ID each site.
start_monitor (datetime): Timestamp of start of monitoring.
end (datetime): Public alert timestamp.
Returns:
dataframe: Contains timestamp range of alert, three-letter site code,
operational trigger, alert level, and alert symbol from
start of monitoring
"""
query = "SELECT op.trigger_id, op.trigger_sym_id, ts, site_id, source_id, alert_level, "
query += "alert_symbol, ts_updated FROM"
query += " (SELECT * FROM operational_triggers "
query += " WHERE site_id = %s" % site_id
query += " AND ts_updated >= '%s' AND ts <= '%s' " % (start_monitor, end)
query += " ) AS op "
query += "INNER JOIN "
query += " operational_trigger_symbols AS sym "
query += "ON op.trigger_sym_id = sym.trigger_sym_id "
query += "ORDER BY ts DESC"
op_trigger = qdb.get_db_dataframe(query)
return op_trigger
def get_alert_history(current_events):
site_id = current_events['site_id'].values[0]
start_ts = current_events['ts'].values[0]
public_alert_symbols = current_events['alert_symbol'].values[0]
query = "SELECT CONCAT(cdb.firstname, ' ', cdb.lastname) as iomp, "
query += "sites.site_code, OTS.alert_symbol, ALS.ts_last_retrigger, OTS.alert_level, "
query += "ALS.remarks, TH.trigger_source, ALS.alert_status, PAS.alert_symbol as public_alert_symbol "
query += "FROM alert_status as ALS "
query += " JOIN operational_triggers as OT "
query += " ON ALS.trigger_id = OT.trigger_id "
# LOUIE - added commons_db.
query += " JOIN commons_db.sites "
query += " ON sites.site_id = OT.site_id "
query += " JOIN operational_trigger_symbols as OTS "
query += " ON OT.trigger_sym_id = OTS.trigger_sym_id "
query += " JOIN trigger_hierarchies as TH "
query += " ON OTS.source_id = TH.source_id "
query += " JOIN comms_db.users as cdb "
query += " ON ALS.user_id = cdb.user_id "
query += " JOIN public_alerts as PA"
query += " ON PA.site_id = OT.site_id"
query += " JOIN public_alert_symbols as PAS "
query += " ON PA.pub_sym_id = PAS.pub_sym_id "
query += "WHERE OT.site_id = '%s' " % site_id
query += "AND OT.ts >= '%s' " % start_ts
query += "AND PAS.alert_symbol = '%s' " % public_alert_symbols
query += "ORDER BY OT.ts DESC"
current_events_history = qdb.get_db_dataframe(query)
return current_events_history
def get_site_moms_alerts(site_id, start, end):
"""
Returns sorted site moms and
highest moms alert level or -1 if no moms is in db
Args:
TODO
"""
query = "SELECT * FROM senslopedb.monitoring_moms as moms"
query = f"{query} JOIN senslopedb.moms_instances as mi"
query = f"{query} ON moms.instance_id = mi.instance_id"
query = f"{query} JOIN commons_db.sites as site"
query = f"{query} ON mi.site_id = site.site_id"
query = f"{query} WHERE site.site_id = {site_id}"
query = f"{query} AND moms.observance_ts >= '{start}'"
query = f"{query} AND moms.observance_ts <= '{end}'"
query = f"{query} ORDER BY moms.observance_ts DESC"
site_moms_alerts_df = qdb.get_db_dataframe(query)
sorted_df = site_moms_alerts_df.sort_values(['op_trigger'],
ascending=[False])
moms_op_trigger = 0
if not sorted_df.empty:
moms_op_trigger = sorted_df.iloc[0]["op_trigger"]
else:
moms_op_trigger = -1
return site_moms_alerts_df, moms_op_trigger
def get_moms_feature_types():
"""Dataframe containing moms feature types for use in tech info
"""
query = "SELECT feature_id, feature_type FROM moms_features"
moms_types = qdb.get_db_dataframe(query)
return moms_types
def to_DB(df):
df = df.rename(columns={'dyna': 'alert', 'sandbox': 'alert_ref'})
if not qdb.does_table_exist('db_comparison', hostdb='sandbox'):
create_db_comparison()
query = "SELECT EXISTS (SELECT * FROM db_comparison"
query += " WHERE ts = '%s' AND site_code = '%s')" % (
df['ts'].values[0], df['site_code'].values[0])
if qdb.get_db_dataframe(query, hostdb='sandbox').values[0][0] == 0:
qdb.push_db_dataframe(df, 'db_comparison', index=False)
def event_start(site_id, end):
"""Timestamp of start of event monitoring. Start of event is computed
by checking if event progresses from non A0 to higher alert.
Args:
site_id (int): ID of each site.
end (datetime): Current public alert timestamp.
Returns:
datetime: Timestamp of start of monitoring.
"""
query = "SELECT ts, ts_updated FROM "
query += " (SELECT * FROM public_alerts "
query += " WHERE site_id = %s " % site_id
query += " AND (ts_updated <= '%s' " % end
query += " OR (ts_updated >= '%s' " % end
query += " AND ts <= '%s')) " % end
query += " ) AS pub " % site_id
query += "INNER JOIN "
query += " (SELECT * FROM public_alert_symbols "
query += " WHERE alert_type = 'event') AS sym "
query += "ON pub.pub_sym_id = sym.pub_sym_id "
query += "ORDER BY ts DESC LIMIT 3"
# previous positive alert
prev_pub_alerts = qdb.get_db_dataframe(query)
if len(prev_pub_alerts) == 1:
start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[0])
# two previous positive alert
elif len(prev_pub_alerts) == 2:
# one event with two previous positive alert
if pd.to_datetime(prev_pub_alerts['ts'].values[0]) - \
pd.to_datetime(prev_pub_alerts['ts_updated'].values[1]) <= \
timedelta(hours=0.5):
start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[1])
else:
start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[0])
# three previous positive alert
else:
if pd.to_datetime(prev_pub_alerts['ts'].values[0]) - \
pd.to_datetime(prev_pub_alerts['ts_updated'].values[1]) <= \
timedelta(hours=0.5):
# one event with three previous positive alert
if pd.to_datetime(prev_pub_alerts['ts'].values[1]) - \
pd.to_datetime(prev_pub_alerts['ts_updated'].values[2]) \
<= timedelta(hours=0.5):
start_monitor = pd.to_datetime(prev_pub_alerts['ts']\
.values[2])
# one event with two previous positive alert
else:
start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[1])
else:
start_monitor = pd.to_datetime(prev_pub_alerts['ts'].values[0])
return start_monitor
def query_surficial_alerts(site_id, latest_trigger_ts):
query = "SELECT * FROM marker_alerts as ma "
query += "JOIN site_markers as sm "
query += "ON ma.marker_id = sm.marker_id "
query += "WHERE sm.site_id = '%s' and ts = '%s'" % (site_id,
latest_trigger_ts)
query += "AND alert_level > 0"
result = qdb.get_db_dataframe(query)
return result
def query_od_alerts(site_id, latest_trigger_ts):
query = "SELECT * FROM senslopedb.public_alert_on_demand as paod " + \
f"WHERE site_id = {site_id} and ts = '{latest_trigger_ts}'"
result = qdb.get_db_dataframe(query)
if not result.empty:
result = result.to_dict('records')[0]
return result
def get_unprocessed_data():
"""
Get dataframe of unprocessed marker alert.
"""
query = "SELECT * FROM unprocessed"
df = qdb.get_db_dataframe(query)
return df
def get_surficial_data():
"""
Get dataframe of all marker measurements.
"""
query = "SELECT * FROM marker_meas"
df = qdb.get_db_dataframe(query)
return df
def query_eq_alerts(site_id, latest_trigger_ts):
query = "SELECT * FROM senslopedb.earthquake_events " + \
f"WHERE site_id = {site_id} and ts = '{latest_trigger_ts}'"
result = qdb.get_db_dataframe(query)
if not result.empty:
result = result.to_dict('records')[0]
return result
def query_current_events(end):
query = "SELECT PA.ts, PA.ts_updated, PA.site_id, PAS.alert_symbol FROM public_alerts as PA "
query += " JOIN public_alert_symbols as PAS "
query += " ON PA.pub_sym_id = PAS.pub_sym_id "
query += " WHERE PAS.alert_level > 0 "
query += " AND ts_updated >= '%s' " % end
query += " ORDER BY ts DESC "
events = qdb.get_db_dataframe(query)
current_events = events.groupby('site_id', as_index=False)
return current_events
def get_public_symbols():
"""Dataframe containing public alert level and its corresponding symbol.
"""
query = "SELECT * FROM public_alert_symbols"
public_symbols = qdb.get_db_dataframe(query)
public_symbols = public_symbols.sort_values(['alert_type', 'alert_level'],
ascending=[True, False])
return public_symbols
def query_rainfall_alerts(site_id, latest_trigger_ts):
query = "SELECT ra.*, rp.* "
query += "FROM rainfall_alerts AS ra "
query += "JOIN rain_props AS rp "
query += "ON ra.rain_id = rp.rain_id "
query += "AND ra.site_id = rp.site_id "
query += "WHERE ra.site_id = '%s' AND ts = '%s'" % (site_id,
latest_trigger_ts)
result = qdb.get_db_dataframe(query)
return result
def query_moms_alerts(site_id, latest_trigger_ts):
query = "SELECT * FROM senslopedb.monitoring_moms as moms " + \
"JOIN senslopedb.moms_instances as mi " + \
"ON moms.instance_id = mi.instance_id " + \
"JOIN commons_db.sites as site " + \
"ON mi.site_id = site.site_id " + \
f"WHERE site.site_id = '{site_id}'" + \
f"AND moms.observance_ts = '{latest_trigger_ts}'" + \
f"AND moms.op_trigger > 0"
result = qdb.get_db_dataframe(query)
return result
def get_trigger_symbols():
"""Dataframe containing operational trigger alert level and
its corresponding id/symbol.
"""
query = "SELECT trigger_sym_id, alert_level, alert_symbol, "
query += "op.source_id, trigger_source FROM "
query += " operational_trigger_symbols AS op "
query += "INNER JOIN "
query += " trigger_hierarchies AS trig "
query += "ON op.source_id = trig.source_id"
trig_symbols = qdb.get_db_dataframe(query)
return trig_symbols
def query_tsm_alerts(site_id, start_ts, latest_trigger_ts):
query = "SELECT ts, t_s.tsm_name, node_id, disp_alert, vel_alert FROM node_alerts "
query += "JOIN tsm_sensors AS t_s"
query += " ON node_alerts.tsm_id = t_s.tsm_id "
query += "JOIN "
query += " (SELECT site_code, site_id FROM sites WHERE site_id = '%s') AS sc " % (
site_id)
query += " ON t_s.site_id = sc.site_id "
query += "WHERE ts >= '%s' " % (start_ts)
query += "AND ts <= '%s' " % (latest_trigger_ts)
query += "AND t_s.tsm_name LIKE CONCAT(sc.site_code, '%') "
query += "ORDER BY ts DESC"
result = qdb.get_db_dataframe(query)
return result
def get_surficial_data(site_id, use_plot=False):
"""
Get dataframe of surficial measurement per marker per marker observation.
Get only last 10 marker observation if not for plotting.
"""
query = "select * "
query += "from "
query += " (select data_id, ts, marker_id, meas, alert_level "
query += " from "
query += " (select distinct(ts), mo_id from marker_observations "
query += " where site_id = %s " % site_id
query += " order by ts desc "
if not use_plot:
query += "limit 10 "
query += " ) as mo "
query += " inner join "
query += " (select marker_id, data_id, mo_id, meas, alert_level "
query += " from "
query += " marker_data "
query += " inner join "
query += " markers "
query += " using (marker_id) "
query += " ) as marker_meas "
query += " using (mo_id) "
query += " ) as data "
query += " inner join "
query += " (select marker_id, name "
query += " from "
query += " marker_histories "
query += " inner join "
query += " (select max(ts) as ts, marker_id "
query += " from marker_histories "
query += " where event_id in ( "
query += " select event_id "
query += " from marker_events "
query += " where event_type in ( "
query += " 'rename', 'add')) "
query += " group by marker_id "
query += " ) as hist "
query += " using (ts, marker_id) "
query += " ) as marker_name "
query += " using (marker_id)"
df = qdb.get_db_dataframe(query)
return df
def replace_rainfall_alert_if_rx(internal_df, internal_symbols, site_id, end,
rainfall_id, rain75_id):
"""Current internal alert sysmbol: includes rainfall symbol if
above 75% of threshold
Args:
internal_df (dataframe): Current internal alert level and sysmbol.
internal_symbols (dataframe): Internal alert symbols and id
corresponding to its alert level.
site_id (dataframe): ID each site.
end (datetime): Public alert timestamp.
rainfall_id (int): id of rainfall operational trigger
Returns:
dataframe: alert symbol indicating event triggers, data presence
and status of rainfall.
"""
query = "SELECT * FROM rainfall_alerts "
query += "where site_id = '%s' " % site_id
query += "and ts = '%s'" % end
rainfall_df = qdb.get_db_dataframe(query)
is_x = False
if len(rainfall_df) != 0:
is_x = True
if rainfall_id in internal_df['source_id'].values:
rain_alert = internal_symbols[internal_symbols.trigger_sym_id == \
rain75_id]['alert_symbol'].values[0]
trigger_sym_id = internal_symbols[internal_symbols.trigger_sym_id == \
rain75_id]['trigger_sym_id'].values[0]
internal_df.loc[internal_df.source_id == rainfall_id,
'alert_symbol'] = rain_alert
internal_df.loc[internal_df.source_id == rainfall_id,
'trigger_sym_id'] = trigger_sym_id
else:
rain_df = internal_symbols[internal_symbols.trigger_sym_id == \
rain75_id]
rain_df['alert_symbol'] = rain_df['alert_symbol'].apply(lambda x: \
x.lower())
internal_df = internal_df.append(rain_df, ignore_index=True)
return internal_df, is_x
def get_tsm_alert(site_id, end):
"""Dataframe containing alert level on each tsm sensor
Args:
site_id (dataframe): ID each site.
end (datetime): Public alert timestamp.
Returns:
dataframe: Contains tsm name, alert level, and alert symbol
for current release
"""
query = "SELECT tsm_name, sub.alert_level FROM "
query += " (SELECT tsm_name, alert_level FROM "
query += " (SELECT * FROM tsm_alerts "
query += " WHERE ts <= '%s' " % end
query += " AND ts_updated >= '%s' " % (end - timedelta(hours=0.5))
query += " ORDER BY ts DESC "
query += " ) AS alert "
query += " INNER JOIN "
query += " (SELECT tsm_id, tsm_name FROM tsm_sensors "
query += " WHERE site_id = %s " % site_id
query += " ) AS tsm "
query += " ON tsm.tsm_id = alert.tsm_id) "
query += " AS sub "
query += "INNER JOIN "
query += " (SELECT sym.source_id, alert_symbol, alert_level FROM "
query += " (SELECT source_id, alert_level, alert_symbol FROM "
query += " operational_trigger_symbols "
query += " ) AS sym "
query += " INNER JOIN "
query += " (SELECT source_id FROM trigger_hierarchies "
query += " WHERE trigger_source = 'subsurface' "
query += " ) AS hier "
query += " ON hier.source_id = sym.source_id "
query += " ) AS sub2 "
query += "ON sub.alert_level = sub2.alert_level"
subsurface = qdb.get_db_dataframe(query)
subsurface = subsurface.drop_duplicates('tsm_name')
return subsurface
def get_internal_symbols():
"""Dataframe containing trigger alert level, source and,
hierarchy in writing its symbol in internal alert
"""
query = "SELECT trigger_sym_id, trig.source_id, trigger_source, "
query += "alert_level, alert_symbol, hierarchy_id FROM ( "
query += " SELECT op.trigger_sym_id, source_id, alert_level, "
query += " inte.alert_symbol FROM "
query += " internal_alert_symbols AS inte "
query += " INNER JOIN "
query += " operational_trigger_symbols AS op "
query += " ON op.trigger_sym_id = inte.trigger_sym_id "
query += " ) AS sub "
query += "INNER JOIN "
query += " trigger_hierarchies AS trig "
query += "ON trig.source_id = sub.source_id "
query += "ORDER BY hierarchy_id"
internal_symbols = qdb.get_db_dataframe(query)
return internal_symbols
def main():
start_time = datetime.now()
qdb.print_out(start_time)
ts = pub.round_data_ts(start_time)
release_data_ts = pub.release_time(ts) - timedelta(hours=0.5)
if qdb.does_table_exist('operational_triggers') == False:
qdb.create_operational_triggers()
query = "SELECT trigger_id, ts, site_id, trigger_source, "
query += "alert_level, ts_updated FROM "
query += " (SELECT * FROM operational_triggers "
query += " WHERE ts <= '%s' " % ts
query += " AND ts_updated >= '%s' " % (ts - timedelta(1))
query += " ) AS op "
query += "INNER JOIN "
query += " (SELECT trigger_sym_id, alert_level, trigger_source FROM "
query += " (SELECT * FROM operational_trigger_symbols "
query += " WHERE alert_level > 0 "
query += " ) AS trig_sym "
query += " INNER JOIN "
query += " trigger_hierarchies AS trig "
query += " ON trig_sym.source_id = trig.source_id "
query += " ) AS sym "
query += "ON op.trigger_sym_id = sym.trigger_sym_id "
query += "ORDER BY ts_updated DESC"
curr_trig = qdb.get_db_dataframe(query)
if len(curr_trig) == 0:
qdb.print_out('no new trigger')
return
if not qdb.does_table_exist('alert_status'):
qdb.create_alert_status()
curr_trig = curr_trig.rename(columns={"ts_updated": "ts_last_retrigger"})
site_curr_trig = curr_trig.groupby('site_id', as_index=False)
site_curr_trig.apply(site_alerts, ts=ts, release_data_ts=release_data_ts)
def writeOperationalTriggers(site_id, end):
query = "SELECT sym.alert_level, trigger_sym_id FROM ( "
query += " SELECT alert_level FROM "
query += " (SELECT * FROM tsm_alerts "
query += " where ts <= '%s' " % end
query += " and ts_updated >= '%s' " % end
query += " ) as ta "
query += " INNER JOIN "
query += " (SELECT tsm_id FROM tsm_sensors "
query += " where site_id = %s " % site_id
query += " ) as tsm "
query += " on ta.tsm_id = tsm.tsm_id "
query += " ) AS sub "
query += "INNER JOIN "
query += " (SELECT trigger_sym_id, alert_level FROM "
query += " operational_trigger_symbols AS op "
query += " INNER JOIN "
query += " (SELECT source_id FROM trigger_hierarchies "
query += " WHERE trigger_source = 'subsurface' "
query += " ) AS trig "
query += " ON op.source_id = trig.source_id "
query += " ) as sym "
query += "on sym.alert_level = sub.alert_level"
df = qdb.get_db_dataframe(query)
trigger_sym_id = df.sort_values(
'alert_level', ascending=False)['trigger_sym_id'].values[0]
operational_trigger = pd.DataFrame({
'ts': [end],
'site_id': [site_id],
'trigger_sym_id': [trigger_sym_id],
'ts_updated': [end]
})
qdb.alert_to_db(operational_trigger, 'operational_triggers')
def main(end=datetime.now()):
"""Compiles all alerts to compute for public alert and internal alert.
Writes result to public_alert table and publicalert.json
Args:
end (datetime): Optional. Public alert timestamp.
"""
start_time = datetime.now()
print(start_time)
# LOUIE
# qdb.print_out(start_time)
end = round_data_ts(pd.to_datetime(end))
# alert symbols
# public alert
public_symbols = get_public_symbols()
pub_map = alert_map(public_symbols)
# internal alert symbols
internal_symbols = get_internal_symbols()
# operational triggers symbols
trig_symbols = get_trigger_symbols()
# subsurface alert
subsurface_map = trig_symbols[trig_symbols.trigger_source == 'subsurface']
subsurface_map = alert_map(subsurface_map)
# surficial alert
surficial_map = trig_symbols[trig_symbols.trigger_source == 'surficial']
surficial_map = alert_map(surficial_map)
# Manifestation Of Movement
moms_map = trig_symbols[trig_symbols.trigger_source == 'moms']
moms_map = alert_map(moms_map)
# rainfall alert
rain_map = trig_symbols[trig_symbols.trigger_source == 'rainfall']
rain_map = alert_map(rain_map)
# LOUIE
# site id and code
query = "SELECT site_id, site_code FROM commons_db.sites WHERE active = 1"
props = qdb.get_db_dataframe(query)
props = props[props.site_code == 'mar']
site_props = props.groupby('site_id', as_index=False)
alerts = site_props.apply(site_public_alert,
end=end,
public_symbols=public_symbols,
internal_symbols=internal_symbols,
start_time=start_time).reset_index(drop=True)
alerts = alerts.sort_values(['public_alert', 'site_code'],
ascending=[False, True])
# map alert level to alert symbol
alerts['public_alert'] = alerts['public_alert'].map(pub_map)
alerts['rainfall'] = alerts['rainfall'].map(rain_map)
alerts['surficial'] = alerts['surficial'].map(surficial_map)
alerts['moms'] = alerts['moms'].map(moms_map)
site_alerts = alerts.groupby('site_code', as_index=False)
alerts = site_alerts.apply(subsurface_sym,
sym_map=subsurface_map).reset_index(drop=True)
# map invalid alerts
current_events = query_current_events(end)
current_alerts = current_events.apply(get_alert_history)
columns = [
'iomp', 'site_code', 'alert_symbol', 'ts_last_retrigger',
'alert_level', 'remarks', 'trigger_source', 'alert_status',
'public_alert_symbol'
]
invalid_alerts = pd.DataFrame(columns=columns)
try:
for site in current_alerts.site_code.unique():
site_df = current_alerts[current_alerts.site_code == site]
count = len(site_df)
for i in range(0, count):
if site_df.alert_status.values[i] == -1:
alert = pd.Series(site_df.values[i], index=columns)
invalid_alerts = invalid_alerts.append(alert,
ignore_index=True)
else:
invalid_alerts = invalid_alerts
invalid_alerts = invalid_alerts.drop_duplicates(
['alert_symbol', 'site_code'])
invalid_alerts['ts_last_retrigger'] = invalid_alerts[
'ts_last_retrigger'].apply(lambda x: str(x))
except AttributeError as att_err:
print("Empty dataframe")
invalid_alerts = pd.DataFrame()
except Exception as err:
raise (err)
all_alerts = pd.DataFrame({
'invalids': [invalid_alerts],
'alerts': [alerts]
})
public_json = all_alerts.to_json(orient="records")
output_path = os.path.abspath(
os.path.join(os.path.dirname(__file__), '../..'))
sc = qdb.memcached()
# LOUIE
if not os.path.exists(output_path + sc['fileio']['output_path']):
os.makedirs(output_path + sc['fileio']['output_path'])
print(output_path)
with open(
output_path + sc['fileio']['output_path'] +
'PublicAlertRefDB.json', 'w') as w:
w.write(public_json)
# LOUIE
print(
f"PublicAlertRefDB.json written at {output_path+sc['fileio']['output_path']}"
)
print('runtime = %s' % (datetime.now() - start_time))
return alerts
Site surficial plot
"""
if site_id == '':
site_id = sys.argv[1].lower()
data = get_surficial_data(site_id, use_plot=True)
fig = plt.figure()
ax = fig.add_subplot(111)
for name in sorted(set(data['name'])):
marker_data = data[data.name == name]
ax = plotter(marker_data, ax)
fmt = md.DateFormatter('%b %Y')
ax.xaxis.set_major_formatter(fmt)
plt.legend(loc=2)
plt.xticks(rotation=45)
fig.tight_layout()
plt.savefig('surficial' + str(site_id) + '.png',
dpi=200,
facecolor='w',
edgecolor='w',
orientation='landscape',
mode='w')
###############################################################################
if __name__ == "__main__":
query = "SELECT site_id FROM sites"
sites = qdb.get_db_dataframe(query)['site_id'].values
for site_id in sites:
surficial_plot(site_id)
