Example #1
0
def _combined(session, dataset):
    """

    args:
        session (Session): SQLAlchemy session
        runcat (Runningcatalog):  Running catalog model object
        dataset (Dataset): Dataset model object

    return: a SQLALchemy subquery
    """
    runcat = aliased(Runningcatalog, name='r')
    match_assoc = aliased(Assocxtrsource, name='match_assoc')
    match_ex = aliased(Extractedsource, name='match_ex')
    match_img = aliased(Image, name='match_img')
    agg_img = aliased(Image, name='agg_img')
    agg_assoc = aliased(Assocxtrsource, name='agg_assoc')
    agg_ex = aliased(Extractedsource, name='agg_ex')

    newsrc_trigger_query = _newsrc_trigger(session, dataset)
    last_ts_fmax_query = _last_ts_fmax(session, dataset)

    return session.query(
        runcat.id.label('runcat'),
        runcat.wm_ra.label('ra'),
        runcat.wm_decl.label('decl'),
        runcat.wm_uncertainty_ew,
        runcat.wm_uncertainty_ns,
        runcat.xtrsrc_id,
        runcat.dataset_id.label('dataset_id'),
        runcat.datapoints,
        match_assoc.v_int,
        match_assoc.eta_int,
        match_img.band_id,
        newsrc_trigger_query.c.id.label('newsource'),
        newsrc_trigger_query.c.sigma_rms_max.label('sigma_rms_max'),
        newsrc_trigger_query.c.sigma_rms_min.label('sigma_rms_min'),
        func.max(agg_ex.f_int).label('lightcurve_max'),
        func.avg(agg_ex.f_int).label('lightcurve_avg'),
        func.median(agg_ex.f_int).label('lightcurve_median')
    ). \
        select_from(last_ts_fmax_query). \
        join(match_assoc, match_assoc.runcat_id == last_ts_fmax_query.c.runcat_id). \
        join(match_ex,
             (match_assoc.xtrsrc_id == match_ex.id) &
             (match_ex.f_int == last_ts_fmax_query.c.max_flux)). \
        join(runcat, runcat.id == last_ts_fmax_query.c.runcat_id). \
        join(match_img, match_ex.image_id == match_img.id). \
        outerjoin(newsrc_trigger_query, newsrc_trigger_query.c.rc_id == runcat.id). \
        join(agg_assoc, runcat.id == agg_assoc.runcat_id). \
        join(agg_ex, agg_assoc.xtrsrc_id == agg_ex.id). \
        join(agg_img,
             (agg_ex.image_id == agg_img.id) & (agg_img.band_id == match_img.band_id)). \
        group_by(runcat.id,
                 runcat.wm_ra,
                 runcat.wm_decl,
                 runcat.wm_uncertainty_ew,
                 runcat.wm_uncertainty_ns,
                 runcat.xtrsrc_id,
                 runcat.dataset_id,
                 runcat.datapoints,
                 match_assoc.v_int,
                 match_assoc.eta_int,
                 match_img.band_id,
                 newsrc_trigger_query.c.id,
                 newsrc_trigger_query.c.sigma_rms_max,
                 newsrc_trigger_query.c.sigma_rms_min,
                 ). \
        filter(runcat.dataset == dataset). \
        subquery()
 def build_query_to_report(self, query, aggregate_table):
     median = func.median(aggregate_table.c.rtt_samples)
     return query.column(label("MedianRTT", median))
 def build_query_to_report(self, query, aggregate_table):
     median = func.median(aggregate_table.c.upload_samples)
     return query.column(label("upload_median", median))
Example #4
0
def _combined(session, dataset):
    """

    args:
        session (Session): SQLAlchemy session
        runcat (Runningcatalog):  Running catalog model object
        dataset (Dataset): Dataset model object

    return: a SQLALchemy subquery
    """
    runcat = aliased(Runningcatalog, name='r')
    match_assoc = aliased(Assocxtrsource, name='match_assoc')
    match_ex = aliased(Extractedsource, name='match_ex')
    match_img = aliased(Image, name='match_img')
    agg_img = aliased(Image, name='agg_img')
    agg_assoc = aliased(Assocxtrsource, name='agg_assoc')
    agg_ex = aliased(Extractedsource, name='agg_ex')

    newsrc_trigger_query = _newsrc_trigger(session, dataset)
    last_ts_fmax_query = _last_ts_fmax(session, dataset)

    return session.query(
        runcat.id.label('runcat'),
        runcat.wm_ra.label('ra'),
        runcat.wm_decl.label('decl'),
        runcat.wm_uncertainty_ew,
        runcat.wm_uncertainty_ns,
        runcat.xtrsrc_id,
        runcat.dataset_id.label('dataset_id'),
        runcat.datapoints,
        match_assoc.v_int,
        match_assoc.eta_int,
        match_img.band_id,
        newsrc_trigger_query.c.id.label('newsource'),
        newsrc_trigger_query.c.sigma_rms_max.label('sigma_rms_max'),
        newsrc_trigger_query.c.sigma_rms_min.label('sigma_rms_min'),
        func.max(agg_ex.f_int).label('lightcurve_max'),
        func.avg(agg_ex.f_int).label('lightcurve_avg'),
        func.median(agg_ex.f_int).label('lightcurve_median')
    ). \
        select_from(last_ts_fmax_query). \
        join(match_assoc, match_assoc.runcat_id == last_ts_fmax_query.c.runcat_id). \
        join(match_ex,
             (match_assoc.xtrsrc_id == match_ex.id) &
             (match_ex.f_int == last_ts_fmax_query.c.max_flux)). \
        join(runcat, runcat.id == last_ts_fmax_query.c.runcat_id). \
        join(match_img, match_ex.image_id == match_img.id). \
        outerjoin(newsrc_trigger_query, newsrc_trigger_query.c.rc_id == runcat.id). \
        join(agg_assoc, runcat.id == agg_assoc.runcat_id). \
        join(agg_ex, agg_assoc.xtrsrc_id == agg_ex.id). \
        join(agg_img,
             (agg_ex.image_id == agg_img.id) & (agg_img.band_id == match_img.band_id)). \
        group_by(runcat.id,
                 runcat.wm_ra,
                 runcat.wm_decl,
                 runcat.wm_uncertainty_ew,
                 runcat.wm_uncertainty_ns,
                 runcat.xtrsrc_id,
                 runcat.dataset_id,
                 runcat.datapoints,
                 match_assoc.v_int,
                 match_assoc.eta_int,
                 match_img.band_id,
                 newsrc_trigger_query.c.id,
                 newsrc_trigger_query.c.sigma_rms_max,
                 newsrc_trigger_query.c.sigma_rms_min,
                 ). \
        filter(runcat.dataset == dataset). \
        subquery()