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.
SELECT * FROM "B/chandra/chandra"
Get all records from the table chandra.
SELECT "B/chandra/chandra".Target FROM "B/chandra/chandra"
Get all target's names in the table chandra.
Limit the number of records to display using the TOP instruction:
SELECT TOP 100 * FROM "I/261/fonac"
Get the first 100 records of the query in the table "I/261/fonac.
Sort records in ascending (ASC) or descending (DESC) with using the ORDER instruction.
SELECT TOP 100 * FROM "I/261/fonac" ORDER BY Bmag ASC
Get the 100 brightness records of the table I/261/fonac.
Use constraints to filter records according to logical expressions. In an ADQL query, the constraints are gathered in the WHERE part of the query.
The different operators of logical comparisons : = or > or < or >= or <= or <> ...
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).
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.
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.
The BETWEEN operator can determine whether a value is within a given interval, regardless of the type specified reference values (alpha, numeric, date ...).
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.
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.
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.
Compute columns using mathematical operations : +, -, *, /:
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.
Arithmetic functions apply a mathematical function on the expression of a data line.
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.
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.
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.
SQL aggregate functions return a single value, calculated from values in a column :
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.
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.
SELECT MIN(Jmag), MAX(Jmag) FROM "II/295/SSTGC"
Get the minimum and maximum J magnitude of the table II/295/SSTGC (Spitzer IRAC).
ADQL provides a set of 2D-functions and geometries (or "REGION") :
We describe below the ADQL REGIONS:
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('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('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('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.
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".
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).
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).
SELECT TOP 50 RAJ2000, DEJ2000, IVO_HEALPIX_INDEX(15, RAJ2000, DEJ2000) FROM "II/246/out"
Note: use the order 15 to use the index!
SELECT TOP 50 * FROM "II/246/out" WHERE CDS_HEALPIX_INDEX(RAJ2000, DEJ2000) BETWEEN 100 AND 200
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.
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
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/.
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:
--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