Beispiel #1
0
def store_config(config, dataset_id):
    """
    Store a config defined in d into the database.

    Args:
        config (dict): nested dict containing config, [section][key] -> [value]
    """
    logger.info("storing config to database for dataset %s" % dataset_id)
    error = "type of value %s, key %s in section %s has type %s, we only do %s"
    for section, v in config.items():
        for key, value in v.items():
            if key == 'password':
                logger.debug("not storing %s password to DB" % section)
                continue
            if type(value) not in types:
                msg = error % (value, key, section, type(value).__name__,
                               ", ".join(t.__name__ for t in types))
                logger.error(msg)
                raise TypeError(msg)

            values = {
                'dataset': dataset_id,
                'section': section,
                'key': key,
                'value': str(value),
                'type': type(value).__name__
            }
            execute(store_query, values)
Beispiel #2
0
 def test_bad_type_in_db(self):
     """
     fetch_config should raise TypeError if invalid type in DB
     """
     q = """
     INSERT INTO config (dataset, section, key, value, type)
     VALUES (%s, 'bad', 'type', '[]', 'list');
     """
     execute(q, (self.dataset_id,))
     self.assertRaises(TypeError, fetch_config, self.dataset_id)
Beispiel #3
0
 def test_bad_type_in_db(self):
     """
     fetch_config should raise TypeError if invalid type in DB
     """
     q = """
     INSERT INTO config (dataset, section, key, value, type)
     VALUES (%s, 'bad', 'type', '[]', 'list');
     """
     execute(q, (self.dataset_id, ))
     self.assertRaises(TypeError, fetch_config, self.dataset_id)
Beispiel #4
0
 def test_config_store(self):
     store_config(config, self.dataset_id)
     query1 = """
     select count(id)
     from config
     where section=%s and key=%s and value=%s and dataset=%s
     """
     args1 = ('section1', 'key1', 'value1', self.dataset_id)
     num1 = execute(query1, args1).rowcount
     self.assertEquals(num1, 1)
     args2 = ('section2', 'key3', 'value3', self.dataset_id)
     num2 = execute(query1, args2).rowcount
     self.assertEquals(num2, 1)
Beispiel #5
0
 def test_config_store(self):
     store_config(config, self.dataset_id)
     query1 = """
     select count(id)
     from config
     where section=%s and key=%s and value=%s and dataset=%s
     """
     args1 = ('section1', 'key1', 'value1', self.dataset_id)
     num1 = execute(query1, args1).rowcount
     self.assertEquals(num1, 1)
     args2 = ('section2', 'key3', 'value3', self.dataset_id)
     num2 = execute(query1, args2).rowcount
     self.assertEquals(num2, 1)
Beispiel #6
0
def _update_monitor_runcats(image_id):
    """
    Update ``runcat`` col of ``monitor`` table for newly extracted positions.
    """
    query = """\
UPDATE monitor
   SET runcat = (SELECT rc.id
                   FROM runningcatalog rc
                   JOIN extractedsource ex
                     ON rc.xtrsrc = ex.id
                  WHERE monitor.runcat is NULL
                    AND ex.image = %(image_id)s
                    AND ex.ff_monitor = monitor.id
                  )
    WHERE EXISTS (SELECT rc.id
                   FROM runningcatalog rc
                   JOIN extractedsource ex
                     ON rc.xtrsrc = ex.id
                  WHERE monitor.runcat is NULL
                    AND ex.image = %(image_id)s
                    AND ex.ff_monitor = monitor.id
                    )

    """

    cursor = execute(query, {'image_id': image_id}, commit=True)
    up = cursor.rowcount
    logger.debug("Updated runcat cols for %s newly monitored sources" % up)
Beispiel #7
0
 def setUpClass(cls):
     #NB This is basically nonsense, we're just selecting any old
     #extracted source so we can check the feature extraction syntax.
     xtrsrc_qry = """SELECT id as trigger_xtrsrc
                       FROM extractedsource"""
     cursor = execute(xtrsrc_qry)
     cls.transients = get_db_rows_as_dicts(cursor)
Beispiel #8
0
def _insert_new_runcat(image_id):
    """Insert the fits of the monitoring sources as new sources
    into the runningcatalog
    """

    query = """\
INSERT INTO runningcatalog
  (xtrsrc
  ,dataset
  ,datapoints
  ,zone
  ,wm_ra
  ,wm_decl
  ,avg_ra_err
  ,avg_decl_err
  ,wm_uncertainty_ew
  ,wm_uncertainty_ns
  ,avg_wra
  ,avg_wdecl
  ,avg_weight_ra
  ,avg_weight_decl
  ,x
  ,y
  ,z
  ,mon_src
  )
  SELECT x.id AS xtrsrc
        ,i.dataset
        ,1 AS datapoints
        ,x.zone
        ,x.ra AS wm_ra
        ,x.decl AS wm_decl
        ,x.ra_err AS avg_ra_err
        ,x.decl_err AS avg_decl_err
        ,x.uncertainty_ew AS wm_uncertainty_ew
        ,x.uncertainty_ns AS wm_uncertainty_ns
        ,x.ra / (x.uncertainty_ew * x.uncertainty_ew) AS avg_wra
        ,x.decl / (x.uncertainty_ns * x.uncertainty_ns) AS avg_wdecl
        ,1 / (x.uncertainty_ew * x.uncertainty_ew) AS avg_weight_ra
        ,1 / (x.uncertainty_ns * x.uncertainty_ns) AS avg_weight_decl
        ,x.x
        ,x.y
        ,x.z
        ,TRUE
    FROM image i
        ,extractedsource x
         LEFT OUTER JOIN temprunningcatalog t
         ON t.xtrsrc = x.id
   WHERE i.id = %(image_id)s
     AND x.image = i.id
     AND x.extract_type = 2
     AND t.xtrsrc IS NULL
"""
    cursor = execute(query, {'image_id': image_id}, commit=True)
    ins = cursor.rowcount
    if ins > 0:
        logger.info("Added %s new monitoring sources to runningcatalog" % ins)
Beispiel #9
0
def _insert_new_runcat_flux(image_id):
    """Insert the fitted fluxes of the monitoring sources as new datapoints
    into the runningcatalog_flux.

    Extractedsources for which not a counterpart was found in the
    runningcatalog, i.e., those that do not have an entry in the
    tempruncat table (t0) will be added as a new source in the
    runningcatalog_flux table.

    """
    query = """\
INSERT INTO runningcatalog_flux
  (runcat
  ,band
  ,stokes
  ,f_datapoints
  ,avg_f_peak
  ,avg_f_peak_sq
  ,avg_f_peak_weight
  ,avg_weighted_f_peak
  ,avg_weighted_f_peak_sq
  ,avg_f_int
  ,avg_f_int_sq
  ,avg_f_int_weight
  ,avg_weighted_f_int
  ,avg_weighted_f_int_sq
  )
  SELECT rc.id
        ,i.band
        ,i.stokes
        ,1 AS f_datapoints
        ,x.f_peak
        ,x.f_peak * x.f_peak
        ,1 / (x.f_peak_err * x.f_peak_err)
        ,x.f_peak / (x.f_peak_err * x.f_peak_err)
        ,x.f_peak * x.f_peak / (x.f_peak_err * x.f_peak_err)
        ,x.f_int
        ,x.f_int * x.f_int
        ,1 / (x.f_int_err * x.f_int_err)
        ,x.f_int / (x.f_int_err * x.f_int_err)
        ,x.f_int * x.f_int / (x.f_int_err * x.f_int_err)
  FROM image i
    JOIN extractedsource x
      ON i.id = x.image
    JOIN monitor mon
      ON x.ff_monitor = mon.id
    JOIN runningcatalog rc
      ON rc.xtrsrc = x.id
   WHERE i.id = %(image_id)s
     AND x.extract_type = 2
     AND mon.runcat IS NULL
"""
    cursor = execute(query, {'image_id': image_id}, commit=True)
    ins = cursor.rowcount
    if ins > 0:
        logger.debug("Added %s new monitoring fluxes to runningcatalog_flux" %
                     ins)
Beispiel #10
0
def _insert_1_to_1_assoc():
    """
    The runcat-monitoring pairs are appended to the assocxtrsource
    (light-curve) table as a type = 9 datapoint.
    """
    cursor = execute(ONE_TO_ONE_ASSOC_QUERY, {'type': 9}, commit=True)
    cnt = cursor.rowcount
    logger.debug(
        "Inserted %s runcat-monitoring source pairs in assocxtrsource" % cnt)
