def load(self):
ydays = self._get_ydays()
radiants = {}
try:
cur = self._db_conn.cursor()
cur.execute('SELECT * FROM radiant ORDER BY shower, month, day')
except Exception as e:
raise DBException(str(e))
column_names = [desc[0] for desc in cur.description]
for r in cur:
r = dict(zip(column_names, r))
iau_code = r['shower']
if iau_code not in radiants:
radiants[iau_code] = []
radiants[iau_code].append({
'yday': ydays[r['month'] - 1] + r['day'],
'pos': Position(r['ra'], r['dec'])
})
try:
cur.close()
except Exception as e:
raise DBException(str(e))
return dict((rad[0], Drift(rad[1])) for rad in radiants.items())
def write(self, cur, sky):
mid = self.id
freq = int(sum(m for m in self.magn.values()))
magn_items = self.magn.items()
mean = sum(float(m) * float(n) for m, n in magn_items) / freq
sl_start = sky.solarlong(self.start)
sl_end = sky.solarlong(self.end)
iau_code = self.shower
magn = {
'id': mid,
'shower': iau_code,
'period_start': self.start,
'period_end': self.end,
'sl_start': math.degrees(sl_start),
'sl_end': math.degrees(sl_end),
'session_id': self.session_id,
'freq': freq,
'mean': mean,
}
try:
cur.execute(self._insert_stmt, magn)
except Exception as e:
raise DBException(str(e))
for m, n in magn_items:
magn = {
'id': mid,
'magn': int(m),
'freq': float(n),
}
try:
cur.execute(self._insert_detail_stmt, magn)
except Exception as e:
raise DBException(str(e))
def parse_row(self, row, cur):
row = dict(zip(self.column_names, row))
try:
session_id = self._parse_session_id(row['session id'])
lat = self._parse_latitude(row['latitude'], session_id)
long = self._parse_longitude(row['longitude'], session_id)
elevation = self._parse_elevation(row['elevation'], session_id)
observer_id = self._parse_observer_id(row['observer id'], session_id)
observer_name = self._parse_observer_name(row['actual observer name'], session_id)
city = self._parse_text(row['city'], 'city', session_id)
country = self._parse_text(row['country'], 'country', session_id)
except ImportException as err:
self._log_error(str(err))
return False
record = {
'id': session_id,
'observer_id': observer_id,
'observer_name': observer_name,
'latitude': lat,
'longitude': long,
'elevation': elevation,
'city': city,
'country': country
}
try:
cur.execute(self._insert_stmt, record)
except Exception as e:
raise DBException(str(e))
return True
def on_start(self, cur):
if self._do_delete:
try:
cur.execute(
self._db_conn.convert_stmt('DELETE FROM imported_rate'))
except Exception as e:
raise DBException(str(e))
def parse_row(self, row, cur):
row = dict(zip(self.column_names, row))
try:
shower = self._parse_shower(row['shower'])
ra = self._parse_ra(row['ra'], shower)
dec = self._parse_dec(row['dec'], shower)
month = self._parse_int(row['month'], 'month', shower)
day = self._parse_int(row['day'], 'day', shower)
self._validate_date(month, day, shower)
if ra is None or dec is None:
raise ImportException('ID %s: ra and dec must be set.' % shower)
except ImportException as err:
self._log_error(str(err))
return False
record = {
'shower': shower,
'ra': ra,
'dec': dec,
'month': month,
'day': day,
}
try:
cur.execute(self._insert_stmt, record)
except Exception as e:
raise DBException(str(e))
return True
def load(self, radiants):
try:
cur = self._db_conn.cursor()
cur.execute('SELECT * FROM shower')
except Exception as e:
raise DBException(str(e))
column_names = [desc[0] for desc in cur.description]
showers = {}
for record in cur:
record = dict(zip(column_names, record))
iau_code = record['iau_code']
showers[iau_code] = Shower(record, radiants[iau_code] if iau_code in radiants else None)
try:
cur.close()
except Exception as e:
raise DBException(str(e))
return showers
def run(self):
db_conn = self._db_conn
try:
cur = db_conn.cursor()
cur.execute(db_conn.convert_stmt('''
SELECT
id,
latitude,
longitude,
elevation,
observer_id,
observer_name,
country,
city
FROM imported_session
'''))
except Exception as e:
raise DBException(str(e))
column_names = [desc[0] for desc in cur.description]
try:
write_cur = db_conn.cursor()
except Exception as e:
raise DBException(str(e))
delete_stmt = db_conn.convert_stmt('DELETE FROM obs_session WHERE id = %(id)s')
for _record in cur:
self.counter_read += 1
record = Record(dict(zip(column_names, _record)))
try:
write_cur.execute(delete_stmt, {'id': record.id})
except Exception as e:
raise DBException(str(e))
record.write(write_cur)
self.counter_write += 1
cur.close()
write_cur.close()
def parse_row(self, row, cur):
row = dict(zip(self.column_names, row))
try:
rate_id = self._parse_rate_id(row['rate id'])
session_id = self._parse_session_id(row['obs session id'], rate_id)
observer_id = self._parse_observer_id(row['user id'],
row['user id'], session_id)
shower = self._parse_shower(row['shower'])
period_start = self._parse_date_time(row['start date'],
'start date', rate_id,
session_id)
period_end = self._parse_date_time(row['end date'], 'end date',
rate_id, session_id)
period_start, period_end = self._check_period(
period_start, period_end, timedelta(days=0.49), rate_id,
session_id)
t_eff = self._parse_t_eff(row['teff'], rate_id, session_id)
f = self._parse_f(row['f'], rate_id, session_id)
freq = self._parse_freq(row['number'], rate_id, session_id)
lm = self._parse_lm(row['lm'], rate_id, session_id)
ra = self._parse_ra(row['ra'], rate_id, session_id)
dec = self._parse_dec(row['decl'], rate_id, session_id)
ra, dec = self._check_ra_dec(ra, dec, rate_id, session_id)
except ImportException as err:
self._log_error(str(err))
return False
record = {
'id': rate_id,
'observer_id': observer_id,
'session_id': session_id,
'start': period_start,
'end': period_end,
't_eff': t_eff,
'f': f,
'lm': lm,
'shower': shower,
'method': row['method'],
'number': freq,
'ra': ra,
'dec': dec,
}
try:
cur.execute(self._insert_stmt, record)
except Exception as e:
raise DBException(str(e))
return True
def write(self, cur):
rate = {
'id': self.id,
'latitude': self.latitude,
'longitude': self.longitude,
'elevation': self.elevation,
'observer_id': self.observer_id,
'observer_name': self.observer_name,
'country': self.country,
'city': self.city
}
try:
cur.execute(self._insert_stmt, rate)
except Exception as e:
raise DBException(str(e))
def parse_row(self, row, cur):
row = dict(zip(self.column_names, row))
try:
iau_code = self._parse_iau_code(row['iau_code'])
ra = self._parse_ra(row['ra'], iau_code)
dec = self._parse_dec(row['de'], iau_code)
v = self._parse_velocity(row['v'], iau_code)
r = self._parse_r_value(row['r'], iau_code)
peak = self._create_date(row['peak'], 'peak', iau_code)
period_start = self._create_date(row['start'], 'start', iau_code)
period_end = self._create_date(row['end'], 'end', iau_code)
ra, dec = self._check_ra_dec(ra, dec, iau_code)
except ImportException as err:
self._log_error(str(err))
return False
shower_name = row['name'].strip()
if 0 == len(shower_name):
self._logger.warning("%s: name of shower is empty." % iau_code)
zhr = row['zhr'].strip()
record = {
'id': int(row['id'].strip()),
'iau_code': iau_code,
'name': shower_name,
'start_month': period_start[0],
'start_day': period_start[1],
'end_month': period_end[0],
'end_day': period_end[1],
'peak_month': peak[0],
'peak_day': peak[1],
'ra': ra,
'dec': dec,
'v': v,
'r': r,
'zhr': zhr if '' != zhr else None,
}
try:
cur.execute(self._insert_stmt, record)
except Exception as e:
raise DBException(str(e))
return True
def parse_row(self, row, cur):
row = dict(zip(self.column_names, row))
try:
magn_id = self._parse_magn_id(row['magnitude id'])
session_id = self._parse_session_id(row['obs session id'], magn_id)
observer_id = self._parse_observer_id(row['user id'], row['user id'], session_id)
shower = self._parse_shower(row['shower'])
period_start = self._parse_date_time(row['start date'], 'start date', magn_id, session_id)
period_end = self._parse_date_time(row['end date'], 'end date', magn_id, session_id)
period_start, period_end = self._check_period(
period_start,
period_end,
timedelta(days=0.49),
magn_id,
session_id
)
except ImportException as err:
self._log_error(str(err))
return False
magn = {}
try:
for column in range(1, 7):
n = float(row['mag n' + str(column)])
magn[str(-column)] = n
for column in range(0, 8):
n = float(row['mag ' + str(column)])
magn[str(column)] = n
except ValueError:
self._log_error(
'ID %s in session %s: Invalid count value of magnitudes found.' %
(magn_id, session_id)
)
return False
try:
for m, n in magn.items():
self._validate_count(n, m, magn_id, session_id)
self._validate_total_count(magn, magn_id, session_id)
except ImportException as err:
self._log_error(str(err))
return False
freq = int(sum(n for n in magn.values()))
if 0 == freq:
return True
magn = json.dumps({m: n for m, n in magn.items() if n > 0})
record = {
'id': magn_id,
'observer_id': observer_id,
'session_id': session_id,
'shower': shower,
'start': period_start,
'end': period_end,
'magn': magn
}
try:
cur.execute(self._insert_stmt, record)
except Exception as e:
raise DBException(str(e))
return True
def write(self, cur, sky, showers):
iau_code = self.shower
t_abs = self.end - self.start
t_mean = self.start + t_abs / 2
sl_start = sky.solarlong(self.start)
sl_end = sky.solarlong(self.end)
shower = showers[iau_code] if iau_code in showers else None
radiant = shower.get_radiant(t_mean) if shower is not None else None
field_alt = None
field_az = None
if self.ra is not None and self.dec is not None:
field = sky.alt_az(
Sphere(math.radians(self.ra), math.radians(self.dec)), t_mean,
self.loc)
field_alt = math.degrees(field.lat)
field_az = math.degrees(field.lng)
if field_alt is not None and field_alt < 0.0:
msg = "session %s: field is below horizon (%s degrees)." % (
self.session_id, round(field_alt))
raise NormalizerException(msg)
sun = sky.sun(t_mean, self.loc)
if sun.lat > 0.0:
msg = "session %s: sun is above horizon (%s degrees)."
msg = msg % (self.session_id, round(math.degrees(sun.lat)))
raise NormalizerException(msg)
moon = sky.moon(t_mean, self.loc)
moon_illumination = sky.moon_illumination(t_mean)
rad_alt = None
rad_az = None
if radiant is not None:
rad_radec = Sphere(math.radians(radiant.ra),
math.radians(radiant.dec))
rad_coord = sky.alt_az(rad_radec, t_mean, self.loc)
rad_az = math.degrees(rad_coord.lng)
rad_alt = math.degrees(self._zenith_coor(rad_coord.lat, shower.v))
if rad_alt is not None and rad_alt < -5.0:
msg = "session %s: radiant of %s is too far below the horizon (%s degrees)."
msg = msg % (self.session_id, iau_code, round(rad_alt))
raise NormalizerException(msg)
rate = {
'id': self.id,
'shower': iau_code,
'period_start': self.start,
'period_end': self.end,
'sl_start': math.degrees(sl_start),
'sl_end': math.degrees(sl_end),
'session_id': self.session_id,
'freq': self.freq,
'lim_mag': self.lm,
't_eff': self.t_eff,
'f': self.f,
'sidereal_time': math.degrees(sky.sidereal_time(t_mean, self.loc)),
'sun_alt': math.degrees(sun.lat),
'sun_az': math.degrees(sun.lng),
'moon_alt': math.degrees(moon.lat),
'moon_az': math.degrees(moon.lng),
'moon_illum': moon_illumination,
'field_alt': field_alt,
'field_az': field_az,
'rad_alt': rad_alt,
'rad_az': rad_az
}
try:
cur.execute(self._insert_stmt, rate)
except Exception as e:
raise DBException(str(e))
def run(self):
db_conn = self._db_conn
try:
cur = db_conn.cursor()
cur.execute(
db_conn.convert_stmt('''
SELECT
r.id,
s.longitude,
s.latitude,
s.elevation,
s.observer_id AS "session_observer_id",
r.shower,
r.session_id,
r.observer_id,
r."start",
r."end",
r.t_eff,
r.f,
r.lm,
r.ra,
r.dec,
r."number" AS freq
FROM imported_rate as r
INNER JOIN obs_session as s ON s.id = r.session_id
ORDER BY
r.session_id ASC,
r.shower ASC,
r."start" ASC,
r."end" DESC
'''))
except Exception as e:
raise DBException(str(e))
column_names = [desc[0] for desc in cur.description]
try:
write_cur = db_conn.cursor()
except Exception as e:
raise DBException(str(e))
prev_record = None
delete_stmt = db_conn.convert_stmt(
'DELETE FROM rate WHERE id = %(id)s')
for _record in cur:
self.counter_read += 1
record = Record(dict(zip(column_names, _record)))
if record.observer_id != record.session_observer_id:
msg = "session %s: observer ID of the rate observation is different"
msg += " from the observer ID of the session. Observation %s discarded."
self._log_error(msg % (record.session_id, record.id))
prev_record = record
continue
try:
write_cur.execute(delete_stmt, {'id': record.id})
except Exception as err:
raise DBException(str(err))
if prev_record is None:
prev_record = record
continue
if record in prev_record:
msg = "session %s: rate observation %s contains observation %s. Observation %s discarded."
self._log_error(msg % (record.session_id, prev_record.id,
record.id, prev_record.id))
prev_record = record
continue
if prev_record == record:
msg = "session %s: rate observation %s overlaps observation %s. Observation %s discarded."
self._log_error(
msg %
(record.session_id, prev_record.id, record.id, record.id))
continue
try:
prev_record.write(write_cur, self._sky, self._showers)
except NormalizerException as err:
prev_record = record
self._log_error(str(err))
continue
self.counter_write += 1
prev_record = record
if prev_record is not None:
try:
prev_record.write(write_cur, self._sky, self._showers)
except NormalizerException as err:
self._log_error(str(err))
self.counter_write += 1
try:
cur.close()
write_cur.close()
except Exception as e:
raise DBException(str(e))
def create_rate_magn(db_conn):
try:
cur = db_conn.cursor()
# find magnitude-rate-pairs containing each other
cur.execute(
db_conn.convert_stmt('''
WITH selection AS (
SELECT
r.id as rate_id,
m.id as magn_id,
r.period_start as rate_period_start,
r.period_end as rate_period_end,
m.period_start as magn_period_start,
m.period_end as magn_period_end,
r.freq as rate_n,
m.freq as magn_n
FROM rate as r
INNER JOIN magnitude as m
ON
r.session_id = m.session_id AND
(
r.shower = m.shower OR
(r.shower IS NULL AND m.shower IS NULL)
)
),
rate_magnitude_rel AS (
SELECT
rate_id,
magn_id,
rate_n,
magn_n,
true as "equals"
FROM selection
WHERE
rate_period_start = magn_period_start AND
rate_period_end = magn_period_end
UNION
SELECT
rate_id,
magn_id,
rate_n,
magn_n,
false as "equals"
FROM selection
WHERE
-- magnitude period contains rate period
rate_period_start BETWEEN magn_period_start AND magn_period_end AND
rate_period_end BETWEEN magn_period_start AND magn_period_end AND
NOT (
-- rate period contains magnitude period
magn_period_start BETWEEN rate_period_start AND rate_period_end AND
magn_period_end BETWEEN rate_period_start AND rate_period_end
)
),
aggregates AS (
SELECT
rate_id,
magn_id,
sum(rate_n) OVER (PARTITION BY magn_id) as rate_n,
magn_n,
"equals",
count(magn_id) OVER (PARTITION BY rate_id) as magn_id_count
FROM rate_magnitude_rel
),
unique_rate_ids AS (
SELECT
rate_id,
magn_id,
"equals"
FROM aggregates
WHERE
magn_id_count = 1 AND
rate_n >= magn_n
)
SELECT rate_id, magn_id, "equals" FROM unique_rate_ids
'''))
except Exception as e:
raise DBException(str(e))
column_names = [desc[0] for desc in cur.description]
insert_stmt = db_conn.convert_stmt('''
INSERT INTO rate_magnitude (
rate_id,
magn_id,
"equals"
) VALUES (
%(rate_id)s,
%(magn_id)s,
%(equals)s
)
''')
try:
write_cur = db_conn.cursor()
except Exception as e:
raise DBException(str(e))
for record in cur:
record = dict(zip(column_names, record))
magn_rate = {
'rate_id': record['rate_id'],
'magn_id': record['magn_id'],
'equals': record['equals'],
}
try:
write_cur.execute(insert_stmt, magn_rate)
except Exception as e:
raise DBException(str(e))
# set limiting magnitude
try:
cur.execute(
db_conn.convert_stmt('UPDATE magnitude SET lim_mag = NULL'))
cur.execute(
db_conn.convert_stmt('''
WITH limiting_magnitudes AS (
SELECT rm.magn_id, sum(r.t_eff*r.lim_mag)/sum(r.t_eff) as lim_mag
FROM rate r
INNER JOIN rate_magnitude rm ON rm.rate_id = r.id
GROUP BY rm.magn_id
)
SELECT magn_id, round(lim_mag*100)/100.0 as lim_mag
FROM limiting_magnitudes
'''))
except Exception as e:
raise DBException(str(e))
column_names = [desc[0] for desc in cur.description]
update_stmt = db_conn.convert_stmt(
'UPDATE magnitude SET lim_mag = %(lim_mag)s WHERE id = %(magn_id)s')
for record in cur:
record = dict(zip(column_names, record))
try:
write_cur.execute(update_stmt, {
'lim_mag': record['lim_mag'],
'magn_id': record['magn_id']
})
except Exception as e:
raise DBException(str(e))
try:
write_cur.close()
cur.close()
except Exception as e:
raise DBException(str(e))