SDSS hosts a SQL database containing all of the summary files for each data release with a web interface called CasJobs. You will need to create a free account to use CasJobs. CasJobs provides significant documentation and a help desk, but as part of the larger effort to expand tutorials in DR19 and highlight newer data products, a number of example SQL queries are provided below. These queries are designed to cover a wide variety of tables and joins; users should feel free to combine lines from different queries and experiment.
As a general note, there are almost always many ways to write a SQL query. The queries below reflect some of the style choices of the SDSS developers who wrote them. Users should feel free to modify queries and apply their own conventions.
These queries are designed to work in the DR19 context. More sample queries are available on the SkyServer Sample Queries page.
“How can I find the target priorities associated with each carton?” One possible answer:
WITH cart_cte AS (
SELECT distinct c2t.priority, cart.carton_pk, cart.carton
FROM mos_carton_to_target c2t
JOIN mos_carton cart
ON c2t.carton_pk = cart.carton_pk
)
SELECT carton, STRING_AGG(priority, ',') FROM cart_cte
GROUP BY carton
INTO mydb.carton_pri;“How many targets from the bhm_csc_apogee carton are available?”:
SELECT COUNT(1) FROM mos_carton_to_target
WHERE carton_pk =
(SELECT max(carton_pk) FROM mos_carton WHERE carton = 'bhm_csc_apogee');“How many targets from the bhm_csc_apogee carton were assigned?” One possible answer:
WITH max_carton_pk_cte AS (
SELECT MAX(carton_pk) AS max_carton_pk FROM mos_carton WHERE carton = 'bhm_csc_apogee'
)
SELECT COUNT(distinct alpha.carton_to_target_pk) FROM
mos_carton_to_target alpha
INNER JOIN
mos_assignment beta
ON alpha.carton_to_target_pk = beta.carton_to_target_pk
WHERE alpha.carton_pk =
(SELECT max_carton_pk FROM max_carton_pk_cte);“I’d like to know if a particular RA/Dec has been observed (i.e. a cone search)”:
SELECT
t.target_pk, d2s.mjd
FROM mos_target t
JOIN dbo.fGetNearbyMosTargetEq(16.1412,-2.1128,10) n ON n.target_pk = t.target_pk
JOIN mos_carton_to_target c2t ON t.target_pk = c2t.target_pk
JOIN mos_assignment assn ON assn.carton_to_target_pk = c2t.carton_to_target_pk
JOIN mos_opsdb_apo_design_to_status d2s ON d2s.design_id = assn.design_id
JOIN mos_opsdb_apo_completion_status stat ON stat.pk = d2s.completion_status_pk
WHERE stat.label = 'done';Note that cone searches of this format have arguments (ra, dec, radius) where ra and dec are in degrees and radius is in arcminutes.
“I have a Gaia DR2 ID, has that star been observed?”
SELECT
tic.gaia, t.target_pk, d2s.mjd
FROM mos_target t
JOIN mos_catalog cat ON t.catalogid = cat.catalogid
JOIN mos_catalog_to_tic_v8 c2tic ON cat.catalogid = c2tic.catalogid
JOIN mos_tic_v8 tic ON c2tic.target_id = tic.id
JOIN mos_carton_to_target c2t ON t.target_pk = c2t.target_pk
JOIN mos_assignment assn ON assn.carton_to_target_pk = c2t.carton_to_target_pk
JOIN mos_opsdb_apo_design_to_status d2s ON d2s.design_id = assn.design_id
JOIN mos_opsdb_apo_completion_status stat ON stat.pk = d2s.completion_status_pk
WHERE stat.label = 'done' AND tic.gaia = 2240412966327987584;“I have a list of IDs (or a small catalog) that I’d like to upload to query against.” To do this, you need to use the “mydb” feature. The SQL below assumes mytable and mytable_out do not already exist; if they do you’ll need to delete them from mydb or call them something different. For example purposes, we’ll create a “test” mytable for demonstration, but in practice you can of course upload your own.
SELECT top 10000 source_id INTO mydb.mytable FROM mos_gaia_dr2_source ORDER BY newid();
SELECT mytable.source_id into mydb.mytable_out
FROM mydb.mytable mytable
INNER JOIN
mos_catalog_to_gaia_dr2_source c2g2
ON mytable.source_id = c2g2.target_id
INNER JOIN
mos_target target
ON c2g2.catalogid = target.catalogid
INNER JOIN
mos_carton_to_target c2t
ON target.target_pk = c2t.target_pk
INNER JOIN mos_assignment assignment
ON assignment.carton_to_target_pk = c2t.carton_to_target_pk
INNER JOIN mos_opsdb_apo_design_to_status d2s
ON d2s.design_id = assignment.design_id
INNER JOIN mos_opsdb_apo_completion_status status
ON status.pk = d2s.completion_status_pk
WHERE status.label = 'done';“How many target-observations are planned to be observed per carton?”
SELECT c.carton, COUNT(c2t.carton_to_target_pk)
FROM mos_carton_to_target c2t
JOIN mos_carton c on c.carton_pk = c2t.carton_pk
JOIN mos_assignment assn on assn.carton_to_target_pk = c2t.carton_to_target_pk
JOIN mos_design_to_field d2f on d2f.design_id = assn.design_id
JOIN mos_field f ON f.pk = d2f.field_pk
JOIN mos_targetdb_version v ON v.pk = f.version_pk
WHERE c.carton NOT LIKE '%ops%' AND v.planname = 'zeta-3'
group by c.carton;“How many targets from a given carton were observed on a given day?”
SELECT d2s.mjd, COUNT(c2t.carton_to_target_pk)
FROM mos_carton_to_target c2t
JOIN mos_carton c ON c.carton_pk = c2t.carton_pk
JOIN mos_assignment assn ON assn.carton_to_target_pk = c2t.carton_to_target_pk
JOIN mos_opsdb_apo_design_to_status d2s ON d2s.design_id = assn.design_id
JOIN mos_opsdb_apo_completion_status stat ON stat.pk = d2s.completion_status_pk
WHERE stat.label = 'done' AND c.carton = 'mwm_yso_nebula_apogee'
GROUP BY d2s.mjd;“How many observing epochs will my target get?”
SELECT distinct tic.gaia, fl.field_id, cad.nepochs, cad.label
FROM mos_carton_to_target c2t
JOIN mos_target tar ON c2t.target_pk = tar.target_pk
JOIN mos_catalog cat ON tar.catalogid = cat.catalogid
JOIN mos_catalog_to_tic_v8 c2tic ON cat.catalogid = c2tic.catalogid
JOIN mos_tic_v8 tic ON c2tic.target_id = tic.id
JOIN mos_assignment assi ON assi.carton_to_target_pk = c2t.carton_to_target_pk
JOIN mos_design_to_field d2f ON d2f.design_id = assi.design_id
JOIN mos_field fl ON fl.pk = d2f.field_pk
JOIN mos_cadence cad ON cad.pk = fl.cadence_pk
JOIN mos_targetdb_version v ON v.pk = fl.version_pk
WHERE tic.gaia = 4802814987974575616 AND v.planname = 'zeta-3';“What do the pipelines report for my carton?”
SELECT lite.sdss_id, lite.fe_h, lite.mg_h
FROM lite_all_star lite
JOIN mos_sdss_id_stacked stacked ON stacked.sdss_id = lite.sdss_id
JOIN mos_target targ ON targ.catalogid = stacked.catalogid25
JOIN mos_carton_to_target c2t ON c2t.target_pk = targ.target_pk
JOIN mos_carton cart ON cart.carton_pk = c2t.carton_pk
WHERE cart.carton = 'mwm_snc_100pc_apogee';lite_all_star is only one of many catalogs; queries like this may be helpful for any table with an sdss_id column.