Beispiel #11
0
def _insert_new_runcat(image_id):
    """Insert the fits of the monitoring sources as new sources
    into the runningcatalog
    """

    query = """\
INSERT INTO runningcatalog
  (xtrsrc
  ,dataset
  ,datapoints
  ,zone
  ,wm_ra
  ,wm_decl
  ,avg_ra_err
  ,avg_decl_err
  ,wm_uncertainty_ew
  ,wm_uncertainty_ns
  ,avg_wra
  ,avg_wdecl
  ,avg_weight_ra
  ,avg_weight_decl
  ,x
  ,y
  ,z
  ,mon_src
  )
  SELECT x.id AS xtrsrc
        ,i.dataset
        ,1 AS datapoints
        ,x.zone
        ,x.ra AS wm_ra
        ,x.decl AS wm_decl
        ,x.ra_err AS avg_ra_err
        ,x.decl_err AS avg_decl_err
        ,x.uncertainty_ew AS wm_uncertainty_ew
        ,x.uncertainty_ns AS wm_uncertainty_ns
        ,x.ra / (x.uncertainty_ew * x.uncertainty_ew) AS avg_wra
        ,x.decl / (x.uncertainty_ns * x.uncertainty_ns) AS avg_wdecl
        ,1 / (x.uncertainty_ew * x.uncertainty_ew) AS avg_weight_ra
        ,1 / (x.uncertainty_ns * x.uncertainty_ns) AS avg_weight_decl
        ,x.x
        ,x.y
        ,x.z
        ,TRUE
    FROM image i
         JOIN extractedsource x
           ON i.id = x.image
         JOIN monitor mon
           ON x.ff_monitor = mon.id
   WHERE i.id = %(image_id)s
     AND x.extract_type = 2
     AND mon.runcat IS NULL
"""
    cursor = execute(query, {'image_id': image_id}, commit=True)
    ins = cursor.rowcount
    if ins > 0:
        logger.debug("Added %s new monitoring sources to runningcatalog" % ins)
Beispiel #12
0
def _insert_1_to_1_assoc():
    """
    The null detection forced fits are appended to the assocxtrsource
    (light-curve) table as a type = 7 datapoint.
    Subtable t1 has to take care of the cases where values and
    differences might get too small to cause divisions by zero.

    """
    cursor = execute(ONE_TO_ONE_ASSOC_QUERY, {'type': 7}, commit=True)
    cnt = cursor.rowcount
    logger.debug("Inserted %s 1-to-1 null detections in assocxtrsource" % cnt)
Beispiel #13
0
def _insert_1_to_1_assoc():
    """
    The null detection forced fits are appended to the assocxtrsource
    (light-curve) table as a type = 7 datapoint.
    Subtable t1 has to take care of the cases where values and
    differences might get too small to cause divisions by zero.

    """
    cursor = execute(ONE_TO_ONE_ASSOC_QUERY, {'type': 7}, commit=True)
    cnt = cursor.rowcount
    logger.debug("Inserted %s 1-to-1 null detections in assocxtrsource" % cnt)
Beispiel #14
0
def _increment_forcedfits_count():
    """
    Increment the forcedfits count for every runningcatalog entry in the
    temprunningcatalog table.
    """
    query = """\
UPDATE
    runningcatalog
SET
    forcedfits_count = forcedfits_count + 1
WHERE id IN (
    SELECT
        t.runcat
    FROM
        temprunningcatalog t,
        runningcatalog r
    WHERE
        t.runcat = r.id
)
"""
    execute(query)
Beispiel #15
0
def _insert_1_to_1_assoc():
    """
    The runcat-monitoring pairs are appended to the assocxtrsource
    (light-curve) table as a type = 9 datapoint.
    """
    query = """\
INSERT INTO assocxtrsource
  (runcat
  ,xtrsrc
  ,type
  ,distance_arcsec
  ,r
  ,v_int
  ,eta_int
  )
  SELECT t.runcat
        ,t.xtrsrc
        ,9 AS type
        ,0 AS distance_arcsec
        ,0 AS r
        ,t.v_int_inter / t.avg_f_int
        ,t.eta_int_inter / t.avg_f_int_weight
    FROM (SELECT runcat
                ,xtrsrc
                ,CASE WHEN avg_f_int = 0.0
                      THEN 0.000001
                      ELSE avg_f_int
                 END AS avg_f_int
                ,avg_f_int_weight
                ,CASE WHEN f_datapoints = 1
                      THEN 0
                      ELSE CASE WHEN ABS(avg_f_int_sq - avg_f_int * avg_f_int) < 8e-14
                                THEN 0
                                ELSE SQRT(CAST(f_datapoints AS DOUBLE PRECISION)
                                         * (avg_f_int_sq - avg_f_int * avg_f_int)
                                         / (CAST(f_datapoints AS DOUBLE PRECISION) - 1.0)
                                         )
                           END
                 END AS v_int_inter
                ,CASE WHEN f_datapoints = 1
                      THEN 0
                      ELSE (CAST(f_datapoints AS DOUBLE PRECISION)
                            / (CAST(f_datapoints AS DOUBLE PRECISION) - 1.0))
                           * (avg_f_int_weight * avg_weighted_f_int_sq
                             - avg_weighted_f_int * avg_weighted_f_int)
                 END AS eta_int_inter
            FROM temprunningcatalog
           ) t
"""
    cursor = execute(query, commit=True)
    cnt = cursor.rowcount
    if cnt > 0:
        logger.info("Inserted %s runcat-monitoring source pairs in assocxtrsource" % cnt)
Beispiel #16
0
def _increment_forcedfits_count():
    """
    Increment the forcedfits count for every runningcatalog entry in the
    temprunningcatalog table.
    """
    query = """\
UPDATE
    runningcatalog
SET
    forcedfits_count = forcedfits_count + 1
WHERE id IN (
    SELECT
        t.runcat
    FROM
        temprunningcatalog t,
        runningcatalog r
    WHERE
        t.runcat = r.id
)
"""
    execute(query)
Beispiel #17
0
def store_config(config, dataset_id):
    """
    Store a config defined in d into the database.

    Args:
        config (dict): nested dict containing config, [section][key] -> [value]
    """
    logger.info("storing config to database for dataset %s" % dataset_id)
    error = "type of value %s, key %s in section %s has type %s, we only do %s"
    for section, v in config.items():
        for key, value in v.items():
            if key == 'password':
                logger.debug("not storing %s password to DB" % section)
                continue
            if type(value) not in types:
                msg = error % (value, key, section, type(value).__name__,
                               ", ".join(t.__name__ for t in types))
                logger.error(msg)
                raise TypeError(msg)

            values = {'dataset': dataset_id, 'section': section, 'key': key,
                      'value': str(value), 'type': type(value).__name__}
            execute(store_query, values)
Beispiel #18
0
def _insert_runcat_flux():
    """Monitoring sources that were not yet fitted in this frequency band before,
    will be appended to it. Those have their first f_datapoint.
    """

    query = """\
INSERT INTO runningcatalog_flux
  (runcat
  ,band
  ,stokes
  ,f_datapoints
  ,avg_f_peak
  ,avg_f_peak_sq
  ,avg_f_peak_weight
  ,avg_weighted_f_peak
  ,avg_weighted_f_peak_sq
  ,avg_f_int
  ,avg_f_int_sq
  ,avg_f_int_weight
  ,avg_weighted_f_int
  ,avg_weighted_f_int_sq
  )
  SELECT runcat
        ,band
        ,stokes
        ,f_datapoints
        ,avg_f_peak
        ,avg_f_peak_sq
        ,avg_f_peak_weight
        ,avg_weighted_f_peak
        ,avg_weighted_f_peak_sq
        ,avg_f_int
        ,avg_f_int_sq
        ,avg_f_int_weight
        ,avg_weighted_f_int
        ,avg_weighted_f_int_sq
    FROM temprunningcatalog
   WHERE f_datapoints = 1
    """
    cursor = execute(query, commit=True)
    cnt = cursor.rowcount
    if cnt > 0:
        logger.debug(
            "Inserted new-band fluxes for %s monitoring sources in runcat_flux"
            % cnt)
