def pagarConta(self):
try:
# Update Status se valor pago igual ou maior que valor parcela
status = peewee.Case(None, (
(ContaAPagar.valor_pago >= ContaAPagar.valor, '1'),
), '2')
# Query
row = (ContaAPagar.update(
forma_pagamento=self.formaPagamento,
data_pagamento=self.dataPagamento,
valor_pago=ContaAPagar.valor_pago + self.valorPago,
status_pagamento=status)
.where(ContaAPagar.id == self.id)
)
# Executando a query
row.execute()
# Fechando a Conexao
Conexao().dbhandler.close()
except peewee.InternalError as err:
print(err)
pass
def list():
"""List known transport nodes."""
config_connect()
import tabulate
data = (st.StorageNode.select(
st.StorageNode.name,
pw.Case(st.StorageNode.active, [(True, "Y"), (False, "-")]),
st.StorageNode.host,
st.StorageNode.root,
st.StorageNode.notes,
).where(st.StorageNode.storage_type == "T").tuples())
if data:
print(
tabulate.tabulate(
data, headers=["Name", "Mounted", "Host", "Root", "Notes"]))
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):
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 subtree(node_id, cattext = ""):
global cat_names
node = nodes[node_id]
subnodes = subcats[node_id]
title = node.findtext('TITLE', default = 'None')
if cattext:
name = cattext + ' | ' + title
else:
name = title
cat_names[node_id] = name
for subnode in subnodes:
node.append(nodes[subnode])
subtree(subnode, name)
custom_order = peewee.Case(CategoryLang.id_lang, [
(LANG_ID, 100),
(0, 99),
], -1000)
for category in Category.select(Category, CategoryLang.name,
peewee.fn.max(custom_order)).join(CategoryLang, on = (Category.id_category ==
CategoryLang.id_category)) \
.where(CategoryLang.id_lang == LANG_ID and \
CategoryLang.id_shop == SHOP_ID).group_by(Category.id_category).dicts():
#print(category)
node = Element("ITEM")
node_id = category['id_category']
i = SubElement(node, "URL")
i.text = CATEGORY_URL_TEMPLATE.format(id_category =
category['id_category'])
i = SubElement(node, "TITLE")
i.text = category['name']
subcats[category['id_parent']].append(node_id)
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