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:

• All basic data on objects basic
  Select all information about all astronomical objects.
  QUERY

  SELECT *
  FROM basic;

• Coordinates of objects basic
  Select the position (and one id) of all astronomical objects.
  QUERY

  SELECT main_id, ra, dec, coo_err_maj, coo_err_min, coo_err_angle
  FROM basic;

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.

Select the 50 first rows of the table basic.

SELECT TOP 50 *
FROM basic;

• Order by column name

  SELECT TOP 50 *
  FROM ident
  ORDER BY id;

• Order by column label

  SELECT TOP 50 oidref, id AS ObjectName
  FROM ident
  ORDER BY ObjectName;

• Order by column index

  SELECT TOP 50 oidref, id AS ObjectName
  FROM ident
  ORDER BY 2;

• Order by descending column name

  SELECT TOP 50 *
  FROM ident
  ORDER BY id DESC;

• Objects most cited

  basic
  Select all objects which are referenced more than 3000 times.

  SELECT oid, main_id, nbref
  FROM basic
  WHERE nbref >= 3000
  ORDER BY nbref;

• Select all object identifiers from catalogue ACO (name starting with 'ACO' string).

  SELECT id
  FROM ident
  WHERE id like 'ACO%';
  

• Select the 50 first objects whose right ascension and declineason are not null.

  SELECT TOP 50 oid, main_id, ra, dec
  FROM basic
  WHERE ra IS NOT NULL
    AND dec IS NOT NULL;

• All children of an object h_link
  Select children names of an object.
  QUERY

  SELECT main_id as child, membership  from h_link JOIN ident as p on p.oidref=parent JOIN basic on oid=child
  WHERE p.id = 'Pleiades Moving Group' and (membership >=95 or membership is null);

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;

Region definition

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

• POINT(coord_sys, right_ascension, declineason)
• CIRCLE(coord_sys, ra_center, dec_center, radius_in_degree)

But the following regions also exist:

• BOX(coord_sys, ra_center, dec_center, width_in_degrees, height_in_degrees)
• POLYGON(coord_sys, ra_vertice1, dec_vertice1, ra_vertice2, dec_vertice2, ra_vertice3, dec_vertice3 [, ...])
• REGION(stc_region) (not implemented in Simbad-TAP)

Functions

The two main geometrical functions of ADQL are:

• CONTAINS(region1, region2)
• INTERSECTS(region1, region2)

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

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

• DISTANCE(POINT1, POINT2)
• AREA(region)
• COORD1(point)
• COORD2(point)
• COORDSYS(region) (not implemented in Simbad-TAP)
• CENTROID(region) (not implemented in Simbad-TAP)

• All basic data from an object name basic ident
  QUERY Get all information about SMC:

  SELECT oid, main_id, ra, dec, nbRef
  FROM basic JOIN ident ON oid = oidref
  WHERE id = 'smc';

• All specific measurements on an object mesVelocities ident
  QUERY Get all velocities of * alf cen:

  SELECT *
  FROM mesVelocities JOIN ident USING(oidref)
  WHERE id = '* alf Cen';

8. Multi Ordered Coverage output

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

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;

• Spectral type:

  You can find this information in the column sptype of the table basic. You can filter objects by their spectral type with the following operators: =, !=, <, <=, >, >=, BETWEEN '..' AND '..', IN and NOT IN.

  • Filter objects by spectral type basic
    QUERY

    SELECT TOP 100 oid, main_id, sp_type
    FROM basic
    WHERE sp_type BETWEEN 'F3' AND 'F5';

• Object type:

  This information is available in the columns otype, otype_txt of the table basic. You can filter objects by their type with the following operators: =, !=, IN and NOT IN. Since object types can be more or less precise (for instance: AGN are particular types of Galaxy), you can specify if you search for a precise type of object or for its descendants by adding 2 points ('..') at the end of the type name:

  • Filter objects by exact object type basic
    QUERY If you want to search for all objects labelled exactly Galaxy:

    SELECT TOP 100 oid, main_id, otype_txt
    FROM basic
    WHERE otype = 'Galaxy';

  • Filter objects by extended object type basic
    QUERY If you want to search for all galaxies:

    SELECT TOP 100 oid, main_id, otype_txt
    FROM basic
    WHERE otype = 'Galaxy..';

  Note: 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, ...).
• Object name/ID:

  This information is available in the column main_id of the table basic and especially in the column id of the table ident.

  Both columns provide an ID normalised in a very specific way by Simbad (e.g. M1 is normalised into M 1). Thus, you will need the help of a function in order to search for an object by its name/ID: normId(VARCHAR) -> VARCHAR. This function takes a name/ID (e.g. m1) and returns its normalisation (e.g. M 1).

  By default, this function is automatically applied when comparing the column ident.id with a string expression. This is the case for the following operators: = and !=, as well as IN and NOT IN (but only if a list instead of a query is provided ; if with a query, use the normalisation function). Generally, you won't need to explicitly use the normalisation function, but for very special queries, it could be useful to know that it exists.

  • Main identifier of an object basic
    QUERY Select the main name/ID of an object:

    SELECT main_id
    FROM basic
    WHERE oid = 2894585;

  • All identifiers of an object ident
    QUERY Select all names/IDs of an object:

    SELECT id
    FROM ident
    WHERE oidref = 2894585;

  • Search an object by name/ID ident basic
    QUERY Search an object by its name/ID:

    SELECT basic.*
    FROM ident JOIN basic ON ident.oidref = basic.oid
    WHERE id = 'm13';

  • Search several objects by name/ID ident basic
    QUERY Search several objects by their name/ID:

    SELECT myTable.id AS "MyID", ident.id AS "SimbadId", basic.*
    FROM TAP_UPLOAD.myTable LEFT OUTER JOIN ident ON ident.id = myTable.id
                            LEFT OUTER JOIN basic ON ident.oidref = basic.oid

    Note: The LEFT OUTER JOIN lets you preserve all records of the left table, even if there is no match in the right table. Here, this is useful as it lets us know for which of our IDs no object has been found in Simbad.
  • All bibcodes referencing an object biblio
    QUERY Select all bibcodes associated with an object.

    SELECT biblio
    FROM biblio JOIN ident USING(oidref)
    WHERE id = 'NGC 17';

• Magnitudes:
  • Basic magnitudes of an object allfluxes
    QUERY Shows some magnitudes of an object (from one identifier)

    SELECT B, V, R ,I, J, g_-r_ AS "g-r" from allfluxes JOIN ident USING(oidref) WHERE id = 'M13';

• BibCode:
  • All keywords of a paper keywords
    QUERY Select all keywords of a paper (agregate all of them into a single string):

    SELECT array_agg(keyword)
    FROM keywords JOIN ref ON oidbib=oidbibref
    WHERE bibcode = '2006A&A...460..695T'

• By default ADQL is not case-sensitive. So you can write: SELECT, Select, select, FROM, from, etc...
• The column and table names (and their aliases) are also not case-sensitive except if surrounded with double-quotes.
• Tables and columns of Simbad are always in lower case except for uploaded tables.
• String literals must always be surrounded by simple-quotes.
• Right ascensions, declineasons and radius must be given in degrees.
• The coordinate system of ADQL regions is not interpreted in Simbad-TAP and is supposed to be ICRS.
• Don't forget to compare the functions CONTAINS and INTERSECTS with 1 or 0.
• An uploaded table name must always be prefixed by TAP_UPLOAD.