Beispiel #19
0
def _insert_new_1_to_1_assoc(image_id):
    """
    The forced fits of the monitoring sources which are new
    are appended to the assocxtrsource (light-curve) table
    as a type = 8 datapoint.
    """

    query = """\
INSERT INTO assocxtrsource
  (runcat
  ,xtrsrc
  ,type
  ,distance_arcsec
  ,r
  ,v_int
  ,eta_int
  ,f_datapoints
  )
  SELECT rc.id
        ,rc.xtrsrc
        ,8 AS type
        ,0
        ,0
        ,0 AS v_int
        ,0 AS eta_int
        ,1 as f_datapoints
    FROM runningcatalog rc
    JOIN extractedsource x
      ON rc.xtrsrc = x.id
    JOIN image i
      on x.image = i.id
    JOIN monitor mon
      ON x.ff_monitor = mon.id
   WHERE i.id = %(image_id)s
    AND mon.runcat IS NULL
    AND x.extract_type = 2
    """
    cursor = execute(query, {'image_id': image_id}, commit=True)
    cnt = cursor.rowcount
    if cnt > 0:
        logger.debug(
            "Inserted %s new runcat-monitoring source pairs in assocxtrsource"
            % cnt)
Beispiel #20
0
 def test_median(self):
     if Database().engine == "monetdb":
         qry = """
           SELECT sys.median(id) as median_id
                 ,sys.median(rms_max) as median_rms_max
           FROM image
           WHERE dataset = %(dataset_id)s
           """
     else:
         qry = """
           SELECT median(id) as median_id
                 ,median(rms_max) as median_rms_max
           FROM image
           WHERE dataset = %(dataset_id)s
           """
     cursor = execute(qry, {"dataset_id": self.dataset.id})
     results = db_subs.get_db_rows_as_dicts(cursor)
     # self.assertAlmostEqual(results[0]['median_id'], median(self.image_ids))
     self.assertAlmostEqual(results[0]["median_rms_max"], median([p["rms_max"] for p in self.im_params]))
Beispiel #21
0
def _insert_runcat_flux():
    """Monitoring sources that were not yet fitted in this frequency band before,
    will be appended to it. Those have their first f_datapoint.
    """

    query = """\
INSERT INTO runningcatalog_flux
  (runcat
  ,band
  ,stokes
  ,f_datapoints
  ,avg_f_peak
  ,avg_f_peak_sq
  ,avg_f_peak_weight
  ,avg_weighted_f_peak
  ,avg_weighted_f_peak_sq
  ,avg_f_int
  ,avg_f_int_sq
  ,avg_f_int_weight
  ,avg_weighted_f_int
  ,avg_weighted_f_int_sq
  )
  SELECT runcat
        ,band
        ,stokes
        ,f_datapoints
        ,avg_f_peak
        ,avg_f_peak_sq
        ,avg_f_peak_weight
        ,avg_weighted_f_peak
        ,avg_weighted_f_peak_sq
        ,avg_f_int
        ,avg_f_int_sq
        ,avg_f_int_weight
        ,avg_weighted_f_int
        ,avg_weighted_f_int_sq
    FROM temprunningcatalog
   WHERE f_datapoints = 1
    """
    cursor = execute(query, commit=True)
    cnt = cursor.rowcount
    if cnt > 0:
        logger.info("Inserted new-band fluxes for %s monitoring sources in runcat_flux" % cnt)
Beispiel #22
0
 def test_median(self):
     if Database().engine == 'monetdb':
         qry = ("""
           SELECT sys.median(id) as median_id
                 ,sys.median(rms_max) as median_rms_max
           FROM image
           WHERE dataset = %(dataset_id)s
           """)
     else:
         qry = ("""
           SELECT median(id) as median_id
                 ,median(rms_max) as median_rms_max
           FROM image
           WHERE dataset = %(dataset_id)s
           """)
     cursor = execute(qry, {'dataset_id': self.dataset.id})
     results = db_subs.get_db_rows_as_dicts(cursor)
     # self.assertAlmostEqual(results[0]['median_id'], median(self.image_ids))
     self.assertAlmostEqual(results[0]['median_rms_max'],
                      median([p['rms_max'] for p in self.im_params]))
Beispiel #23
0
def fetch_config(dataset_id):
    """
    Retrieve the stored config for given dataset id

    Returns:
        nested dict [section][key] -> [value]
    """
    logger.info("fetching config from database for dataset %s" % dataset_id)
    error = "type in database is %s but we only support %s"
    result = execute(fetch_query, {'dataset': dataset_id}).fetchall()
    config = adict()
    for section, key, value, type_ in result:
        if type_ not in (t.__name__ for t in types):
            msg = error % (type_, ", ".join(t.__name__ for t in types))
            logger.error(msg)
            raise TypeError(msg)
        converted = eval(type_)(value)
        if not section in config:
            config[section] = adict()
        config[section][key] = converted
    return config
Beispiel #24
0
def fetch_config(dataset_id):
    """
    Retrieve the stored config for given dataset id

    Returns:
        nested dict [section][key] -> [value]
    """
    logger.info("fetching config from database for dataset %s" % dataset_id)
    error = "type in database is %s but we only support %s"
    result = execute(fetch_query, {'dataset': dataset_id}).fetchall()
    config = adict()
    for section, key, value, type_ in result:
        if type_ not in (t.__name__ for t in types):
            msg = error % (type_, ", ".join(t.__name__ for t in types))
            logger.error(msg)
            raise TypeError(msg)
        converted = eval(type_)(value)
        if not section in config:
            config[section] = adict()
        config[section][key] = converted
    return config
Beispiel #25
0
def get_monitor_entries(dataset_id):
    """
    Returns the ``monitor`` entries relevant to this dataset.

    Args:
        dataset_id (int): Parent dataset.

    Returns:
        list of tuples [(monitor_id, ra, decl)]
    """

    query = """\
SELECT id
      ,ra
      ,decl
  FROM monitor
 WHERE dataset = %(dataset_id)s
"""
    qry_params = {'dataset_id': dataset_id}
    cursor = execute(query, qry_params)
    res = cursor.fetchall()
    return res
Beispiel #26
0
def _insert_new_1_to_1_assoc(image_id):
    """
    The forced fits of the monitoring sources which are new
    are appended to the assocxtrsource (light-curve) table
    as a type = 8 datapoint.
    """
    query = """\
INSERT INTO assocxtrsource
  (runcat
  ,xtrsrc
  ,type
  ,distance_arcsec
  ,r
  ,v_int
  ,eta_int
  )
  SELECT r0.id AS runcat
        ,r0.xtrsrc
        ,8 AS type
        ,0 AS distance_arcsec
        ,0 AS r
        ,0 AS v_int
        ,0 AS eta_int
    FROM (SELECT x1.id AS xtrsrc
            FROM extractedsource x1
                 LEFT OUTER JOIN temprunningcatalog trc1
                 ON x1.id = trc1.xtrsrc
            WHERE x1.image = %(image_id)s
              AND x1.extract_type = 2
              AND trc1.xtrsrc IS NULL
         ) t0
        ,runningcatalog r0
   WHERE r0.xtrsrc = t0.xtrsrc
    """
    cursor = execute(query, {'image_id': image_id}, commit=True)
    cnt = cursor.rowcount
    if cnt > 0:
        logger.info("Inserted %s new runcat-monitoring source pairs in assocxtrsource" % cnt)
Beispiel #27
0
def get_nulldetections(image_id):
    """
    Returns the runningcatalog sources which:

      * Are associated with the skyregion of the current image.
      * Do not have a counterpart in the extractedsources of the current
        image after source association has run.
      * Have been seen (in any band) at a timestamp earlier than that of the
        current image.

    NB This runs *after* the source association.

    We determine null detections only as those sources which have been
    seen at earlier times which don't appear in the current image.
    Sources which have not been seen earlier, and which appear in
    different bands at the current timestep, are *not* null detections,
    but are considered as "new" sources.

    Returns: list of tuples [(runcatid, ra, decl)]
    """

    # The first temptable t0 looks for runcat sources that have been seen
    # in the same sky region as the current image,
    # but at an earlier timestamp, irrespective of the band.
    # The second temptable t1 returns the runcat source ids for those sources
    # that have an association with the current extracted sources.
    # The left outer join in combination with the t1.runcat IS NULL then
    # returns the runcat sources that could not be associated.

    query = """\
SELECT t0.id
      ,t0.wm_ra
      ,t0.wm_decl
  FROM (SELECT r0.id
              ,r0.wm_ra
              ,r0.wm_decl
          FROM image i0
              ,assocskyrgn a0
              ,runningcatalog r0
              ,extractedsource x0
              ,image i1
         WHERE i0.id = %(image_id)s
           AND a0.skyrgn = i0.skyrgn
           AND r0.id = a0.runcat
           AND x0.id = r0.xtrsrc
           AND i1.id = x0.image
           AND i0.taustart_ts > i1.taustart_ts
       ) t0
       LEFT OUTER JOIN (SELECT a.runcat
                          FROM extractedsource x
                              ,assocxtrsource a
                         WHERE x.image = %(image_id)s
                           AND a.xtrsrc = x.id
                       ) t1
       ON t0.id = t1.runcat
 WHERE t1.runcat IS NULL
"""
    qry_params = {'image_id': image_id}
    cursor = execute(query, qry_params)
    res = cursor.fetchall()
    return res
