def _load_targets(self, RModel):
"""Load data from the intermediate table tp targetdb.target."""
log.debug('loading data into targetdb.target.')
n_inserted = (tdb.Target.insert_from(
cdb.Catalog.select(
cdb.Catalog.catalogid,
cdb.Catalog.ra,
cdb.Catalog.dec,
cdb.Catalog.pmra,
cdb.Catalog.pmdec,
cdb.Catalog.parallax,
peewee.Value(EPOCH),
).join(
RModel,
on=(cdb.Catalog.catalogid == RModel.catalogid
)).where(RModel.selected >> True).where(~peewee.fn.EXISTS(
tdb.Target.select(peewee.SQL('1')).where(
tdb.Target.catalogid == RModel.catalogid))),
[
tdb.Target.catalogid,
tdb.Target.ra,
tdb.Target.dec,
tdb.Target.pmra,
tdb.Target.pmdec,
tdb.Target.parallax,
tdb.Target.epoch,
],
).returning().execute())
log.info(f'Inserted {n_inserted:,} new rows into targetdb.target.')
return
def build_query(self, version_id, query_region=None):
optical_prov = peewee.Value('sdss_psfmag_tycho2').cast('text')
g = peewee.Value(None).cast('real')
r = peewee.Value(None).cast('real')
i = peewee.Value(None).cast('real')
z = peewee.Value(None).cast('real')
gaia_g = peewee.Value(None).cast('real')
query = (CatalogToTycho2.select(
CatalogToTycho2.catalogid, Tycho2.tycid, Tycho2.designation,
Tycho2.ramdeg.alias('tycho2_ra'),
Tycho2.demdeg.alias('tycho2_dec'),
Tycho2.pmra.alias('tycho2_pmra'), Tycho2.pmde.alias('tycho2_pmde'),
Tycho2.vtmag, Tycho2.btmag, optical_prov.alias('optical_prov'),
g.alias('g'), r.alias('r'), i.alias('i'), z.alias('z'),
gaia_g.alias('gaia_g')).join(
Tycho2,
on=(CatalogToTycho2.target_id == Tycho2.designation)).where(
CatalogToTycho2.version_id == version_id,
CatalogToTycho2.best >> True, Tycho2.vtmag < 13))
if query_region:
query = (query.join_from(CatalogToTycho2, Catalog).where(
peewee.fn.q3c_radial_query(Catalog.ra, Catalog.dec,
query_region[0], query_region[1],
query_region[2])))
return query
def build_query(self, version_id, query_region=None):
c = Catalog.alias()
ls = Legacy_Survey_DR8.alias()
c2ls = CatalogToLegacy_Survey_DR8.alias()
s2020 = BHM_eFEDS_Veto.alias()
sV = SDSSV_BOSS_SPALL.alias()
xx = EROSITASupersetClusters.alias()
x = (xx.select(
fn.rank().over(partition_by=[xx.ero_detuid],
order_by=[xx.xmatch_metric.desc()]).alias('x_rank'),
xx.ero_detuid.alias('ero_detuid'),
xx.ls_id.alias('ls_id'),
xx.target_has_spec.alias('target_has_spec'),
).where(
(xx.ero_version == self.parameters['ero_version']),
(xx.xmatch_method == self.parameters['xmatch_method']),
(xx.xmatch_version == self.parameters['xmatch_version']),
(xx.opt_cat == self.parameters['opt_cat']),
(xx.xmatch_metric > self.parameters['xmatch_metric_min']),
(xx.ero_det_like > self.parameters['det_like_min']),
).alias('x'))
instrument = peewee.Value(self.instrument)
inertial = peewee.Value(self.inertial).cast('bool')
fibertotflux_r_max = AB2nMgy(self.parameters['fibertotmag_r_min'])
fibertotflux_r_min = AB2nMgy(self.parameters['fibertotmag_r_max'])
fibertotflux_z_max = AB2nMgy(self.parameters['fibertotmag_z_min'])
fibertotflux_z_min = AB2nMgy(self.parameters['fibertotmag_z_max'])
fibertotflux_r_min_for_cadence1 = AB2nMgy(
self.parameters['fibertotmag_r_for_cadence1'])
fibertotflux_z_min_for_cadence1 = AB2nMgy(
self.parameters['fibertotmag_z_for_cadence1'])
fibertotflux_r_min_for_cadence2 = AB2nMgy(
self.parameters['fibertotmag_r_for_cadence2'])
gaia_g_max_for_cadence1 = self.parameters['gaia_g_max_for_cadence1']
gaia_rp_max_for_cadence1 = self.parameters['gaia_rp_max_for_cadence1']
# flux30 = AB2nMgy(30.00)
# match_radius_spectro = self.parameters['spec_join_radius'] / 3600.0
# #########################################################################
# prepare the spectroscopy catalogues
match_radius_spectro = self.parameters['spec_join_radius'] / 3600.0
spec_sn_thresh = self.parameters['spec_sn_thresh']
spec_z_err_thresh = self.parameters['spec_z_err_thresh']
# SDSS DR16
c2s16 = CatalogToSDSS_DR16_SpecObj.alias()
ss16 = SDSS_DR16_SpecObj.alias()
s16 = (ss16.select(ss16.specobjid.alias('specobjid'), ).where(
ss16.snmedian >= spec_sn_thresh,
ss16.zwarning == 0,
ss16.zerr <= spec_z_err_thresh,
ss16.zerr > 0.0,
ss16.scienceprimary > 0,
).alias('s16'))
# SDSS-IV/eFEDS March2020
c2s2020 = CatalogToBHM_eFEDS_Veto.alias()
ss2020 = BHM_eFEDS_Veto.alias()
s2020 = (ss2020.select(ss2020.pk.alias('pk'), ).where(
ss2020.sn_median_all >= spec_sn_thresh,
ss2020.zwarning == 0,
ss2020.z_err <= spec_z_err_thresh,
ss2020.z_err > 0.0,
).alias('s2020'))
# SDSS-V spAll
ssV = SDSSV_BOSS_SPALL.alias()
sV = (ssV.select(
ssV.specobjid.alias('specobjid'),
ssV.plug_ra.alias('plug_ra'),
ssV.plug_dec.alias('plug_dec'),
).where(
ssV.sn_median_all >= spec_sn_thresh,
ssV.zwarning == 0,
ssV.z_err <= spec_z_err_thresh,
ssV.z_err > 0.0,
ssV.specprimary > 0,
).alias('sV'))
# SDSS-V plateholes - only consider plateholes that
# were drilled+shipped but that were not yet observed
ssph = SDSSV_Plateholes.alias()
ssphm = SDSSV_Plateholes_Meta.alias()
ssconf = SDSSV_BOSS_Conflist.alias()
sph = (ssph.select(
ssph.pkey.alias('pkey'),
ssph.target_ra.alias('target_ra'),
ssph.target_dec.alias('target_dec'),
).join(ssphm, on=(ssph.yanny_uid == ssphm.yanny_uid)).join(
ssconf, JOIN.LEFT_OUTER, on=(ssphm.plateid == ssconf.plate)).where(
(ssph.holetype == 'BOSS_SHARED'),
(ssph.sourcetype == 'SCI') | (ssph.sourcetype == 'STA'),
ssphm.isvalid > 0,
ssconf.plate.is_null(),
).alias('sph'))
# priority is determined by target rank within cluster
# start with a priority floor value (per carton)
# then increment if any conditions are met:
priority = peewee.Case(None, (
(x.c.x_rank == 1, self.parameters['priority_floor_bcg']),
(x.c.x_rank > 1, self.parameters['priority_floor_member'] +
fn.least(self.parameters['priority_levels'] - 2, x.c.x_rank - 2)),
), None)
value = peewee.Case(None, (
(x.c.x_rank == 1, self.parameters['value_bcg']),
(x.c.x_rank > 1, self.parameters['value_member']),
), None).cast('float')
# choose cadence based on fiber magnitude in r-band
cadence1 = self.parameters['cadence1']
cadence2 = self.parameters['cadence2']
cadence3 = self.parameters['cadence3']
cadence4 = 'unknown_cadence' # catch failures
cadence = peewee.Case(None, (
(((ls.fibertotflux_r > fibertotflux_r_min_for_cadence1) |
(ls.fibertotflux_z > fibertotflux_z_min_for_cadence1) |
(ls.gaia_phot_g_mean_mag.between(0.1, gaia_g_max_for_cadence1)) |
(ls.gaia_phot_rp_mean_mag.between(
0.1, gaia_rp_max_for_cadence1))), cadence1),
(ls.fibertotflux_r > fibertotflux_r_min_for_cadence2, cadence2),
(ls.fibertotflux_r <= fibertotflux_r_min_for_cadence2, cadence3),
), cadence4)
# compute transformed SDSS mags for pointlike and extended sources uniformly
# transform the legacysurvey grz into sdss psfmag griz
# extract coeffs from fit logs via:
# awk 'BEGIN {print("coeffs = {")} /POLYFIT/{ if($3~/sdss_psfmag/){pe="p"} else if ($3~/sdss_fiber2mag/){pe="e"} else{pe="error"}; printf("\"%s%d_%s\": %s,\n", substr($3,length($3)), $8, pe, $10)} END {print("}")}' bhm_spiders_clusters_lsdr8/lsdr8_fibermag_to_sdss_fiber2mag_?_results.log # noqa
coeffs = {
"g2_e": -0.897719,
"g1_e": 2.298300,
"g0_e": -1.019299,
"i2_e": -0.950114,
"i1_e": 0.981972,
"i0_e": -0.261645,
"r2_e": -0.201741,
"r1_e": 0.697128,
"r0_e": -0.120926,
"z2_e": -1.424312,
"z1_e": 2.415301,
"z0_e": -0.677163,
}
nMgy_min = 1e-3 # equiv to AB=30
# extended - start from ls8 fiberfluxes
g0_e = (
22.5 -
2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.fiberflux_g)))
r0_e = (
22.5 -
2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.fiberflux_r)))
z0_e = (
22.5 -
2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.fiberflux_z)))
g_r_e = (-2.5 * peewee.fn.log(
peewee.fn.greatest(nMgy_min, ls.fiberflux_g) /
peewee.fn.greatest(nMgy_min, ls.fiberflux_r)))
r_z_e = (-2.5 * peewee.fn.log(
peewee.fn.greatest(nMgy_min, ls.fiberflux_r) /
peewee.fn.greatest(nMgy_min, ls.fiberflux_z)))
g_e = (g0_e + coeffs['g0_e'] + coeffs['g1_e'] * g_r_e +
coeffs['g2_e'] * g_r_e * g_r_e)
r_e = (r0_e + coeffs['r0_e'] + coeffs['r1_e'] * g_r_e +
coeffs['r2_e'] * g_r_e * g_r_e)
i_e = (r0_e + coeffs['i0_e'] + coeffs['i1_e'] * r_z_e +
coeffs['i2_e'] * r_z_e * r_z_e)
z_e = (z0_e + coeffs['z0_e'] + coeffs['z1_e'] * r_z_e +
coeffs['z2_e'] * r_z_e * r_z_e)
# validity checks
valid = (g0_e.between(0.1, 29.9) & r0_e.between(0.1, 29.9)
& z0_e.between(0.1, 29.9))
opt_prov = peewee.Case(None, ((valid, 'sdss_fiber2mag_from_lsdr8'), ),
'undefined')
magnitude_g = peewee.Case(None, ((valid, g_e), ), 'NaN')
magnitude_r = peewee.Case(None, ((valid, r_e), ), 'NaN')
magnitude_i = peewee.Case(None, ((valid, i_e), ), 'NaN')
magnitude_z = peewee.Case(None, ((valid, z_e), ), 'NaN')
magnitude_gaia_g = peewee.Case(None, ((ls.gaia_phot_g_mean_mag.between(
0.1, 29.9), ls.gaia_phot_g_mean_mag), ), 'NaN')
magnitude_gaia_bp = peewee.Case(
None, ((ls.gaia_phot_bp_mean_mag.between(
0.1, 29.9), ls.gaia_phot_bp_mean_mag), ), 'NaN')
magnitude_gaia_rp = peewee.Case(
None, ((ls.gaia_phot_rp_mean_mag.between(
0.1, 29.9), ls.gaia_phot_rp_mean_mag), ), 'NaN')
# # We want to switch between psfmags and fibertotmags depending on
# # ls.type parameter (PSF or extended)
# # For 'PSF' targets, we use psfmags, but for extended sources use fiber2mags
# opt_prov = peewee.Case(
# ls.type,
# (('PSF', 'ls_psfmag'),),
# 'ls_fibertotmag')
#
# magnitude_g = peewee.Case(
# ls.type,
# (('PSF', (22.5 - 2.5 * fn.log10(fn.greatest(flux30, ls.flux_g))).cast('float')),),
# (22.5 - 2.5 * fn.log10(fn.greatest(flux30, ls.fibertotflux_g))).cast('float'))
#
# magnitude_r = peewee.Case(
# ls.type,
# (('PSF', (22.5 - 2.5 * fn.log10(fn.greatest(flux30, ls.flux_r))).cast('float')),),
# (22.5 - 2.5 * fn.log10(fn.greatest(flux30, ls.fibertotflux_r))).cast('float'))
#
# magnitude_z = peewee.Case(
# ls.type,
# (('PSF', (22.5 - 2.5 * fn.log10(fn.greatest(flux30, ls.flux_z))).cast('float')),),
# (22.5 - 2.5 * fn.log10(fn.greatest(flux30, ls.fibertotflux_z))).cast('float'))
#
# magnitude_i = peewee.Case(
# ls.type,
# (('PSF',
# (22.5 - 2.5 * fn.log10(
# fn.greatest(flux30, 0.5 * (ls.flux_r + ls.flux_z)))).cast('float')),),
# (22.5 - 2.5 * fn.log10(
# fn.greatest(flux30, 0.5 * (ls.fibertotflux_r +
# ls.fibertotflux_z)))).cast('float'))
spec_sn_thresh = self.parameters['spec_sn_thresh']
spec_z_err_thresh = self.parameters['spec_z_err_thresh']
query = (
c.select(
c.catalogid.alias('catalogid'),
ls.ls_id.alias('ls_id'), # extra
x.c.ero_detuid.cast('text').alias('ero_detuid'), # extra
c.ra.alias('ra'), # extra
c.dec.alias('dec'), # extra
priority.alias('priority'),
value.alias('value'),
cadence.alias('cadence'),
instrument.alias('instrument'),
opt_prov.alias('optical_prov'),
magnitude_g.alias('g'),
magnitude_r.alias('r'),
magnitude_i.alias('i'),
magnitude_z.alias('z'),
magnitude_gaia_g.alias('gaia_g'),
magnitude_gaia_bp.alias('bp'),
magnitude_gaia_rp.alias('rp'),
inertial.alias('inertial'),
g0_e.alias('ls8_fibermag_g'), # extra
r0_e.alias('ls8_fibermag_r'), # extra
z0_e.alias('ls8_fibermag_z'), # extra
).join(c2ls).join(ls).join(x, on=(ls.ls_id == x.c.ls_id))
# start joining the spectroscopy
.switch(c).join(c2s16, JOIN.LEFT_OUTER).join(
s16,
JOIN.LEFT_OUTER,
on=((c2s16.target_id == s16.c.specobjid) &
(c2s16.version_id == version_id))).switch(c).join(
c2s2020, JOIN.LEFT_OUTER).join(
s2020,
JOIN.LEFT_OUTER,
on=((c2s2020.target_id == s2020.c.pk) &
(c2s2020.version_id == version_id))).join(
sV,
JOIN.LEFT_OUTER,
on=(fn.q3c_join(
sV.c.plug_ra, sV.c.plug_dec, c.ra,
c.dec, match_radius_spectro))).join(
sph,
JOIN.LEFT_OUTER,
on=(fn.q3c_join(
sph.c.target_ra,
sph.c.target_dec, c.ra, c.dec,
match_radius_spectro)))
# finished joining the spectroscopy
.where(c.version_id == version_id, c2ls.version_id == version_id,
c2ls.best >> True).where(
s16.c.specobjid.is_null(
True), # all of these must be satisfied
s2020.c.pk.is_null(True),
sV.c.specobjid.is_null(True),
sph.c.pkey.is_null(True),
).where(
((ls.fibertotflux_r.between(fibertotflux_r_min,
fibertotflux_r_max)) |
(ls.fibertotflux_z.between(fibertotflux_z_min,
fibertotflux_z_max))),
(x.c.target_has_spec == 0),
# gaia safety checks to avoid bad ls photometry
~(ls.gaia_phot_g_mean_mag.between(
0.1, self.parameters['gaia_g_mag_limit'])),
~(ls.gaia_phot_rp_mean_mag.between(
0.1, self.parameters['gaia_rp_mag_limit'])),
))
if query_region:
query = query.where(
peewee.fn.q3c_radial_query(c.ra, c.dec, query_region[0],
query_region[1], query_region[2]))
return query
def build_query(self, version_id, query_region=None):
c = Catalog.alias()
ps = Panstarrs1.alias()
c2ps = CatalogToPanstarrs1.alias(
) # only exists after v0.5 cross-match
# s2020 = BHM_eFEDS_Veto.alias()
# sV = SDSSV_BOSS_SPALL.alias()
xx = EROSITASupersetClusters.alias()
x = (xx.select(
fn.rank().over(partition_by=[xx.ero_detuid],
order_by=[xx.xmatch_metric.desc()]).alias('x_rank'),
xx.ero_detuid.alias('ero_detuid'),
xx.ps1_dr2_id.alias('ps1_dr2_id'),
xx.target_has_spec.alias('target_has_spec'),
).where(
(xx.ero_version == self.parameters['ero_version']),
(xx.xmatch_method == self.parameters['xmatch_method']),
(xx.xmatch_version == self.parameters['xmatch_version']),
(xx.opt_cat == self.parameters['opt_cat']),
(xx.xmatch_metric > self.parameters['xmatch_metric_min']),
(xx.ero_det_like > self.parameters['det_like_min']),
).alias('x'))
instrument = peewee.Value(self.instrument)
inertial = peewee.Value(self.inertial).cast('bool')
r_psf_flux_max = AB2Jy(self.parameters['r_psf_mag_min'])
i_psf_flux_max = AB2Jy(self.parameters['i_psf_mag_min'])
z_psf_flux_max = AB2Jy(self.parameters['z_psf_mag_min'])
r_psf_flux_min_for_cadence1 = AB2Jy(
self.parameters['r_psf_mag_max_for_cadence1'])
i_psf_flux_min_for_cadence1 = AB2Jy(
self.parameters['i_psf_mag_max_for_cadence1'])
z_psf_flux_min_for_cadence1 = AB2Jy(
self.parameters['z_psf_mag_max_for_cadence1'])
r_psf_flux_min_for_cadence2 = AB2Jy(
self.parameters['r_psf_mag_max_for_cadence2'])
i_psf_flux_min_for_cadence2 = AB2Jy(
self.parameters['i_psf_mag_max_for_cadence2'])
z_psf_flux_min_for_cadence2 = AB2Jy(
self.parameters['z_psf_mag_max_for_cadence2'])
# match_radius_spectro = self.parameters['spec_join_radius'] / 3600.0
# #########################################################################
# prepare the spectroscopy catalogues
match_radius_spectro = self.parameters['spec_join_radius'] / 3600.0
spec_sn_thresh = self.parameters['spec_sn_thresh']
spec_z_err_thresh = self.parameters['spec_z_err_thresh']
# SDSS DR16
c2s16 = CatalogToSDSS_DR16_SpecObj.alias()
ss16 = SDSS_DR16_SpecObj.alias()
s16 = (ss16.select(ss16.specobjid.alias('specobjid'), ).where(
ss16.snmedian >= spec_sn_thresh,
ss16.zwarning == 0,
ss16.zerr <= spec_z_err_thresh,
ss16.zerr > 0.0,
ss16.scienceprimary > 0,
).alias('s16'))
# SDSS-IV/eFEDS March2020
c2s2020 = CatalogToBHM_eFEDS_Veto.alias()
ss2020 = BHM_eFEDS_Veto.alias()
s2020 = (ss2020.select(ss2020.pk.alias('pk'), ).where(
ss2020.sn_median_all >= spec_sn_thresh,
ss2020.zwarning == 0,
ss2020.z_err <= spec_z_err_thresh,
ss2020.z_err > 0.0,
).alias('s2020'))
# SDSS-V spAll
ssV = SDSSV_BOSS_SPALL.alias()
sV = (ssV.select(
ssV.specobjid.alias('specobjid'),
ssV.plug_ra.alias('plug_ra'),
ssV.plug_dec.alias('plug_dec'),
).where(
ssV.sn_median_all >= spec_sn_thresh,
ssV.zwarning == 0,
ssV.z_err <= spec_z_err_thresh,
ssV.z_err > 0.0,
ssV.specprimary > 0,
).alias('sV'))
# SDSS-V plateholes - only consider plateholes that
# were drilled+shipped but that were not yet observed
ssph = SDSSV_Plateholes.alias()
ssphm = SDSSV_Plateholes_Meta.alias()
ssconf = SDSSV_BOSS_Conflist.alias()
sph = (ssph.select(
ssph.pkey.alias('pkey'),
ssph.target_ra.alias('target_ra'),
ssph.target_dec.alias('target_dec'),
).join(ssphm, on=(ssph.yanny_uid == ssphm.yanny_uid)).join(
ssconf, JOIN.LEFT_OUTER, on=(ssphm.plateid == ssconf.plate)).where(
(ssph.holetype == 'BOSS_SHARED'),
(ssph.sourcetype == 'SCI') | (ssph.sourcetype == 'STA'),
ssphm.isvalid > 0,
ssconf.plate.is_null(),
).alias('sph'))
# priority is determined by target rank within cluster
# start with a priority floor value (per carton)
# then increment if any conditions are met:
priority = peewee.Case(None, (
(x.c.x_rank == 1, self.parameters['priority_floor_bcg']),
(x.c.x_rank > 1, self.parameters['priority_floor_member'] +
fn.least(self.parameters['priority_levels'] - 2, x.c.x_rank - 2)),
), None)
value = peewee.Case(None, (
(x.c.x_rank == 1, self.parameters['value_bcg']),
(x.c.x_rank > 1, self.parameters['value_member']),
), None)
# choose cadence based on psf_flux magnitude in panstarrs1 g,r,i-bands
cadence1 = self.parameters['cadence1']
cadence2 = self.parameters['cadence2']
cadence3 = self.parameters['cadence3']
cadence4 = 'unknown_cadence'
cadence = peewee.Case(None, (
((ps.r_stk_psf_flux > r_psf_flux_min_for_cadence1) |
(ps.i_stk_psf_flux > i_psf_flux_min_for_cadence1) |
(ps.z_stk_psf_flux > z_psf_flux_min_for_cadence1), cadence1),
((ps.r_stk_psf_flux > r_psf_flux_min_for_cadence2) |
(ps.i_stk_psf_flux > i_psf_flux_min_for_cadence2) |
(ps.z_stk_psf_flux > z_psf_flux_min_for_cadence2), cadence2),
((ps.r_stk_psf_flux <= r_psf_flux_min_for_cadence2) &
(ps.i_stk_psf_flux <= i_psf_flux_min_for_cadence2) &
(ps.z_stk_psf_flux <= z_psf_flux_min_for_cadence2), cadence3),
), cadence4)
# compute transformed SDSS mags for all sources uniformly
# transform the panstarrs1-dr2 griz into sdss psfmag griz
# extract coeffs from fit logs via:
# awk 'BEGIN {print("coeffs = {")} /POLYFIT/{ if($3~/sdss_psfmag/){pe="p"} else if ($3~/sdss_fiber2mag/){pe="e"} else{pe="error"}; printf("\"%s%d_%s\": %s,\n", substr($3,length($3)), $8, pe, $10)} END {print("}")}' bhm_spiders_clusters_ps1dr2/ps1dr2_stk_psf_to_sdss_fiber2mag_?_results.log # noqa
coeffs = {
"g2_e": -0.353294,
"g1_e": 0.699658,
"g0_e": 0.581569,
"i2_e": -0.446208,
"i1_e": 0.776628,
"i0_e": 0.421538,
"r2_e": -0.123243,
"r1_e": 0.401786,
"r0_e": 0.422531,
"z2_e": -0.488437,
"z1_e": 0.595132,
"z0_e": 0.439771,
}
Jy_min = AB2Jy(30.00)
# start from ps1dr2 stk psf fluxes
g0 = (
8.9 -
2.5 * peewee.fn.log(peewee.fn.greatest(Jy_min, ps.g_stk_psf_flux)))
r0 = (
8.9 -
2.5 * peewee.fn.log(peewee.fn.greatest(Jy_min, ps.r_stk_psf_flux)))
i0 = (
8.9 -
2.5 * peewee.fn.log(peewee.fn.greatest(Jy_min, ps.i_stk_psf_flux)))
z0 = (
8.9 -
2.5 * peewee.fn.log(peewee.fn.greatest(Jy_min, ps.z_stk_psf_flux)))
g_r = g0 - r0
r_i = r0 - i0
i_z = i0 - z0
# use single set of transform coeffs
g_e = (g0 + coeffs['g0_e'] + coeffs['g1_e'] * g_r +
coeffs['g2_e'] * g_r * g_r)
r_e = (r0 + coeffs['r0_e'] + coeffs['r1_e'] * g_r +
coeffs['r2_e'] * g_r * g_r)
i_e = (i0 + coeffs['i0_e'] + coeffs['i1_e'] * r_i +
coeffs['i2_e'] * r_i * r_i)
z_e = (z0 + coeffs['z0_e'] + coeffs['z1_e'] * i_z +
coeffs['z2_e'] * i_z * i_z)
# validity checks
valid = (g0.between(0.1, 29.9) & r0.between(0.1, 29.9)
& i0.between(0.1, 29.9) & z0.between(0.1, 29.9))
opt_prov = peewee.Case(None, ((valid, 'sdss_fiber2mag_from_ps1dr2'), ),
'undefined')
magnitude_g = peewee.Case(None, ((valid, g_e), ), 'NaN')
magnitude_r = peewee.Case(None, ((valid, r_e), ), 'NaN')
magnitude_i = peewee.Case(None, ((valid, i_e), ), 'NaN')
magnitude_z = peewee.Case(None, ((valid, z_e), ), 'NaN')
# # We want to switch between psfmags and fibertotmags depending on
# # ps.flags EXT+EXT_ALT (i.e. extended sources)
# # For non-extended targets, we use psfmags, but for extended sources use apermag
# flux30 = AB2Jy(30.00)
# ps1_ext_flags = 8388608 + 16777216
# ps1_good_stack_flag = 134217728
# opt_prov = peewee.Case(
# ps.flags.bin_and(ps1_ext_flags),
# ((0, 'ps_psfmag'),),
# 'ps_apermag')
#
# magnitude_g = peewee.Case(
# ps.flags.bin_and(ps1_ext_flags),
# ((0, (8.9 - 2.5 * fn.log10(fn.greatest(flux30, ps.g_stk_psf_flux))).cast('float')),),
# (8.9 - 2.5 * fn.log10(fn.greatest(flux30, ps.g_stk_aper_flux))).cast('float'))
#
# magnitude_r = peewee.Case(
# ps.flags.bin_and(ps1_ext_flags),
# ((0, (8.9 - 2.5 * fn.log10(fn.greatest(flux30, ps.r_stk_psf_flux))).cast('float')),),
# (8.9 - 2.5 * fn.log10(fn.greatest(flux30, ps.r_stk_aper_flux))).cast('float'))
#
# magnitude_i = peewee.Case(
# ps.flags.bin_and(ps1_ext_flags),
# ((0, (8.9 - 2.5 * fn.log10(fn.greatest(flux30, ps.i_stk_psf_flux))).cast('float')),),
# (8.9 - 2.5 * fn.log10(fn.greatest(flux30, ps.i_stk_aper_flux))).cast('float'))
#
# magnitude_z = peewee.Case(
# ps.flags.bin_and(ps1_ext_flags),
# ((0, (8.9 - 2.5 * fn.log10(fn.greatest(flux30, ps.z_stk_psf_flux))).cast('float')),),
# (8.9 - 2.5 * fn.log10(fn.greatest(flux30, ps.z_stk_aper_flux))).cast('float'))
# these control matching to spectroscopy
match_radius_spectro = self.parameters['spec_join_radius'] / 3600.0
spec_sn_thresh = self.parameters['spec_sn_thresh']
spec_z_err_thresh = self.parameters['spec_z_err_thresh']
# this controls use of bad panstarrs photometry
ps1_good_stack_flag = 134217728
query = (
c.select(
c.catalogid.alias('catalogid'),
ps.catid_objid.alias('ps1_catid_objid'), # extra
x.c.ero_detuid.cast('text').alias('ero_detuid'), # extra
c.ra.alias('ra'), # extra
c.dec.alias('dec'), # extra
priority.alias('priority'),
value.cast('float').alias('value'),
cadence.alias('cadence'),
instrument.alias('instrument'),
opt_prov.alias('optical_prov'),
magnitude_g.alias('g'),
magnitude_r.alias('r'),
magnitude_i.alias('i'),
magnitude_z.alias('z'),
(ps.flags.bin_and(ps1_good_stack_flag) >
0).cast('bool').alias('ps1_good_stack_flag'), # extra
inertial.alias('inertial'),
).join(c2ps).join(ps).join(x,
on=(ps.catid_objid == x.c.ps1_dr2_id))
# start joining the spectroscopy
.switch(c).join(c2s16, JOIN.LEFT_OUTER).join(
s16,
JOIN.LEFT_OUTER,
on=((c2s16.target_id == s16.c.specobjid) &
(c2s16.version_id == version_id))).switch(c).join(
c2s2020, JOIN.LEFT_OUTER).join(
s2020,
JOIN.LEFT_OUTER,
on=((c2s2020.target_id == s2020.c.pk) &
(c2s2020.version_id == version_id))).join(
sV,
JOIN.LEFT_OUTER,
on=(fn.q3c_join(
sV.c.plug_ra, sV.c.plug_dec, c.ra,
c.dec, match_radius_spectro))).join(
sph,
JOIN.LEFT_OUTER,
on=(fn.q3c_join(
sph.c.target_ra,
sph.c.target_dec, c.ra, c.dec,
match_radius_spectro)))
# finished joining the spectroscopy
.where(c.version_id == version_id, c2ps.version_id == version_id,
c2ps.best >> True).where(
s16.c.specobjid.is_null(
True), # all of these must be satisfied
s2020.c.pk.is_null(True),
sV.c.specobjid.is_null(True),
sph.c.pkey.is_null(True),
).
where(
(x.c.target_has_spec == 0),
(ps.r_stk_psf_flux < r_psf_flux_max),
(ps.i_stk_psf_flux < i_psf_flux_max),
(ps.z_stk_psf_flux < z_psf_flux_max),
(ps.r_stk_psf_flux !=
'NaN'), # TODO check this is correct test via peewee
(ps.i_stk_psf_flux != 'NaN'),
(ps.z_stk_psf_flux != 'NaN'),
# TODO - check panstarrs photometry quality ??
# (ps.flags.bin_and(ps1_good_stack_flag) > 0),
# TODO gaia safety checks to avoid bad ls photometry???
).order_by(x.c.ps1_dr2_id, x.c.x_rank.asc()).distinct([
x.c.ps1_dr2_id,
]) # avoid duplicate entries
)
if query_region:
query = query.where(
peewee.fn.q3c_radial_query(c.ra, c.dec, query_region[0],
query_region[1], query_region[2]))
return query
def build_query(self, version_id, query_region=None):
self.log.debug(f'Processing file {self._file_path}.')
# We need to copy the data to a temporary table so that we can
# join on it. We could use a Peewee ValueList but for large tables
# that will hit the limit of 1GB in PSQL.
# Create model for temporary table from FITS table columns.
# This works fine because we know there are no arrays.
temp_table = self.name.lower() + '_temp'
temp = create_model_from_table(temp_table, self._table)
temp._meta.database = self.database
temp.create_table(temporary=True)
# Copy data.
copy_data(self._table, self.database, temp_table)
self.database.execute_sql(f'CREATE INDEX ON "{temp_table}" ("Gaia_DR2_Source_ID")')
self.database.execute_sql(f'CREATE INDEX ON "{temp_table}" ("LegacySurvey_DR8_ID")')
self.database.execute_sql(f'CREATE INDEX ON "{temp_table}" ("PanSTARRS_DR2_ID")')
self.database.execute_sql(f'CREATE INDEX ON "{temp_table}" ("TwoMASS_ID")')
vacuum_table(self.database, temp_table, vacuum=False, analyze=True)
inertial_case = peewee.Case(
None,
((temp.inertial.cast('boolean').is_null(), False),),
temp.inertial.cast('boolean'))
query_common = (Catalog
.select(Catalog.catalogid,
temp.Gaia_DR2_Source_ID.alias('gaia_source_id'),
temp.LegacySurvey_DR8_ID.alias('ls_id'),
temp.PanSTARRS_DR2_ID.alias('catid_objid'),
temp.TwoMASS_ID.alias('designation'),
Catalog.ra,
Catalog.dec,
temp.delta_ra.cast('double precision'),
temp.delta_dec.cast('double precision'),
inertial_case.alias('inertial'),
temp.cadence,
temp.priority,
temp.instrument,
peewee.Value(0).alias('value'))
.distinct(Catalog.catalogid))
query_gaia_dr2 = \
(query_common
.join(CatalogToTIC_v8)
.join(TIC_v8, on=(CatalogToTIC_v8.target_id == TIC_v8.id))
.join(Gaia_DR2, on=(TIC_v8.gaia_int == Gaia_DR2.source_id))
.join(temp,
on=(temp.Gaia_DR2_Source_ID == Gaia_DR2.source_id))
.switch(Catalog)
.where(CatalogToTIC_v8.version_id == version_id,
(CatalogToTIC_v8.best >> True) |
CatalogToTIC_v8.best.is_null(),
Catalog.version_id == version_id))
query_legacysurvey_dr8 = \
(query_common
.join(CatalogToLegacy_Survey_DR8)
.join(Legacy_Survey_DR8)
.join(temp,
on=(temp.LegacySurvey_DR8_ID == Legacy_Survey_DR8.ls_id))
.switch(Catalog)
.where(CatalogToLegacy_Survey_DR8.version_id == version_id,
(CatalogToLegacy_Survey_DR8.best >> True) |
CatalogToLegacy_Survey_DR8.best.is_null(),
Catalog.version_id == version_id))
query_panstarrs_dr2 = \
(query_common
.join(CatalogToPanstarrs1)
.join(Panstarrs1)
.join(temp,
on=(temp.PanSTARRS_DR2_ID == Panstarrs1.catid_objid))
.switch(Catalog)
.where(CatalogToPanstarrs1.version_id == version_id,
(CatalogToPanstarrs1.best >> True) |
CatalogToPanstarrs1.best.is_null(),
Catalog.version_id == version_id))
query_twomass_psc = \
(query_common
.join(CatalogToTIC_v8,
on=(Catalog.catalogid == CatalogToTIC_v8.catalogid))
.join(TIC_v8,
on=(CatalogToTIC_v8.target_id == TIC_v8.id))
.join(TwoMassPSC,
on=(TIC_v8.twomass_psc == TwoMassPSC.designation))
.join(temp,
on=(temp.TwoMASS_ID == TwoMassPSC.designation))
.switch(Catalog)
.where(CatalogToTIC_v8.version_id == version_id,
(CatalogToTIC_v8.best >> True) |
CatalogToTIC_v8.best.is_null(),
Catalog.version_id == version_id))
len_table = len(self._table)
len_gaia_dr2 =\
len(self._table[self._table['Gaia_DR2_Source_ID'] > 0])
len_legacysurvey_dr8 =\
len(self._table[self._table['LegacySurvey_DR8_ID'] > 0])
len_panstarrs_dr2 =\
len(self._table[self._table['PanSTARRS_DR2_ID'] > 0])
# TwoMass_ID corresponds to the designation column of
# the table catalogdb.twomass_psc.
# Since the designation column is a text column, below
# we are comparing it to the string 'NA' and not the integer 0.
#
len_twomass_psc =\
len(self._table[self._table['TwoMASS_ID'] != 'NA'])
# There must be exactly one non-zero id per row else raise an exception.
if ((len_gaia_dr2 + len_legacysurvey_dr8 +
len_panstarrs_dr2 + len_twomass_psc) != len_table):
raise TargetSelectionError('error in get_file_carton(): ' +
'(len_gaia_dr2 + len_legacysurvey_dr8 + ' +
'len_panstarrs_dr2 + len_twomass_psc) != ' +
'len_table')
if (len_gaia_dr2 > 0):
is_gaia_dr2 = True
else:
is_gaia_dr2 = False
if (len_legacysurvey_dr8 > 0):
is_legacysurvey_dr8 = True
else:
is_legacysurvey_dr8 = False
if (len_panstarrs_dr2 > 0):
is_panstarrs_dr2 = True
else:
is_panstarrs_dr2 = False
if (len_twomass_psc > 0):
is_twomass_psc = True
else:
is_twomass_psc = False
query = None
if(is_gaia_dr2 is True):
if(query is None):
query = query_gaia_dr2
else:
query = query | query_gaia_dr2
if(is_legacysurvey_dr8 is True):
if(query is None):
query = query_legacysurvey_dr8
else:
query = query | query_legacysurvey_dr8
if(is_panstarrs_dr2 is True):
if(query is None):
query = query_panstarrs_dr2
else:
query = query | query_panstarrs_dr2
if(is_twomass_psc is True):
if(query is None):
query = query_twomass_psc
else:
query = query | query_twomass_psc
if(query is None):
# At least one of the four boolean variables above
# must be True, so we should not get here.
raise TargetSelectionError('error in get_file_carton(): ' +
'(is_gaia_dr2 is False) and ' +
'(is_legacysurvey_dr8 is False) and ' +
'(is_panstarrs_dr2 is False) and ' +
'(is_twomass_psc is False)')
if 'lambda_eff' in self._table.colnames:
query = query.select_extend(temp.lambda_eff.alias('lambda_eff'))
return query
def build_query(self, version_id, query_region=None):
c = Catalog.alias()
c2t = CatalogToBHM_RM_v0.alias()
t = BHM_RM_v0_2.alias()
stw = BHM_RM_Tweaks.alias()
self.alias_c = c
self.alias_t = t
fieldlist = self.get_fieldlist()
tw = (stw.select(
stw.pkey.alias('pkey'),
stw.ra.alias('ra'),
stw.dec.alias('dec'),
stw.rm_suitability.alias('rm_suitability'),
).where((stw.date_set == '30-Nov-2020')
| (stw.date_set == '25-May-2021')))
self.alias_tw = tw
# #########################################################################
# prepare the spectroscopy catalogues
# SDSS-V spAll - select only objects we want to exclude on
# the basis of their pipeline classifications
# Currently this is only for secure STARs in the COSMOS field
ssV = SDSSV_BOSS_SPALL.alias()
sV = (
ssV.select(
ssV.specobjid.alias('specobjid'),
ssV.plug_ra.alias('plug_ra'),
ssV.plug_dec.alias('plug_dec'),
fn.rank().over(partition_by=[ssV.catalogid],
order_by=[ssV.sn_median_all.desc()
]).alias('sn_rank'),
).where(
ssV.programname.contains('RM'),
ssV.firstcarton.contains('bhm_rm_'),
ssV.class_ == 'STAR',
ssV.zwarning == 0,
ssV.sn_median_all > 2.0,
# select only COSMOS plates
ssV.plate << [15038, 15070, 15071, 15252, 15253, 15289
]).alias('sV'))
# SDSS-V plateholes - only consider plateholes that
# were drilled+shipped and that have firstcarton ~ 'bhm_rm_'
ssph = SDSSV_Plateholes.alias()
ssphm = SDSSV_Plateholes_Meta.alias()
sph = (ssph.select(
ssph.pkey.alias('pkey'),
ssph.target_ra.alias('target_ra'),
ssph.target_dec.alias('target_dec'),
).join(ssphm, on=(ssph.yanny_uid == ssphm.yanny_uid)).where(
ssph.holetype == 'BOSS_SHARED',
ssph.sourcetype == 'SCI',
ssph.firstcarton.contains('bhm_rm_'),
ssphm.isvalid > 0,
).distinct([ssph.catalogid]).alias('sph'))
# fold in tiers of magnitude-based priority
priority_mag_step = 0.5
priority_mag_bright = 17.0
priority_mag_faint = 22.0
priority_mag_bright_known_spec = 20.5
priority_floor = self.parameters.get('priority', 10000)
priority1 = peewee.Case(None, (
((t.mi <= priority_mag_bright), priority_floor + 0),
(((self.name == 'bhm_rm_known_spec')
& ~(t.field_name.contains('SDSS-RM')) &
(t.mi <= priority_mag_bright_known_spec)), priority_floor + 0),
((t.mi <= priority_mag_faint), priority_floor + 5 *
(1 + peewee.fn.floor(
(t.mi - priority_mag_bright) / priority_mag_step).cast('int'))
),
((t.mi > priority_mag_faint), priority_floor + 95),
), None)
# # this secondary priority rule is based on whether this target was
# # assigned a platehole during the SDSSV plate programme
# # boost the priorities of those targets that were put onto plates
# priority2 = peewee.Case(
# None,
# (
# (sph.c.pkey.is_null(False), -100),
# (sph.c.pkey.is_null(True), 0),
# ),
# None
# )
# this secondary priority rule boosts the priority of targets that
# have rm_suitability >= 1 in the bhm_rm_tweaks table
priority2 = peewee.Case(None, ((tw.c.rm_suitability >= 1, -100), ), 0)
# combine the two priorities
priority = priority1 + priority2
# this just checks if this target was
# assigned a platehole during the SDSSV plate programme
# for information only - no action taken
in_SDSSV_plates = peewee.Case(None,
((sph.c.pkey.is_null(False), True), ),
False).cast('bool')
value = peewee.Value(self.parameters.get('value', 1.0)).cast('float')
instrument = peewee.Value(self.instrument)
inertial = peewee.Value(self.inertial).cast('bool')
match_radius_spectro = 1.0 / 3600.0
# This is the scheme used in v0
cadence_v0 = peewee.Case(
None, ((t.field_name.contains('S-CVZ'), 'bhm_rm_lite5_100x8'), ),
'bhm_rm_174x8')
# this gives the new names for the same cadences assumed in v0
cadence_v0p5 = peewee.Case(
None, ((t.field_name.contains('S-CVZ'), 'dark_100x8'), ),
'dark_174x8')
# the following will replace old generic cadences when relevant table has been populated
# TODO - replace when correct cadences are loaded
cadence_v1p0 = peewee.Case(None, (
(t.field_name.contains('SDSS-RM'), 'bhm_rm_sdss-rm'),
(t.field_name.contains('COSMOS'), 'bhm_rm_cosmos'),
(t.field_name.contains('XMM-LSS'), 'bhm_rm_xmm-lss'),
(t.field_name.contains('S-CVZ'), 'bhm_rm_cvz-s'),
(t.field_name.contains('CDFS'), 'bhm_rm_cdfs'),
(t.field_name.contains('ELIAS-S1'), 'bhm_rm_elias-s1'),
), 'dark_174x8')
# Photometric precedence: DES>PS1>SDSS(>Gaia)>NSC.
opt_prov = peewee.Case(None, (
(t.sdss == 1, 'sdss_psfmag'),
(t.des == 1, 'psfmag'),
(t.ps1 == 1, 'ps_psfmag'),
(t.optical_survey == 'Gaia', 'other'),
(t.nsc == 1, 'psfmag'),
), 'other')
magnitude_g = peewee.Case(
None,
(
((t.sdss == 1) & (t.psfmag_sdss[1] > 0.0), t.psfmag_sdss[1]),
((t.des == 1) & (t.psfmag_des[0] > 0.0), t.psfmag_des[0]),
((t.ps1 == 1) & (t.psfmag_ps1[0] > 0.0), t.psfmag_ps1[0]),
((t.optical_survey == 'Gaia') & (t.mag_gaia[0] > 0.0),
t.mag_gaia[0]), # just using gaia G for now
((t.nsc == 1) & (t.mag_nsc[0] > 0.0), t.mag_nsc[0]),
),
99.9) # should never get here
magnitude_r = peewee.Case(None, (
((t.sdss == 1) & (t.psfmag_sdss[2] > 0.0), t.psfmag_sdss[2]),
((t.des == 1) & (t.psfmag_des[1] > 0.0), t.psfmag_des[1]),
((t.ps1 == 1) & (t.psfmag_ps1[1] > 0.0), t.psfmag_ps1[1]),
((t.nsc == 1) & (t.mag_nsc[1] > 0.0), t.mag_nsc[1]),
), 99.9) # should never get here
magnitude_i = peewee.Case(
None,
(
((t.sdss == 1) & (t.psfmag_sdss[3] > 0.0), t.psfmag_sdss[3]),
((t.des == 1) & (t.psfmag_des[2] > 0.0), t.psfmag_des[2]),
((t.ps1 == 1) & (t.psfmag_ps1[2] > 0.0), t.psfmag_ps1[2]),
((t.nsc == 1) & (t.mag_nsc[2] > 0.0), t.mag_nsc[2]),
(t.mi > 0.0, t.mi),
((t.optical_survey == 'Gaia') & (t.mag_gaia[2] > 0.0),
t.mag_gaia[2]), # just using gaia RP for now
),
99.9) # should never get here
magnitude_z = peewee.Case(None, (
((t.sdss == 1) & (t.psfmag_sdss[4] > 0.0), t.psfmag_sdss[4]),
((t.des == 1) & (t.psfmag_des[3] > 0.0), t.psfmag_des[3]),
((t.ps1 == 1) & (t.psfmag_ps1[3] > 0.0), t.psfmag_ps1[3]),
((t.nsc == 1) & (t.mag_nsc[3] > 0.0), t.mag_nsc[3]),
), 99.9) # should never get here
query = (
c.select(
c.catalogid,
c.ra, # extra
c.dec, # extra
t.field_name.alias('rm_field_name'), # extra
t.pk.alias('rm_pk'), # extra
instrument.alias('instrument'),
priority.alias('priority'),
priority1.alias('priority1'),
priority2.alias('priority2'),
value.alias('value'),
cadence_v0p5.alias('cadence'),
cadence_v0.alias('cadence_v0'), # extra
cadence_v0p5.alias('cadence_v0p5'), # extra
cadence_v1p0.alias('cadence_v1p0'), # extra
magnitude_g.alias('g'),
magnitude_r.alias('r'),
magnitude_i.alias('i'),
magnitude_z.alias('z'),
opt_prov.alias('optical_prov'),
inertial.alias('inertial'),
t.optical_survey.alias('optical_survey'), # extra
c2t.best.alias("c2t_best"), # extra
in_SDSSV_plates.alias('in_SDSSV_plates'), # extra
tw.c.rm_suitability.cast('int').alias(
'rm_suitability'), # extra
).join(c2t)
# An explicit join is needed because we are using c2t for Catalog_to_BHM_RM_v0
# rather than a native c2t for Catalog_to_BHM_RM_v0_2
.join(t, on=(c2t.target_id == t.pk)).where(
c.version_id == version_id,
c2t.version_id == version_id,
# c2t.best >> True # TODO check if this is dropping RM targets
# # like it does for AQMES
).where(((t.mi >= self.parameters['mag_i_min'])
& (t.mi < self.parameters['mag_i_max'])) | (
# S-CVZ targets often have only Gaia photom
(t.field_name.contains('S-CVZ'))
& (t.mg >= self.parameters['mag_g_min_cvz_s'])
& (t.mg < self.parameters['mag_g_max_cvz_s']))).
switch(c).join(
tw,
JOIN.LEFT_OUTER,
on=(fn.q3c_join(
tw.c.ra, tw.c.dec, c.ra, c.dec,
match_radius_spectro))).join(
sV,
JOIN.LEFT_OUTER,
on=(
fn.q3c_join(sV.c.plug_ra, sV.c.plug_dec, c.ra,
c.dec, match_radius_spectro) &
(sV.c.sn_rank == 1
) # only consider the best spectrum per object
)).join(sph,
JOIN.LEFT_OUTER,
on=(fn.q3c_join(sph.c.target_ra,
sph.c.target_dec, c.ra, c.dec,
match_radius_spectro))).
where(
# Reject any objects where the highest SNR spectrum for
# this target in sdssv_boss_spall is classified as STAR
sV.c.specobjid.is_null(True),
#
# Reject any targets that are flagged as being unsuitable for RM in bhm_rm_tweaks
# bhm_rm_tweaks.rm_suitability==0 means:
# 'target is probably unsuitable for RM, do not observe in the future'
(tw.c.pkey.is_null(True) |
(tw.c.rm_suitability != 0))).distinct(
[t.pk]) # avoid duplicates - trust the RM parent sample
# - only needed if NOT using c2t.best = True condition
)
query = self.append_spatial_query(query, fieldlist)
return query
def build_query(self, version_id, query_region=None):
c = Catalog.alias()
c2s = CatalogToSDSS_DR16_SpecObj.alias()
s = SDSS_DR16_SpecObj.alias()
t = SDSS_DR16_QSO.alias()
self.alias_c = c
self.alias_t = t
self.alias_c2s = c2s
# SDSS-V plateholes - only consider plateholes that
# were drilled+shipped and that have firstcarton ~ 'bhm_aqmes_'
ssph = SDSSV_Plateholes.alias()
ssphm = SDSSV_Plateholes_Meta.alias()
sph = (
ssph.select(
ssph.pkey.alias('pkey'),
ssph.target_ra.alias('target_ra'),
ssph.target_dec.alias('target_dec'),
)
.join(
ssphm,
on=(ssph.yanny_uid == ssphm.yanny_uid)
)
.where(
ssph.holetype == 'BOSS_SHARED',
ssph.sourcetype == 'SCI',
ssph.firstcarton.contains('bhm_aqmes_'),
ssphm.isvalid > 0,
)
.distinct([ssph.catalogid])
.alias('sph')
)
# set the Carton priority+values here - read from yaml
priority_floor = peewee.Value(int(self.parameters.get('priority', 999999)))
value = peewee.Value(self.parameters.get('value', 1.0)).cast('float')
instrument = peewee.Value(self.instrument)
inertial = peewee.Value(self.inertial).cast('bool')
opt_prov = peewee.Value('sdss_psfmag')
cadence_v0 = peewee.Value(cadence_map_v0p5_to_v0[self.cadence_v0p5]).cast('text')
# cadence = peewee.Value(cadence_v0)
cadence = peewee.Value(self.cadence_v0p5).cast('text')
# # this is DEBUG until the new v0.5 cadences exist in the DB
# # - doesn't work because self.cadence is checked before this point
# # - so give up until targetdb.cadence is populated
# assert self.cadence in cadence_map_v0p5_to_v0
# v0_cadence = cadence_map_v0p5_to_v0[self.cadence]
# cadence = peewee.Value(v0_cadence).alias('cadence')
match_radius_spectro = 1.0 / 3600.0
priority_boost = peewee.Case(
None,
(
(sph.c.pkey.is_null(False), 0), # has a platehole entry
(sph.c.pkey.is_null(True), 1), # not in plate programme
),
None
)
priority = priority_floor + priority_boost
magnitude_sdss_g = peewee.Case(
None, ((t.psfmag[1].between(0.1, 29.9), t.psfmag[1]),), 'NaN').cast('float')
magnitude_sdss_r = peewee.Case(
None, ((t.psfmag[2].between(0.1, 29.9), t.psfmag[2]),), 'NaN').cast('float')
magnitude_sdss_i = peewee.Case(
None, ((t.psfmag[3].between(0.1, 29.9), t.psfmag[3]),), 'NaN').cast('float')
magnitude_sdss_z = peewee.Case(
None, ((t.psfmag[4].between(0.1, 29.9), t.psfmag[4]),), 'NaN').cast('float')
magnitude_gaia_g = peewee.Case(
None, ((t.gaia_g_mag.between(0.1, 29.9), t.gaia_g_mag),), 'NaN').cast('float')
magnitude_gaia_bp = peewee.Case(
None, ((t.gaia_bp_mag.between(0.1, 29.9), t.gaia_bp_mag),), 'NaN').cast('float')
magnitude_gaia_rp = peewee.Case(
None, ((t.gaia_rp_mag.between(0.1, 29.9), t.gaia_rp_mag),), 'NaN').cast('float')
bquery = (
c.select(
c.catalogid,
t.pk.alias('dr16q_pk'), # extra
s.specobjid.cast('text').alias('dr16_specobjid'), # extra
c.ra, # extra
c.dec, # extra
priority.alias('priority'),
value.alias('value'),
inertial.alias('inertial'),
instrument.alias('instrument'),
cadence.alias('cadence'),
cadence_v0.alias('cadence_v0'),
opt_prov.alias('optical_prov'),
magnitude_sdss_g.alias('g'),
magnitude_sdss_r.alias('r'),
magnitude_sdss_i.alias('i'),
magnitude_sdss_z.alias('z'),
magnitude_gaia_g.alias('gaia_g'),
magnitude_gaia_bp.alias('bp'),
magnitude_gaia_rp.alias('rp'),
t.plate.alias('dr16q_plate'), # extra
t.mjd.alias('dr16q_mjd'), # extra
t.fiberid.alias('dr16q_fiberid'), # extra
t.ra.alias("dr16q_ra"), # extra
t.dec.alias("dr16q_dec"), # extra
t.gaia_ra.alias("dr16q_gaia_ra"), # extra
t.gaia_dec.alias("dr16q_gaia_dec"), # extra
t.sdss2gaia_sep.alias("dr16q_sdss2gaia_sep"), # extra
t.z.alias("dr16q_redshift"), # extra
c2s.best.alias("c2s_best"), # extra
)
.join(c2s)
.join(s)
.join(
t,
on=((s.plate == t.plate) &
(s.mjd == t.mjd) &
(s.fiberid == t.fiberid))
)
.join(
sph, JOIN.LEFT_OUTER,
on=(
fn.q3c_join(sph.c.target_ra, sph.c.target_dec,
c.ra, c.dec,
match_radius_spectro)
)
)
.where(
c.version_id == version_id,
c2s.version_id == version_id,
# c2s.best >> True, # TODO check this is working in v0.5
# # - this condition killed many AQMES
# # targets in v0 cross-match
)
.where
(
t.psfmag[3] >= self.parameters['mag_i_min'],
t.psfmag[3] < self.parameters['mag_i_max'],
# (t.z >= self.parameters['redshift_min']), # not needed
# (t.z <= self.parameters['redshift_max']),
)
# .distinct([t.pk]) # avoid duplicates - trust the QSO parent sample
.distinct([c.catalogid]) # avoid duplicates - trust the catalog
.cte('bquery', materialized=True)
)
query = bquery.select(peewee.SQL('bquery.*'))
query = self.append_spatial_query(query, bquery, self.get_fieldlist())
query = query.with_cte(bquery)
return query
def build_query(self, version_id, query_region=None):
# Do a quick check to be sure the GG carton exists in targetdb.
gg_exists = (targetdb.Carton.select().join(targetdb.Version).where(
targetdb.Carton.carton == 'mwm_galactic_core',
targetdb.Version.plan == self.plan,
targetdb.Version.target_selection >> True).exists())
if not gg_exists:
raise RuntimeError('mwm_galactic has not been loaded yet.')
fn = peewee.fn
# GG quality flags
ph_qual = TwoMassPSC.ph_qual
cc_flg = TwoMassPSC.cc_flg
rd_flg = TwoMassPSC.rd_flg
rd_flag_1 = peewee.fn.substr(rd_flg, 2, 1).cast('integer')
gal_contam = TwoMassPSC.gal_contam
gallong = TIC_v8.gallong
gallat = TIC_v8.gallat
ipar = 1. / TIC_v8.plx # kpc
zz = ipar * fn.sin(fn.radians(gallat))
xx = ipar * fn.cos(fn.radians(gallat)) * fn.cos(fn.radians(gallong))
yy = ipar * fn.cos(fn.radians(gallat)) * fn.sin(fn.radians(gallong))
dist = fn.sqrt(fn.pow(xx, 2) + fn.pow(yy, 2) + fn.pow(zz, 2))
aks_glimpse = 0.918 * (GLIMPSE.mag_h - GLIMPSE.mag4_5 - 0.08)
aks_allwise = 0.918 * (AllWise.h_m_2mass - AllWise.w2mpro - 0.08)
aks = fn.coalesce(aks_glimpse, aks_allwise)
Ej_ks = 1.5 * aks
j_ks_0_glimpse = GLIMPSE.mag_j - GLIMPSE.mag_ks - Ej_ks
j_ks_0_allwise = AllWise.j_m_2mass - AllWise.k_m_2mass - Ej_ks
j_ks_0 = fn.coalesce(j_ks_0_glimpse, j_ks_0_allwise)
plxfracunc = TIC_v8.e_plx / TIC_v8.plx
dm = 5 * fn.log(1000. / TIC_v8.plx / 10)
absmag = TIC_v8.kmag - aks - dm
query = (
CatalogToTIC_v8.select(
CatalogToTIC_v8.catalogid,
peewee.Value(False).alias('selected'), # Set selected to False
TIC_v8.gaia_int.alias('gaia_souce_id'),
TIC_v8.gallong,
TIC_v8.gallat,
TIC_v8.plx,
TIC_v8.gaiamag,
TIC_v8.hmag,
TIC_v8.kmag,
aks.alias('a_ks'),
j_ks_0.alias('j_ks_0')).join(TIC_v8).join_from(
CatalogToTIC_v8,
CatalogToAllWise,
peewee.JOIN.LEFT_OUTER,
on=(CatalogToAllWise.catalogid == CatalogToTIC_v8.catalogid
)).join(AllWise, peewee.JOIN.LEFT_OUTER).join_from(
TIC_v8, TwoMassPSC,
peewee.JOIN.LEFT_OUTER).join_from(
CatalogToAllWise,
CatalogToGLIMPSE,
peewee.JOIN.LEFT_OUTER,
on=(CatalogToGLIMPSE.catalogid ==
CatalogToAllWise.catalogid)).join(
GLIMPSE, peewee.JOIN.LEFT_OUTER).
where(((CatalogToAllWise.version_id == version_id)
& (CatalogToAllWise.best >> True))
| (CatalogToAllWise.catalogid >> None)).where(
CatalogToTIC_v8.version_id == version_id,
CatalogToTIC_v8.best >> True).
where(((CatalogToGLIMPSE.version_id == version_id)
& (CatalogToGLIMPSE.best >> True))
| (CatalogToGLIMPSE.catalogid >> None)).where(
TIC_v8.hmag < 11.2,
fn.abs(zz) < 0.2, j_ks_0.is_null(False), j_ks_0 > 0.5,
dist < 5, plxfracunc < 0.2, plxfracunc > 0,
absmag < 2.6).where(
ph_qual.regexp('.(A|B).'), gal_contam == 0,
peewee.fn.substr(cc_flg, 2, 1) == '0', rd_flag_1 > 0,
rd_flag_1 <= 3))
if query_region:
query = (query.join_from(CatalogToAllWise, Catalog).where(
peewee.fn.q3c_radial_query(Catalog.ra, Catalog.dec,
query_region[0], query_region[1],
query_region[2])))
return query
def build_query(self, version_id, query_region=None):
ps = Panstarrs1.alias()
c2ps = CatalogToPanstarrs1.alias()
tic = TIC_v8.alias()
c2tic = CatalogToTIC_v8.alias()
# an alias to simplify accessing the query parameters:
pars = self.parameters
# transform the panstarrs1-dr2 griz into sdss psfmag griz
# use transforms decribed here:
# https://wiki.sdss.org/display/OPS/All-sky+BOSS+standards#All-skyBOSSstandards-TransformingphotometryofeBOSS-likestandardsintoSDSSsystem # noqa
# extract coeffs from fit logs via:
# awk 'BEGIN {print("coeffs = {")} /POLYFIT/{ pe=""; printf("\"%s%d_%s\": %s,\n", substr($3,length($3)), $8, pe, $10)} END {print("}")}' ops_std_eboss/ps1dr2_chp_psf_to_sdss_psfmag_?_results.log # noqa
coeffs = {
"g2": 0.115563,
"g1": 0.068765,
"g0": 0.012047,
"i2": -0.385214,
"i1": 0.149677,
"i0": -0.026127,
"r2": -0.070151,
"r1": 0.070129,
"r0": -0.014197,
"z2": -2.141255,
"z1": 0.147746,
"z0": -0.034845,
}
# start from ps1dr2 chp psf mags
g_r = ps.g_chp_psf - ps.r_chp_psf
r_i = ps.r_chp_psf - ps.i_chp_psf
i_z = ps.i_chp_psf - ps.z_chp_psf
# compute apparent sdss psfmags
g = (ps.g_chp_psf + coeffs['g0'] + coeffs['g1'] * g_r + coeffs['g2'] * g_r * g_r)
r = (ps.r_chp_psf + coeffs['r0'] + coeffs['r1'] * g_r + coeffs['r2'] * g_r * g_r)
i = (ps.i_chp_psf + coeffs['i0'] + coeffs['i1'] * r_i + coeffs['i2'] * r_i * r_i)
z = (ps.z_chp_psf + coeffs['z0'] + coeffs['z1'] * i_z + coeffs['z2'] * i_z * i_z)
# dereddining steps
# extinction terms for Panstarrs
# Use R_b from Schlafly & Finkbeiner 2011, Table 6
# https://ui.adsabs.harvard.edu/abs/2011ApJ...737..103S/abstract
# assume R_V = 3.1
# R_b = A_V / E(B-V)
# for Panstarrs1 grizy bands we have:
R_g = 3.172
R_r = 2.271
R_i = 1.682
R_z = 1.322
# R_y = 1.087
E_g_r = R_g - R_r
E_r_i = R_r - R_i
E_i_z = R_i - R_z
# extinction terms for Gaia
# Use table 3 from Wang & Chen 2019
# https://ui.adsabs.harvard.edu/abs/2019ApJ...877..116W/abstract
# R_G = 1.890
# R_BP = 2.429
# R_RP = 1.429
# These R_b are expressed with E(BP-RP) as the denominator - so need to convert to
# using E(B-V) as denominator instead
# an email from Keivan Stassun helps with this:
# Hi Jennifer, we had to work
# these out for the TIC paper because we used Gaia G mags and
# Bp-Rp colors for everything... you can see the details in
# Section 2.3.3 of Stassun et al (2019), but here's the final
# relations we adopted:
# E(${G}_{\mathrm{BP}}$ − ${G}_{\mathrm{RP}}$) = 1.31 E(B − V)
# AG = 2.72 E(B − V)
E_bp_rp = 1.31
R_gaia_g = 1.890 * E_bp_rp
R_gaia_bp = 2.429 * E_bp_rp
# R_gaia_rp = 1.429 * 1.31
E_bp_g = R_gaia_bp - R_gaia_g # = 0.7061
# use ebv from tic_v8 match
g_r_dered = g_r - tic.ebv * E_g_r
r_i_dered = r_i - tic.ebv * E_r_i
i_z_dered = i_z - tic.ebv * E_i_z
bp_rp_dered = tic.gaiabp - tic.gaiarp - tic.ebv * E_bp_rp
bp_g_dered = tic.gaiabp - tic.gaiamag - tic.ebv * E_bp_g
g_r_dered_nominal = pars['g_r_dered_nominal']
r_i_dered_nominal = pars['r_i_dered_nominal']
i_z_dered_nominal = pars['i_z_dered_nominal']
bp_rp_dered_nominal = pars['bp_rp_dered_nominal']
bp_g_dered_nominal = pars['bp_g_dered_nominal']
dered_dist2 = (
(g_r_dered - g_r_dered_nominal) * (g_r_dered - g_r_dered_nominal) +
(r_i_dered - r_i_dered_nominal) * (r_i_dered - r_i_dered_nominal) +
(i_z_dered - i_z_dered_nominal) * (i_z_dered - i_z_dered_nominal) +
(bp_rp_dered - bp_rp_dered_nominal) * (bp_rp_dered - bp_rp_dered_nominal) +
(bp_g_dered - bp_g_dered_nominal) * (bp_g_dered - bp_g_dered_nominal)
)
optical_prov = peewee.Value('sdss_psfmag_from_ps1dr2')
ext_flags = 8388608 + 16777216
dered_dist_max2 = pars['dered_dist_max'] * pars['dered_dist_max']
# the following are just to bracket the result to make the query run faster
r_stk_psf_flux_min = AB2Jy(pars['mag_ps_r_max'] + 0.2)
r_stk_psf_flux_max = AB2Jy(pars['mag_ps_r_min'] - 0.2)
query = (
Catalog
.select(
Catalog.catalogid,
Catalog.ra,
Catalog.dec,
ps.catid_objid.alias('ps1_catid_objid'),
tic.gaia_int.alias('gaia_source'),
ps.g_chp_psf.alias("ps1dr2_chp_psfmag_g"),
ps.r_chp_psf.alias("ps1dr2_chp_psfmag_r"),
ps.i_chp_psf.alias("ps1dr2_chp_psfmag_i"),
ps.z_chp_psf.alias("ps1dr2_chp_psfmag_z"),
g_r.alias("ps1dr2_chp_psfmag_g_r"),
r_i.alias("ps1dr2_chp_psfmag_r_i"),
i_z.alias("ps1dr2_chp_psfmag_i_z"),
tic.ebv.alias("tic_ebv"),
g_r_dered.alias("ps1dr2_chp_psfmag_g_r_dered"),
r_i_dered.alias("ps1dr2_chp_psfmag_r_i_dered"),
i_z_dered.alias("ps1dr2_chp_psfmag_i_z_dered"),
bp_rp_dered.alias("gdr2_mag_dered_bp_rp"),
bp_g_dered.alias("gdr2_mag_dered_bp_g"),
dered_dist2.alias("dered_dist2"),
optical_prov.alias('optical_prov'),
g.alias("g"),
r.alias("r"),
i.alias("i"),
z.alias("z"),
tic.gaiamag.alias("gaia_g"),
tic.gaiabp.alias("bp"),
tic.gaiarp.alias("rp"),
tic.jmag.alias("j"),
tic.hmag.alias("h"),
tic.kmag.alias("k"),
tic.plx.alias('parallax'),
tic.e_plx.alias('parallax_error'),
)
.join(c2ps,
on=(Catalog.catalogid == c2ps.catalogid))
.join(ps,
on=(c2ps.target_id == ps.catid_objid))
.join(c2tic,
on=(Catalog.catalogid == c2tic.catalogid))
.join(tic,
on=(c2tic.target_id == tic.id))
.where(
c2ps.version_id == version_id,
c2tic.version_id == version_id,
c2ps.best >> True,
c2tic.best >> True,
ps.flags.bin_and(ext_flags) == 0,
tic.plx < pars['parallax_max'],
tic.plx > (
pars['parallax_min_at_g16'] +
(tic.gaiamag - 16.0) * pars['parallax_min_slope']
),
dered_dist2 < dered_dist_max2,
ps.r_chp_psf.between(pars['mag_ps_r_min'], pars['mag_ps_r_max']),
# the following are just to bracket the result to make the query run faster
tic.gaiamag.between(pars['mag_gaia_g_min'], pars['mag_gaia_g_max']),
ps.r_stk_psf_flux.between(r_stk_psf_flux_min, r_stk_psf_flux_max),
)
)
# Below ra, dec and radius are in degrees
# query_region[0] is ra of center of the region
# query_region[1] is dec of center of the region
# query_region[2] is radius of the region
if query_region:
query = (
query.where(
peewee.fn.q3c_radial_query(Catalog.ra,
Catalog.dec,
query_region[0],
query_region[1],
query_region[2]),
)
)
return query
def build_query(self, version_id, query_region=None):
ls = Legacy_Survey_DR8.alias()
c2ls = CatalogToLegacy_Survey_DR8.alias()
# an alias to simplify accessing the query parameters:
pars = self.parameters
# safety catch to avoid log-of-zero and divide by zero errors.
# => use a flux in nano-maggies below which we give up
nMgy_min = 1e-3 # equiv to AB=30
# Below line is used to avoid divide by zero or log of zero,
# peewee.fn.greatest(nMgy_min, Legacy_Survey_DR8.flux_g)
# Below peewee.fn.log is log to the base 10.
# peewee.fn.log(peewee.fn.greatest(nMgy_min, Legacy_Survey_DR8.flux_r))
# transform the legacysurvey grz into sdss psfmag griz
# use transforms decribed here:
# https://wiki.sdss.org/display/OPS/All-sky+BOSS+standards#All-skyBOSSstandards-TransformingphotometryofeBOSS-likestandardsintoSDSSsystem # noqa
# extract coeffs from fit logs via:
# gawk 'BEGIN {print("coeffs = {")} /POLYFIT/{printf("\"%s%d\": %s,\n", substr($3,length($3)), $8, $10)} END {print("}")}' ops_std_eboss/lsdr8_mag_to_sdss_psfmag_*.log # noqa
coeffs = {
"g2": 0.193896,
"g1": -0.051181,
"g0": 0.032614,
"i2": 0.044794,
"i1": -0.513119,
"i0": -0.021466,
"r2": -0.034595,
"r1": 0.132328,
"r0": 0.011408,
"z2": -0.381446,
"z1": 0.135980,
"z0": -0.020589,
}
g0 = (22.5 - 2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.flux_g)))
r0 = (22.5 - 2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.flux_r)))
z0 = (22.5 - 2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.flux_z)))
g_r = (-2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.flux_g) /
peewee.fn.greatest(nMgy_min, ls.flux_r)))
r_z = (-2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.flux_r) /
peewee.fn.greatest(nMgy_min, ls.flux_z)))
g = (g0 + coeffs['g0'] + coeffs['g1'] * g_r + coeffs['g2'] * g_r * g_r)
r = (r0 + coeffs['r0'] + coeffs['r1'] * g_r + coeffs['r2'] * g_r * g_r)
i = (r0 + coeffs['i0'] + coeffs['i1'] * r_z + coeffs['i2'] * r_z * r_z)
z = (z0 + coeffs['z0'] + coeffs['z1'] * r_z + coeffs['z2'] * r_z * r_z)
g0_dered = (22.5 - 2.5 * peewee.fn.log(
peewee.fn.greatest(nMgy_min, ls.flux_g / ls.mw_transmission_g)))
r0_dered = (22.5 - 2.5 * peewee.fn.log(
peewee.fn.greatest(nMgy_min, ls.flux_r / ls.mw_transmission_r)))
z0_dered = (22.5 - 2.5 * peewee.fn.log(
peewee.fn.greatest(nMgy_min, ls.flux_z / ls.mw_transmission_z)))
g_r_dered = (-2.5 * peewee.fn.log(
peewee.fn.greatest(nMgy_min, ls.flux_g / ls.mw_transmission_g) /
peewee.fn.greatest(nMgy_min, ls.flux_r / ls.mw_transmission_r)))
r_z_dered = (-2.5 * peewee.fn.log(
peewee.fn.greatest(nMgy_min, ls.flux_r / ls.mw_transmission_r) /
peewee.fn.greatest(nMgy_min, ls.flux_z / ls.mw_transmission_z)))
# lsdr8 quotes ebv so we can go straight to E(G_BP-G_RP) and E(G_BP-G) directly
# using the Stassun et al relation:
# E(BP-RP) = 1.31 * E(B-V)
# NO! # but we first need to recalibrate the SFD E(B-V) following Schlafly&Finkbeiner2011
# NO! # such that SF11 E(B-V) = SFD EB(-V) * 0.884
# NO! E_b_v_corr = 0.884
# NO! E_bp_rp = 1.31 * E_b_v_corr
E_bp_rp = 1.31
R_gaia_g = 1.890 * E_bp_rp
R_gaia_bp = 2.429 * E_bp_rp
E_bp_g = R_gaia_bp - R_gaia_g # = 0.7061
bp_rp_dered = (ls.gaia_phot_bp_mean_mag - ls.gaia_phot_rp_mean_mag
- ls.ebv * E_bp_rp)
bp_g_dered = (ls.gaia_phot_bp_mean_mag - ls.gaia_phot_g_mean_mag
- ls.ebv * E_bp_g)
# bp_rp_dered = (
# (
# ls.gaia_phot_bp_mean_mag
# + 2.5 * peewee.fn.log(ls.mw_transmission_g)
# ) - (
# ls.gaia_phot_rp_mean_mag
# + 2.5 * peewee.fn.log(0.5 * (ls.mw_transmission_r + ls.mw_transmission_z))
# )
# )
# bp_g_dered = (
# (
# ls.gaia_phot_bp_mean_mag
# + 2.5 * peewee.fn.log(ls.mw_transmission_g)
# ) - (
# ls.gaia_phot_g_mean_mag
# + 2.5 * peewee.fn.log(ls.mw_transmission_r)
# )
# )
g_r_dered_nominal = pars['g_r_dered_nominal']
r_z_dered_nominal = pars['r_z_dered_nominal']
bp_rp_dered_nominal = pars['bp_rp_dered_nominal']
bp_g_dered_nominal = pars['bp_g_dered_nominal']
dered_dist2 = (
(g_r_dered - g_r_dered_nominal) * (g_r_dered - g_r_dered_nominal) +
(r_z_dered - r_z_dered_nominal) * (r_z_dered - r_z_dered_nominal) +
(bp_rp_dered - bp_rp_dered_nominal) * (bp_rp_dered - bp_rp_dered_nominal) +
(bp_g_dered - bp_g_dered_nominal) * (bp_g_dered - bp_g_dered_nominal)
)
dered_dist_max2 = pars['dered_dist_max'] * pars['dered_dist_max']
optical_prov = peewee.Value('sdss_psfmag_from_lsdr8')
query = (
Catalog
.select(
Catalog.catalogid,
Catalog.ra,
Catalog.dec,
ls.ls_id,
# ls.flux_g,
# ls.flux_r,
# ls.flux_z,
# ls.flux_w1,
# ls.flux_ivar_g,
# ls.flux_ivar_r,
# ls.flux_ivar_z,
# ls.flux_ivar_w1,
g0.alias("ls8_mag_g"),
r0.alias("ls8_mag_r"),
z0.alias("ls8_mag_z"),
g_r.alias("ls8_mag_g_r"),
r_z.alias("ls8_mag_r_z"),
g0_dered.alias("ls8_mag_dered_g"),
r0_dered.alias("ls8_mag_dered_r"),
z0_dered.alias("ls8_mag_dered_z"),
g_r_dered.alias("ls8_mag_dered_g_r"),
r_z_dered.alias("ls8_mag_dered_r_z"),
bp_rp_dered.alias("gdr2_mag_dered_bp_rp"),
bp_g_dered.alias("gdr2_mag_dered_bp_g"),
dered_dist2.alias("dered_dist2"),
optical_prov.alias('optical_prov'),
g.alias("g"),
r.alias("r"),
i.alias("i"),
z.alias("z"),
ls.gaia_phot_g_mean_mag.alias("gaia_g"),
ls.gaia_phot_bp_mean_mag.alias("bp"),
ls.gaia_phot_rp_mean_mag.alias("rp"),
ls.ebv.alias("ls8_ebv"),
ls.mw_transmission_g.alias("ls8_mw_transmission_g"),
ls.mw_transmission_r.alias("ls8_mw_transmission_r"),
ls.mw_transmission_z.alias("ls8_mw_transmission_z"),
ls.parallax,
ls.parallax_ivar,
ls.nobs_g.alias("ls8_nobs_g"),
ls.nobs_r.alias("ls8_nobs_r"),
ls.nobs_z.alias("ls8_nobs_z"),
# ls.maskbits,
)
.join(c2ls,
on=(Catalog.catalogid == c2ls.catalogid))
.join(ls,
on=(c2ls.target_id == ls.ls_id))
.where(
c2ls.version_id == version_id,
c2ls.best >> True,
ls.type == 'PSF',
ls.ref_cat == 'G2',
ls.gaia_phot_g_mean_mag > pars['mag_gaia_g_min'],
ls.parallax < pars['parallax_max'],
ls.parallax > (
pars['parallax_min_at_g16'] +
(ls.gaia_phot_g_mean_mag - 16.0) * pars['parallax_min_slope']
),
ls.gaia_duplicated_source >> False,
ls.nobs_g >= 1, # TODO increase to >= 2 in lsdr9
ls.nobs_r >= 1, # TODO increase to >= 2 in lsdr9
ls.nobs_z >= 1, # TODO increase to >= 2 in lsdr9
ls.flux_g > nMgy_min,
ls.flux_r > nMgy_min,
ls.flux_z > nMgy_min,
ls.maskbits == 0,
dered_dist2 < dered_dist_max2,
r0.between(pars['mag_ls_r_min'], pars['mag_ls_r_max']),
#
# g_r.between(pars['ls_g_r_min'], pars['ls_g_r_max']),
# r_z.between(pars['ls_r_z_min'], pars['ls_r_z_max']),
# (ls.gaia_phot_bp_mean_mag - ls.gaia_phot_rp_mean_mag)
# .between(pars['gaia_bp_rp_min'], pars['gaia_bp_rp_max']),
# (ls.gaia_phot_g_mean_mag - r0)
# .between(pars['gaia_g_ls_r_min'], pars['gaia_g_ls_r_max'])
# # (22.5 -
# # 2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.flux_r))) # noqa: E501
# # .between(pars['mag_ls_r_min'], pars['mag_ls_r_max']),
# # (-2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.flux_g) / # noqa: E501
# # peewee.fn.greatest(nMgy_min, ls.flux_r))) # noqa: E501
# # .between(pars['ls_g_r_min'], pars['ls_g_r_max']),
# # (-2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.flux_r) / # noqa: E501
# # peewee.fn.greatest(nMgy_min, ls.flux_z))) # noqa: E501
# # .between(pars['ls_r_z_min'], pars['ls_r_z_max']),
# # (ls.gaia_phot_g_mean_mag -
# # (22.5 -
# # 2.5 * peewee.fn.log(peewee.fn.greatest(nMgy_min, ls.flux_r)))) # noqa: E501
# # .between(pars['gaia_g_ls_r_min'], pars['gaia_g_ls_r_max'])
)
)
# Below ra, dec and radius are in degrees
# query_region[0] is ra of center of the region
# query_region[1] is dec of center of the region
# query_region[2] is radius of the region
if query_region:
query = (
query.where(
peewee.fn.q3c_radial_query(Catalog.ra,
Catalog.dec,
query_region[0],
query_region[1],
query_region[2]),
peewee.fn.q3c_radial_query(ls.ra,
ls.dec,
query_region[0],
query_region[1],
query_region[2]),
)
)
return query
def build_query(self, version_id, query_region=None):
tic = TIC_v8.alias()
c2tic = CatalogToTIC_v8.alias()
# an alias to simplify accessing the query parameters:
pars = self.parameters
# transform the gaia g,bp,rp into sdss psfmag griz
# use transforms decribed here:
# https://wiki.sdss.org/display/OPS/All-sky+BOSS+standards#All-skyBOSSstandards-TransformingphotometryofeBOSS-likestandardsintoSDSSsystem # noqa
# extract coeffs from fit logs via:
# awk 'BEGIN {print("coeffs = {")} /POLYFIT/{ pe=""; printf("\"%s%d%s\": %s,\n", substr($3,length($3)), $8, pe, $10)} END {print("}")}' ops_std_eboss/gdr2_mag_to_sdss_psfmag_?_results.log # noqa
coeffs = {
"g2": 0.226514,
"g1": 0.373358,
"g0": -0.073834,
"i2": 0.038586,
"i1": -0.505039,
"i0": 0.216803,
"r2": 0.212874,
"r1": -0.381950,
"r0": 0.156923,
"z2": -0.246274,
"z1": -0.372790,
"z0": 0.235517,
}
bp_rp = tic.gaiabp - tic.gaiarp
# compute apparent sdss psfmags
g = (tic.gaiamag + coeffs['g0'] + coeffs['g1'] * bp_rp +
coeffs['g2'] * bp_rp * bp_rp)
r = (tic.gaiamag + coeffs['r0'] + coeffs['r1'] * bp_rp +
coeffs['r2'] * bp_rp * bp_rp)
i = (tic.gaiamag + coeffs['i0'] + coeffs['i1'] * bp_rp +
coeffs['i2'] * bp_rp * bp_rp)
z = (tic.gaiamag + coeffs['z0'] + coeffs['z1'] * bp_rp +
coeffs['z2'] * bp_rp * bp_rp)
# dereddining steps
# extinction terms for Gaia
# Use Stassun+19 (Ticv8) presciption
E_bp_rp = 1.31
R_gaia_g = 1.890 * E_bp_rp
R_gaia_bp = 2.429 * E_bp_rp
R_gaia_rp = 1.429 * E_bp_rp
E_bp_g = R_gaia_bp - R_gaia_g # = 0.7061
E_g_rp = R_gaia_g - R_gaia_rp # = 0.6039
# use ebv from tic_v8 match
bp_rp_dered = tic.gaiabp - tic.gaiarp - tic.ebv * E_bp_rp
bp_g_dered = tic.gaiabp - tic.gaiamag - tic.ebv * E_bp_g
g_rp_dered = tic.gaiamag - tic.gaiarp - tic.ebv * E_g_rp
bp_rp_dered_nominal = pars['bp_rp_dered_nominal']
bp_g_dered_nominal = pars['bp_g_dered_nominal']
g_rp_dered_nominal = pars['g_rp_dered_nominal']
dered_dist2 = (
(bp_rp_dered - bp_rp_dered_nominal) * (bp_rp_dered - bp_rp_dered_nominal) +
(bp_g_dered - bp_g_dered_nominal) * (bp_g_dered - bp_g_dered_nominal) +
(g_rp_dered - g_rp_dered_nominal) * (g_rp_dered - g_rp_dered_nominal)
)
optical_prov = peewee.Value('sdss_psfmag_from_gaia')
dered_dist_max2 = pars['dered_dist_max'] * pars['dered_dist_max']
query = (
Catalog
.select(
Catalog.catalogid,
Catalog.ra,
Catalog.dec,
tic.id.alias('tic_id'), # extra
tic.gaia_int.alias('gaia_source'), # extra
tic.ebv.alias("tic_ebv"), # extra
bp_rp_dered.alias("gdr2_mag_dered_bp_rp"), # extra
bp_g_dered.alias("gdr2_mag_dered_bp_g"), # extra
g_rp_dered.alias("gdr2_mag_dered_g_rp"), # extra
dered_dist2.alias("dered_dist2"), # extra
optical_prov.alias('optical_prov'),
g.alias("g"),
r.alias("r"),
i.alias("i"),
z.alias("z"),
tic.gaiamag.alias("gaia_g"),
tic.gaiabp.alias("bp"),
tic.gaiarp.alias("rp"),
tic.jmag.alias("j"),
tic.hmag.alias("h"),
tic.kmag.alias("k"),
tic.plx.alias('parallax'), # extra
tic.e_plx.alias('parallax_error'), # extra
tic.gallong.alias('tic_gal_l'), # extra
tic.gallat.alias('tic_gal_b'), # extra
)
.join(c2tic,
on=(Catalog.catalogid == c2tic.catalogid))
.join(tic,
on=(c2tic.target_id == tic.id))
.where(
c2tic.version_id == version_id,
c2tic.best >> True,
tic.plx < pars['parallax_max'],
tic.plx > (
pars['parallax_min_at_g16'] +
(tic.gaiamag - 16.0) * pars['parallax_min_slope']
),
dered_dist2 < dered_dist_max2,
tic.gaiamag.between(pars['mag_gaia_g_min'], pars['mag_gaia_g_max']),
# the following are just to bracket the result to make the query run faster
tic.gaiabp.between(pars['mag_gaia_bp_min'], pars['mag_gaia_bp_max']),
tic.gaiarp.between(pars['mag_gaia_rp_min'], pars['mag_gaia_rp_max']),
tic.ebv < pars['ebv_max'],
~(tic.gallat.between(-10., 10.0)),
)
)
# Below ra, dec and radius are in degrees
# query_region[0] is ra of center of the region
# query_region[1] is dec of center of the region
# query_region[2] is radius of the region
if query_region:
query = (
query.where(
peewee.fn.q3c_radial_query(Catalog.ra,
Catalog.dec,
query_region[0],
query_region[1],
query_region[2]),
)
)
return query
def _load_carton_to_target(self, RModel):
"""Populate targetdb.carton_to_target."""
log.debug('Loading data into targetdb.carton_to_target.')
version_pk = tdb.Version.get(
plan=self.plan,
tag=self.tag,
target_selection=True,
)
carton_pk = tdb.Carton.get(carton=self.name, version_pk=version_pk).pk
Target = tdb.Target
CartonToTarget = tdb.CartonToTarget
select_from = (RModel.select(Target.pk, carton_pk).join(
Target, on=(Target.catalogid == RModel.catalogid)
).where(RModel.selected >> True).where(~peewee.fn.EXISTS(
CartonToTarget.select(peewee.SQL('1')).join(tdb.Carton).where(
CartonToTarget.target_pk == Target.pk,
CartonToTarget.carton_pk == carton_pk,
tdb.Carton.version_pk == version_pk,
))))
if self.cadence is not None:
# Check that not both the carton cadence and the cadence column
# are not null.
if 'cadence' in RModel._meta.fields:
if RModel.select().where(~(RModel.cadence >> None)).exists():
raise TargetSelectionError(
'both carton cadence and target '
'cadence defined. This is not '
'allowed.')
cadence_pk = tdb.Cadence.get(label=self.cadence)
select_from = select_from.select_extend(cadence_pk)
if not self.value:
# improve robustness of cadence name pattern matching slightly:
try:
cadence_payload = [
s for s in self.cadence.split("_") if 'x' in s
][0].split('x')
except BaseException:
raise ("Uninterpretable cadence name: ", self.cadence)
self.value = float(
numpy.multiply(
# *map(int, self.cadence.split('_')[-1].split('x'))
*map(int, cadence_payload)))
else:
# If all cadences are null we'll set that as a value and save us
# a costly join.
if not RModel.select().where(~(RModel.cadence >> None)).exists():
select_from = select_from.select_extend(peewee.SQL('null'))
else:
select_from = (select_from.select_extend(
tdb.Cadence.pk).switch(RModel).join(
tdb.Cadence,
'LEFT OUTER JOIN',
on=(tdb.Cadence.label == RModel.cadence),
))
if self.priority is None:
select_from = select_from.select_extend(RModel.priority)
else:
select_from = select_from.select_extend(self.priority)
if self.value is not None:
select_from = select_from.select_extend(self.value)
else:
# We will use the cadence to determine the value. First, if there is
# not a user-defined value column, create it.
if 'value' not in RModel._meta.columns:
self.database.execute_sql(f'ALTER TABLE {self.path} '
'ADD COLUMN value REAL;')
# We need to add the field like this and not call get_model() because
# at this point the temporary table is locked and reflection won't work.
RModel._meta.add_field('value', peewee.FloatField())
# Get the value as the n_epochs * n_exposures_per_epoch. Probably this can
# be done directly in SQL but it's just easier in Python. Note that because
# we set value above in the case when cadence is a single value, if we
# are here that means there is a cadence column.
data = numpy.array(
RModel.select(RModel.catalogid, RModel.cadence).where(
RModel.cadence.is_null(False)).tuples())
if data.size > 0:
values = tuple(
int(
numpy.multiply(
# *map(int, cadence.split('_')[-1].split('x'))))
# improve robustness of cadence name pattern matching slightly:
*map(int,
[s for s in cadence.split("_")
if 'x' in s][0].split('x'))))
for cadence in data[:, 1])
catalogid_values = zip(map(int, data[:, 0]), values)
vl = peewee.ValuesList(catalogid_values,
columns=('catalogid', 'value'),
alias='vl')
(RModel.update(value=vl.c.value).from_(vl).where(
RModel.catalogid == vl.c.catalogid).where(
RModel.value.is_null())).execute()
select_from = select_from.select_extend(RModel.value)
if 'instrument' in RModel._meta.columns:
select_from = (select_from.select_extend(
tdb.Instrument.pk).switch(RModel).join(
tdb.Instrument,
'LEFT OUTER JOIN',
on=(tdb.Instrument.label == RModel.instrument)))
elif self.instrument is not None:
select_from = select_from.select_extend(
tdb.Instrument.get(label=self.instrument).pk)
else:
raise RuntimeError(
f'Instrument not defined for carton {self.name}')
for colname in ['delta_ra', 'delta_dec', 'inertial']:
if colname in RModel._meta.columns:
select_from = select_from.select_extend(
RModel._meta.columns[colname])
else:
if colname == 'inertial':
select_from = select_from.select_extend(
peewee.Value(False))
else:
select_from = select_from.select_extend(peewee.Value(0.0))
if 'lambda_eff' in RModel._meta.columns:
select_from = select_from.select_extend(
RModel._meta.columns['lambda_eff'])
else:
if self.instrument is not None:
instrument = self.instrument
else:
instrument = RModel.instrument
select_from = select_from.select_extend(
tdb.Instrument.select(tdb.Instrument.default_lambda_eff).where(
tdb.Instrument.label == instrument))
# Now do the insert
n_inserted = (CartonToTarget.insert_from(
select_from,
[
CartonToTarget.target_pk, CartonToTarget.carton_pk,
CartonToTarget.cadence_pk, CartonToTarget.priority,
CartonToTarget.value, CartonToTarget.instrument_pk,
CartonToTarget.delta_ra, CartonToTarget.delta_dec,
CartonToTarget.inertial, CartonToTarget.lambda_eff
],
).returning().execute())
log.info(
f'Inserted {n_inserted:,} rows into targetdb.carton_to_target.')
def add_optical_magnitudes(self):
"""Adds ``gri`` magnitude columns."""
Model = self.RModel
magnitudes = ['g', 'r', 'i', 'z']
# Check if ALL the columns have already been created in the query.
# If so, just return.
if any([mag in Model._meta.columns for mag in magnitudes]):
if not all([mag in Model._meta.columns for mag in magnitudes]):
raise TargetSelectionError(
'Some optical magnitudes are defined in the query '
'but not all of them.')
if 'optical_prov' not in Model._meta.columns:
raise TargetSelectionError(
'optical_prov column does not exist.')
warnings.warn(
'All optical magnitude columns are defined in the query.',
TargetSelectionUserWarning)
return
# First create the columns. Also create z to speed things up. We won't
# use transformations for z but we can use the initial query to populate
# it and avoid doing the same query later when loading the magnitudes.
for mag in magnitudes:
self.database.execute_sql(
f'ALTER TABLE {self.path} ADD COLUMN {mag} REAL;')
Model._meta.add_field(mag, peewee.FloatField())
self.database.execute_sql(
f'ALTER TABLE {self.path} ADD COLUMN optical_prov TEXT;')
Model._meta.add_field('optical_prov', peewee.TextField())
# Step 1: join with sdss_dr13_photoobj and use SDSS magnitudes.
with self.database.atomic():
self.database.execute_sql('DROP TABLE IF EXISTS ' +
self.table_name + '_sdss')
temp_table = peewee.Table(self.table_name + '_sdss')
(Model.select(
Model.catalogid, cdb.SDSS_DR13_PhotoObj.psfmag_g,
cdb.SDSS_DR13_PhotoObj.psfmag_r,
cdb.SDSS_DR13_PhotoObj.psfmag_i,
cdb.SDSS_DR13_PhotoObj.psfmag_z).join(
cdb.CatalogToSDSS_DR13_PhotoObj_Primary,
on=(cdb.CatalogToSDSS_DR13_PhotoObj_Primary.catalogid ==
Model.catalogid)).join(
cdb.SDSS_DR13_PhotoObj,
on=(cdb.CatalogToSDSS_DR13_PhotoObj_Primary.
target_id == cdb.SDSS_DR13_PhotoObj.objid)).
where(
cdb.CatalogToSDSS_DR13_PhotoObj_Primary.best >> True,
cdb.CatalogToSDSS_DR13_PhotoObj_Primary.version_id == self.
get_version_id()).where(Model.selected >> True).create_table(
temp_table._path[0], temporary=True))
nrows = (Model.update({
Model.g:
temp_table.c.psfmag_g,
Model.r:
temp_table.c.psfmag_r,
Model.i:
temp_table.c.psfmag_i,
Model.optical_prov:
peewee.Value('sdss_psfmag')
}).from_(temp_table).where(
Model.catalogid == temp_table.c.catalogid).where(
temp_table.c.psfmag_g.is_null(False)
& temp_table.c.psfmag_r.is_null(False)
& temp_table.c.psfmag_i.is_null(False))).execute()
self.log.debug(f'{nrows:,} associated with SDSS magnitudes.')
# Step 2: localise entries with empty magnitudes and use PanSTARRS1
# transformations.
# PS1 fluxes are in Janskys. We use stacked fluxes instead of mean
# magnitudes since they are more complete on the faint end.
ps1_g = 8.9 - 2.5 * peewee.fn.log(cdb.Panstarrs1.g_stk_psf_flux)
ps1_r = 8.9 - 2.5 * peewee.fn.log(cdb.Panstarrs1.r_stk_psf_flux)
ps1_i = 8.9 - 2.5 * peewee.fn.log(cdb.Panstarrs1.i_stk_psf_flux)
# Use transformations to SDSS from Tonry et al. 2012 (section 3.2, table 6).
x = ps1_g - ps1_r
ps1_sdss_g = 0.013 + 0.145 * x + 0.019 * x * x + ps1_g
ps1_sdss_r = -0.001 + 0.004 * x + 0.007 * x * x + ps1_r
ps1_sdss_i = -0.005 + 0.011 * x + 0.010 * x * x + ps1_i
with self.database.atomic():
self.database.execute_sql('DROP TABLE IF EXISTS ' +
self.table_name + '_ps1')
temp_table = peewee.Table(self.table_name + '_ps1')
(Model.select(
Model.catalogid, ps1_sdss_g.alias('ps1_sdss_g'),
ps1_sdss_r.alias('ps1_sdss_r'),
ps1_sdss_i.alias('ps1_sdss_i')).join(
cdb.CatalogToPanstarrs1,
on=(cdb.CatalogToPanstarrs1.catalogid == Model.catalogid)).
join(cdb.Panstarrs1,
on=(cdb.CatalogToPanstarrs1.target_id ==
cdb.Panstarrs1.catid_objid)).where(Model.g.is_null()
| Model.r.is_null()
| Model.i.is_null()).
where(Model.selected >> True).where(
cdb.CatalogToPanstarrs1.best >> True,
cdb.CatalogToPanstarrs1.version_id == self.get_version_id()
).where(
cdb.Panstarrs1.g_stk_psf_flux.is_null(False)
& (cdb.Panstarrs1.g_stk_psf_flux > 0)).where(
cdb.Panstarrs1.r_stk_psf_flux.is_null(False)
& (cdb.Panstarrs1.r_stk_psf_flux > 0)).where(
cdb.Panstarrs1.i_stk_psf_flux.is_null(False)
& (cdb.Panstarrs1.i_stk_psf_flux > 0)).create_table(
temp_table._path[0], temporary=True))
nrows = (Model.update({
Model.g:
temp_table.c.ps1_sdss_g,
Model.r:
temp_table.c.ps1_sdss_r,
Model.i:
temp_table.c.ps1_sdss_i,
Model.optical_prov:
peewee.Value('sdss_psfmag_ps1')
}).from_(temp_table).where(
Model.catalogid == temp_table.c.catalogid).where(
temp_table.c.ps1_sdss_g.is_null(False)
& temp_table.c.ps1_sdss_r.is_null(False)
& temp_table.c.ps1_sdss_i.is_null(False))).execute()
self.log.debug(f'{nrows:,} associated with PS1 magnitudes.')
# Step 3: localise entries with empty magnitudes and use Gaia transformations
# from Evans et al (2018).
gaia_G = cdb.Gaia_DR2.phot_g_mean_mag
gaia_BP = cdb.Gaia_DR2.phot_bp_mean_mag
gaia_RP = cdb.Gaia_DR2.phot_rp_mean_mag
x = gaia_BP - gaia_RP
x2 = x * x
x3 = x * x * x
gaia_sdss_g = -1 * (0.13518 - 0.46245 * x - 0.25171 * x2 +
0.021349 * x3) + gaia_G
gaia_sdss_r = -1 * (-0.12879 + 0.24662 * x - 0.027464 * x2 -
0.049465 * x3) + gaia_G
gaia_sdss_i = -1 * (-0.29676 + 0.64728 * x - 0.10141 * x2) + gaia_G
with self.database.atomic():
self.database.execute_sql('DROP TABLE IF EXISTS ' +
self.table_name + '_gaia')
temp_table = peewee.Table(self.table_name + '_gaia')
(Model.select(
Model.catalogid, gaia_sdss_g.alias('gaia_sdss_g'),
gaia_sdss_r.alias('gaia_sdss_r'),
gaia_sdss_i.alias('gaia_sdss_i')).join(
cdb.CatalogToTIC_v8,
on=(cdb.CatalogToTIC_v8.catalogid == Model.catalogid
)).join(cdb.TIC_v8).join(cdb.Gaia_DR2).
where(Model.g.is_null() | Model.r.is_null()
| Model.i.is_null()).where(Model.selected >> True).where(
cdb.CatalogToTIC_v8.best >> True,
cdb.CatalogToTIC_v8.version_id == self.get_version_id()
).where(
cdb.Gaia_DR2.phot_g_mean_mag.is_null(False)).where(
cdb.Gaia_DR2.phot_bp_mean_mag.is_null(False)).where(
cdb.Gaia_DR2.phot_rp_mean_mag.is_null(
False)).create_table(temp_table._path[0],
temporary=True))
nrows = (Model.update({
Model.g:
temp_table.c.gaia_sdss_g,
Model.r:
temp_table.c.gaia_sdss_r,
Model.i:
temp_table.c.gaia_sdss_i,
Model.optical_prov:
peewee.Value('sdss_psfmag_gaia')
}).from_(temp_table).where(
Model.catalogid == temp_table.c.catalogid).where(
temp_table.c.gaia_sdss_g.is_null(False)
& temp_table.c.gaia_sdss_r.is_null(False)
& temp_table.c.gaia_sdss_i.is_null(False))).execute()
self.log.debug(f'{nrows:,} associated with Gaia magnitudes.')
# Finally, check if there are any rows in which at least some of the
# magnitudes are null.
n_empty = (Model.select().where(Model.g.is_null() | Model.r.is_null()
| Model.i.is_null()).where(
Model.selected >> True).count())
if n_empty > 0:
warnings.warn(f'Found {n_empty} entries with empty magnitudes.',
TargetSelectionUserWarning)
def build_query(self, version_id, query_region=None):
c = Catalog.alias()
# ## c2t = CatalogToGaia_unWISE_AGN.alias() - deprecated - but leave this as a reminder
c2tic = CatalogToTIC_v8.alias()
tic = TIC_v8.alias()
# s2020 = BHM_eFEDS_Veto.alias()
# sV = SDSSV_BOSS_SPALL.alias()
# ph = SDSSV_Plateholes.alias()
# phm = SDSSV_Plateholes_Meta.alias()
# g2 = Gaia_DR2.alias()
t = Gaia_unWISE_AGN.alias()
match_radius_spectro = self.parameters['spec_join_radius'] / 3600.0
spec_sn_thresh = self.parameters['spec_sn_thresh']
spec_z_err_thresh = self.parameters['spec_z_err_thresh']
# #########################################################################
# prepare the spectroscopy catalogues
# SDSS DR16
c2s16 = CatalogToSDSS_DR16_SpecObj.alias()
ss16 = SDSS_DR16_SpecObj.alias()
s16 = (ss16.select(ss16.specobjid.alias('specobjid'), ).where(
ss16.snmedian >= spec_sn_thresh,
ss16.zwarning == 0,
ss16.zerr <= spec_z_err_thresh,
ss16.zerr > 0.0,
ss16.scienceprimary > 0,
).alias('s16'))
# SDSS-IV/eFEDS March2020
c2s2020 = CatalogToBHM_eFEDS_Veto.alias()
ss2020 = BHM_eFEDS_Veto.alias()
s2020 = (ss2020.select(ss2020.pk.alias('pk'), ).where(
ss2020.sn_median_all >= spec_sn_thresh,
ss2020.zwarning == 0,
ss2020.z_err <= spec_z_err_thresh,
ss2020.z_err > 0.0,
).alias('s2020'))
# SDSS-V spAll
ssV = SDSSV_BOSS_SPALL.alias()
sV = (ssV.select(
ssV.specobjid.alias('specobjid'),
ssV.plug_ra.alias('plug_ra'),
ssV.plug_dec.alias('plug_dec'),
).where(ssV.sn_median_all >= spec_sn_thresh, ssV.zwarning == 0,
ssV.z_err <= spec_z_err_thresh, ssV.z_err > 0.0,
ssV.specprimary > 0, ssV.specobjid.is_null()))
# SDSS-V plateholes - only consider plateholes that
# were drilled+shipped but that were not yet observed
ssph = SDSSV_Plateholes.alias()
ssphm = SDSSV_Plateholes_Meta.alias()
ssconf = SDSSV_BOSS_Conflist.alias()
sph = (ssph.select(
ssph.pkey.alias('pkey'),
ssph.target_ra.alias('target_ra'),
ssph.target_dec.alias('target_dec'),
).join(ssphm,
on=(ssph.yanny_uid == ssphm.yanny_uid)).join(
ssconf, JOIN.LEFT_OUTER,
on=(ssphm.plateid == ssconf.plate)).where(
(ssph.holetype == 'BOSS_SHARED'),
(ssph.sourcetype == 'SCI') | (ssph.sourcetype == 'STA'),
ssphm.isvalid > 0, ssconf.plate.is_null(),
ssph.pkey.is_null()))
# set the Carton priority+values here - read from yaml
priority = peewee.Value(int(self.parameters.get('priority', 10000)))
value = peewee.Value(self.parameters.get('value', 1.0)).cast('float')
inertial = peewee.Value(True)
cadence = peewee.Value(self.parameters['cadence'])
instrument = peewee.Value(self.instrument)
match_radius_spectro = self.parameters['spec_join_radius'] / 3600.0
spec_sn_thresh = self.parameters['spec_sn_thresh']
spec_z_err_thresh = self.parameters['spec_z_err_thresh']
# compute transformed SDSS mags for pointlike and extended sources separately
# transform the Gaia dr2 G,BP,RP into sdss psfmag griz
# extract coeffs from fit logs via:
# awk 'BEGIN {print("coeffs = {")} /POLYFIT/{ if($3~/sdss_psfmag/){pe="p"} else if ($3~/sdss_fiber2mag/){pe="e"} else{pe="error"}; printf("\"%s%d_%s\": %s,\n", substr($3,length($3)), $8, pe, $10)} END {print("}")}' bhm_gua/gdr2_mag_to_sdss_psfmag_?_results.log # noqa
coeffs = {
"g3_p": 0.184158,
"g2_p": -0.457316,
"g1_p": 0.553505,
"g0_p": -0.029152,
"i3_p": 0.709818,
"i2_p": -2.207549,
"i1_p": 1.520957,
"i0_p": -0.417666,
"r3_p": 0.241611,
"r2_p": -0.803702,
"r1_p": 0.599944,
"r0_p": -0.119959,
"z3_p": 0.893988,
"z2_p": -2.759177,
"z1_p": 1.651668,
"z0_p": -0.440676,
}
bp_rp = t.bp - t.rp
g = (t.g + coeffs['g0_p'] + coeffs['g1_p'] * bp_rp +
coeffs['g2_p'] * bp_rp * bp_rp +
coeffs['g3_p'] * bp_rp * bp_rp * bp_rp)
r = (t.g + coeffs['r0_p'] + coeffs['r1_p'] * bp_rp +
coeffs['r2_p'] * bp_rp * bp_rp +
coeffs['r3_p'] * bp_rp * bp_rp * bp_rp)
i = (t.g + coeffs['i0_p'] + coeffs['i1_p'] * bp_rp +
coeffs['i2_p'] * bp_rp * bp_rp +
coeffs['i3_p'] * bp_rp * bp_rp * bp_rp)
z = (t.g + coeffs['z0_p'] + coeffs['z1_p'] * bp_rp +
coeffs['z2_p'] * bp_rp * bp_rp +
coeffs['z3_p'] * bp_rp * bp_rp * bp_rp)
# validity checks - set limits semi-manually
bp_rp_min = 0.0
bp_rp_max = 1.8
valid = (t.g.between(0.1, 29.9) & t.bp.between(0.1, 29.9)
& t.rp.between(0.1, 29.9)
& bp_rp.between(bp_rp_min, bp_rp_max))
opt_prov = peewee.Case(None, ((valid, 'sdss_psfmag_from_gaiadr2'), ),
'undefined')
magnitude_g = peewee.Case(None, ((valid, g), ), 'NaN')
magnitude_r = peewee.Case(None, ((valid, r), ), 'NaN')
magnitude_i = peewee.Case(None, ((valid, i), ), 'NaN')
magnitude_z = peewee.Case(None, ((valid, z), ), 'NaN')
# Create temporary tables for the base query and the Q3C cross-match
# tables.
bquery = (
c.select(
c.catalogid,
c.ra, # extra
c.dec, # extra
t.gaia_sourceid, # extra
t.unwise_objid, # extra
priority.alias('priority'),
value.alias('value'),
inertial.alias('inertial'),
cadence.alias('cadence'),
instrument.alias('instrument'),
opt_prov.alias('optical_prov'),
magnitude_g.alias('g'),
magnitude_r.alias('r'),
magnitude_i.alias('i'),
magnitude_z.alias('z'),
t.g.alias('gaia_g'),
t.bp.alias('bp'),
t.rp.alias('rp'),
t.w1.alias('gua_w1'), # extra
t.w2.alias('gua_w2'), # extra
t.prob_rf.alias('gua_prob_rf'), # extra
t.phot_z.alias('gua_phot_z'), # extra
# rely on the centralised magnitude routines for 'real' griz, bp,rp,gaia_g
).join(c2tic).join(tic)
# .join(g2) # can skip this join using the gaia_int from the TIC
# .join(t, on=(g2.source_id == t.gaia_sourceid))
.join(t, on=(tic.gaia_int == t.gaia_sourceid))
# start joining the spectroscopy
.switch(c).join(c2s16, JOIN.LEFT_OUTER).join(
s16,
JOIN.LEFT_OUTER,
on=((c2s16.target_id == s16.c.specobjid)
# (c2s16.version_id == version_id)
)).switch(c).join(c2s2020, JOIN.LEFT_OUTER).join(
s2020,
JOIN.LEFT_OUTER,
on=((c2s2020.target_id == s2020.c.pk)
# (c2s2020.version_id == version_id)
))
# finished joining the spectroscopy
.where(
c.version_id == version_id,
# c2tic.version_id == version_id,
c2tic.best >> True,
).where(
(t.prob_rf >= self.parameters['prob_rf_min']),
(t.g >= self.parameters['mag_g_min']),
(t.rp >= self.parameters['mag_rp_min']),
((t.g < self.parameters['mag_g_max']) |
(t.rp < self.parameters['mag_rp_max'])),
)
# then reject any GUA targets with existing good DR16+SDSS-V spectroscopy
.where(s16.c.specobjid.is_null(True), s2020.c.pk.is_null(True))
# avoid duplicates - trust the gaia ids in the GUA parent sample
.distinct([t.gaia_sourceid]))
# Below ra, dec and radius are in degrees
# query_region[0] is ra of center of the region
# query_region[1] is dec of center of the region
# query_region[2] is radius of the region
if query_region:
bquery = (bquery.where(
peewee.fn.q3c_radial_query(c.ra, c.dec, query_region[0],
query_region[1], query_region[2])))
self.log.debug('Creating temporary table for base query ...')
bquery.create_table(self.name + '_bquery', temporary=True)
self.database.execute_sql(
f'CREATE INDEX ON {self.name}_bquery (ra, dec)')
self.database.execute_sql(f'ANALYZE {self.name}_bquery')
sph.create_table(self.name + '_sph', temporary=True)
self.database.execute_sql(
f'CREATE INDEX ON {self.name}_sph (target_ra, target_dec)')
self.database.execute_sql(f'ANALYZE {self.name}_sph')
sV.create_table(self.name + '_sv', temporary=True)
self.database.execute_sql(
f'CREATE INDEX ON {self.name}_sv (plug_ra, plug_dec)')
self.database.execute_sql(f'ANALYZE {self.name}_sv')
bquery_table = peewee.Table(f'{self.name}_bquery', alias='bquery')
sph_table = peewee.Table(f'{self.name}_sph')
sV_table = peewee.Table(f'{self.name}_sv')
query = (
bquery_table.select(peewee.SQL('bquery.*')).join(
sV_table,
JOIN.LEFT_OUTER,
on=(fn.q3c_join(bquery_table.c.ra, bquery_table.c.dec,
sV_table.c.plug_ra, sV_table.c.plug_dec,
match_radius_spectro))).join(
sph_table,
JOIN.LEFT_OUTER,
on=(fn.q3c_join(bquery_table.c.ra,
bquery_table.c.dec,
sph_table.c.target_ra,
sph_table.c.target_dec,
match_radius_spectro)))
# then reject any GUA targets with existing good SDSS-V spectroscopy or a platehole
.where(
sV_table.c.specobjid.is_null(True),
sph_table.c.pkey.is_null(True),
))
return query
def build_query(self, version_id, query_region=None):
c = Catalog.alias()
c2t = CatalogToBHM_CSC.alias()
t = BHM_CSC.alias()
self.alias_t = t
# c2s16 = CatalogToSDSS_DR16_SpecObj.alias()
# s16 = SDSS_DR16_SpecObj.alias()
# s2020 = BHM_eFEDS_Veto.alias()
# sV = SDSSV_BOSS_SPALL.alias()
# ph = SDSSV_Plateholes.alias()
# phm = SDSSV_Plateholes_Meta.alias()
# set the Carton priority+values here - read from yaml
value = peewee.Value(self.parameters.get('value', 1.0)).cast('float')
instrument = peewee.Value(self.instrument)
cadence = peewee.Value(self.this_cadence)
# opt_prov = peewee.Value('ps1_psfmag')
if (self.instrument == 'BOSS'):
# #########################################################################
# prepare the spectroscopy catalogues
match_radius_spectro = self.parameters['spec_join_radius'] / 3600.0
spec_sn_thresh = self.parameters['spec_sn_thresh']
spec_z_err_thresh = self.parameters['spec_z_err_thresh']
dpriority_has_spec = self.parameters['dpriority_has_spec']
# SDSS DR16
c2s16 = CatalogToSDSS_DR16_SpecObj.alias()
ss16 = SDSS_DR16_SpecObj.alias()
s16 = (
ss16.select(
ss16.specobjid.alias('specobjid'),
)
.where(
ss16.snmedian >= spec_sn_thresh,
ss16.zwarning == 0,
ss16.zerr <= spec_z_err_thresh,
ss16.zerr > 0.0,
ss16.scienceprimary > 0,
)
.alias('s16')
)
# SDSS-IV/eFEDS March2020
c2s2020 = CatalogToBHM_eFEDS_Veto.alias()
ss2020 = BHM_eFEDS_Veto.alias()
s2020 = (
ss2020.select(
ss2020.pk.alias('pk'),
)
.where(
ss2020.sn_median_all >= spec_sn_thresh,
ss2020.zwarning == 0,
ss2020.z_err <= spec_z_err_thresh,
ss2020.z_err > 0.0,
)
.alias('s2020')
)
# SDSS-V spAll
ssV = SDSSV_BOSS_SPALL.alias()
sV = (
ssV.select(
ssV.specobjid.alias('specobjid'),
ssV.plug_ra.alias('plug_ra'),
ssV.plug_dec.alias('plug_dec'),
)
.where(
ssV.sn_median_all >= spec_sn_thresh,
ssV.zwarning == 0,
ssV.z_err <= spec_z_err_thresh,
ssV.z_err > 0.0,
ssV.specprimary > 0,
)
.alias('sV')
)
# SDSS-V plateholes - only consider plateholes that
# were drilled+shipped but that were not yet observed
ssph = SDSSV_Plateholes.alias()
ssphm = SDSSV_Plateholes_Meta.alias()
ssconf = SDSSV_BOSS_Conflist.alias()
sph = (
ssph.select(
ssph.pkey.alias('pkey'),
ssph.target_ra.alias('target_ra'),
ssph.target_dec.alias('target_dec'),
)
.join(
ssphm,
on=(ssph.yanny_uid == ssphm.yanny_uid)
)
.join(
ssconf, JOIN.LEFT_OUTER,
on=(ssphm.plateid == ssconf.plate)
)
.where(
(ssph.holetype == 'BOSS_SHARED'),
(ssph.sourcetype == 'SCI') | (ssph.sourcetype == 'STA'),
ssphm.isvalid > 0,
ssconf.plate.is_null(),
)
.alias('sph')
)
# adjust priority if target aleady has an SDSS spectrum
priority_1 = peewee.Case(
None,
(
(s16.c.specobjid.is_null(False), 1), # any of these can be satisfied
(s2020.c.pk.is_null(False), 1),
(sV.c.specobjid.is_null(False), 1),
(sph.c.pkey.is_null(False), 1),
),
0)
#
# Compute net priority
priority = (
peewee.Value(self.parameters['priority_floor']) +
priority_1 * dpriority_has_spec
)
else:
priority = peewee.Value(self.parameters['priority_floor'])
# compute transformed SDSS mags for pointlike and extended sources separately
# transform the csc (panstarrs1-dr1) griz into sdss psfmag griz
# extract coeffs from fit logs via:
# awk 'BEGIN {print("coeffs = {")} /POLYFIT/{ if($3~/sdss_psfmag/){pe="p"} else if ($3~/sdss_fiber2mag/){pe="e"} else{pe="error"}; printf("\"%s%d_%s\": %s,\n", substr($3,length($3)), $8, pe, $10)} END {print("}")}' bhm_csc_boss/ts_mag_to_sdss_psfmag_?_results.log # noqa
coeffs = {
"g2_p": 0.087878,
"g1_p": 0.063329,
"g0_p": 0.021488,
"i2_p": -0.011220,
"i1_p": 0.020782,
"i0_p": 0.000154,
"r2_p": -0.093371,
"r1_p": 0.136032,
"r0_p": -0.011477,
"z2_p": -0.180526,
"z1_p": 0.007284,
"z0_p": -0.037933,
}
# Note that the corrections for r and i are very small,
# however g+z both have non-negligible colour terms
g0 = peewee.Case(None, ((t.mag_g <= 0.0, None),), t.mag_g)
r0 = peewee.Case(None, ((t.mag_r <= 0.0, None),), t.mag_r)
i0 = peewee.Case(None, ((t.mag_i <= 0.0, None),), t.mag_i)
z0 = peewee.Case(None, ((t.mag_z <= 0.0, None),), t.mag_z)
g_r = g0 - r0
r_i = r0 - i0
i_z = i0 - z0
# use single set of transforms because we do not have any info in csc parent table to
# differentiate between pointlike and extended sources)
g = (g0 + coeffs['g0_p'] + coeffs['g1_p'] * g_r + coeffs['g2_p'] * g_r * g_r)
r = (r0 + coeffs['r0_p'] + coeffs['r1_p'] * g_r + coeffs['r2_p'] * g_r * g_r)
i = (i0 + coeffs['i0_p'] + coeffs['i1_p'] * r_i + coeffs['i2_p'] * r_i * r_i)
z = (z0 + coeffs['z0_p'] + coeffs['z1_p'] * i_z + coeffs['z2_p'] * i_z * i_z)
# validity checks (only griz) - set limits semi-manually
g_r_min = -0.3
g_r_max = 1.7
r_i_min = -0.5
r_i_max = 2.5
i_z_min = -0.3
i_z_max = 1.25
valid = (g0.between(0.1, 29.9) &
r0.between(0.1, 29.9) &
i0.between(0.1, 29.9) &
z0.between(0.1, 29.9) &
g_r.between(g_r_min, g_r_max) &
r_i.between(r_i_min, r_i_max) &
i_z.between(i_z_min, i_z_max))
opt_prov = peewee.Case(None, ((valid, 'sdss_psfmag_from_csc'),), 'undefined')
magnitude_g = peewee.Case(None, ((valid, g),), 'NaN')
magnitude_r = peewee.Case(None, ((valid, r),), 'NaN')
magnitude_i = peewee.Case(None, ((valid, i),), 'NaN')
magnitude_z = peewee.Case(None, ((valid, z),), 'NaN')
magnitude_h = peewee.Case(None, ((t.mag_h <= 0.0, None),), t.mag_h).cast('float')
# # Process the bhm_csc.[g,r,i,z,h] magnitudes to deal with zeros
# magnitude_g = peewee.Case(None, ((t.mag_g <= 0.0, None),), t.mag_g).cast('float')
# magnitude_r = peewee.Case(None, ((t.mag_r <= 0.0, None),), t.mag_r).cast('float')
# magnitude_i = peewee.Case(None, ((t.mag_i <= 0.0, None),), t.mag_i).cast('float')
# magnitude_z = peewee.Case(None, ((t.mag_z <= 0.0, None),), t.mag_z).cast('float')
# magnitude_h = peewee.Case(None, ((t.mag_h <= 0.0, None),), t.mag_h).cast('float')
# Create a subquery that will calculate the minimum catalog_to_bhm_csc.distance for each
# csc candidate target
subq = (
c2t
.select(
c2t.target_id,
fn.MIN(c2t.distance).alias('min_distance'))
.where(
c2t.version_id == version_id,
c2t.best >> True
)
.group_by(c2t.target_id)
.alias('min_dist_subq')
)
query = (
c.select(
c.catalogid,
t.cxo_name, # extra
t.pk.alias('csc_pk'), # extra
c.ra, # extra
c.dec, # extra
priority.alias('priority'),
value.alias('value'),
cadence.alias('cadence'),
instrument.alias('instrument'),
opt_prov.alias('optical_prov'),
magnitude_g.alias('g'),
magnitude_r.alias('r'),
magnitude_i.alias('i'),
magnitude_z.alias('z'),
magnitude_h.alias('h'),
t.mag_g.alias('csc_mag_g'), # extra
t.mag_r.alias('csc_mag_r'), # extra
t.mag_i.alias('csc_mag_i'), # extra
t.mag_z.alias('csc_mag_z'), # extra
t.oir_ra.alias('csc_ra'), # extra
t.oir_dec.alias('csc_dec'), # extra
)
.join(c2t)
.join(t)
.join(
subq,
on=(
(c2t.target_id == subq.c.target_id) &
(
(c2t.distance == subq.c.min_distance) |
(c2t.distance.is_null() & subq.c.min_distance.is_null())
)
),
)
.where(
c.version_id == version_id,
c2t.version_id == version_id,
c2t.best >> True
)
# .distinct([c2t.target_id]) # avoid duplicates - trust the CSC parent sample,
# .distinct([c.catalogid]) # avoid duplicates - trust the catalogid,
# avoid duplicates - trust uniquness in both CSC name and catalogid
.distinct([c.catalogid])
# .distinct([t.cxo_name])
.where
(
t.spectrograph == self.instrument
)
)
if (self.instrument == 'BOSS'):
# Append the spectro query
query = (
query
.switch(c)
.join(c2s16, JOIN.LEFT_OUTER)
.join(
s16, JOIN.LEFT_OUTER,
on=(
(c2s16.target_id == s16.c.specobjid) &
(c2s16.version_id == version_id)
)
)
.switch(c)
.join(c2s2020, JOIN.LEFT_OUTER)
.join(
s2020, JOIN.LEFT_OUTER,
on=(
(c2s2020.target_id == s2020.c.pk) &
(c2s2020.version_id == version_id)
)
)
.join(
sV, JOIN.LEFT_OUTER,
on=(
fn.q3c_join(sV.c.plug_ra, sV.c.plug_dec,
c.ra, c.dec,
match_radius_spectro)
)
)
.join(
sph, JOIN.LEFT_OUTER,
on=(
fn.q3c_join(sph.c.target_ra, sph.c.target_dec,
c.ra, c.dec,
match_radius_spectro)
)
)
)
if query_region:
query = query.where(peewee.fn.q3c_radial_query(c.ra, c.dec,
query_region[0],
query_region[1],
query_region[2]))
return query