ADQL cheat sheet

Outline

1 - Introduction
2 - SQL minimal queries
3 - Limit the number of records in output
4 - Ordering records
5 - Filtering results
6 - Computed columns in ADQL
7 - 2D-geometrical functions
8 - Working with several tables
9 - Query Optimisation

1 - Introduction

The ADQL or Astronomical Data Query Language is used by the VO to represent astronomical queries send to IVOA services. It is a SQL-like searching language improved with geometrical functions.

This cheat sheet gathers principal ADQL features required to understand generated queries or to create your own queries. All information about ADQL are available at this IVOA Document.

2 - SQL minimal queries

example :
```
 SELECT *
 FROM "B/chandra/chandra"
```
Get all records from the table chandra.

example :

SELECT  "B/chandra/chandra".Target
FROM  "B/chandra/chandra"

Get all target's names in the table chandra.

3 - Limit the number of records in output

Limit the number of records to display using the TOP instruction:

example :

SELECT TOP 100 *
FROM "I/261/fonac"

Get the first 100 records of the query in the table "I/261/fonac.

4 - Ordering records

Sort records in ascending (ASC) or descending (DESC) with using the ORDER instruction.

example :

SELECT TOP 100 *
FROM "I/261/fonac" ORDER BY Bmag ASC

Get the 100 brightness records of the table I/261/fonac.

5 - Filtering results

Use constraints to filter records according to logical expressions. In an ADQL query, the constraints are gathered in the WHERE part of the query.

5.1 - Comparison operators

The different operators of logical comparisons : = or > or < or >= or <= or <> ...

example :

 SELECT TOP 100 "I/261/fonac".RAJ2000, "I/261/fonac".DEJ2000, "I/261/fonac".pmRA, "I/261/fonac".pmDE, "I/261/fonac".Bmag
 FROM "I/261/fonac"
 WHERE "I/261/fonac".Bmag<15 AND "I/261/fonac".Bmag>14

Get positions, proper motions and B magnitude of the table I/261/fonac when the B magnitude is greater than 14 and less than 15 (the output is limited to 100 records).

5.2 - IN operator

The IN operator can determine whether a value is within a given set, regardless of the type specified reference values (alpha, numeric, date ...). You can reverse the operation of the IN operator by adding to the NOT operator.

example :

SELECT "B/chandra/chandra".Target,  "B/chandra/chandra".ObsID,  "B/chandra/chandra".RAJ2000,  "B/chandra/chandra".DEJ2000,  "B/chandra/chandra".Status
FROM "B/chandra/chandra"
WHERE "B/chandra/chandra".status NOT IN ('archived','scheduled')

Get target, obsID, RAJ2000, DEJ2000 and status if status does not equal to 'archive' neither to 'scheduled' in the table chandra.

5.3 - BETWEEN operator

The BETWEEN operator can determine whether a value is within a given interval, regardless of the type specified reference values (alpha, numeric, date ...).

example :

SELECT "V/134/arxa".RAJ2000, "V/134/arxa".DEJ2000, "V/134/arxa".Rmag
FROM "V/134/arxa"
WHERE "V/134/arxa".Rmag between 3 and 6

Get positions and R magnitude (Rmag) whose the R magnitude is between 3 and 6 in the table V/134/arxa.

5.4 - LIKE operator

The LIKE operator allows for a partial comparison. It is mainly used with columns with data type alpha. It uses wild cards % and _ ('percent' and 'underscore'). The wild card % replaces any string of characters, including the empty string. The underscore replaces exactly one character.

example :

SELECT "B/chandra/chandra".Target,  "B/chandra/chandra".RAJ2000,  "B/chandra/chandra".DEJ2000,  "B/chandra/chandra".Category
FROM "B/chandra/chandra"
WHERE "B/chandra/chandra".Category LIKE '%BINARIES%'

Get positions and category whose category contains the word 'BINARIES' in the table chandra.

6 - Computed columns in ADQL

6.1 - Mathematical operations :

Compute columns using mathematical operations : +, -, *, /:

example :

 SELECT "RA(ICRS)", "DE(ICRS)", Btmag, VTmag, BTmag - VTmag AS BV
 FROM "I/259/tyc2"
 WHERE Btmag-VTmag>0 AND BTmag-VTMag<0.2

Return values from the tycho catalog with computing the color (BTmag-VTmag) and with adding a constraint on it.

Note: the table "II/259/tyc2" is optional in the SELECT and WHERE part of an ADQL query when a unique table is in action (FROM part).

Note: the columns "RA(ICRS)" and "DE(ICRS)" must be quoted because they contain a special caracter ('()[].').

6.2 - Arithmetical functions :

Arithmetic functions apply a mathematical function on the expression of a data line.

