Example SQL Queries for YSE-PZ

Below we include a few example queries for YSE-PZ. For a complete picture of the available tables and columns that can be used in queries like the ones below, go to http://127.0.0.1:8000/explorer/new/, and select the show schema button.

1. Every spectroscopically classified SN Ia in the last 30 days

SELECT t.name
FROM YSE_App_transient t
WHERE t.TNS_spec_class = 'SN Ia' AND DATEDIFF(CURDATE(),t.disc_date) < 30

To include peculiar SNe Ia, the easiest way is to modify the WHERE statement to t.TNS_spec_class LIKE 'SN Ia%'.

2. Every SN that has been tagged as Young

SELECT t.name
FROM YSE_App_transient t
INNER JOIN YSE_App_transient_tags tt ON tt.transient_id = t.id
INNER JOIN YSE_App_transienttag tg ON tg.id = tt.transienttag_id
WHERE tg.name = 'Young'

3. Every SN where the most recent magnitude is brighter than 18

SELECT t.name, pd.mag
 FROM YSE_App_transient t, YSE_App_transientphotdata pd, YSE_App_transientphotometry p
 WHERE pd.photometry_id = p.id AND pd.mag < 18 AND
 pd.id = (
       SELECT pd2.id FROM YSE_App_transientphotdata pd2, YSE_App_transientphotometry p2
       WHERE pd2.photometry_id = p2.id AND p2.transient_id = t.id AND ISNULL(pd2.data_quality_id) = True
       ORDER BY pd2.obs_date DESC
       LIMIT 1
   )

4. Every SN where the peak magnitude is brighter than 18

SELECT DISTINCT t.name, pd.mag, t.ra, t.dec
FROM YSE_App_transient t, YSE_App_transientphotdata pd, YSE_App_transientphotometry p, YSE_App_transient_tags tt, YSE_App_transienttag tg
WHERE pd.photometry_id = p.id AND pd.mag < 18.6 AND
pd.id = (
   SELECT pd2.id FROM YSE_App_transientphotdata pd2, YSE_App_transientphotometry p2
   WHERE pd2.photometry_id = p2.id AND p2.transient_id = t.id AND
   ISNULL(pd2.mag) = False
   ORDER BY pd2.mag ASC
   LIMIT 1
)
LIMIT 10

5. All transients with host galaxy data

SELECT t.name, t.ra, t.dec, t.disc_date, t.redshift,
t.TNS_spec_class AS 'classification',
h.ra AS host_RA, h.dec AS host_Dec, h.name as host_name,
h.redshift AS host_z

FROM YSE_App_transient t
INNER JOIN YSE_App_host h ON h.id = t.host_id

/*Query from Alex Gagliano*/

6. Transients Observed (S/N > 3) by PS1 or PS2

SELECT DISTINCT t.name,
                t.ra,
                t.dec
FROM YSE_App_transient t
WHERE t.id IN
(SELECT DISTINCT t.id
FROM YSE_App_transient t
INNER JOIN YSE_App_transientphotometry tp ON t.id = tp.transient_id
INNER JOIN YSE_App_transientphotdata tpd ON tp.id = tpd.photometry_id
INNER JOIN YSE_App_instrument i ON i.id = tp.instrument_id
WHERE (i.name = "GPC1" or i.name = 'GPC2')
AND tpd.flux/tpd.flux_err > 3
GROUP BY t.id
HAVING count(t.id) >= 1)

7. Every Instrument/Telescope/Observatory in YSE_PZ

SELECT i.name as instrument_name,
       t.name as telescope_name,
       o.name as observatory_name
FROM YSE_App_instrument i, YSE_App_telescope t, YSE_App_observatory o
WHERE i.telescope_id = t.id AND t.observatory_id = o.id

8. All Transients with Status of “FollowupRequested”

SELECT t.name,
       t.ra,
       t.dec,
       ts.name,
       t.disc_date AS disc_date,

FROM YSE_App_transient t
INNER JOIN YSE_App_transientstatus ts ON ts.id = t.status_id
WHERE ts.name = 'FollowupRequested'

/*Query from Cesar Rojas-Bravo*/

You can use OR syntax to include multiple statuses in the query (ts.name = 'FollowupRequested' or ts.name = 'Following')

9. Coordinate Query: All Transients Near Virgo

SELECT t.name
FROM YSE_App_transient t

WHERE
/* using approx Virgo coords from YSE observations */
t.ra > 187.7059-1.65 AND t.ra < 187.7059+1.65 AND
t.dec > 12.391-1.65 AND t.dec < 12.391+1.65

10. Every Transient within 30 arcsec of another Transient

SELECT DISTINCT t.name as first_transient, t2.name as second_transient
FROM YSE_App_transient t, YSE_App_transient t2

WHERE t2.id = (
SELECT t2.id
FROM YSE_App_transient t2
WHERE t2.ra > t.ra - 0.008/COS(t.dec*0.017) AND t2.ra < t.ra + 0.008/COS(t.dec*0.017) AND
t2.dec > t.dec - 0.008 AND t2.dec < t.dec + 0.008 AND t.name != t2.name
LIMIT 1)
LIMIT 10
/*runs pretty slowly, adjust limit as needed*/

11. Survey Observations

SELECT f.field_id,
       f.ra_cen,
       f.dec_cen,
       f.width_deg,
       s.obs_mjd,
       s.pos_angle_deg,
       s.airmass,
       s.image_id,
       s.survey_field_id,
       s.mag_lim
FROM YSE_App_surveyfield f
JOIN YSE_App_surveyobservation s ON s.survey_field_id = f.id
WHERE s.obs_mjd != 'None'
/* Query from Chris Carroll, Vivienne Baldassare */

12. Transients Scheduled for Follow-up on Keck in the last two weeks

SELECT t.name
FROM YSE_App_transient t
JOIN YSE_App_transientfollowup tf ON t.id = tf.transient_id
JOIN YSE_App_classicalresource rc ON rc.id = tf.classical_resource_id
JOIN YSE_App_telescope tl ON tl.id = rc.telescope_id

WHERE tl.name LIKE 'Keck%' and DATEDIFF(CURDATE(),tf.valid_start) < 14

13. Spectroscopic Observations for Transients from 2021

SELECT t.name,
       t.ra AS transient_RA,
       t.dec AS transient_Dec,
       t.non_detect_date AS non_detect_date,
       t.disc_date AS disc_date,
       spec.obs_date AS spec_date,
       DATEDIFF(spec.obs_date, disc_date) AS spec_epoch,
       t.TNS_spec_class AS spec_class,
       t.redshift AS transient_z,
       t.non_detect_limit,
       t.mw_ebv,
       spec.obs_date

FROM YSE_App_transient t, YSE_App_transientspectrum spec

WHERE spec.transient_id = t.id
AND t.name LIKE "2021%"
/* Query from Kaew Tinyanont */

14. Every SN within 40 kpc of a z < 0.01 host galaxy

SELECT t.name,
     t.ra AS transient_RA,
     t.`dec` AS transient_Dec,
     t.TNS_spec_class AS spec_class,
     t.redshift AS transient_z,
     h.ra AS host_RA,
     h.`dec` AS host_Dec,
     h.redshift AS host_z,
     DEGREES(ACOS(SIN(RADIANS(t.`dec`))*SIN(RADIANS(h.`dec`)) + COS(RADIANS(t.`dec`))*COS(RADIANS(h.`dec`))*COS(RADIANS(ABS(t.ra - h.ra)))))*3600 AS AngSepArcSec,
     (3e+5*COALESCE(t.redshift, h.redshift)/73) AS LuminosityDistanceMpc,
     (3e+5*COALESCE(t.redshift, h.redshift)/73)/POW((1.0 + COALESCE(t.redshift, h.redshift)), 2) AS AngularDiameterDistanceMpc,
     (ACOS(SIN(RADIANS(t.`dec`))*SIN(RADIANS(h.`dec`)) + COS(RADIANS(t.`dec`))*COS(RADIANS(h.`dec`))*COS(RADIANS(ABS(t.ra - h.ra))))*(3e+5*COALESCE(t.redshift, h.redshift)/73)/POW((1.0 + COALESCE(t.redshift, h.redshift)), 2)*1000) AS ProjectedDistKpc
FROM YSE_App_transient t
INNER JOIN YSE_App_host h ON h.id = t.host_id
WHERE t.host_id IS NOT NULL
AND (t.redshift
OR h.redshift) IS NOT NULL
AND COALESCE(t.redshift, h.redshift) > 0.028
AND COALESCE(t.redshift, h.redshift) < 0.032
AND t.TNS_spec_class = "SN Ia"
AND (ACOS(SIN(RADIANS(t.`dec`))*SIN(RADIANS(h.`dec`)) + COS(RADIANS(t.`dec`))*COS(RADIANS(h.`dec`))*COS(RADIANS(ABS(t.ra - h.ra))))*(3e+5*COALESCE(t.redshift, h.redshift)/73)/POW((1.0 + COALESCE(t.redshift, h.redshift)), 2)*1000) < 40;

/*Query from Dave Coulter*/