Querying databases1. Background

Within the context of the Virtual Observatory, queries need to be formulated in ADQL, a query language based on SQL, with extensions necessary to support astronomy specific queries. The Workbench Query Builder allows you to build queries that conform to the ADQL syntax, and to visualise the metadata associated to the specific dataset you want to query. The Query Builder window will open from the Task Launcher whenever an application that executes ADQL queries is selected.

For use in the VO Desktop (2008) see Querying databases

The Query Builder has 3 possible views (different tabs in the window): Tree, Adql/s and Adql/x. In the Tree view the query appears as a tree of elements that together construct the query: at each given position in the tree, the builder will let you insert only those building blocks that will make the query conform to ADQL syntax. The Adql/s view allows you to type in the query as a string - this assumes familiarity with SQL and the dataset one is querying (e.g. by using the exact column names given in the right-hand panel). Finally, the Adql/x view shows the xml corresponding to a query built in one of the previous two views.

2. Some basic ADQL queries

Below are some examples of valid ADQL queries in Adql/s format.

Important: When using the ADQL/S pane of the query builder and due to the standard language used (ADQL), Column names which are reserved words such as "dec" or which begin with a non-alphabetical character should be enclosed in inverted commas e.g. SELECT * FROM table AS t WHERE t."dec" > 10.0 AND t."2mass" < 17

• SELECT * FROM usnob_psc as u where u.B2Mag > 21

  This means, select all columns from the USNO-B catalogue (which has to be given an alias, in this case u) where the column labelled B2Mag is greater than 21. Note that this should return only the few very faintest sources in the catalogue. The whole USNO-B catalogue is >109 sources, so be careful when querying such large data sets.

• SELECT u.seqNo, u.ra, u.dec, u.B2Mag FROM usnob_psc as u where u.B2Mag > 21

  This will return just 3 columns, labelled seqNo, ra and dec, form USNO-B, where B2Mag is greater than 21.

• SELECT * FROM twomass_psc as o where (o.ra > 56.25) and (o.ra < 57.25) and (o.dec > 23.6167) and (o.dec < 24.6167)

  This selects all columns from the 2MASS point source catalogue within a box bounded by blc (56.25, 23.6167) and trc (57.25, 24.6167).

  Note that position constraints must be in the same units (here, decimal degrees) and coordinate frame as the columns in question.

• SELECT * FROM eitdata AS T1 where T1.DATE_OBS > '2002-07-28T01:00:00.000' and T1.DATE_OBS <= '2002-07-28T06:00:00.000' and T1.WAVELNTH = 195

  This select all columns for SOHO/EIT images at the specified wavelength and within the given times (note quotes).

• SELECT time_start, time_end, nar FROM goes_xray_flare AS s where s.nar > 9850 and s.nar < 9880 and s.xray_class > 'M5'

  Selects columns giving start time, end time and Active Region Number (nar) of flares in the GOES x-ray flare list with nar in the specified range and flare class above M5. (Solar Event Catalogue)

A few points to note regarding these queries: usnob_psc, twomass_psc, eitdata and goes_xray_flare are names of tables associated to specific datasets or catalogues. B2Mag, ra, dec, DATE_OBS WAVELNTH and time_start are names of columns (Column references) in the above tables.

The Query Builder in tree view allows you to visualise which tables are associated to a specific dataset and which column references for a given table you can use in your query.

3. Building a query in tree view

As an example of usage of the query builder in tree view, we will build the second of the queries listed in Section 2: SELECT u.seqNo, u.ra, u.dec, u.B2Mag FROM usnob_psc as u where u.B2Mag > 21 .

Start the Task Launcher and search for usnob. Click on the names of the applications listed and find the one that requires an ADQL input. Select the application by clicking on the box next to its name. This will open the Query Builder.

There is one table associated with this dataset, called usnob_psc (as displayed under Overview on the right of the window). Click on it and the names of columns in the table (column references) will be listed under Name and their UCDs, Units, Types and Descriptions can be viewed.

The query builder opens with the elements Select, Items and From already in the tree. The general mouse behaviour in tree view is that you select a position in the tree and right-click to obtain a list of elements that can be inserted at that position (under Insert). You can use '+' and '-' to expand or collapse the information associated to each element.

Start by specifying which table should be used in the query: right click on From, choose Insert and then the table name, in the example usnob_psc.

To specify which columns you want to retrieve, click on Items, right-click and under Insert choose Columns, the table name and select the name of the column required (in the example, seqNo, ra, dec, B2Mag - repeat the step for each column).

Now highlight the top of the tree (Select), right click and choose to insert Where. The next step will be to specify which operator needs to be used in the Where. Click on Where, right-click and under Insert choose the operator. For the example USNOB query, choose Comparison and >. To add the column name that should be added to the left of the operator, click on the name of the column in the rhs panel, right-click and choose Insert reference into .... Next specify what goes to the right of the operator (assuming it is a number or string), click on Comparison, right-click and choose to insert a Literal. To type in its value, highlight it, then press the Ctrl and space key simultaneously. You will find an edit box opens: type in the value (in the example, 21).

4. Inserting and editing a 'TOP' statement in tree view

A query like SELECT TOP 100 * FROM usnob_psc as u where u.B2Mag > 21 is very useful to test a query by returning a small number of rows (here, the first 100 with B2Mag > 21). This must be done in tree view (after typing the rest of the query as SQL, if wished. Right-click on Select at the top of the tree and chose Top from the drop-down menu.
Right-click on any image to open in full-size in a new window

If you then want to edit the number of rows returned or edit any other value in the Tree view, select the statement e.g. Top as shown above on the right and CNTRL-SHIFT. This allows you to edit the number; press RETURN when you are finished.

Last revised : 3 Nov 2006