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

iDR2: Simple Example Queries

This document pertains to releases iDR2 and iDR2iDR3 of the GES archive. It should not be used with earlier releases of the archive; some aspects of the discussion and examples are incorrect for previous releases.

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, homogenised parameters

    SELECT * FROM RecommendedAstroAnalysis;
              

    This query lists all the recommended, homogenised physical parameters (effective surface temperature, log surface gravity etc) and element abundances for stars in the GES archive as determined by working group 15 ('WG15'). It is Consortium policy that these WG15 parameters and abundances must be used in all GES publications and calculations leading to such publications.

    As formulated, the query returns all the columns of recommended, homogenised 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. Aside on sets of physical parameters and abundances in the GES Archive

    Though the recommended physical parameters and abundances, as homogenised by WG15, are the set that you are most likely to use, the archive contains several other sets produced by the GES Consortium, which you may need for some purposes. Like the WG15 homogenised set each of these sets is available as a view in the database. The following sets are available:

    View Description of set of parameters
    RecommendedAstroAnalysis Overall, recommended, homogenised WG15 parameters and abundances.
    WgRecommendedAstroAnalysis Recommended parameters and abundances produced by individual working groups (for working groups WG10 to WG14).
    WpNaAstroAnalysis Abundances produced by individual nodes within each of the working groups using their parent working group's recommended astrophysical parameters (for working groups WG10 to WG13).
    NpNaAstroAnalysis Physical parameters and abundances determined independently by the individual nodes within each of the working groups (again for working groups WG10 to WG13).

    Please remember that it is Consortium policy that only WG15 recommended, homogenised parameters are used in publications.

  3. List the working group recommended parameters

    SELECT TOP 10 * FROM WgRecommendedAstroAnalysis;
              

    Lists the recommended physical parameters and abundances produced by working groups 10 - 14.

  4. List node abundances determined with working group parameters

    SELECT TOP 10 * FROM WpNaAstroAnalysis;
              

    Lists the abundances determined by individual nodes by adopting their parent working group's physical parameters. Note that these values are only available for some nodes of some working groups.

  5. List node parameters and abundances

    SELECT TOP 10 * FROM NpNaAstroAnalysis;
              

    Lists the physical parameters and abundances determined independently by individual nodes within each of the working groups. Note that these values are available for all nodes within working groups WG10 to WG13.

  6. 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 WgRecommendedAstroAnalysis;
    
    SELECT COUNT(*) FROM WpNaAstroAnalysis;
    
    SELECT COUNT(*) FROM NpNaAstroAnalysis;
              

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 homogenised, recommended parameters for a given star

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

    View 'RecommendedAstroAnalysis' contains the WG15 homogenised parameters.

  4. List the working group recommended parameters for all the observations of a given star

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

    View 'WgRecommendedAstroAnalysis' 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 WgRecommendedAstroAnalysis
    WHERE cName='08091845-4653440'
      AND wg='WG12';
              
  5. List the parameters determined by all the individual nodes of a given working group for all the observations of a given star

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

    The parameters determined by the various individual nodes of the working group are listed. In this case view 'NpNaAstroAnalysis' is being searched to list the values found when the nodes were determining both the stellar parameters (effective temperature, surface gravity etc.) as well as elemental abundances. These values will always be available. In some cases the nodes also determined a second set of abundances by adopting the recommended effective temperature etc. of their working group. These values are available in view 'WpNaAstroAnalysis' which can be queried in the same way:

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

    (In this case WG12 did not record any 'WpNaAstroAnalysis' values.)

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

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

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

  7. 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.

  8. 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
13/2/2015