ges logo
GES Home
Start Here
Data Overview
Known Issues
the Surveys
Schema browser
Data access
Login
Freeform SQL
Example Queries
Q&A
Glossary
Release History
Gallery
Publications
Monitor
Downtime
Links
ges logo bottom
IFA     ROE
Home | Overview | Browser | Access | Login | Examples 
  ges logo

iDR1: Simple Example Queries

This document pertains to release iDR1 of the GES archive.

This page gives a few simple introductory examples of querying the archive. It is divided into two sections:

Start here: very simple examples

  1. List the recommended parameters

    SELECT * FROM RecommendedAstroAnalysis;
              

    This query lists all the physical parameters (effective surface temperature, log surface gravity etc) and element abundances for stars in the GES archive as recommended by working groups WG10 to WG13.

    As formulated, the query returns all the columns of recommended parameters and abundances for the all survey stars for which they are tabulated. However, it is usually neither efficient nor useful to query the entire contents of a table or view. For the purposes of these first, introductory examples the queries will be changed to return just a few rows for example:

    SELECT TOP 10 * FROM RecommendedAstroAnalysis;
              

    which returns just ten rows. This form will be used in the remaining examples in this section (the next section gives some more practical examples of selecting the rows to be listed).

  2. List all working group recommended and node parameters

    SELECT TOP 10 * FROM AstroAnalysis;
              

    Lists all the parameters and abundances determined by working groups WG10 to WG13, both each working group's recommended parameters and those determined by individual nodes.

  3. List the number of rows in each of the sets of parameters

    It is possible to modify the above queries to list the number of rows in each of the various sets of parameters and abundances:

    SELECT COUNT(*) FROM RecommendedAstroAnalysis;
    
    SELECT COUNT(*) FROM AstroAnalysis;
              

Queries to show the data available for a star

The following examples present some simple queries to show the data available in various tables for the star whose 'cName' is '08091845-4653440'.

  1. Check whether a given star is in the database

    SELECT * FROM Target
    WHERE cName='08091845-4653440';
              

    The star is listed in the 'Target' table, so there are data for it in the GES archive.

  2. List all the spectra for a given star

    SELECT * FROM Spectrum
    WHERE cName='08091845-4653440';
              

    The star has only one entry in the 'Spectrum' table, so only a single spectrum of it has been obtained.

  3. List the recommended parameters for a given star

    SELECT * FROM RecommendedAstroAnalysis
    WHERE cName='08091845-4653440';
              

    View 'RecommendedAstroAnalysis' contains two entries for 08091845-4653440. One is the recommended parameters determined by WG10 and the other those determined by WG12. The query can be modified to return just the recommended parameters determined by one of the working groups, for example WG12:

    SELECT * FROM RecommendedAstroAnalysis
    WHERE cName='08091845-4653440'
      AND wg='WG12';
              
  4. List the recommended and individual node parameters determined by a given working group for all the observations of a given star

    SELECT * FROM AstroAnalysis
    WHERE cName='08091845-4653440'
      AND wg='WG12';
              

    Both the recommended parameters and those determined by the various individual nodes of the working group are listed.

  5. List the parameters determined by a given node for all the observations of a given star

    SELECT * FROM AstroAnalysis
    WHERE cName='08091845-4653440'
      AND wg='WG12'
      AND nodeName='Arcetri';
              

    Only the parameters determined by the WG12 Arcetri node are listed.

  6. List selected parameters for a given star

    All the previous examples have listed all the columns available in each of the tables or views. It is possible (and usually more useful) to list just selected columns:

    SELECT 
      cName, teff, logg
    FROM 
      RecommendedAstroAnalysis
    WHERE cName='08091845-4653440';
              

    Here just the 'cName', effective temperature and surface gravity are being listed.

  7. List non-default values for a chosen column

    Finally, though not an example of listing parameters for a given star, another useful technique is to just list stars with non-default values for some column. Recall that not all physical parameters and abundances were determined for all the stars in the survey. Where no value is available the archive stores a so-called default value, usually -9.999995e+08, chosen to be well outside the range of feasible values for the quantity being tabulated. To list only stars with actual determinations of some quantity (that is, non-default values for it) a query is constructed which includes a selection to exclude the default values. For example, to list stars with a non-default effective temperature:

    SELECT 
      TOP 20 cName, teff, logg
    FROM 
      RecommendedAstroAnalysis
    WHERE teff > -9.9e8;
              

    Here the query has been arbitrarily restricted to list just twenty stars. Similarly it is possible to list just the twenty stars with the hottest or coolest effective temperature. Hottest:

    SELECT 
      TOP 20 cName, teff, logg
    FROM 
      RecommendedAstroAnalysis
    WHERE teff > -9.9e8
    ORDER BY teff DESC;
              

    …and coolest:

    SELECT 
      TOP 20 cName, teff, logg
    FROM 
      RecommendedAstroAnalysis
    WHERE teff > -9.9e8
    ORDER BY teff ASC;
              



Home | Overview | Browser | Access | Login | Examples

Links | Credits

WFAU, Institute for Astronomy,
Royal Observatory, Blackford Hill
Edinburgh, EH9 3HJ, UK
Tel +44 131 668 8356 (office)
or +44 131 668 8100 (switchboard)

ges-support@roe.ac.uk
18/8/2014