Beispiel #28
0
def _update_runcat_flux():
    """We only have to update those runcat sources that already had a detection,
    so their f_datapoints is larger than 1.

    """
    query = """\
UPDATE runningcatalog_flux
   SET f_datapoints = (SELECT f_datapoints
                         FROM temprunningcatalog
                        WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                          AND temprunningcatalog.band = runningcatalog_flux.band
                          AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                          AND temprunningcatalog.inactive = FALSE
                          AND temprunningcatalog.f_datapoints > 1
                      )
      ,avg_f_peak = (SELECT avg_f_peak
                       FROM temprunningcatalog
                      WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                        AND temprunningcatalog.band = runningcatalog_flux.band
                        AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                        AND temprunningcatalog.inactive = FALSE
                        AND temprunningcatalog.f_datapoints > 1
                    )
      ,avg_f_peak_sq = (SELECT avg_f_peak_sq
                          FROM temprunningcatalog
                         WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                           AND temprunningcatalog.band = runningcatalog_flux.band
                           AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                           AND temprunningcatalog.inactive = FALSE
                           AND temprunningcatalog.f_datapoints > 1
                       )
      ,avg_f_peak_weight = (SELECT avg_f_peak_weight
                              FROM temprunningcatalog
                             WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                               AND temprunningcatalog.band = runningcatalog_flux.band
                               AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                               AND temprunningcatalog.inactive = FALSE
                               AND temprunningcatalog.f_datapoints > 1
                           )
      ,avg_weighted_f_peak = (SELECT avg_weighted_f_peak
                                FROM temprunningcatalog
                               WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                                 AND temprunningcatalog.band = runningcatalog_flux.band
                                 AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                                 AND temprunningcatalog.inactive = FALSE
                                 AND temprunningcatalog.f_datapoints > 1
                             )
      ,avg_weighted_f_peak_sq = (SELECT avg_weighted_f_peak_sq
                                   FROM temprunningcatalog
                                  WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                                    AND temprunningcatalog.band = runningcatalog_flux.band
                                    AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                                    AND temprunningcatalog.inactive = FALSE
                                    AND temprunningcatalog.f_datapoints > 1
                                )
      ,avg_f_int = (SELECT avg_f_int
                      FROM temprunningcatalog
                     WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                       AND temprunningcatalog.band = runningcatalog_flux.band
                       AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                       AND temprunningcatalog.inactive = FALSE
                       AND temprunningcatalog.f_datapoints > 1
                   )
      ,avg_f_int_sq = (SELECT avg_f_int_sq
                         FROM temprunningcatalog
                        WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                          AND temprunningcatalog.band = runningcatalog_flux.band
                          AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                          AND temprunningcatalog.inactive = FALSE
                          AND temprunningcatalog.f_datapoints > 1
                      )
      ,avg_f_int_weight = (SELECT avg_f_int_weight
                             FROM temprunningcatalog
                             WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                               AND temprunningcatalog.band = runningcatalog_flux.band
                               AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                               AND temprunningcatalog.inactive = FALSE
                               AND temprunningcatalog.f_datapoints > 1
                          )
      ,avg_weighted_f_int = (SELECT avg_weighted_f_int
                               FROM temprunningcatalog
                              WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                                AND temprunningcatalog.band = runningcatalog_flux.band
                                AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                                AND temprunningcatalog.inactive = FALSE
                                AND temprunningcatalog.f_datapoints > 1
                            )
      ,avg_weighted_f_int_sq = (SELECT avg_weighted_f_int_sq
                                  FROM temprunningcatalog
                                 WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                                   AND temprunningcatalog.band = runningcatalog_flux.band
                                   AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                                   AND temprunningcatalog.inactive = FALSE
                                   AND temprunningcatalog.f_datapoints > 1
                               )
 WHERE EXISTS (SELECT runcat
                 FROM temprunningcatalog
                WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                  AND temprunningcatalog.band = runningcatalog_flux.band
                  AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                  AND temprunningcatalog.inactive = FALSE
                  AND temprunningcatalog.f_datapoints > 1
              )
"""
    cursor = execute(query, commit=True)
    cnt = cursor.rowcount
    if cnt > 0:
        logger.info("Updated flux for %s null_detections" % cnt)
Beispiel #29
0
def _insert_tempruncat(image_id):
    """
    Here the associations of forced fits and their runningcatalog counterparts
    are inserted into the temporary table.

    We follow the implementation of the normal association procedure,
    except that we don't need to match with a De Ruiter radius, since
    the counterpart pairs are from the same runningcatalog source.
    """

    query = """\
INSERT INTO temprunningcatalog
  (runcat
  ,xtrsrc
  ,distance_arcsec
  ,r
  ,dataset
  ,band
  ,stokes
  ,datapoints
  ,zone
  ,wm_ra
  ,wm_decl
  ,wm_uncertainty_ew
  ,wm_uncertainty_ns
  ,avg_ra_err
  ,avg_decl_err
  ,avg_wra
  ,avg_wdecl
  ,avg_weight_ra
  ,avg_weight_decl
  ,x
  ,y
  ,z
  ,f_datapoints
  ,avg_f_peak
  ,avg_f_peak_sq
  ,avg_f_peak_weight
  ,avg_weighted_f_peak
  ,avg_weighted_f_peak_sq
  ,avg_f_int
  ,avg_f_int_sq
  ,avg_f_int_weight
  ,avg_weighted_f_int
  ,avg_weighted_f_int_sq
  )
  SELECT t0.runcat
        ,t0.xtrsrc
        ,0 as distance_arcsec
        ,0 as r
        ,t0.dataset
        ,t0.band
        ,t0.stokes
        ,t0.datapoints
        ,t0.zone
        ,t0.wm_ra
        ,t0.wm_decl
        ,t0.wm_uncertainty_ew
        ,t0.wm_uncertainty_ns
        ,t0.avg_ra_err
        ,t0.avg_decl_err
        ,t0.avg_wra
        ,t0.avg_wdecl
        ,t0.avg_weight_ra
        ,t0.avg_weight_decl
        ,t0.x
        ,t0.y
        ,t0.z
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1
              ELSE rf.f_datapoints + 1
         END AS f_datapoints
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak
              ELSE (rf.f_datapoints * rf.avg_f_peak
                    + t0.f_peak)
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak * t0.f_peak
              ELSE (rf.f_datapoints * rf.avg_f_peak_sq
                    + t0.f_peak * t0.f_peak)
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1 / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_f_peak_weight
                    + 1 / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak_weight
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_peak
                    + t0.f_peak / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_peak
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak * t0.f_peak / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_peak_sq
                    + (t0.f_peak * t0.f_peak) / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_peak_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int
              ELSE (rf.f_datapoints * rf.avg_f_int
                    + t0.f_int)
                   / (rf.f_datapoints + 1)
         END AS avg_f_int
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int * t0.f_int
              ELSE (rf.f_datapoints * rf.avg_f_int_sq
                    + t0.f_int * t0.f_int)
                   / (rf.f_datapoints + 1)
         END AS avg_f_int_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1 / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_f_int_weight
                    + 1 / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_f_int_weight
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_int
                    + t0.f_int / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_int
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int * t0.f_int / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_int_sq
                    + (t0.f_int * t0.f_int) / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_int_sq
    FROM (SELECT r.id AS runcat
                ,x.id AS xtrsrc
                ,x.f_peak
                ,x.f_peak_err
                ,x.f_int
                ,x.f_int_err
                ,i.dataset
                ,i.band
                ,i.stokes
                ,r.datapoints
                ,r.zone
                ,r.wm_ra
                ,r.wm_decl
                ,r.wm_uncertainty_ew
                ,r.wm_uncertainty_ns
                ,r.avg_ra_err
                ,r.avg_decl_err
                ,r.avg_wra
                ,r.avg_wdecl
                ,r.avg_weight_ra
                ,r.avg_weight_decl
                ,r.x
                ,r.y
                ,r.z
            FROM runningcatalog r
                ,image i
                ,extractedsource x
           WHERE i.id = %(image_id)s
             AND r.dataset = i.dataset
             AND x.image = i.id
             AND x.image = %(image_id)s
             AND x.extract_type = 1
             AND r.mon_src = FALSE
             AND r.zone BETWEEN CAST(FLOOR(x.decl - x.error_radius/3600) AS INTEGER)
                              AND CAST(FLOOR(x.decl + x.error_radius/3600) AS INTEGER)
             AND r.wm_decl BETWEEN x.decl - x.error_radius/3600
                               AND x.decl + x.error_radius/3600
             AND r.wm_ra BETWEEN x.ra - alpha(x.error_radius/3600, x.decl)
                             AND x.ra + alpha(x.error_radius/3600, x.decl)
             AND r.x * x.x + r.y * x.y + r.z * x.z > COS(RADIANS(r.wm_uncertainty_ew))
         ) t0
         LEFT OUTER JOIN runningcatalog_flux rf
         ON t0.runcat = rf.runcat
         AND t0.band = rf.band
         AND t0.stokes = rf.stokes
"""
    qry_params = {'image_id': image_id}
    cursor = execute(query, qry_params, commit=True)
    cnt = cursor.rowcount
    logger.info("Inserted %s null detections in tempruncat" % cnt)
