def build_query(self, version_id, query_region=None):
GD = GeometricDistances_Gaia_DR2
FUV = GUVCat.fuv_mag
NUV = GUVCat.nuv_mag
FUV_abs = FUV - 5 * peewee.fn.log(GD.r_est / 10)
query = (CatalogToTIC_v8.select(
CatalogToTIC_v8.catalogid, GD.source_id, GD.r_est, FUV, NUV,
TIC_v8.hmag.alias('h'), Gaia_DR2.phot_g_mean_mag.alias(
'gaia_g')).join(TIC_v8).join(Gaia_DR2).join(GD).join_from(
CatalogToTIC_v8,
CatalogToGUVCat,
on=(CatalogToGUVCat.catalogid == CatalogToTIC_v8.catalogid
)).join(GUVCat).where(
GD.r_est < 300,
FUV_abs > 14 * (FUV - NUV) - 46).where(
CatalogToGUVCat.version_id == version_id,
CatalogToGUVCat.best >> True,
CatalogToTIC_v8.version_id == version_id,
CatalogToTIC_v8.best >> True))
if query_region:
query = (query.join_from(CatalogToTIC_v8, 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):
FUV = GUVCat.fuv_mag
gaiag = Gaia_DR2.phot_g_mean_mag
FUV_abs = FUV + 5 * peewee.fn.log(Gaia_DR2.parallax / 1000.) + 5
query = (CatalogToTIC_v8.select(
CatalogToTIC_v8.catalogid,
Gaia_DR2.source_id.alias('gaia_source_id'), Gaia_DR2.parallax,
Gaia_DR2.parallax_error, FUV, gaiag.alias('phot_g_mean_mag'),
TIC_v8.hmag.alias('h')).join(TIC_v8).join(Gaia_DR2).join_from(
CatalogToTIC_v8,
CatalogToGUVCat,
on=(CatalogToGUVCat.catalogid == CatalogToTIC_v8.catalogid
)).join(GUVCat).where(
Gaia_DR2.parallax / Gaia_DR2.parallax_error > 3,
gaiag < 20, FUV > -999., FUV_abs >
(1.5 + 1.28 * (FUV - gaiag))).where(
CatalogToGUVCat.version_id == version_id,
CatalogToGUVCat.best >> True,
CatalogToTIC_v8.version_id == version_id,
CatalogToTIC_v8.best >> True))
if query_region:
query = (query.join_from(CatalogToTIC_v8, 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):
km = TwoMassPSC.k_m
hm = TwoMassPSC.h_m
jm = TwoMassPSC.j_m
Gm = Gaia_DR2.phot_g_mean_mag
query = (CatalogToTIC_v8.select(
CatalogToTIC_v8.catalogid, Gaia_DR2.parallax,
Gaia_DR2.source_id.alias('gaia_source_id'),
km.alias('ks_m')).join(TIC_v8).join(Gaia_DR2).join(
Gaia_DR2_RUWE,
on=(Gaia_DR2.source_id == Gaia_DR2_RUWE.source_id)).join(
TMBN, on=(TMBN.source_id == Gaia_DR2_RUWE.source_id)).join(
TwoMassPSC,
on=(TMBN.tmass_pts_key == TwoMassPSC.pts_key)).where(
CatalogToTIC_v8.version_id == version_id,
CatalogToTIC_v8.best >> True).where(
Gaia_DR2.parallax < fn.pow(
10, ((10. - km - 0.61) / 5.)), Gm < 16.,
jm - km - 0.25 * (Gm - km) < 0.10,
jm - km - 0.25 * (Gm - km) > -0.30,
jm - hm < 0.15 * (Gm - km) + 0.05,
jm - hm > 0.15 * (Gm - km) - 0.15,
jm - km < 0.23 * (Gm - km) + 0.03,
Gm > 2 * (Gm - km) + 3.0,
TMBN.angular_distance < 1.))
if query_region:
query = (query.join_from(CatalogToTIC_v8, 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):
query = (CatalogToTIC_v8.select(
CatalogToTIC_v8.catalogid, Gaia_DR2.source_id,
Gaia_DR2.ra.alias('gaia_dr2_ra'),
Gaia_DR2.dec.alias('gaia_dr2_dec'),
Gaia_DR2.pmra.alias('gaia_dr2_pmra'),
Gaia_DR2.pmdec.alias('gaia_dr2_pmdec'),
Gaia_DR2.phot_g_mean_mag.alias('gaia_g'),
Gaia_DR2.phot_bp_mean_mag.alias('bp'),
Gaia_DR2.phot_rp_mean_mag.alias('rp')).join(
TIC_v8, on=(CatalogToTIC_v8.target_id == TIC_v8.id)).join(
Gaia_DR2,
on=(TIC_v8.gaia_int == Gaia_DR2.source_id)).where(
CatalogToTIC_v8.version_id == version_id,
CatalogToTIC_v8.best >> True,
Gaia_DR2.phot_g_mean_mag < 13))
# Gaia_DR2 peewee model class corresponds to
# table catalogdb.gaia_dr2_source.
if query_region:
query = (query.join_from(CatalogToTIC_v8, 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):
# We do not select pmra and pmdec below because
# twomass_psc table does not have pmra and pmdec.
query = (CatalogToTIC_v8.select(
CatalogToTIC_v8.catalogid,
TwoMassPSC.designation.alias('twomass_psc_designation'),
TwoMassPSC.ra.alias('twomass_psc_ra'),
TwoMassPSC.decl.alias('twomass_psc_dec'),
TwoMassPSC.j_m.alias('twomass_psc_j_m'),
TwoMassPSC.h_m.alias('twomass_psc_h_m'),
TwoMassPSC.k_m.alias('twomass_psc_k_m')).join(
TIC_v8, on=(CatalogToTIC_v8.target_id == TIC_v8.id)).join(
TwoMassPSC,
on=(TIC_v8.twomass_psc == TwoMassPSC.designation)).where(
CatalogToTIC_v8.version_id == version_id,
CatalogToTIC_v8.best >> True, TwoMassPSC.h_m < 7))
if query_region:
query = (query.join_from(CatalogToTIC_v8, 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):
query = (CatalogToTIC_v8.select(
CatalogToTIC_v8.catalogid, TwoMassPSC.j_m,
TwoMassPSC.h_m.alias('twomass_psc_h_m'), TwoMassPSC.k_m,
Gaia_DR2.source_id, Gaia_DR2.ra.alias('gaia_dr2_ra'),
Gaia_DR2.dec.alias('gaia_dr2_dec'), TwoMassPSC.pts_key,
TwoMassPSC.designation.alias('twomass_psc_designation'),
Gaia_DR2.phot_g_mean_mag.alias('gaia_dr2_g'),
Gaia_DR2.phot_bp_mean_mag, Gaia_DR2.phot_rp_mean_mag,
Gaia_DR2.parallax, Gaia_DR2.pmra, Gaia_DR2.pmdec,
EROSITASupersetStars.target_priority,
EROSITASupersetStars.ero_detuid,
EROSITASupersetStars.xmatch_metric, EROSITASupersetStars.ero_flux,
EROSITASupersetStars.ero_ra, EROSITASupersetStars.ero_dec,
EROSITASupersetStars.opt_ra,
EROSITASupersetStars.opt_dec).distinct(
CatalogToTIC_v8.catalogid).join(
TIC_v8, on=(CatalogToTIC_v8.target_id == TIC_v8.id)).join(
Gaia_DR2, on=(TIC_v8.gaia_int == Gaia_DR2.source_id)).
switch(TIC_v8).join(
TwoMassPSC,
peewee.JOIN.LEFT_OUTER,
on=(TIC_v8.twomass_psc == TwoMassPSC.designation
)).switch(Gaia_DR2).join(
EROSITASupersetStars,
on=(Gaia_DR2.source_id ==
EROSITASupersetStars.gaia_dr2_id)).where(
CatalogToTIC_v8.version_id == version_id,
CatalogToTIC_v8.best >> True,
EROSITASupersetStars.target_priority == 1,
EROSITASupersetStars.xmatch_metric > 0.5))
# Gaia_DR2 peewee model class corresponds to
# table catalogdb.gaia_dr2_source.
#
# All values of TIC_v8.plx (for non-null entries) are not the same as
# values of Gaia_DR2.parallax.
# Hence, in the above query, we cannot use TIC_v8.plx instead
# of Gaia_DR2.parallax.
if query_region:
query = (query.join_from(CatalogToTIC_v8, 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):
query = (CatalogToTIC_v8.select(
CatalogToTIC_v8.catalogid, CataclysmicVariables.source_id,
CataclysmicVariables.phot_g_mean_mag,
TIC_v8.hmag.alias('h')).join(TIC_v8).join(
CataclysmicVariables,
on=(CataclysmicVariables.source_id == TIC_v8.gaia_int)).where(
CatalogToTIC_v8.version_id == version_id,
CatalogToTIC_v8.best >> True))
if query_region:
query = (query.join_from(CatalogToTIC_v8, 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):
query = (CatalogToTIC_v8.select(
CatalogToTIC_v8.catalogid, Gaia_DR2.source_id,
Gaia_DR2.ra.alias('gaia_dr2_ra'),
Gaia_DR2.dec.alias('gaia_dr2_dec'),
Gaia_DR2.phot_g_mean_mag.alias('gaia_dr2_g'),
Gaia_DR2.phot_bp_mean_mag, Gaia_DR2.phot_rp_mean_mag,
Gaia_DR2.parallax, Gaia_DR2.pmra, Gaia_DR2.pmdec,
EROSITASupersetCompactobjects.target_priority,
EROSITASupersetCompactobjects.ero_detuid,
EROSITASupersetCompactobjects.xmatch_metric,
EROSITASupersetCompactobjects.ero_flux,
EROSITASupersetCompactobjects.ero_ra,
EROSITASupersetCompactobjects.ero_dec,
EROSITASupersetCompactobjects.opt_ra,
EROSITASupersetCompactobjects.opt_dec).distinct(
CatalogToTIC_v8.catalogid).join(
TIC_v8, on=(CatalogToTIC_v8.target_id == TIC_v8.id)).join(
Gaia_DR2, on=(TIC_v8.gaia_int == Gaia_DR2.source_id)).
join(EROSITASupersetCompactobjects,
on=(Gaia_DR2.source_id ==
EROSITASupersetCompactobjects.gaia_dr2_id)).where(
CatalogToTIC_v8.version_id == version_id,
CatalogToTIC_v8.best >> True,
EROSITASupersetCompactobjects.xmatch_version ==
'ASJK_0212020_select2univ'))
# Gaia_DR2 peewee model class corresponds to
# table catalogdb.gaia_dr2_source.
#
# All values of TIC_v8.plx (for non-null entries) are not the same as
# values of Gaia_DR2.parallax.
# Hence, in the above query, we cannot use TIC_v8.plx instead
# of Gaia_DR2.parallax.
if query_region:
query = (query.join_from(CatalogToTIC_v8, 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):
query = (CatalogToTIC_v8.select(
CatalogToTIC_v8.catalogid, SkyMapperGaia.gaia_source_id,
Gaia_DR2.phot_g_mean_mag).join(TIC_v8).join(
SkyMapperGaia,
on=(TIC_v8.gaia_int == SkyMapperGaia.gaia_source_id)).join(
Gaia_DR2,
on=(Gaia_DR2.source_id == SkyMapperGaia.gaia_source_id
)).where(SkyMapperGaia.feh < -1.7,
CatalogToTIC_v8.version_id == version_id,
CatalogToTIC_v8.best >> True))
if query_region:
query = (query.join_from(CatalogToTIC_v8, 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):
# 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):
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 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