ADQL Cheat sheet

Result

			

ADQL means Astronomical Data Query Language. This language is used by the IVOA to represent astronomy queries posted to VO services. It is based on SQL (Structured Query Language) and enriched with geometrical functions such as CONTAINS and INTERSECTS. But contrary to SQL, ADQL is only designed to interrogate a database !

All information about ADQL are available at this IVOA Document. This page gathers minimal ADQL features required to interrogate Simbad-TAP. These features are explained through query examples on the following Simbad tables:

Tables example

1. Minimal queries

2. Labeling columns

In the output of a query execution, columns are identified by their name in the database (i.e. ra, dec and main_id). However, these output names can be changed while writing the query:

SELECT ra, dec, main_id AS "Usual ID"
FROM basic;

=> Output column names will be: ra, dec and Usual ID.

3. Rows limit

Select the 50 first rows of the table basic.

SELECT TOP 50 *
FROM basic;

4. Ordering rows

5. Filtering rows

6. Geometrical functions

ADQL also allows interrogation on position like Search Cone.

-- All objects around M13 with a radius of 0.1°
SELECT TOP 50 oid, main_id, ra, dec
FROM basic
WHERE CONTAINS(POINT('ICRS', ra, dec), CIRCLE('ICRS', 250.423475, 36.4613194444444, 0.1)) = 1
  AND ra IS NOT NULL
  AND dec IS NOT NULL;

Note: A line or a part of line starting with -- is a comment, and so will be ignored at the query execution.

Region definition

In this example only two types of regions have been used:

But the following regions also exist:

IMPORTANT: In Simbad, the coordinate system parameter is never interpreted. All coordinates MUST be expressed in the ICRS coordinate system !

Functions

The two main geometrical functions of ADQL are:

These functions return 1 if region1 contains/intersects region2, 0 otherwise.

ADQL also provides functions to compute distance, area, ...

7. Using several tables

Note: To join tables you can use either ON condition or USING(column_name). However USING can be used only if the given column exists in both tables with exactly the same name.
Note: As you surely notice, tables can also have a label using the keyword AS. But contrary to the columns, this label is actually an alias and MUST always be used to reference the corresponding table. These table alias are particularly usefull when columns with the same name come from different tables.

Multi Ordered Coverage output


			-- Young Stellar objects
			SELECT hpx
			FROM basic 
			WHERE otype='YSO'

8. Upload tables

In TAP, you can upload VOTables and interrogate them as tables in an ADQL query. When uploading a VOTable, a table name must be provided. This name prefixed by TAP_UPLOAD must be used to reference the table in the ADQL query

SELECT *
FROM TAP_UPLOAD.foo;

9. Object and spectral types in Simbad

Note: Spectral and object type names are case insensitive and can be either the full or the condensed representation (for instance: Galaxy = G, AGN = AGN, Star = *, Radio = Rad, gamma = gam, ...).

10. ADQL traps & tricks