Querying databases

Tabular data can cover anything from an observatory log or list of spectra, to a source catalogue, to complicated lists of properties or simulated data. The **ADD LINK All-VO Scope provides simple position- or time-based searches but many archive databases support more detailed queries, for example ''find all data within a given region where (Bmag - Vmag > 1) and the source is point-like''. This has to be expressed in ADQL, a simplified version of SQL (Structured Query Language), with extensions necessary to support astronomy-specific terms. The AstroGrid Query Builder shows the columns present in the data and provides a tree interface or a validator to help you to build queries that conform to the ADQL syntax.

Queries from the VO Desktop

Select a database which supports ADQL, such as those in (** add VOExplorer help link) VOExplorer Examples: Queryable database examples. When you choose a suitable catalogue, e.g. 2MASS, a button appears on the left to Build ADQL. This brings up the **LINK TO HELP Task Runner configured to query your chosen data set.

You can construct a query in two ways. 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 lower panel lets you type in the query as a string - this assumes familiarity with SQL and the dataset one is querying (such as by using the exact column names given in the right-hand panel). If you type the query freehand, you should then click on Validate which should construct the corresponding tree view (or give an error message). You can mix and match as long as you highlight the part of the Tree you are editing (highlight the topmost node Select to enter the entire query freehand) and validate after every free text editing before Tree editing.

Once you have finished building the query, optionally choose a MySpace for the Result (top centre) and then Execute (top right). If you have not chosen an output file, the result will be returned into memory. Tip: This is only suitable for small files; save large area searches to MySpace.. You can then view the output in TopCat or any suitable application.

Some basic ADQL queries

Here are some examples of valid ADQL queries.

Tip: 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 TOP 100 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) and (o.j_m - o.k_m) < 1.0

  This selects the first 100 columns from the 2MASS point source catalogue within a box bounded by blc (56.25, 23.6167) and trc (57.25, 24.6167), where Jmag - Kmag < 1.0

  Note that position constraints must be in the same units (here, decimal degrees) and coordinate frame as the columns in question. Tip: TOP 100 or some other small number is useful for testing without getting a very large data set back.

• 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)

3. Building a query in tree view

As an example of usage of the query builder in tree view, we will build a simple query:

Select Build ADQL for 2MASS using VOExplorer Examples: Queryable database examples as described above. The Table tab lists the three tables associated with this dataset, twomass_psc, twomass_scn and twomass_xsc. Choose twomass_psc, which causes the list of column names to be displayed along with their UCDs, Units, Types and Descriptions.

** REPLACE WITH NEW

The query builder opens with the elements Select, Items and From already in the tree. You can click on any node to expand or collapse it. 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).

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

The default is to return all columns (Items *). To specify which columns you want to retrieve, highlight Items, then go to the right-hand panel list of columns. Right-click on the first column which you want, then CNTRL-click (or equivalent for your system) to select further columns; a series of adjacent columns may be selected by SHIFT-click. Alternatively, in the tree, right-click on Items and under Insert choose Columns, the table name and select the name of the column required (ra, dec, etc.) .

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 example 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 right hand panel, right-click and choose Insert 1 reference into .... (or use the pull-down menu in the tree, as for Items). 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 and then Microedit to type in the required value e.g. 56.25.

Finally, insert a Top statement into Select. This is particularly useful for complex or large queries to test whether they are returning what you expect.

Authors: Silvia Dalla and Anita Richards

Last revised : 23 Nov 2007