Minimal queries

Get all records from the table chandra, for catalogue B/chandra .

SELECT  *
   FROM  "B/chandra/chandra";

Try it !

Querying : vizier

Get values of the Target column in the table chandra.

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

Try it !

Querying : vizier

Limit the number of records in output

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

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

SELECT TOP 10 *
FROM "I/261/fonac";

Try it !

Querying : vizier

Ordering records

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

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

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

Try it !

Querying : vizier

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.

Comparison operators

The different operators for logical comparisons are : =, >, <, >=, <= and <>.

Get positions, proper motions and B magnitude of the table I/261/fonac

where the B magnitude is greater than 14 and less than 15 (output limited to 20 records).

SELECT TOP 20 "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;

Try it !

Querying : vizier

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.

Get target, obsID, RAJ2000, DEJ2000 and status if status is not equal to 'archived' or 'scheduled' in the table chandra.

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');

Try it !

Querying : vizier

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

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

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;

Try it !

Querying : vizier

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 wild card _ replaces exactly one character.

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

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%';

Try it !

Querying : vizier

Computed columns in ADQL

Mathematical operations

New columns can be computed using mathematical operations : +, -, *, /.

Return values from the Tycho-2 ( I/259/tyc2 ) catalog while computing the (BTmag-VTmag) color (renamed BV in the output), and adding a constraint on this color.

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

Try it !

Querying : vizier

The table prefix of the column names ("II/259/tyc2".) is optional in the SELECT and WHERE parts of an ADQL query when a unique table is used in the FROM part.

The columns "RA(ICRS)" and "DE(ICRS)" must be quoted because they contain a special character (special characters are ()[].).

Arithmetical functions

Several arithmetical functions are available in ADQL :

POWER(value, n)

returns value raised to the power n. n must be a integer (positive or negative).

SQRT(value)

returns the square root of value.

CEILING(value) (or FLOOR(value))

round up (or round down) value to the nearest integer.

ABS(value)

returns the absolute value of value.

Get positions and proper motions from the I/259/tyc2 catalog.

SELECT TOP 20 HIP, "RA(ICRS)", "DE(ICRS)", pmRA, pmDE, SQRT(POWER(pmRA,2)+POWER(pmDE,2)) AS pm
FROM "I/259/tyc2";

Try it !

Querying : vizier

Get the rounded up and rounded down of "K.K20e" values (where "K.K20e"" < 5) in the table VII/233/xsc .

SELECT CEILING("VII/233/xsc"."K.K20e"), FLOOR("VII/233/xsc"."K.K20e")
FROM "VII/233/xsc"
WHERE "VII/233/xsc"."K.K20e"<5;

Try it !

Querying : vizier

Get the absolute values of RVC in the table VII/256/table1 .

SELECT TOP 20 "VII/256/table1"."RVC", ABS("VII/256/table1"."RVC" AS abs_RVC)
FROM "VII/256/table1";

Try it !

Querying : vizier

Note that in the query above, we need to use quotes around the column name "RVC" to force case-sensitive interpretation, because the same table contains another column named RVc.

SQL aggregate functions

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

AVG(column_name)

returns the average value in a column for a group of data lines. AVG() applies only to numeric data.

Get the average redshift for sources in table VII/182/tables .

SELECT AVG(z) AS avg_z
    FROM "VII/182/tables";

Try it !

Querying : vizier

COUNT(column_name)

returns a count of rows where the value of column_name is not NULL.

Search the number of records in the 2MASS catalog II/246/out for which the J magnitude is between 4 and 5.

SELECT COUNT(*)
FROM "II/246/out"
WHERE "II/246/out".Jmag<5 AND "II/246/out".Jmag>4;

Try it !

Querying : vizier

To count objects according to the distinct values of an other column, use the GROUP BY directive.

Compute the number of objects for each distinct value of Mtype in the table VII/159/catalog .

SELECT Mtype, COUNT(Mtype) AS Number
FROM "VII/159/catalog"
GROUP BY Mtype;

Try it !

Querying : vizier

SUM(column_name)

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(column_name))

returns the largest (or smallest) value of a column for a group of data lines.

Get the minimum and maximum J magnitude of the table II/295/SSTGC (Spitzer IRAC).

SELECT MIN(Jmag) AS "Min J", MAX(Jmag) AS "Max J"
FROM "II/295/SSTGC";

Try it !

Querying : vizier

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

2D-geometrical functions

Available geometries

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

A region is always attached to a coordinate System: FK4, FK5, ICRS, GALACTIC.

The coordinates, expressed in degrees, can be a constant or the result of a mathematical expression.

We describe below the ADQL REGIONS:

• POINT('coordinate system', right_ascension, declination) expresses a point in a 2D coordinates system

  • example : POINT('ICRS', 0.0, 10.0) represents a point with a right ascension of 0.0 degrees and a declination of +10.0 degrees according to the ICRS coordinate system.

