Recent luminous extragalactic transients - joining tables¶
(Modified from an original by Joe Lyman). In this notebook we will be looking for luminous extragalactic transients by ustilising the information provided from the Sherlock contextual classifier. We will specifically look for transients that are luminous compared to their host galaxies, where there is a detection in the last 10 days. The aim is to quickly find superluminous supernovae candidates (very bright supernovae that often inhabit dwarf galaxies).
As part of the process we will be looking at joining tables in SQL queries, order to retrieve information about a given object from different tables.
First of all we need to do some imports, setup plotting, and connect to the ZTF database
import mysql.connector
import numpy as np
from astropy.table import Table
import astropy.coordinates as coord
import astropy.units as u
import matplotlib
import matplotlib.pyplot as plt
%matplotlib inline
import settings
msl = mysql.connector.connect(
user=settings.DB_USER,
password=settings.DB_PASS,
host=settings.DB_HOST, database='ztf')
cursor = msl.cursor()
As an example, lets take a look at what information is provided by the Sherlock crossmatching:
names=['transient_object_id', 'catalogue_object_id', 'catalogue_table_id',
'separationArcsec', 'northSeparationArcsec', 'eastSeparationArcsec', 'id', 'z',
'scale', 'distance', 'distance_modulus', 'photoZ', 'photoZErr',
'association_type', 'dateCreated', 'physical_separation_kpc',
'catalogue_object_type', 'catalogue_object_subtype', 'association_rank',
'catalogue_table_name', 'catalogue_view_name', 'rank', 'rankScore',
'search_name', 'major_axis_arcsec', 'direct_distance', 'direct_distance_scale',
'direct_distance_modulus', 'raDeg', 'decDeg', 'original_search_radius_arcsec',
'catalogue_view_id', 'U', 'UErr', 'B', 'BErr', 'V', 'VErr', 'R', 'RErr',
'I', 'IErr', 'J', 'JErr', 'H', 'HErr', 'K', 'KErr', '_u', '_uErr',
'_g', '_gErr', '_r', '_rErr', '_i', '_iErr', '_z', '_zErr', '_y', '_yErr',
'G', 'GErr', 'unkMag', 'unkMagErr', 'dateLastModified', 'updated',
'classificationReliability', 'transientAbsMag', 'merged_rank']
cursor.execute("SELECT * FROM sherlock_crossmatches LIMIT 10")
results = cursor.fetchall()
table = Table(rows=results, names=names)
table
Of interest to us here is to select on objects that have a sherlock_crossmatches.rank = 1 (i.e. the most likely) crossmatch object that has sherlock_crossmatches.catalogue_object_type = 'galaxy'. (Note we could also try to select objects where the sherlock_crossmatches.association_type = 'SN' (supernova) if we wished, you can alter the query below as appropriate, if desired) We will impose a cut on our objects that the must have at least 8 detections also, for us to assess the light curves - i.e. objects.ncand > 8.
We know what objects we want to select, now we need to decide the information (columns) we want to retrieve about the objects and their Sherlock crossmatches. Since we are comparing transient and host galaxy magnitudes, we want to retrieve the brightest g and r magnitudes from the objects table (confusingly named magrmin and maggmin, currently), and the _r and _g magnitudes from the sherlock_crossmatches table (note the underscores are used distinguish these columns from the other photometric systems in the table, e.g. R) - we should also grab the id, name and coordinates of our objects.
Since we require information from two different tables, and we want to retrieve that information for rows that are associated with the same object id, we must use a table JOIN and specifically and ON condition. You can see this used in the query below - there is plenty of SQL documentation online to find out more about these.
First we compute the current Julian Day from the Unix time:
import time, datetime
now = datetime.datetime.now()
print ("This notbook was run on " + now.isoformat())
jdnow = time.time()/86400 + 2440587.5
print ("and the Julian Date is {}".format(jdnow))
Now get the light curve information about all the objects we have found:
query = "SELECT o.primaryid, o.objectid, o.magrmin, o.maggmin, o.ncand, sc._r, sc._g "
query += "FROM sherlock_crossmatches AS sc JOIN objects as o ON sc.transient_object_id = o.primaryid "
query += "WHERE sc.rank = 1 AND sc.catalogue_object_type = 'galaxy' "
query += "AND o.jdmax > {} - 10".format(jdnow)
print(query)
cursor.execute(query)
results = cursor.fetchall()
table = Table(rows=results, names=["primaryid", "objectid", "magrmin", "maggmin", "ncand", "host_r", "host_g"])
table
lum_objid = []
for filter_name, tran_mag, host_mag, c in zip(("g", "r"), ("maggmin", "magrmin"), ("host_g", "host_r"), ("C2", "C3")):
# Remove those where there's no data
m1 = table[tran_mag] != None
m2 = table[host_mag] != None
t = table[m1 & m2]
# Calculate the magnitude difference
magdiff = t[tran_mag] - t[host_mag]
# Store those significantly brighter than their hosts
lum_objid.extend(t["objectid"][magdiff <= -4])
# Plot histogram
plt.hist(magdiff, bins=25, color=c)
plt.xlabel("transient - host mag")
plt.ylabel("N")
plt.title("{}".format(filter_name))
plt.show()
# Remove duplicates (i.e. those added for both g and r filters)
lum_objid = tuple(set(lum_objid))
We can use our list of luminous object ids to grab their lightcurves by querying the candidates table.
print(lum_objid)
# Get the light curves
query = "SELECT objectid, jd-{} AS ago, magpsf, sigmapsf, fid FROM candidates WHERE objectid IN {};".format(jdnow, lum_objid)
print(query)
cursor.execute(query)
result = cursor.fetchall()
ztf_lc_res = Table(rows=result, names=("objectid", "ago", "magpsf", "sigmapsf", "fid")).group_by("objectid")
ztf_lc_res
For each luminous object, plot the light curve
for objid in lum_objid:
lc = ztf_lc_res[ztf_lc_res["objectid"] == objid]
if len(lc) < 8:
continue
lcg = lc[[lc["fid"] == 1]]
plt.errorbar(lcg["ago"], lcg["magpsf"], yerr=lcg["sigmapsf"], c="C2")
lcr = lc[[lc["fid"] == 2]]
plt.errorbar(lcr["ago"], lcr["magpsf"], yerr=lcr["sigmapsf"], c="C3")
plt.xlabel("days in the past")
plt.ylabel("psf mag")
plt.title("{}".format(objid))
plt.gca().invert_yaxis()
plt.show()
