def get_crossmatch_where_clause(cone, wl_radius):
cone_id = cone.cone_id
name = cone.name
myRA = cone["ra"]
myDecl = cone.decl
print(myRA)
print(myDecl)
subClause = htmCircle.htmCircleRegion(16, myRA, myDecl, wl_radius)
subClause = subClause.replace('htm16ID', 'htm16').replace('where', '')
return subClause
def _build_sql_query_from_htm(
self):
""" build sql query from htm
"""
self.log.debug('starting the ``_build_sql_query_from_htm`` method')
# BUILD WHERE SECTION OF CLAUSE
self.radius = float(self.radius)
htmWhereClause = htmCircle.htmCircleRegion(
self.htmLevel, self.ra, self.dec, self.radius)
# CONVERT RA AND DEC TO CARTESIAN COORDINATES
ra = math.radians(self.ra)
dec = math.radians(self.dec)
cos_dec = math.cos(dec)
cx = math.cos(ra) * cos_dec
cy = math.sin(ra) * cos_dec
cz = math.sin(dec)
cartesians = (cx, cy, cz)
# CREATE CARTESIAN SECTION OF QUERY
cartesianClause = 'and (cx * %.17f + cy * %.17f + cz * %.17f >= cos(%.17f))' % (
cartesians[0], cartesians[1], cartesians[2], math.radians(self.radius / 3600.0))
# DECIDE WHAT COLUMNS TO REQUEST
if self.queryType == 1:
columns = self.quickColumns
elif self.queryType == 3:
columns = ['count(*) number']
else:
columns = ['*']
columns = ','.join(columns)
tableName = self.tableName
# FINALLY BUILD THE FULL QUERY
self.sqlQuery = "select %(columns)s from %(tableName)s %(htmWhereClause)s %(cartesianClause)s" % locals(
)
self.log.debug('completed the ``_build_sql_query_from_htm`` method')
return None
# Check that RA and DEC are in decimal degrees. If not, assume
# sexagesimal and attempt to convert
if ':' in str(ra):
ra = dat.ra_sexegesimal_to_decimal.ra_sexegesimal_to_decimal(ra)
if ':' in str(dec):
dec = dat.declination_sexegesimal_to_decimal.declination_sexegesimal_to_decimal(
dec)
try:
quickColumns = CAT_ID_RA_DEC_COLS[tableName][0]
except KeyError, e:
return "Table %s not recognised." % tableName, []
htmWhereClause = htmCircle.htmCircleRegion(htmLevel, ra, dec, radius)
cartesians = calculate_cartesians(ra, dec)
cartesianClause = 'and (cx * %.17f + cy * %.17f + cz * %.17f >= cos(%.17f))' % (
cartesians[0], cartesians[1], cartesians[2], math.radians(radius / 3600.0))
columns = ['*']
if queryType == QUICK:
columns = quickColumns
elif queryType == COUNT:
columns = ['count(*) number']
query = 'select ' + \
','.join(columns) + ' from %s' % tableName + \
htmWhereClause + cartesianClause
def run_watchlist(wl_id, delete_old=True):
"""run_watchlist.
Args:
wl_id:
delete_old:
"""
# runs the crossmatch of a given watchlist with all the objects
cursor = msl.cursor(buffered=True, dictionary=True)
cursor2 = msl.cursor(buffered=True, dictionary=True)
cursor3 = msl.cursor(buffered=True, dictionary=True)
# get information about the watchlist we are running
query = 'SELECT name,radius FROM watchlists WHERE wl_id=%d' % wl_id
cursor.execute(query)
for watchlist in cursor:
wl_name = watchlist['name']
wl_radius = watchlist['radius']
# print ("default radius = %.1f" % wl_radius)
# clean out previous hits
if delete_old:
query = 'DELETE FROM watchlist_hits WHERE wl_id=%d' % wl_id
cursor.execute(query)
msl.commit()
# make a list of all the hits to return it
newhitlist = []
# get all the cones and run them
query = 'SELECT cone_id,name,ra,decl,radius FROM watchlist_cones WHERE wl_id=%d' % wl_id
cursor.execute(query)
ncandidate = 0
for watch_pos in cursor:
cone_id = watch_pos['cone_id']
name = watch_pos['name']
myRA = watch_pos['ra']
myDecl = watch_pos['decl']
radius = watch_pos['radius']
if not radius:
radius = wl_radius
subClause = htmCircle.htmCircleRegion(16, myRA, myDecl, radius)
subClause = subClause.replace('htm16ID', 'htm16')
query2 = 'SELECT * FROM objects WHERE htm16 ' + subClause[14: -2]
# subClause = subClause.replace('htm16ID', 'htmid16')
# query2 = 'SELECT objectId,ra,decl,count(*) AS ncand FROM candidates '
# query2 += 'WHERE htmid16 ' + subClause[15: -2]
# print(query2)
cursor2.execute(query2)
for row in cursor2:
objectId = row['objectId']
if not objectId: continue
ndethist = row['ncand']
# arcsec = 3600*distance(myRA, myDecl, row['ramean'], row['decmean'])
arcsec = 3600*distance(myRA, myDecl, row['ramean'], row['decmean'])
if arcsec > radius:
continue
ndethist = row['ncand']
ncandidate += ndethist
query3 = 'INSERT INTO watchlist_hits (wl_id, cone_id, objectId, ndethist, arcsec, name) '
query3 += 'VALUES (%d, %d, "%s", %d, %f, "%s")' % (wl_id, cone_id, objectId, ndethist, arcsec, name)
# print(query3)
try:
cursor3.execute(query3)
msl.commit()
newhitlist.append({
'wl_id':wl_id,
'cone_id':cone_id,
'name':name,
'objectId':objectId,
'ndethist':ndethist,
'arcsec':arcsec
})
except mysql.connector.errors.IntegrityError as e:
# print(e)
pass # this objectId is already recorded as a hit for this watchlist
return {'newhitlist':newhitlist, 'ncandidate':ncandidate}
def run_tns_crossmatch(radius):
"""run_tns_crossmatch.
Args:
radius:
"""
# runs the crossmatch of TNS with all the objects
msl = mysql.connector.connect(**config)
cursor = msl.cursor(buffered=True, dictionary=True)
# get user id of user lasair, named "Lasair Bot"
already_commented = {}
query = 'SELECT * FROM comments WHERE user=%d' % settings.LASAIR_USERID
cursor.execute(query)
for row in cursor:
already_commented[row['objectId']] = row['content']
# print("%d comments already from Lasair Bot" % len(already_commented))
cursor.close()
msl.close()
msl = mysql.connector.connect(**config)
cursor = msl.cursor(buffered=True, dictionary=True)
cursor2 = msl.cursor(buffered=True, dictionary=True)
cursor3 = msl.cursor(buffered=True, dictionary=True)
n_tns = 0
n_hits = 0
n_newhits = 0
# get all the cones and run them
query = 'SELECT tns_prefix, tns_name, z, type, ra,decl, disc_date, host_name, discovering_groups FROM crossmatch_tns'
cursor.execute(query)
for row in cursor:
tns_name = row['tns_name']
type = row['type']
z = row['z']
tns_prefix = row['tns_prefix']
disc_date = row['disc_date']
host_name = row['host_name']
myRA = row['ra']
myDecl = row['decl']
discover = row['discovering_groups']
n_tns += 1
iso = str(disc_date).replace(' ', 'T')
# ancient supernovae breaks the date functions!
if iso.startswith('1604'): continue
if iso.startswith('1572'): continue
if iso.startswith('10'): continue
jd = jd_from_iso(iso)
mjd = jd - 2400000.5
subClause = htmCircle.htmCircleRegion(16, myRA, myDecl, radius)
subClause = subClause.replace('htm16ID', 'htm16')
query2 = 'SELECT * FROM objects WHERE htm16 ' + subClause[14:-2]
# print(query2)
cursor2.execute(query2)
for row in cursor2:
objectId = row['objectId']
arcsec = 3600 * distance(myRA, myDecl, row['ramean'],
row['decmean'])
if arcsec > radius:
continue
n_hits += 1
# content = 'In TNS as <a href=https://wis-tns.weizmann.ac.il/object/%s>%s%s</a> at %.1f arcsec, discovered %s (MJD %.2f) by %s' % (tns_name, tns_prefix, tns_name, arcsec, disc_date, mjd, discover)
content = 'In TNS as <a href=https://wis-tns.weizmann.ac.il/object/%s>%s%s</a> (%.1f arcsec separation from ZTF coordinates), discovered (MJD %.2f): '
content = content % (tns_name, tns_prefix, tns_name, arcsec, mjd)
if discover: content += 'by %s. ' % discover
if type: content += 'Classified as a %s. ' % type
if z: content += 'At a redshift z = %s. ' % z
if objectId in already_commented:
if already_commented[objectId] == content:
continue
else:
query3 = 'DELETE from comments where user=%s and objectId="%s"' % (
settings.LASAIR_USERID, objectId)
cursor3.execute(query3)
n_newhits += 1
query3 = 'INSERT INTO comments (user, objectId, content) '
query3 += 'VALUES (%d, "%s", "%s")' % (settings.LASAIR_USERID,
objectId, content)
# print(query3)
cursor3.execute(query3)
msl.commit()
print("%d entries in TNS, %d hits in ZTF (%d new)" %
(n_tns, n_hits, n_newhits))
return
def _remove_previous_ned_queries(
self,
coordinateList):
""" remove previous ned queries
**Key Arguments:**
# - ``coordinateList`` -- set of coordinate to check for previous queries
**Return:**
- ``updatedCoordinateList`` -- coordinate list with previous queries removed
"""
self.log.info('starting the ``_remove_previous_ned_queries`` method')
# IMPORTS
import htmCircle
import math
from dryxPython import astrotools as dat
from datetime import datetime, date, time, timedelta
# 1 DEGREE QUERY RADIUS
radius = 60. * 60.
updatedCoordinateList = []
# FOR EACH TRANSIENT IN COORDINATE LIST
for c in coordinateList:
this = c.split(" ")
raDeg = float(this[0])
decDeg = float(this[1])
# BUILD WHERE SECTION OF CLAUSE
htmWhereClause = htmCircle.htmCircleRegion(
16, raDeg, decDeg, float(radius))
# CONVERT RA AND DEC TO CARTESIAN COORDINATES
ra = math.radians(raDeg)
dec = math.radians(decDeg)
cos_dec = math.cos(dec)
cx = math.cos(ra) * cos_dec
cy = math.sin(ra) * cos_dec
cz = math.sin(dec)
cartesians = (cx, cy, cz)
# CREATE CARTESIAN SECTION OF QUERY
cartesianClause = 'and (cx * %.17f + cy * %.17f + cz * %.17f >= cos(%.17f))' % (
cartesians[0], cartesians[1], cartesians[2], math.radians(radius / 3600.0))
# CALCULATE THE OLDEST RESULTS LIMIT
now = datetime.now()
td = timedelta(
days=self.settings["ned stream refresh rate in days"])
refreshLimit = now - td
refreshLimit = refreshLimit.strftime("%Y-%m-%d %H:%M:%S")
# FINALLY BUILD THE FULL QUERY AND HIT DATABASE
sqlQuery = "select * from tcs_helper_ned_query_history %(htmWhereClause)s %(cartesianClause)s and dateQueried > '%(refreshLimit)s'" % locals(
)
rows = dms.execute_mysql_read_query(
sqlQuery=sqlQuery,
dbConn=self.cataloguesDbConn,
log=self.log
)
# DETERMINE WHICH COORDINATES REQUIRE A NED QUERY
match = False
for row in rows:
raStream = row["raDeg"]
decStream = row["decDeg"]
radiusStream = row["arcsecRadius"]
dateStream = row["dateQueried"]
angularSeparation, northSep, eastSep = dat.get_angular_separation(
log=self.log,
ra1=raDeg,
dec1=decDeg,
ra2=raStream,
dec2=decStream
)
if angularSeparation + self.settings["first pass ned search radius arcec"] < radiusStream:
match = True
if match == False:
updatedCoordinateList.append(c)
self.log.info('completed the ``_remove_previous_ned_queries`` method')
return updatedCoordinateList
