- SHBoost 2024 ( gaiadr3_contrib.shboost2024 )
ADQL and PostgreSQL
The Gaia@AIP services run with PostgresQL database which you can access through the Query interface. To retrieve data from the tables, the tables are queried. An SQL statement is used to do this. For each query you can choose which SQL language to use. The Django Daiquiri framework released with the Gaia DR2, updated with Gaia DR3, supports ADQL and PostgreSQL.
The ADQL or Astronomical Data Query Language was designed specifically to be used in astronomy and comes with statements that facilitate cone-search queries and other positional selections. It is developed by the Virtual Observatory (VO) to represent astronomical queries send to IVOA services.
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 dialect directly. In some cases this is more powerful, 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 DR3 main table:
SELECT source_id, ra, dec
FROM gaiadr3.gaia_source_lite
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 source_id, ra, dec
FROM gaiadr3.gaia_source_lite
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. And the functions require coordinates in RADIANS, not degrees. To learn more about the available PgSphere functions and capabilities please refer to the documentation here
The SQL queries must be either in ADQL or PostgreSQL depending on the task. For example, ADQL provides some specific geometry functions, which other SQL languages do not have. On the other hand, some common SQL syntax will not work with ADQL and you will have to switch to PostgreSQL.
Please take a look at the Gaia query examples provided for both dialects.
ADQL
Additional information on ADQL you can find here:
- A short tutorial how to use ADQL with Gaia by GAVO
- ADQL cookbook on Gaia in the UK
- ADQL help on VizieR
- ADQL Cheat sheet by Simbad
For the complete documentation and specification of ADQL please look up IVOA ADQL documentation.
PostgreSQL
PostgreSQL uses SQL dialect to query the data in the database.
- For an easy start with a SELECT statement look at the PostgresQL website.
- The PostgresQL website will provide you with the full documentation of PostgreSQL as well.
Notes on interoperability and performances
The ADQL Language is common over all IVOA services and provides the best interoperability tool for reproducability and reusability. We recommend to publish SQL queries in the ADQL language.
However, an optimized query often provides better performancies than queries written in ADQL owing to the technical specificity of each Gaia mirror.
Generally at Gaia@AIP, AQDL and PostgreSQL queries run similarly, except in the case of ConeSearch-like queries. We offer a IVOA compatible conesearch service as well as a conesearch web-service. Both run highly optimized queries that may run several hundred times faster than the corresponding ADQL query. The reason for this large discrepancy is the very design of ADQL: interoperability, preventing to use any specific indexing mechanism.
At Gaia@AIP all tables holding some (ra
, dec
) coordinates are updated with a PgSphere column of type SPOINT
called pos
. These columns combine ra
and dec
in a single indexed column making coordinate search extremely more efficient.
At Gaia@AIP to run a query, one should always prefer the pos @ scircle()
PostgreSQL specific construction, but scientific publication of SQL statement the ADQL language should be prefered.