ADQL and PostgreSQL queries in the new framework

One of the new features in the new Daiquiri framework is the support of the Astronomical Data Query Language v2.0 (ADQL). This version of the SQL was designed specifically to be used in astronomy and comes with statements that facilitate cone-search queries and other positional selections. All ADQL queries that are submitted for the execution are translated to PostgreSQL dialect before they are passed to the database server. Users can also choose to write their queries in the PostgreSQL directly. In some cases this is more desirable since the ADQL does not support all features that are available in PostgreSQL, namely working with array structures.

As an example let us write an ADQL query that selects stars around the M4 globular cluster from the Gaia DR1 main table:

SELECT ra, dec
FROM gdr1.gaia_source
WHERE 1 = CONTAINS(POINT('ICRS', ra, dec),
                   CIRCLE('ICRS',4.2917,-0.4629, 0.008))

The cone selection is done using the CONTAINS statement. Alternatively, we can rewrite the same query using the PostgreSQL dialect in the following way:

SELECT ra, dec
FROM gdr1.gaia_source
WHERE pos @ scircle(spoint(RADIANS(4.2917),RADIANS(-0.4629)), RADIANS(0.008))

Here we use the PgSphere functions ‘scircle’ and ‘spoint’ to define the region. We connect the column containing the positional information (right ascension and declination) ‘pos’ to the cone selection with the @ operator. But you need to apply RADIANS() to the (RA,DEC) values. To learn more about the available PgSphere functions and capabilities please refer to the documentation here.

Previous post:     Next post: