Пример #1
0
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
Пример #2
0
    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
Пример #3
0
    # 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
Пример #4
0
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}
Пример #5
0
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
Пример #6
0
    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