def select_events_confirmed_loc(starttime, endtime, table="LOC"):
host, port, user, password, database = get_credentials_sqldb()
db = MySQLdb.connect(host=host,
port=int(port),
user=user,
passwd=password,
db=database)
cursor = db.cursor()
status = "confirmed"
if table == "AUTOLOC":
flag = "SELECT time, longitude, latitude, depth, number_of_stations, gap, rms, status, magnitude FROM AUTOLOC where time BETWEEN '%s' AND '%s' && status='%s'" % (
starttime.strftime("%Y-%m-%d %H:%M:%S"),
endtime.strftime("%Y-%m-%d %H:%M:%S"), status)
elif table == "LOC":
flag = "SELECT time, longitude, latitude, depth, number_of_stations, gap, rms, status, magnitude FROM LOC where time BETWEEN '%s' AND '%s' && status='%s'" % (
starttime.strftime("%Y-%m-%d %H:%M:%S"),
endtime.strftime("%Y-%m-%d %H:%M:%S"), status)
cursor.execute(flag)
res = cursor.fetchall()
db.close()
events_list = []
for row in res:
time, lon, lat, depth, nstats, gap, rms, status, magnitude = row
events_list.append([
UTCDateTime(time), lon, lat, depth, magnitude,
int(nstats), gap, rms, status
])
return events_list
def update_event_localization(evtime_old,
evtime,
evlon,
evlat,
evdepth,
evrms,
everrx,
everry,
everrz,
evmag,
table="LOC"):
host, port, user, password, database = get_credentials_sqldb()
db = MySQLdb.connect(host=host,
port=int(port),
user=user,
passwd=password,
db=database)
cursor = db.cursor()
if table == "AUTOLOC":
flag = "UPDATE AUTOLOC SET time = '%s', longitude = %f, latitude = %f, depth = %f, rms = %.4f, dx = %f, dy = %f, dz = %f, magnitude = %.1f WHERE LOC . time = '%s'" % (
evtime.strftime("%Y-%m-%d %H:%M:%S"), evlon, evlat, evdepth, evrms,
everrx, everry, everrz, evmag,
evtime_old.strftime("%Y-%m-%d %H:%M:%S"))
elif table == "LOC":
flag = "UPDATE LOC SET time = '%s', longitude = %f, latitude = %f, depth = %f, rms = %.4f, dx = %f, dy = %f, dz = %f, magnitude = %.1f WHERE LOC . time = '%s'" % (
evtime.strftime("%Y-%m-%d %H:%M:%S"), evlon, evlat, evdepth, evrms,
everrx, everry, everrz, evmag,
evtime_old.strftime("%Y-%m-%d %H:%M:%S"))
cursor.execute(flag)
db.commit()
db.close()
def insert_event(evtime,
evlon,
evlat,
evdep,
evnstats,
evgap,
evrms,
evmag,
status="automatic",
table="LOC",
figs_dir=None):
host, port, user, password, database = get_credentials_sqldb()
db = MySQLdb.connect(host=host,
port=int(port),
user=user,
passwd=password,
db=database)
cursor = db.cursor()
if table == "LOC":
if figs_dir is not None:
img_wave = glob.glob(figs_dir + "/*record_section*")[0]
img_map = glob.glob(figs_dir + "/*map_event*")[0]
flag = "INSERT INTO LOC (time, longitude, latitude, depth, number_of_stations, gap, rms, magnitude, status, img_wave, img_map) VALUES ('%s', %f, %f, %f, %i, %f, %f, %.1f,'%s','%s','%s')" % (
evtime.strftime("%Y-%m-%d %H:%M:%S"), evlon, evlat, evdep,
evnstats, evgap, evrms, evmag, status,
convertToBinaryData(img_wave), convertToBinaryData(img_map))
else:
flag = "INSERT INTO LOC (time, longitude, latitude, depth, number_of_stations, gap, rms, magnitude, status) VALUES ('%s', %f, %f, %f, %i, %f, %f, %.1f,'%s')" % (
evtime.strftime("%Y-%m-%d %H:%M:%S"), evlon, evlat, evdep,
evnstats, evgap, evrms, evmag, status)
cursor.execute(flag)
db.commit()
db.close()
def ask_last_triggers_stalta_inserted(N=10):
host, port, user, password, database = get_credentials_sqldb()
db = MySQLdb.connect(host=host,
port=int(port),
user=user,
passwd=password,
db=database)
cursor = db.cursor()
flag = "SELECT * FROM AUTOTRIGGERS ORDER BY trig_on DESC LIMIT %i" % (N)
cursor.execute(flag)
res = cursor.fetchall()
for line in res:
print("[%s] %s %s " %
(line[0], line[1].strftime("%Y-%m-%d %H:%M:%S"),
line[2].strftime("%Y-%m-%d %H:%M:%S")))
db.close()
def update_event_status(origin_time, status, table="LOC"):
host, port, user, password, database = get_credentials_sqldb()
db = MySQLdb.connect(host=host,
port=int(port),
user=user,
passwd=password,
db=database)
cursor = db.cursor()
if table == "AUTOLOC":
flag = "UPDATE AUTOLOC SET status='%s' WHERE time='%s'" % (
status, origin_time.strftime("%Y-%m-%d %H:%M:%S"))
elif table == "LOC":
flag = "UPDATE LOC SET status='%s' WHERE time='%s'" % (
status, origin_time.strftime("%Y-%m-%d %H:%M:%S"))
cursor.execute(flag)
db.commit()
db.close()
def update_origin_time(evtime_old, evtime_new, table="LOC"):
host, port, user, password, database = get_credentials_sqldb()
db = MySQLdb.connect(host=host,
port=int(port),
user=user,
passwd=password,
db=database)
cursor = db.cursor()
if table == "AUTOLOC":
flag = "UPDATE AUTOLOC SET time = '%s' WHERE LOC . time = '%s'" % (
evtime_new.strftime("%Y-%m-%d %H:%M:%S"),
evtime_old.strftime("%Y-%m-%d %H:%M:%S"))
elif table == "LOC":
flag = "UPDATE LOC SET time = '%s' WHERE LOC . time = '%s'" % (
evtime_new.strftime("%Y-%m-%d %H:%M:%S"),
evtime_old.strftime("%Y-%m-%d %H:%M:%S.%f"))
cursor.execute(flag)
db.commit()
db.close()
def insert_triggers_stalta(stations_list, triggers_list):
host, port, user, password, database = get_credentials_sqldb()
db = MySQLdb.connect(host=host,
port=int(port),
user=user,
passwd=password,
db=database)
cursor = db.cursor()
for station, triggers in zip(stations_list, triggers_list):
for trigger in triggers:
try:
flag = "INSERT INTO AUTOTRIGGERS (station, trig_on, trig_off) VALUES ('%s', '%s', '%s')" % (
station, trigger[0].strftime("%Y-%m-%d %H:%M:%S"),
trigger[1].strftime("%Y-%m-%d %H:%M:%S"))
cursor.execute(flag)
db.commit()
except:
continue
db.close()
def select_triggers_stalta(station, starttime, endtime):
host, port, user, password, database = get_credentials_sqldb()
db = MySQLdb.connect(host=host,
port=int(port),
user=user,
passwd=password,
db=database)
cursor = db.cursor()
flag = "SELECT station, trig_on, trig_on FROM AUTOTRIGGERS WHERE station='%s' && trig_on BETWEEN '%s' AND '%s'" % (
station, starttime.strftime("%Y-%m-%d %H:%M:%S"),
endtime.strftime("%Y-%m-%d %H:%M:%S"))
cursor.execute(flag)
res = cursor.fetchall()
db.close()
triggers_list = []
for row in res:
station, on, off = row
triggers_list.append([UTCDateTime(on), UTCDateTime(off)])
return triggers_list