Beispiel #30
0
def get_nulldetections(image_id, expiration=10):
    """
    Returns the runningcatalog sources which:

      * Are associated with the skyregion of the current image.
      * Do not have a counterpart in the extractedsources of the current
        image after source association has run.
      * Have been seen (in any band) at a timestamp earlier than that of the
        current image.

    NB This runs *after* the source association.

    We determine null detections only as those sources which have been
    seen at earlier times which don't appear in the current image.
    Sources which have not been seen earlier, and which appear in
    different bands at the current timestep, are *not* null detections,
    but are considered as "new" sources.

    args:
        image_id (int): database ID of image
        expiration (int): number of forced fits performed after a blind fit):

    Returns: list of tuples [(runcatid, ra, decl)]
    """

    # The first temptable t0 looks for runcat sources that have been seen
    # in the same sky region as the current image,
    # but at an earlier timestamp, irrespective of the band.
    # The second temptable t1 returns the runcat source ids for those sources
    # that have an association with the current extracted sources.
    # The left outer join in combination with the t1.runcat IS NULL then
    # returns the runcat sources that could not be associated.

    query = """\
SELECT t0.id
      ,t0.wm_ra
      ,t0.wm_decl
  FROM (SELECT r0.id
              ,r0.wm_ra
              ,r0.wm_decl
          FROM image i0
              ,assocskyrgn a0
              ,runningcatalog r0
              ,extractedsource x0
              ,image i1
         WHERE i0.id = %(image_id)s
           AND a0.skyrgn = i0.skyrgn
           AND r0.id = a0.runcat
           AND r0.forcedfits_count < %(expiration)s
           AND x0.id = r0.xtrsrc
           AND i1.id = x0.image
           AND i0.taustart_ts > i1.taustart_ts
       ) t0
       LEFT OUTER JOIN (SELECT a.runcat
                          FROM extractedsource x
                              ,assocxtrsource a
                         WHERE x.image = %(image_id)s
                           AND a.xtrsrc = x.id
                       ) t1
       ON t0.id = t1.runcat
 WHERE t1.runcat IS NULL
"""
    qry_params = {'image_id': image_id, 'expiration': expiration}
    cursor = execute(query, qry_params)
    res = cursor.fetchall()
    return res
Beispiel #31
0
def _insert_tempruncat(image_id):
    """
    Here the associations of forced fits and their runningcatalog counterparts
    are inserted into the temporary table.

    We follow the analogies of the normal association procedure.
    The difference here is that we know what the runcat ids are for the
    extractedsource.extract_type = 1 (ff_nd) sources are, since these
    were inserted at the same time as well.

    This is why subtable t0 looks simpler than in the normal case. We
    still have to do a left outer join with the runcat_flux table (rf),
    because fluxes might not be detected in other bands.
    Before being inserted the additional properties are calculated.

    """

    query = """\
INSERT INTO temprunningcatalog
  (runcat
  ,xtrsrc
  ,distance_arcsec
  ,r
  ,dataset
  ,band
  ,stokes
  ,datapoints
  ,zone
  ,wm_ra
  ,wm_decl
  ,wm_uncertainty_ew
  ,wm_uncertainty_ns
  ,avg_ra_err
  ,avg_decl_err
  ,avg_wra
  ,avg_wdecl
  ,avg_weight_ra
  ,avg_weight_decl
  ,x
  ,y
  ,z
  ,f_datapoints
  ,avg_f_peak
  ,avg_f_peak_sq
  ,avg_f_peak_weight
  ,avg_weighted_f_peak
  ,avg_weighted_f_peak_sq
  ,avg_f_int
  ,avg_f_int_sq
  ,avg_f_int_weight
  ,avg_weighted_f_int
  ,avg_weighted_f_int_sq
  )
  SELECT t0.runcat
        ,t0.xtrsrc
        ,0 AS distance_arcsec
        ,0 AS r
        ,t0.dataset
        ,t0.band
        ,t0.stokes
        ,t0.datapoints
        ,t0.zone
        ,t0.wm_ra
        ,t0.wm_decl
        ,t0.wm_uncertainty_ew
        ,t0.wm_uncertainty_ns
        ,t0.avg_ra_err
        ,t0.avg_decl_err
        ,t0.avg_wra
        ,t0.avg_wdecl
        ,t0.avg_weight_ra
        ,t0.avg_weight_decl
        ,t0.x
        ,t0.y
        ,t0.z
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1
              ELSE rf.f_datapoints + 1
         END AS f_datapoints
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak
              ELSE (rf.f_datapoints * rf.avg_f_peak
                    + t0.f_peak)
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak * t0.f_peak
              ELSE (rf.f_datapoints * rf.avg_f_peak_sq
                    + t0.f_peak * t0.f_peak)
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1 / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_f_peak_weight
                    + 1 / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak_weight
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_peak
                    + t0.f_peak / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_peak
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak * t0.f_peak / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_peak_sq
                    + (t0.f_peak * t0.f_peak) / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_peak_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int
              ELSE (rf.f_datapoints * rf.avg_f_int
                    + t0.f_int)
                   / (rf.f_datapoints + 1)
         END AS avg_f_int
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int * t0.f_int
              ELSE (rf.f_datapoints * rf.avg_f_int_sq
                    + t0.f_int * t0.f_int)
                   / (rf.f_datapoints + 1)
         END AS avg_f_int_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1 / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_f_int_weight
                    + 1 / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_f_int_weight
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_int
                    + t0.f_int / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_int
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int * t0.f_int / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_int_sq
                    + (t0.f_int * t0.f_int) / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_int_sq
    FROM (SELECT r.id AS runcat
                ,x.id AS xtrsrc
                ,x.f_peak
                ,x.f_peak_err
                ,x.f_int
                ,x.f_int_err
                ,i.dataset
                ,i.band
                ,i.stokes
                ,r.datapoints
                ,r.zone
                ,r.wm_ra
                ,r.wm_decl
                ,r.wm_uncertainty_ew
                ,r.wm_uncertainty_ns
                ,r.avg_ra_err
                ,r.avg_decl_err
                ,r.avg_wra
                ,r.avg_wdecl
                ,r.avg_weight_ra
                ,r.avg_weight_decl
                ,r.x
                ,r.y
                ,r.z
            FROM extractedsource x
                ,image i
                ,runningcatalog r
           WHERE x.image = %(image_id)s
             AND x.extract_type = 1
             AND i.id = x.image
             AND r.id = x.ff_runcat
             AND r.mon_src = FALSE
         ) t0
         LEFT OUTER JOIN runningcatalog_flux rf
         ON t0.runcat = rf.runcat
         AND t0.band = rf.band
         AND t0.stokes = rf.stokes
"""
    qry_params = {'image_id': image_id}
    cursor = execute(query, qry_params, commit=True)
    cnt = cursor.rowcount
    logger.debug("Inserted %s null detections in tempruncat" % cnt)
