Query Editor Contents

Introduction

The Query Editor is used to build queries in ADQL format. These queries can be saved in MySpace and later inserted into a workflow for submittal against an appropriate data collection. Saved queries can also be reloaded from MySpace into the Query Editor for further editing.



ADQL stands for Astronomical Data Query Language and is a variant of SQL, which is the de facto query language for relational databases. ADQL is held internally in XML format, but unless you are specifically interested in XML there is no immediate reason why you should follow this up. The Query Editor provides a textual version of ADQL that is converted to its XML format behind the scenes.



The editor page has three parts:

  • A text area where editing takes place. Some basic controls are present here and a few simple examples that can be cut-and-pasted into the text area itself.
  • A series of ADQL helper buttons below the text area. There are helpers for a range of ADQL keywords and symbols. If you click on the keyword/symbol itself, then it is pasted into the text area. If you click on the prompt next to the keyword, a small dialogue is popped up giving usage and an example.
  • A box is present on the right hand side that initially contains basic instructions of how to use the editor. Column meta data is displayed here if the registry is used to select a table from an astronomical collection. A user can then choose appropriate columns to include in the query. The facility is triggered by the "Select a Table" button in the text area, which displays a powerful registry search. This is one of Astrogrid's so-called micro-browsers, where a main functional part of the VO is subsumed to provide a service for another functional part. It is a powerful adjunct to the Query Editor.

Go to Contents

Text Area

Text Box

The text box is where the query is entered either by typing or by cut-and-paste. If you load a previously saved query from MySpace it will be presented in this area ready for re-editing. The box will start with the text "This is where the query should go" if you have just begun an editing session. Unless you save a query to MySpace the query will be lost when you logoff or indeed if you lose a session inadvertently (session timeout or some network outage) so it is a good idea to save your work every so often. (Jeff NB: It also seems to lose it when you move away to another page: I'm raising this as a bug.)

Controls

  • Mouse Button Right-Click. The edit box is a standard editing window and so supports the standard editing controls supported by clicking the right mouse button (undo, cut, copy, paste, delete, select all).
  • Size. Queries can be complicated and long (I suggest you begin with a simple one!). The edit box initially shows 10 lines, but you can also alter the area of visibility by the controls down the right hand side (from 5 to 50 lines). The box itself is scrollable, so this is simply a convenience.
  • Clear. You can clear the box at any time. Take care - this action is not subject to undo.
  • Help. The help gizmo (show the button here) when used provides some basic help on using the editor
  • Load from MySpace. If you wish to load a query from MySpace, this button will provide a MySpace micro-browser which can be used to select the file. Any existing display in the edit box will be lost (and any associated display of metadata), so be sure you first save your previous work.
  • Save to MySpace. Any query that you wish to save can be saved using this button (Associated metadata is also saved). A MySpace micro-browser is provided for the purpose.
  • Select a Table. This button displays a powerful catalog search facility.

Examples

There are five basic examples to show. Clicking on each example will popup a window showing a piece of ADQL. The text can be copy-and-pasted into the edit box (triple clicking with the left mouse button will also copy the text into the clipboard ready for pasting). Before pasting an example you might need to clear the edit box.

Go to Contents

ADQL

ADQL is a large topic and is a critical part of the VO. As a language it is a variant of SQL, which is the defacto query language for relational databases. As in SQL the basic operator is SELECT, and at present we are using this in its fundamental sense, to select data from one table within an astronomical data source. The syntax is identical to SQL.



Other more complex relational operations, like joining two tables, are not supported at this time. (Jeff: Is this strictly true? Can we join two tables from the same datasource? We cannot join tables across datasources).

Go to Contents

ADQL_Helpers

The ADQL helpers show the available keywords and symbols that can be used. It is possible to divide them into arbitrary categories:

  • Basic SQL structure.
    • select
    • from
    • where
  • Logical Operators.
    • and
    • or
    • not
  • Text Comparison Operators.
    • like
    • notlike
  • Mathematical Type Operators/Symbols
    • (
    • )
    • +
    • -
    • *
    • /
    • =
    • ><
    • <
    • <=
    • >
    • >=
  • Functions
    • region
    • circle
    • square
    • sqrt
    • min
    • max
    • avg
    • sum
    • sin
    • asin
    • cos
    • acos
    • tan
    • atan
    • cot
    • atan2
    • log
    • log10
    • power
    • abs
    • ceiling
    • floor
    • pi
    • degrees
    • radians
  • Others
    • as
    • orderby
    • asc
    • desc
    • distinct

Go to Contents

Getting Meta Data

If you are uncertain which data sources (astronomical catalogues) are available for searching, or more likely the fine details of a data source, then you can request a search of the registry by clicking on the "Select a Table" button, which will display the Resource Microbrowser. The microbrowser is a subset of the full Resource Browser page, which can be referred to here for detailed usage. The microbrowser can be used to display details of one or more catalogues that fulfill certain criteria. If you know the catalogue and its name, then that can be used directly, otherwise search criteria must be chosen; For instance, it can be used to return all the catalogues in the registry that are xray sources. When you have one or more catalogs returned by the microbrowser, the tables and column data can then be examined and a suitable table selected for the query. It is only possible to select cataloges where details of tables and column data are present and have been entered in the registry.

When a table has been selected the Query Editor is displayed with the details of the table shown on the right hand side of the page. The table name appears at the top of this area alongside a suitable alias that can be pasted into the edit box (Normally the pasting is already done so the option is here simply for convenience). Below the table name are buttons, one for each column that the table contains. The area becomes scrollable when there are more buttons than can easily be displayed. If you hover the mouse over a button a small window appears with the metadata associated with that column. Clicking the button will paste the column name into the edit box if you wish to hand-craft the query. The actual process of building up a query in this manner takes some practice and a little knowledge of ADQL. However, for the initial build of a query (rather than a refinement of an already saved query), there is another and powerful option.

At this first build of a query, the Query Editor splits into two tabs, the "Data Query Builder" and the "User friendly Table Query Form". Choose the latter by clicking on the tab displayed at the top of the page.

Go to Contents

Using The Table Query Form

The Table Query Form will aid you in formatting a query. It is a good idea to use it to generate a first-cut of all your queries All columns in the associated table are shown, one to each line of the form. On the left of each line beside the name of the column is a checkbox that indicates whether the column values are to be included in the results set. Beside the name of the column is an entry field where a condition can be entered: this will form part of the WHERE clause of the ADQL and will help decide which rows can be included in the results set. You can use any operator for forming a condition (see the operators displayed as ADQL Helpers for the complete range). If you supply conditions for more than one column, the conditions are combined as logical ANDs when the ADQL is formatted. The conditions are not currently vetted for syntax.



To the right of the condition is displayed metadata about the column. This is presented as units, UCDs, and a brief explanation of meaning. An underscore is used if units information is not available.



Large and reasonably complex queries can be generated using the Table Query Form. However, there are simplicities to the process. As described above, the conditions are combined by ANDing them together. There is also no possible use of functions. And the query cannot be refined by sorting, etc. But once you have the basic query, these aspects can be worked upon in the Query Editor.



When you are satisfied that you have all the columns and conditions that you require, press the green "Click me to proceed" bar down the left hand side. You will be returned to the Query Editor main page with the query preformatted for you. The column buttons will still be present for any further editing. At present the Table Query Form is available only after using the Resource microbrowser to get metadata from the registry.

Go to Contents