Minimal queries

Select all information about all astronomical objects.

SELECT *
  FROM basic;

Try it !

Querying : simbad

Select the position (and one id) of all astronomical objects.

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

Try it !

Querying : simbad

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;

Try it !

Querying : simbad

Output column names ra and dec remain unchanged, while main_id is renamed Usual ID.

Rows limit

Select the 50 first rows of the table basic.

SELECT TOP 50 *
FROM basic;

Try it !

Querying : simbad

Ordering rows

Order by column name

SELECT TOP 50 *
    FROM ident
    ORDER BY id;

Try it !

Querying : simbad

Order by column label

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

Try it !

Querying : simbad

Order by column index

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

Try it !

Querying : simbad

Order by descending column name

SELECT TOP 50 *
    FROM ident
    ORDER BY id DESC;

Try it !

Querying : simbad

Filtering rows

Select all objects which are referenced more than 3000 times

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

Try it !

Querying : simbad

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

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

Try it !

Querying : simbad

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

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

Try it !

Querying : simbad

Select children names of an object

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

Try it !

Querying : simbad

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;

Try it !

Querying : simbad

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:

POINT(coord_sys, right_ascension, declination)

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)

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:

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)

Using several tables (Join)

Get all information about SMC:

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

Try it !

Querying : simbad

Get all velocities of * alf Cen:

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

Try it !

Querying : simbad

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.

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 aliases are particularly useful when columns with the same name come from different tables.

Multi Ordered Coverage output

You can select 'MOC' output if table basic is used in your query

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

Try it !

Querying : simbad

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;

Simbad specific features

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.

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

Try it !

Querying : simbad

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:

If you want to search for all objects labelled exactly Galaxy:

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

Try it !

Querying : simbad

If you want to search for all galaxies:

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

Try it !

Querying : simbad

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, ...). TODO link simbad object types page

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.

Select the main name/ID of an object:

SELECT main_id
    FROM basic
    WHERE oid = 2894585;

Try it !

Querying : simbad

Select all names/IDs of an object:

SELECT id
    FROM ident
    WHERE oidref = 2894585;

Try it !

Querying : simbad

Search an object by its name/ID:

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

Try it !

Querying : simbad

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;

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.

Select all bibcodes associated with an object:

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

Try it !

Querying : simbad

Magnitudes

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

Try it !

Querying : simbad

BibCode

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'

Try it !

Querying : simbad