Beispiel #32
0
def _insert_new_runcat_flux(image_id):
    """Insert the fitted fluxes of the monitoring sources as new datapoints
    into the runningcatalog_flux.

    Extractedsources for which not a counterpart was found in the
    runningcatalog, i.e., those that do not have an entry in the
    tempruncat table (t0) will be added as a new source in the
    runningcatalog_flux table.

    """
    query = """\
INSERT INTO runningcatalog_flux
  (runcat
  ,band
  ,stokes
  ,f_datapoints
  ,avg_f_peak
  ,avg_f_peak_sq
  ,avg_f_peak_weight
  ,avg_weighted_f_peak
  ,avg_weighted_f_peak_sq
  ,avg_f_int
  ,avg_f_int_sq
  ,avg_f_int_weight
  ,avg_weighted_f_int
  ,avg_weighted_f_int_sq
  )
  SELECT r0.id
        ,i0.band
        ,i0.stokes
        ,1 AS f_datapoints
        ,x0.f_peak
        ,x0.f_peak * x0.f_peak
        ,1 / (x0.f_peak_err * x0.f_peak_err)
        ,x0.f_peak / (x0.f_peak_err * x0.f_peak_err)
        ,x0.f_peak * x0.f_peak / (x0.f_peak_err * x0.f_peak_err)
        ,x0.f_int
        ,x0.f_int * x0.f_int
        ,1 / (x0.f_int_err * x0.f_int_err)
        ,x0.f_int / (x0.f_int_err * x0.f_int_err)
        ,x0.f_int * x0.f_int / (x0.f_int_err * x0.f_int_err)
    FROM image i0
        ,(SELECT x1.id AS xtrsrc
            FROM extractedsource x1
                 LEFT OUTER JOIN temprunningcatalog trc1
                 ON x1.id = trc1.xtrsrc
            WHERE x1.image = %(image_id)s
              AND x1.extract_type = 2
              AND trc1.xtrsrc IS NULL
          ) t0
        ,runningcatalog r0
        ,extractedsource x0
   WHERE i0.id = %(image_id)s
     AND r0.xtrsrc = t0.xtrsrc
     AND x0.id = r0.xtrsrc
     AND x0.extract_type = 2
"""
    cursor = execute(query, {'image_id': image_id}, commit=True)
    ins = cursor.rowcount
    if ins > 0:
        logger.info("Added %s new monitoring fluxes to runningcatalog_flux" % ins)
Beispiel #33
0
def _update_runcat():
    """Update the running catalog (positional) properties of the
    monitoring sources with the values in temprunningcatalog"""

    query = """\
        UPDATE runningcatalog
           SET datapoints = (SELECT datapoints
                               FROM temprunningcatalog
                              WHERE temprunningcatalog.runcat = runningcatalog.id
                            )
              ,zone = (SELECT zone
                         FROM temprunningcatalog
                        WHERE temprunningcatalog.runcat = runningcatalog.id
                      )
              ,wm_ra = (SELECT wm_ra
                          FROM temprunningcatalog
                         WHERE temprunningcatalog.runcat = runningcatalog.id
                       )
              ,wm_decl = (SELECT wm_decl
                            FROM temprunningcatalog
                           WHERE temprunningcatalog.runcat = runningcatalog.id
                         )
              ,avg_ra_err = (SELECT avg_ra_err
                              FROM temprunningcatalog
                             WHERE temprunningcatalog.runcat = runningcatalog.id
                           )
              ,avg_decl_err = (SELECT avg_decl_err
                                FROM temprunningcatalog
                               WHERE temprunningcatalog.runcat = runningcatalog.id
                             )
              ,wm_uncertainty_ew = (SELECT wm_uncertainty_ew
                                FROM temprunningcatalog
                               WHERE temprunningcatalog.runcat = runningcatalog.id
                             )
              ,wm_uncertainty_ns = (SELECT wm_uncertainty_ns
                                FROM temprunningcatalog
                               WHERE temprunningcatalog.runcat = runningcatalog.id
                             )
              ,avg_wra = (SELECT avg_wra
                            FROM temprunningcatalog
                           WHERE temprunningcatalog.runcat = runningcatalog.id
                         )
              ,avg_wdecl = (SELECT avg_wdecl
                              FROM temprunningcatalog
                             WHERE temprunningcatalog.runcat = runningcatalog.id
                           )
              ,avg_weight_ra = (SELECT avg_weight_ra
                                  FROM temprunningcatalog
                                 WHERE temprunningcatalog.runcat = runningcatalog.id
                               )
              ,avg_weight_decl = (SELECT avg_weight_decl
                                    FROM temprunningcatalog
                                   WHERE temprunningcatalog.runcat = runningcatalog.id
                                 )
              ,x = (SELECT x
                      FROM temprunningcatalog
                     WHERE temprunningcatalog.runcat = runningcatalog.id
                   )
              ,y = (SELECT y
                      FROM temprunningcatalog
                     WHERE temprunningcatalog.runcat = runningcatalog.id
                   )
              ,z = (SELECT z
                      FROM temprunningcatalog
                     WHERE temprunningcatalog.runcat = runningcatalog.id
                   )
         WHERE EXISTS (SELECT runcat
                         FROM temprunningcatalog
                        WHERE temprunningcatalog.runcat = runningcatalog.id
                      )
"""
    cursor = execute(query, commit=True)
    cnt = cursor.rowcount
    if cnt > 0:
        logger.info("Updated %s monitoring sources in runcat" % cnt)
Beispiel #34
0
def dataset_images(dataset_id, database=None):
    q = "SELECT id FROM image WHERE dataset=%(dataset)s LIMIT 1"
    args = {'dataset': dataset_id}
    cursor = execute(q, args)
    image_ids = [x[0] for x in cursor.fetchall()]
    return image_ids
Beispiel #35
0
def _update_runcat_flux():
    """We only have to update those runcat fluxes that already had a fit,
    so their f_datapoints is larger than 1. The ones had a fit in
    another band, but not in this are handled by the _insert_runcat_flux().

    """
    query = """\
UPDATE runningcatalog_flux
   SET f_datapoints = (SELECT f_datapoints
                         FROM temprunningcatalog
                        WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                          AND temprunningcatalog.band = runningcatalog_flux.band
                          AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                          AND temprunningcatalog.f_datapoints > 1
                      )
      ,avg_f_peak = (SELECT avg_f_peak
                       FROM temprunningcatalog
                      WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                        AND temprunningcatalog.band = runningcatalog_flux.band
                        AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                        AND temprunningcatalog.f_datapoints > 1
                    )
      ,avg_f_peak_sq = (SELECT avg_f_peak_sq
                          FROM temprunningcatalog
                         WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                           AND temprunningcatalog.band = runningcatalog_flux.band
                           AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                           AND temprunningcatalog.f_datapoints > 1
                       )
      ,avg_f_peak_weight = (SELECT avg_f_peak_weight
                              FROM temprunningcatalog
                             WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                               AND temprunningcatalog.band = runningcatalog_flux.band
                               AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                               AND temprunningcatalog.f_datapoints > 1
                           )
      ,avg_weighted_f_peak = (SELECT avg_weighted_f_peak
                                FROM temprunningcatalog
                               WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                                 AND temprunningcatalog.band = runningcatalog_flux.band
                                 AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                                 AND temprunningcatalog.f_datapoints > 1
                             )
      ,avg_weighted_f_peak_sq = (SELECT avg_weighted_f_peak_sq
                                   FROM temprunningcatalog
                                  WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                                    AND temprunningcatalog.band = runningcatalog_flux.band
                                    AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                                    AND temprunningcatalog.f_datapoints > 1
                                )
      ,avg_f_int = (SELECT avg_f_int
                      FROM temprunningcatalog
                     WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                       AND temprunningcatalog.band = runningcatalog_flux.band
                       AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                       AND temprunningcatalog.f_datapoints > 1
                   )
      ,avg_f_int_sq = (SELECT avg_f_int_sq
                         FROM temprunningcatalog
                        WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                          AND temprunningcatalog.band = runningcatalog_flux.band
                          AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                          AND temprunningcatalog.f_datapoints > 1
                      )
      ,avg_f_int_weight = (SELECT avg_f_int_weight
                             FROM temprunningcatalog
                             WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                               AND temprunningcatalog.band = runningcatalog_flux.band
                               AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                               AND temprunningcatalog.f_datapoints > 1
                          )
      ,avg_weighted_f_int = (SELECT avg_weighted_f_int
                               FROM temprunningcatalog
                              WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                                AND temprunningcatalog.band = runningcatalog_flux.band
                                AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                                AND temprunningcatalog.f_datapoints > 1
                            )
      ,avg_weighted_f_int_sq = (SELECT avg_weighted_f_int_sq
                                  FROM temprunningcatalog
                                 WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                                   AND temprunningcatalog.band = runningcatalog_flux.band
                                   AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                                   AND temprunningcatalog.f_datapoints > 1
                               )
 WHERE EXISTS (SELECT runcat
                 FROM temprunningcatalog
                WHERE temprunningcatalog.runcat = runningcatalog_flux.runcat
                  AND temprunningcatalog.band = runningcatalog_flux.band
                  AND temprunningcatalog.stokes = runningcatalog_flux.stokes
                  AND temprunningcatalog.f_datapoints > 1
              )
"""
    cursor = execute(query, commit=True)
    cnt = cursor.rowcount
    if cnt > 0:
        logger.info("Updated fluxes for %s monitoring sources in runcat_flux" % cnt)
Beispiel #36
0
def _insert_tempruncat(image_id):
    """
    Here the associations of forced fits of the monitoring sources
    and their runningcatalog counterparts are inserted into the
    temporary table.

    We follow the implementation of the normal association procedure,
    except that we don't need to match with a De Ruiter radius, since
    the counterpart pairs are from the same runningcatalog source.
    """

    # The query is as follows:
    # t0 searches for matches between the monitoring sources
    # (extract_type = 2) in the current image that have
    # a counterpart among the runningcatalog sources. This
    # matching is done by zone, decl, ra (corrected for alpha
    # infaltion towards the poles) and the dot product by
    # using the Cartesian coordinates. Note that the conical
    # distance is not determined by the De Ruiter radius,
    # since all these sources have identical positions.
    # t0 has a left outer join with the runningcatalog_flux table,
    # since the image might be of a new frequency band. In that case
    # all the rf.values are NULL.
    # The select then determines all the new (statistical) properties
    # for the runcat-monitoring pairs, which are inserted in the
    # tempruncat table.
    # Note that a first image does not have any matches,
    # but that is taken into account by the second part of
    # the associate_ms() function.
    query = """\
INSERT INTO temprunningcatalog
  (runcat
  ,xtrsrc
  ,distance_arcsec
  ,r
  ,dataset
  ,band
  ,stokes
  ,datapoints
  ,zone
  ,wm_ra
  ,wm_decl
  ,wm_uncertainty_ew
  ,wm_uncertainty_ns
  ,avg_ra_err
  ,avg_decl_err
  ,avg_wra
  ,avg_wdecl
  ,avg_weight_ra
  ,avg_weight_decl
  ,x
  ,y
  ,z
  ,f_datapoints
  ,avg_f_peak
  ,avg_f_peak_sq
  ,avg_f_peak_weight
  ,avg_weighted_f_peak
  ,avg_weighted_f_peak_sq
  ,avg_f_int
  ,avg_f_int_sq
  ,avg_f_int_weight
  ,avg_weighted_f_int
  ,avg_weighted_f_int_sq
  )
  SELECT t0.runcat
        ,t0.xtrsrc
        ,0 as distance_arcsec
        ,0 as r
        ,t0.dataset
        ,t0.band
        ,t0.stokes
        ,t0.datapoints
        ,t0.zone
        ,t0.wm_ra
        ,t0.wm_decl
        ,t0.wm_uncertainty_ew
        ,t0.wm_uncertainty_ns
        ,t0.avg_ra_err
        ,t0.avg_decl_err
        ,t0.avg_wra
        ,t0.avg_wdecl
        ,t0.avg_weight_ra
        ,t0.avg_weight_decl
        ,t0.x
        ,t0.y
        ,t0.z
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1
              ELSE rf.f_datapoints + 1
         END AS f_datapoints
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak
              ELSE (rf.f_datapoints * rf.avg_f_peak
                    + t0.f_peak)
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak * t0.f_peak
              ELSE (rf.f_datapoints * rf.avg_f_peak_sq
                    + t0.f_peak * t0.f_peak)
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1 / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_f_peak_weight
                    + 1 / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak_weight
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_peak
                    + t0.f_peak / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_peak
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak * t0.f_peak / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_peak_sq
                    + (t0.f_peak * t0.f_peak) / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_peak_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int
              ELSE (rf.f_datapoints * rf.avg_f_int
                    + t0.f_int)
                   / (rf.f_datapoints + 1)
         END AS avg_f_int
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int * t0.f_int
              ELSE (rf.f_datapoints * rf.avg_f_int_sq
                    + t0.f_int * t0.f_int)
                   / (rf.f_datapoints + 1)
         END AS avg_f_int_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1 / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_f_int_weight
                    + 1 / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_f_int_weight
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_int
                    + t0.f_int / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_int
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int * t0.f_int / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_int_sq
                    + (t0.f_int * t0.f_int) / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_int_sq
    FROM (SELECT r.id AS runcat
                ,x.id AS xtrsrc
                ,x.f_peak
                ,x.f_peak_err
                ,x.f_int
                ,x.f_int_err
                ,i.dataset
                ,i.band
                ,i.stokes
                ,r.datapoints + 1 AS datapoints
                ,r.zone
                ,r.wm_ra
                ,r.wm_decl
                ,r.wm_uncertainty_ew
                ,r.wm_uncertainty_ns
                ,r.avg_ra_err
                ,r.avg_decl_err
                ,r.avg_wra
                ,r.avg_wdecl
                ,r.avg_weight_ra
                ,r.avg_weight_decl
                ,r.x
                ,r.y
                ,r.z
            FROM runningcatalog r
                ,image i
                ,extractedsource x
           WHERE i.id = %(image_id)s
             AND r.dataset = i.dataset
             AND x.image = i.id
             AND x.image = %(image_id)s
             AND x.extract_type = 2
             AND r.mon_src = TRUE
             AND r.zone BETWEEN CAST(FLOOR(x.decl - x.error_radius/3600) AS INTEGER)
                              AND CAST(FLOOR(x.decl + x.error_radius/3600) AS INTEGER)
             AND r.wm_decl BETWEEN x.decl - x.error_radius/3600
                               AND x.decl + x.error_radius/3600
             AND r.wm_ra BETWEEN x.ra - alpha(x.error_radius/3600, x.decl)
                             AND x.ra + alpha(x.error_radius/3600, x.decl)
             AND r.x * x.x + r.y * x.y + r.z * x.z > COS(RADIANS(r.wm_uncertainty_ew))
         ) t0
         LEFT OUTER JOIN runningcatalog_flux rf
         ON t0.runcat = rf.runcat
         AND t0.band = rf.band
         AND t0.stokes = rf.stokes
"""
    qry_params = {'image_id': image_id}
    cursor = execute(query, qry_params, commit=True)
    cnt = cursor.rowcount
    if cnt > 0:
        logger.info("Inserted %s monitoring-runcat candidate pairs in tempruncat" % cnt)
