Introduction to Using the GES Science Archive
=============================================

Workshop given on 2 December 2015 as part of 'GES 2015', the Gaia-ESO
Survey Third Science Meeting' held in Vilnius, Lithuania.

Crib-sheet of example queries used in the tutorial.


SQL: Querying the Archive
-------------------------
SELECT COUNT(*) FROM Target

SELECT * FROM Target

SELECT TOP 10 * FROM Target

SELECT TOP 10 cName, RA, Dec, bMag FROM Target

SELECT cName, RA, Dec, bMag FROM Target
WHERE bMag > 18.0 AND bMag < 18.5

SELECT cName, RA, Dec, bMag FROM Target
WHERE bMag BETWEEN 18.0 AND 18.5
ORDER BY bMag

SELECT cName, RA, Dec, bMag, bMag - rMag as BmR FROM Target
WHERE bMag - rMag BETWEEN 0.1 AND 0.2
ORDER BY bMag

Queries on Target Stars
-----------------------
SELECT * FROM Target WHERE cName = '11053303-7700120'

SELECT * FROM Target
WHERE cName IN ('11034945-7700101', '11044460-7706240')

SELECT * FROM Target
WHERE ra BETWEEN 70 AND 80 AND dec BETWEEN -45 AND -30

Queries on Spectra
------------------
SELECT * FROM Spectrum WHERE cName ='11053303-7700120'

SELECT cName, count(cName) AS num_of_spectra
FROM Spectrum
WHERE cName IN ('11034945-7700101', '11044460-7706240')
GROUP BY cName
ORDER BY cName

Joining tables on primary key: retrieving spectra download links
----------------------------------------------------------------
SELECT cName, rv, instrument, grating, fileName
FROM Spectrum, SpecFrame
WHERE cName IN ('11034945-7700101','11044460-7706240')
AND Spectrum.specFrameID=SpecFrame.specFrameID

SELECT TEff, A.FeH, S.FeH AS "FeH (Spectrum)", rv, snr, fileName
FROM RecommendedAstroAnalysis AS A, SpectrumGroup AS G, Spectrum AS S
WHERE A.specGroupId = G.specGroupId
  AND G.specId = S.specId
  AND A.cName = '11053303-7700120'

Downloading many spectra files at once
--------------------------------------
SELECT dbo.fWgetCmd(fileName) 
FROM RecommendedAstroAnalysis A, SpectrumGroup G 
WHERE fieldName="NGC2264" AND A.specGroupID=G.specGroupID 
  AND fileName LIKE '%GES%'

New SpectrumNightly table
-------------------------
SELECT cName, instrument, grating, 
CASE WHEN nDispElems<60000 THEN 'U' ELSE 'L' END AS "UVES CCD", 
spec.rv as "Target Average RV", sn.rv as "Nightly Average RV"
FROM Spectrum spec, SpectrumNightly sn, SpecFrame frame
WHERE spec.specID = sn.specID AND frame.specFrameID=spec.specFrameID 
  AND spec.rv > -1000 AND sn.rv > -1000
ORDER BY cName

Cross-linking with other surveys
--------------------------------
SELECT GES.targetID, VHS.sourceID, VHS.jmksPnt
FROM Target AS GES, VHSDR1..vhsSource AS VHS, TargetXvhsSource 
WHERE masterObjID=GES.targetID AND slaveObjID=VHS.sourceID AND distanceMins<0.1


SELECT GES.targetID, VHS.sourceID, VHS.jmksPnt
FROM Target AS GES, VHSDR1..vhsSource AS VHS, 
     TargetXvhsSource AS X 
WHERE masterObjID=GES.targetID AND slaveObjID=VHS.sourceID 
  AND distanceMins IN (SELECT MIN(distanceMins) FROM TargetXvhsSource 
                        WHERE masterObjID=X.masterObjID)
  
Finding all analyses of particular group of spectra
---------------------------------------------------
SELECT cName, wg, TEff, peculi
  FROM AstroAnalysis
 WHERE nodeID=1 AND specGroupID=879
 
SELECT cName, wg, nodeName, TEff, peculi
  FROM AstroAnalysis
 WHERE specGroupID=879

Comparing analyses between multiple working groups with a self-join
-------------------------------------------------------------------
SELECT WG10.TEff AS 'WG10 TEff', WG12.TEff AS 'WG12.TEff', 
       WG10.peculi AS 'WG10.peculi', WG12.peculi as 'WG12.peculi'
  FROM WgRecommendedAstroAnalysis AS WG10, WgRecommendedAstroAnalysis AS WG12
 WHERE WG10.wg='WG10' AND WG12.wg='WG12' AND WG10.specGroupID=WG12.specGroupID
       AND WG10.cName='17463553+0531076'
  
Looking for peculiar objects' spectra using LIKE
------------------------------------------------
SELECT DISTINCT(WG14.specID), WG15.specGroupID, WG15.cName, WG15.Teff, 
       WG15.peculi AS 'wg15_peculi', WG14.peculi AS 'wg14_peculi', WG14.fileName 
  FROM RecommendedAstroAnalysis AS WG15, SpectrumOutlierAnalysis AS WG14, 
       SpectrumGroup
 WHERE WG15.specGroupID=SpectrumGroup.specGroupID AND 
       WG14.specID=SpectrumGroup.specID AND 
       WG15.peculi LIKE '%2%c%' AND WG14.peculi LIKE '%2%a%' AND 
       WG14.fileName LIKE '%ges%' AND WG15.TEff > 0
ORDER BY WG15.specGroupID

Default row example
-------------------
SELECT A.specGroupID, A.cName, TEff, peculi, G.specID, ManySpec.fileName 
  FROM RecommendedAstroAnalysis A, SpectrumGroup G, Spectrum S, 
       SpecFrame ManySpec 
 WHERE A.specGroupID=G.specGroupID AND G.specID=S.specID AND 
       S.specFrameID=ManySpec.specFrameID AND A.specGroupID BETWEEN 364 AND 373

TOPCAT Plotting
---------------
SELECT TEff, logG, FeH, fieldName
FROM RecommendedAstroAnalysis
WHERE TEff > 0 AND logG > 0 AND FeH > -10

Contact for queries about using the GES archive: ges-support@roe.ac.uk

Ross Collins
1/12/2015