• CIRCLE('coordinate system', right_ascension_center, declination_center, radius) 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) represents a circle of one degree radius centered on a position of (25.4, -20.0) degrees in the ICRS coordinate system.

• BOX('coordinate system', right_ascension_center, declination_center, width, height) expresses a BOX centered at a POINT(right_ascension_center, declination_center), of width width in degrees and height height in degrees.

  • example : BOX('ICRS', 25.4, -20.0, 10, 10) represents a box of ten by ten degrees centered on 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...) expresses a region on the sky with sides denoted by great circles passing through the specified coordinates. The coordinates of each point are defined by two angles in degrees.

  • example : POLYGON('ICRS', 10.0, -10.5, 20.0, 20.5, 30.0, 30.5) represents a triangle, whose vertices are (10.0, -10.5), (20.0, 20.5) and (30.0,30.5) in degrees in the ICRS coordinate system.

ADQL geometrical functions

DISTANCE(point1, point2)

computes the distance between two points.

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 TOP 10 DISTANCE(POINT('ICRS',0,0), POINT('ICRS',"VII/233/xsc".RAJ2000,"VII/233/xsc".DEJ2000))
FROM "VII/233/xsc";

Try it !

Querying : vizier

CONTAINS(region1, region2)

returns a boolean value :

• true (1) if region2 contains region1
• false (0) otherwise.

Regions can be a POINT(), a CIRCLE(), a BOX(), or a POLYGON().

Get records of the 2MASS catalog (table II/246/out ) within 10' around the position (0, 0).

SELECT *
FROM "II/246/out"
WHERE 1=CONTAINS(POINT('ICRS',"II/246/out".RAJ2000,"II/246/out".DEJ2000), CIRCLE('ICRS', 0, 0, 10/60));

Try it !

Querying : vizier

INTERSECTS(region1, region2)

returns a boolean value :

• true (1) if region2 intersects region1
• false (0) otherwise.

Get records of the 2MASS catalog (table II/246/out ) for which the box centered at (RAJ2000, DEJ2000) intersects the circle centered at (0, 0).

SELECT TOP 20 *
FROM "II/246/out"
WHERE 1=INTERSECTS(BOX('ICRS', RAJ2000, DEJ2000, 10/60.,5/60.), CIRCLE('ICRS',0,0, 10/60));

Try it !

Querying : vizier

IVO_HEALPIX_INDEX(order, RA, DEC)

returns the HEALPix number at the given order for a position (RA,DEC).

SELECT TOP 50 RAJ2000, DEJ2000, IVO_HEALPIX_INDEX(15, RAJ2000, DEJ2000)
FROM "II/246/out";

SELECT TOP 50 RAJ2000, DEJ2000, IVO_HEALPIX_INDEX(15, RAJ2000, DEJ2000)
FROM "II/246/out";

Try it !

Querying : vizier

VizieR tables are indexed by HEALPix numbers computed at order=15. It is therefore recommended to compute HEALPix numbers at order 15 to use the index!

CDS_HEALPIX_INDEX(RA, DEC)

returns the HEALPix number for a position (RA,DEC) at the order (15) used by TAP-VizieR (indexed!).

SELECT *
    FROM "II/246/out"
    WHERE CDS_HEALPIX_INDEX(RAJ2000, DEJ2000) BETWEEN 100 AND 4000;

SELECT *
    FROM "II/246/out"
    WHERE CDS_HEALPIX_INDEX(RAJ2000, DEJ2000) BETWEEN 100 AND 4000;

Try it !

Querying : vizier

Working with several tables

ADQL can join tables according to an identifer, or by positions.

You can specify several tables in the FROM part of an ADQL query and then take advantage of the columns from different tables.

By default, the JOIN operation between 2 tables is a cartesian product, where each record of the first table is associated with all records of the second table. This can be very resource intensive.

Joining two tables with 10⁴ rows each will produce a 10⁸ rows output table !

It is strongly recommended to use the WHERE clause to filter the output.

Join tables according to an identifier

Join the tables J/ApJS/112/557/table1 and III/170B/ps_class on the IRAS identifier.

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;

Try it !

Querying : vizier

Join tables according to coordinates

Crossmatch the II/295/SSTGC table (1065565 rows) with Glimpse sources ( II/293/glimpse , 104240613 rows) within 2 arcsec, in a 30'x10' BOX centered at the Galactic center.

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.));

Try it !

Querying : vizier

You can usually significantly improve your ADQL crossmatch queries by setting the coordinates of the smallest resource in the first parameters of the CONTAINS function (in the example II/295/SSTGC is smaller than II/293/glimpse ).

Try the "Test" capability TAPVizieR/.

Query Optimisation

This section is for advanced users only !

The database QueryPlan is the process responsible to define the fastest way to execute SQL queries. TAP-VizieR 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 options can be added in a comment at the beginning of the ADQL query : 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 "Test" button available in the TAPVizieR interface to see the QueryPlan

This is extra information