POWER(column_name, n) : returns values raised to the power n. n must be a integer positive or negative.
SQRT(column_name) : returns the square root of values.
example :
```
SELECT TOP 100 HIP, "RA(ICRS)", "DE(ICRS)", pmRA, pmDE, SQRT(POWER(pmRA,2)+POWER(pmDE,2)) AS pm
FROM "I/259/tyc2"
```
Get positions, propper motions of the tycho catalogs I/259/tyc2.
CEILING(column_name) (or FLOOR()) : roundup to the nearest (or round down to the next least) integer value.
example :
```
SELECT CEILING("VII/233/xsc"."K.K20e"), FLOOR("VII/233/xsc"."K.K20e")
FROM "VII/233/xsc"
WHERE "VII/233/xsc"."K.K20e"<5
```
Get the rounded up and rounded down of "K.K20e" values when "K.K20e"" is less than 5 in the table VII/233/xsc.
ABS(column_name) : returns the absolute value.
example :
```
SELECT ABS("VII/233/xsc"."K.K20e")
FROM "VII/233/xsc"
WHERE "VII/233/xsc"."K.K20e"<5
```
Get the absolute values of "K.K20e"" values when "K_.20e" is less than 5 in the table VII/233/xsc.

6.3 - SQL aggregate functions :

SQL aggregate functions return a single value, calculated from values in a column :

AVG (column_name) : this function returns the average value in a column for a group of data lines. This function applies only to numeric data.
example :
```
SELECT AVG(BTmag), AVG(VTmag)
FROM "I/239/hip_main"
```
Get the average of the B and V magnitude for the stars in the hipparcos catalogue I/139/hip_main.
COUNT (column_name) : this function returns a count of rows from a reference column values if it is not NULL.
example :
```
SELECT COUNT(*)
FROM "II/246/out"
WHERE "II/246/out".Jmag<5 AND "II/246/out".Jmag>4
```
Search the number of records in the 2MASS catalog (II/246/out) for which the J magnitude is between 4 and 5.
To count objects according to the differents values of an other column, use the GROUP BY directive as folllow:
```
SELECT Mtype, count(Mtype)
FROM "VII/159/catalog"
GROUP BY Mtype
```
The query compute the number of objects per morphology (Mtype field) in the table VII/159/catalog.
SUM(column_name) : this function returns the sum of values in a column for a group of data lines. This function applies only to numeric data.
MAX(column_name) (or MIN): this function returns the largest (or smallest) value of a column for a group of data lines.
example :
```
SELECT MIN(Jmag), MAX(Jmag)
FROM "II/295/SSTGC"
```
Get the minimum and maximum J magnitude of the table II/295/SSTGC (Spitzer IRAC).

6.4 - The trigonometrics functions

COS(angle_in_radian)
SIN(angle_in_radian)
TAN(angle_in_radian)
ACOS(angle_in_radian)
ASIN(angle_in_radian)
ATAN(angle_in_radian)

7 - 2D-geometrical functions

7.1 - Geometries available

ADQL provides a set of 2D-functions and geometries (or "REGION") :

A region is always attached to a coordinate System: FK4, FK5, ICRS, GALACTIC.
The coordinates expressed in degree, can be constant or the result of a mathematical expression.

We describe below the ADQL REGIONS:

POINT('coordinate system', right ascension, declination)
example :
```
POINT('ICRS', 0.0, 0.0)
```
expresses a point with right ascension of 0 degrees and declination of 0 degrees according to the ICRS coordinate system.
CIRCLE('coordinate system',right ascension center, declination center, radius) : This function expresses a circular region on the sky (a cone in space). The radius must be in degrees.
example :
```
CIRCLE('ICRS', 25.4, -20.0, 1)
```
expresses a circle of one degree radius centered in a position of (25.4, -20.0) degrees and defined according to the ICRS coordinate system.
BOX('coordinate system', right ascension center, declination center, width, height) : defines a BOX centered in a position(right ascension center, declination center) of width "width" in degrees and height "height" in degrees.
example :
```
BOX('ICRS', 25.4, -20.0, 10, 10)
```
expresses a box of ten degrees centered in a position (25.4, -20.0) in degrees and defined according to the ICRS coordinate system.
POLYGON('coordinate system', coordinate point 1, coordinate point 2, coordinate point 3...) : This function expresses a region on the sky with sides denoted by great circles passing through specified coordinates. The coordinates of each point are defined by a right ascension and a declination expressed in degrees.
example :
```
POLYGON('ICRS', 10.0, -10.5, 20.0, 20.5, 30.0, 30.5)
```
expresses a triangle, whose vertices are (10.0, -10.5),(20.0, 20.5) and (30.0,30.5) in degrees according to the ICRS coordinate system.

7.2 - ADQL geometrical functions :