Beispiel #37
0
def _insert_tempruncat(image_id):
    """
    Here the associations of forced fits and their runningcatalog counterparts
    are inserted into the temporary table.

    We follow the analogies of the normal association procedure.
    The difference here is that we know what the runcat ids are for the
    extractedsource.extract_type = 1 (ff_nd) sources are, since these
    were inserted at the same time as well.

    This is why subtable t0 looks simpler than in the normal case. We
    still have to do a left outer join with the runcat_flux table (rf),
    because fluxes might not be detected in other bands.
    Before being inserted the additional properties are calculated.

    """

    query = """\
INSERT INTO temprunningcatalog
  (runcat
  ,xtrsrc
  ,distance_arcsec
  ,r
  ,dataset
  ,band
  ,stokes
  ,datapoints
  ,zone
  ,wm_ra
  ,wm_decl
  ,wm_uncertainty_ew
  ,wm_uncertainty_ns
  ,avg_ra_err
  ,avg_decl_err
  ,avg_wra
  ,avg_wdecl
  ,avg_weight_ra
  ,avg_weight_decl
  ,x
  ,y
  ,z
  ,f_datapoints
  ,avg_f_peak
  ,avg_f_peak_sq
  ,avg_f_peak_weight
  ,avg_weighted_f_peak
  ,avg_weighted_f_peak_sq
  ,avg_f_int
  ,avg_f_int_sq
  ,avg_f_int_weight
  ,avg_weighted_f_int
  ,avg_weighted_f_int_sq
  )
  SELECT t0.runcat
        ,t0.xtrsrc
        ,0 AS distance_arcsec
        ,0 AS r
        ,t0.dataset
        ,t0.band
        ,t0.stokes
        ,t0.datapoints
        ,t0.zone
        ,t0.wm_ra
        ,t0.wm_decl
        ,t0.wm_uncertainty_ew
        ,t0.wm_uncertainty_ns
        ,t0.avg_ra_err
        ,t0.avg_decl_err
        ,t0.avg_wra
        ,t0.avg_wdecl
        ,t0.avg_weight_ra
        ,t0.avg_weight_decl
        ,t0.x
        ,t0.y
        ,t0.z
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1
              ELSE rf.f_datapoints + 1
         END AS f_datapoints
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak
              ELSE (rf.f_datapoints * rf.avg_f_peak
                    + t0.f_peak)
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak * t0.f_peak
              ELSE (rf.f_datapoints * rf.avg_f_peak_sq
                    + t0.f_peak * t0.f_peak)
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1 / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_f_peak_weight
                    + 1 / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak_weight
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_peak
                    + t0.f_peak / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_peak
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak * t0.f_peak / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_peak_sq
                    + (t0.f_peak * t0.f_peak) / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_peak_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int
              ELSE (rf.f_datapoints * rf.avg_f_int
                    + t0.f_int)
                   / (rf.f_datapoints + 1)
         END AS avg_f_int
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int * t0.f_int
              ELSE (rf.f_datapoints * rf.avg_f_int_sq
                    + t0.f_int * t0.f_int)
                   / (rf.f_datapoints + 1)
         END AS avg_f_int_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1 / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_f_int_weight
                    + 1 / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_f_int_weight
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_int
                    + t0.f_int / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_int
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int * t0.f_int / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_int_sq
                    + (t0.f_int * t0.f_int) / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_int_sq
    FROM (SELECT r.id AS runcat
                ,x.id AS xtrsrc
                ,x.f_peak
                ,x.f_peak_err
                ,x.f_int
                ,x.f_int_err
                ,i.dataset
                ,i.band
                ,i.stokes
                ,r.datapoints
                ,r.zone
                ,r.wm_ra
                ,r.wm_decl
                ,r.wm_uncertainty_ew
                ,r.wm_uncertainty_ns
                ,r.avg_ra_err
                ,r.avg_decl_err
                ,r.avg_wra
                ,r.avg_wdecl
                ,r.avg_weight_ra
                ,r.avg_weight_decl
                ,r.x
                ,r.y
                ,r.z
            FROM extractedsource x
                ,image i
                ,runningcatalog r
           WHERE x.image = %(image_id)s
             AND x.extract_type = 1
             AND i.id = x.image
             AND r.id = x.ff_runcat
             AND r.mon_src = FALSE
         ) t0
         LEFT OUTER JOIN runningcatalog_flux rf
         ON t0.runcat = rf.runcat
         AND t0.band = rf.band
         AND t0.stokes = rf.stokes
"""
    qry_params = {'image_id': image_id}
    cursor = execute(query, qry_params, commit=True)
    cnt = cursor.rowcount
    logger.debug("Inserted %s null detections in tempruncat" % cnt)
Beispiel #38
0
def dataset_images(dataset_id, database=None):
    q = "SELECT id FROM image WHERE dataset=%(dataset)s LIMIT 1"
    args = {'dataset': dataset_id}
    cursor = execute(q, args)
    image_ids = [x[0] for x in cursor.fetchall()]
    return image_ids
Beispiel #39
0
def _insert_tempruncat(image_id):
    """
    Here the associations of forced fits of the monitoring sources
    and their runningcatalog counterparts are inserted into the
    temporary table.

    We follow the implementation of the normal association procedure,
    except that we don't need to match with a De Ruiter radius, since
    the counterpart pairs are from the same runningcatalog source.
    """

    # The query is as follows:
    # t0 searches for matches between the monitoring sources
    # (extract_type = 2) in the current image that have
    # a counterpart among the runningcatalog sources. This
    # matching is done by zone, decl, ra (corrected for alpha
    # infaltion towards the poles) and the dot product by
    # using the Cartesian coordinates. Note that the conical
    # distance is not determined by the De Ruiter radius,
    # since all these sources have identical positions.
    # t0 has a left outer join with the runningcatalog_flux table,
    # since the image might be of a new frequency band. In that case
    # all the rf.values are NULL.
    # The select then determines all the new (statistical) properties
    # for the runcat-monitoring pairs, which are inserted in the
    # tempruncat table.
    # Note that a first image does not have any matches,
    # but that is taken into account by the second part of
    # the associate_ms() function.
    query = """\
INSERT INTO temprunningcatalog
  (runcat
  ,xtrsrc
  ,distance_arcsec
  ,r
  ,dataset
  ,band
  ,stokes
  ,datapoints
  ,zone
  ,wm_ra
  ,wm_decl
  ,wm_uncertainty_ew
  ,wm_uncertainty_ns
  ,avg_ra_err
  ,avg_decl_err
  ,avg_wra
  ,avg_wdecl
  ,avg_weight_ra
  ,avg_weight_decl
  ,x
  ,y
  ,z
  ,f_datapoints
  ,avg_f_peak
  ,avg_f_peak_sq
  ,avg_f_peak_weight
  ,avg_weighted_f_peak
  ,avg_weighted_f_peak_sq
  ,avg_f_int
  ,avg_f_int_sq
  ,avg_f_int_weight
  ,avg_weighted_f_int
  ,avg_weighted_f_int_sq
  )
  SELECT t0.runcat_id
        ,t0.xtrsrc
        ,0 as distance_arcsec
        ,0 as r
        ,t0.dataset
        ,t0.band
        ,t0.stokes
        ,t0.datapoints
        ,t0.zone
        ,t0.wm_ra
        ,t0.wm_decl
        ,t0.wm_uncertainty_ew
        ,t0.wm_uncertainty_ns
        ,t0.avg_ra_err
        ,t0.avg_decl_err
        ,t0.avg_wra
        ,t0.avg_wdecl
        ,t0.avg_weight_ra
        ,t0.avg_weight_decl
        ,t0.x
        ,t0.y
        ,t0.z
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1
              ELSE rf.f_datapoints + 1
         END AS f_datapoints
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak
              ELSE (rf.f_datapoints * rf.avg_f_peak
                    + t0.f_peak)
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak * t0.f_peak
              ELSE (rf.f_datapoints * rf.avg_f_peak_sq
                    + t0.f_peak * t0.f_peak)
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1 / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_f_peak_weight
                    + 1 / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_f_peak_weight
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_peak
                    + t0.f_peak / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_peak
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_peak * t0.f_peak / (t0.f_peak_err * t0.f_peak_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_peak_sq
                    + (t0.f_peak * t0.f_peak) / (t0.f_peak_err * t0.f_peak_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_peak_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int
              ELSE (rf.f_datapoints * rf.avg_f_int
                    + t0.f_int)
                   / (rf.f_datapoints + 1)
         END AS avg_f_int
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int * t0.f_int
              ELSE (rf.f_datapoints * rf.avg_f_int_sq
                    + t0.f_int * t0.f_int)
                   / (rf.f_datapoints + 1)
         END AS avg_f_int_sq
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN 1 / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_f_int_weight
                    + 1 / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_f_int_weight
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_int
                    + t0.f_int / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_int
        ,CASE WHEN rf.f_datapoints IS NULL
              THEN t0.f_int * t0.f_int / (t0.f_int_err * t0.f_int_err)
              ELSE (rf.f_datapoints * rf.avg_weighted_f_int_sq
                    + (t0.f_int * t0.f_int) / (t0.f_int_err * t0.f_int_err))
                   / (rf.f_datapoints + 1)
         END AS avg_weighted_f_int_sq
    FROM (SELECT mon.runcat AS runcat_id
                ,x.id AS xtrsrc
                ,x.f_peak
                ,x.f_peak_err
                ,x.f_int
                ,x.f_int_err
                ,i.dataset
                ,i.band
                ,i.stokes
                ,r.datapoints + 1 AS datapoints
                ,r.zone
                ,r.wm_ra
                ,r.wm_decl
                ,r.wm_uncertainty_ew
                ,r.wm_uncertainty_ns
                ,r.avg_ra_err
                ,r.avg_decl_err
                ,r.avg_wra
                ,r.avg_wdecl
                ,r.avg_weight_ra
                ,r.avg_weight_decl
                ,r.x
                ,r.y
                ,r.z
            FROM monitor mon
             JOIN extractedsource x
               ON mon.id = x.ff_monitor
             JOIN runningcatalog r
               ON mon.runcat = r.id
             JOIN image i
               ON x.image = i.id
           WHERE mon.runcat IS NOT NULL
             AND x.image = %(image_id)s
             AND x.extract_type = 2
         ) t0
         LEFT OUTER JOIN runningcatalog_flux rf
         ON t0.runcat_id = rf.runcat
         AND t0.band = rf.band
         AND t0.stokes = rf.stokes
"""
    qry_params = {'image_id': image_id}
    cursor = execute(query, qry_params, commit=True)
    cnt = cursor.rowcount
    logger.debug("Inserted %s monitoring-runcat pairs in tempruncat" % cnt)