DISTANCE(point1, point2) : this function compute distance between two points.
example :
```
SELECT TOP 10 DISTANCE(POINT('ICRS',0,0), POINT('ICRS',"VII/233/xsc".RAJ2000,"VII/233/xsc".DEJ2000))
FROM "VII/233/xsc"
```
Compute, for the ten first rows, the distance between the point of coordinates (0,0) and the object from the table "VII/233/xsc".
CONTAINS(region1, region2) : this function returns a boolean value : true if region2 contains region1, false otherwise. The region can be a POINT(), a CIRCLE(), a BOX(), or a POLYGON().
example :
```
SELECT *
FROM "II/246/out"
WHERE 1=CONTAINS(POINT('ICRS',"II/246/out".RAJ2000,"II/246/out".DEJ2000), CIRCLE('ICRS',0,0, 10/60))
```
Get records of the 2MASS catalog (table "II/246/out") arround the position (0,0).
INTERSECTS(region1, region2) : this function returns a boolean value : true if region2 intersect region1, false otherwise.
example :
```
SELECT *
FROM "II/246/out"
WHERE 1=INTERSECTS(BOX('ICRS', RAJ2000, DEJ2000, 10/60.,5/60.), CIRCLE('ICRS',0,0, 10/60))
```
Get records of the 2MASS catalog (table II/246/out) when the box centered in (ra,de) intersects the circle centered in (0,0).
IVO_HEALPIX_INDEX(order, RA, DEC) : the function returns the HEALPix number for a position(RA,DEC) in given order)
example :
```
SELECT TOP 50 RAJ2000, DEJ2000, IVO_HEALPIX_INDEX(15, RAJ2000, DEJ2000)
FROM "II/246/out"
```
Note: use the order 15 to use the index!
CDS_HEALPIX_INDEX(RA, DEC) : the function returns the HEALPix number for a position(RA,DEC) in the order used by TAPVizieR (indexed!))
example :
```
SELECT TOP 50 *
FROM "II/246/out"
WHERE CDS_HEALPIX_INDEX(RAJ2000, DEJ2000) BETWEEN  100 AND 200
```

8 - Working with several tables

ADQL can join tables according to a identifer or by positions.

You can specify several tables in the WHERE part of an ADQL query and then taking advantage of the columns of differents tables. But, regard that by default the the join between 2 tables is a cartesian product (in which each record of a table is gather with all records of a second table). The WHERE part is a way to organize the join.

Join tables according to a identifier

example :

SELECT TOP 100 *
FROM "J/ApJS/112/557/table1","III/170B/ps_class"
WHERE "J/ApJS/112/557/table1".IRAS="III/170B/ps_class".IRAS

Join by identifier the tables J/ApJS/112/557/table1 and III/170B/ps_class with the column IRAS

Join tables according to their positions

example :

SELECT *
FROM "II/295/SSTGC","II/293/glimpse"
WHERE 1=CONTAINS(POINT('ICRS',"II/295/SSTGC".RAJ2000,"II/295/SSTGC".DEJ2000), BOX('GALACTIC', 0, 0, 30/60., 10/60.)) 
  AND 1=CONTAINS(POINT('ICRS',"II/295/SSTGC".RAJ2000,"II/295/SSTGC".DEJ2000), CIRCLE('ICRS',"II/293/glimpse".RAJ2000,"II/293/glimpse".DEJ2000, 2/3600.))

Crossmatch between 2MASS and Glimpse in the BOX centered with the galaxy center.

Note: Improve your ADQL Crossmatch queries with setting the coordinates of the smallest resource in the first parameters of the CONTAINS function (in the example II/295 is smaller than II/293/glimpse)
Try the "Explain" capability at /TAPVizieR/.

9 - Query Optimisation

(for advanced user only)

The database QueryPlan is the process responsible to define the faster way to execute SQL query.
TAPVizieR provides a method to change the queryPlan:

position_priority	set the index priority on position functions (default is true)
enable_seqscan	allows the sequential search (default is false)
enable_nestloop	in a join, allows a sequential search from a table to the other (default is true)
enable_hasjoin	in a join, allows QueryPlan to create a hashtable on the fly (default is true)
enable_sort	in a join, allows the QueryPlan to make a sort (default is true)
enable_material	materialize records into memory - not compatible with index scan (default is true)

The QueryPlan option can be added in the ADQL beginning comment:

example :

	--set position_priority=false
	SELECT TOP 100 * FROM "II/349/ps1" 
	WHERE 1=CONTAINS(POINT('ICRS', RAJ2000, DEJ2000), CIRCLE('ICRS', 45, 0, 20.))
	  AND gmag>15

Note: use the "Explain" button available in theTAPVizieR interface to see the QueryPlan

Help Tap VizieR

ADQL cheat sheet

Outline

1 - Introduction

2 - SQL minimal queries

3 - Limit the number of records in output

4 - Ordering records

5 - Filtering results

5.1 - Comparison operators

5.2 - IN operator

5.3 - BETWEEN operator

5.4 - LIKE operator

6 - Computed columns in ADQL

6.1 - Mathematical operations :

6.2 - Arithmetical functions :

6.3 - SQL aggregate functions :

6.4 - The trigonometrics functions

7 - 2D-geometrical functions

7.1 - Geometries available

7.2 - ADQL geometrical functions :

8 - Working with several tables

Join tables according to a identifier

Join tables according to their positions

9 - Query Optimisation