Help support

Should you have any question, please check the Gaia FAQ section or contact the Gaia Helpdesk

Query examples

 
Cone search (radius 5') sorted by angular separation

Use case: I want to retrieve a sample of filtered sources in a circular region centred on the Large Magellanic Coud [(R.A., Dec) = (81.28, -69.78) deg] with a search radius of 5 arcmin (EDR3, Gaia Collaboration, Luri et al. 2021 A&A 649, A7). The output should be ordered by the angular separation from the centre, from small to large.
Target table: gaiaedr3.gaia_source
Query:

SELECT *, DISTANCE(POINT(81.28, -69.78), POINT(ra, dec)) AS ang_sep
FROM gaiaedr3.gaia_source
WHERE 1 = CONTAINS(
   POINT(81.28, -69.78),
   CIRCLE(ra, dec, 5./60.)) AND
phot_g_mean_mag < 20.5 AND
parallax IS NOT NULL
ORDER BY ang_sep ASC

 

SELECTION OF rectangular SKY region + 2MASS cross-match

Use case: I want to retrieve a set of parameters from a sample of sources contained in a (projected) rectangular region centred on the Small Magellanic Cloud [(l, b) = (302.95, -43.98) deg]in Galactic coordinates, and combine them with the 2MASS Point Source Catalogue.
Target tables: gaiaedr3.gaia_source,gaiadr1.tmass_original_valid, gaiaedr3.tmass_psc_xsc_best_neighbour, gaiaedr3.tmass_psc_xsc_join, gaiadr1.tmass_original_valid.
Query:

SELECT gaia.source_id, gaia.ra, gaia.dec, gaia.parallax, gaia.pmra, gaia.pmdec, tmass.*
FROM gaiaedr3.gaia_source AS gaia
JOIN gaiaedr3.tmass_psc_xsc_best_neighbour AS xmatch USING (source_id)
JOIN gaiaedr3.tmass_psc_xsc_join AS xjoin USING (clean_tmass_psc_xsc_oid)
JOIN gaiadr1.tmass_original_valid AS tmass ON
   xjoin.original_psc_source_id = tmass.designation
WHERE
gaia.l BETWEEN 302.90 AND 303.00 AND
gaia.b BETWEEN -44.03 AND -43.93

 

bright sources in DR2 and edr3

Use case: I want to retrieve the targets with Gmag < 5 in DR2 and their EDR3 counterparts (EDR3, Gaia Collaboration, Brown et al. 2021 A&A 649, A1). This query uses the pre-computed DR2-EDR3 cross-match table. The output should be ordered on the Gaia DR2 source_id field.
Target table: gaiadr2.gaia_source, gaiaedr3.dr2_neighbourhood.
Query:

SELECT dr2.source_id, dr2.phot_g_mean_mag, edr3.*
FROM gaiadr2.gaia_source AS dr2
JOIN gaiaedr3.dr2_neighbourhood AS edr3 ON
   dr2.source_id = edr3.dr2_source_id
WHERE dr2.phot_g_mean_mag < 5
ORDER BY dr2.source_id ASC

 

ADQL positional cross-match: hipparcos vs gaia

Use case: I want to carry out a positional cross-match between two catalogue using a cone-seach radius of 1 arcsecond (0.00028 degrees).
Notes: When cross-matching catalogues, the smaller one should be used to define the 'POINT' and the larger one to create the 'CIRCLE'. Otherwise, the Gaia Archive response time can be degraded by several orders of magnitude. Only the first 1000 objects are retrieved to make the query fast (remove the 'TOP 1000' clause to retrieve the full output). Several pre-computed cross-matches against major surveys are available in the Gaia Archive.
Target table: gaiaedr3.gaia_source, public.hipparcos.
Query:

SELECT TOP 1000 * , DISTANCE(POINT(hip.ra, hip.de), POINT(gaia.ra, gaia.dec)) AS ang_sep
FROM public.hipparcos AS hip
JOIN gaiaedr3.gaia_source AS gaia ON
1 = CONTAINS(
   POINT(hip.ra, hip.de),
   CIRCLE(gaia.ra, gaia.dec, 0.00028))

 

Built-in positional cross-match: Hipparcos vs Gaia (only for registered users)

Use case: I want to carry out a 1" positional cross-match between these two catalogues.
Notes: It can be faster than an ADQL cross-match in some cases, but requires login. The output is a user table, not a job (see Sect. "Cross-match against small user table: Built-in tool" in this tutorial). Several pre-computed cross-matches against major surveys are available in the Gaia Archive.
Target table: gaiaedr3.gaia_source, public.hipparcos.
Query:

SELECT crossmatch_positional(
'public','hipparcos',
'gaiaedr3','gaia_source',1.0,
'xmatch_hipparcos_gaia')
FROM dual

 

ADQL positional cross-match + PROPER MOTION propagation

Use case: I want to propagate Gaia epoch J2016.0 positions form a sample of targets (the Pleiades) to an epoch in the past for comparison against a catalogue with astrometry with epoch J2000. Matches are defined when objects are closer than 1 arcsec. The Pleiades are defined, for simplicity, using a simple cone search with a radius of 5 degrees plus a simple circular cut in proper-motion space (with a radius of 6 mas yr-1).
Notes: This query applies the built-in ADQL function "EPOCH_PROP_POS". The "OFFSET 0" clause increases the query performance, as it instruct the Archive PostgreSQL data base backend to respect the logical order (subquery is executed first and other conditions applied afterwards - for more details, see this tutorial).
Target table: gaiaedr3.gaia_source, external.galex_ais
Query:

SELECT subquery.source_id, subquery.parallax, subquery.phot_g_mean_mag, galex.* 
FROM (
  SELECT gaia.* , EPOCH_PROP_POS(ra, dec, parallax, pmra, pmdec, dr2_radial_velocity, ref_epoch, 2000) AS propagated_position_vector
  FROM gaiaedr3.gaia_source AS gaia
  WHERE 1 = CONTAINS(
     POINT(56.75, 24.12),
     CIRCLE(gaia.ra, gaia.dec, 5.0))
     AND sqrt(power(gaia.pmra - 20.5, 2) + power(gaia.pmdec + 45.5, 2)) < 6.0
  OFFSET 0) AS subquery
JOIN external.galex_ais as galex
ON 1 = CONTAINS(
        POINT(coord1(subquery.propagated_position_vector), coord2(subquery.propagated_position_vector)),
        CIRCLE(galex.raj2000, galex.dej2000, 1./3600.))

 

retrieve average quantities per healpix level 8

Use case: I want to retrieve the number of stars and average quantities in all Healpixels of level 8 inside a rectangular patch on the sky (EDR3, Gaia Collaboration, Antoja et al. 2021 A&A 649, A8).
Target table: gaiaedr3.gaia_source
Notes: The internal TOP 10000 introduces a bias in the output and it is only used to ensure that this query is executed in less than a minute in the Archive. In this example, the SQL COUNT(*) function is used in combination with the GROUP BY clause.
Query:

SELECT subquery.healpix_8,COUNT(*) AS N, AVG(phot_g_mean_mag) AS avg_g, AVG(visibility_periods_used) AS avg_vp
FROM (
  SELECT TOP 10000 GAIA_HEALPIX_INDEX(8, source_id) AS healpix_8, phot_g_mean_mag, visibility_periods_used
  FROM gaiaedr3.gaia_source
  WHERE l < 240 AND l > 120 AND b < 60 AND b > -60 AND ruwe < 1.4
) AS subquery
GROUP BY subquery.healpix_8

 

Extinction-Corrected CMD diagram (HISTOGRAM)

Use case: I want to construct a dereddened CMD diagram (DR2,Gaia Collaboration, Andrae et al. 2018 A&A 616, A8; adapted Fig. 19).
Notes: The random_index is an indexed field included in each gaia_source table. In this example, it is used by the BETWEEN operator within a given range to ensure that this query is executed in less than a minute in the Archive.
Target table: gaiadr2.gaia_source
Query:

SELECT bp_rp_index / 40 AS bp_rp, g_abs_index / 10 AS g_abs, n
FROM (
  SELECT floor((bp_rp - e_bp_min_rp_val) * 40) AS bp_rp_index, floor((phot_g_mean_mag - a_g_val + 5 * log10(parallax) - 10) * 10) AS g_abs_index, count(*) AS n
  FROM gaiadr2.gaia_source
  WHERE parallax_over_error > 5
  AND a_g_val IS NOT NULL
  AND random_index BETWEEN 1000000 AND 1799999
  GROUP BY bp_rp_index, g_abs_index
) AS subquery

 

Light Curves / epoch photometry

Use case: I want to retrieve the light curves for two variable stars (DR2,Gaia Collaboration, Holl et al. 2018 A&A 618, A30; adapted Fig. A1).
Target table: gaiadr2.gaia_source, gaiadr2.vari_rrlyrae, gaiadr2.vari_long_period_variable, gaiadr2.vari_classifier_result
Query:

SELECT gaia.source_id, phot_variable_flag, best_class_name, datalink_url, sosrrl.pf, sosrrl.epoch_g, soslpv.frequency
FROM gaiadr2.gaia_source AS gaia
LEFT OUTER JOIN gaiadr2.vari_classifier_result AS variables USING (source_id)
LEFT OUTER JOIN gaiadr2.vari_rrlyrae AS sosrrl USING (source_id)
LEFT OUTER JOIN gaiadr2.vari_long_period_variable AS soslpv USING (source_id)
WHERE gaia.source_id IN (1372970619261887104, 5368939678153068288)

 

Cepheids: light curve retrieval for all stars

Use case: I want to retrieve complete epoch photometry of a given object class for offline analysis (DR1,Gaia Collaboration, Clementini et al. 2016 A&A 595, A133).
Notes: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial).
Target table: gaiadr1.cepheid, gaiadr1.phot_variable_time_series_gfov
Query:

SELECT gaia.*
FROM gaiadr1.phot_variable_time_series_gfov AS gaia
INNER JOIN gaiadr1.cepheid AS cep USING (source_id)

 

RR Lyrae: phase-folded light curve reconstruction, including errors

Use case: I want to construct a phase-folded light curve for comparison to stars with different periods (DR1,Gaia Collaboration, Brown et al. 2016 A&A 595, A2; adapted Fig. 7).
Notes: After Gaia DR1, light curves are being provided via the DataLink interface (see dedicated Tutorial).
Target table: gaiadr1.rrlyrae, gaiadr1.phot_variable_time_series_gfov
Query:

SELECT curves.observation_time, MOD(curves.observation_time - rrlyrae.epoch_g, rrlyrae.p1)/ rrlyrae.p1 AS phase,
curves.g_magnitude, 2.5 / log(10) * curves.g_flux_error / curves.g_flux AS g_magnitude_error
FROM gaiadr1.phot_variable_time_series_gfov AS curves
INNER JOIN gaiadr1.rrlyrae AS rrlyrae USING (source_id)
WHERE rrlyrae.source_id = 5284240582308398080

 

RR Lyrae: number of data points and estimated parameters

Use case: I want high-level information for a given class of variable objects (DR1, Gaia Collaboration, Clementini et al. 2016 A&A 595, A133).
Notes: After Gaia DR1, light curves are being provided via the DataLink interface (see dedicated Tutorial).
Target table: gaiadr1.rrlyrae, gaiadr1.phot_variable_time_series_gfov_statistical_parameters
Query:

SELECT stat.num_observations_processed, rr.*
FROM gaiadr1.phot_variable_time_series_gfov_statistical_parameters AS stat
INNER JOIN gaiadr1.rrlyrae AS rr USING (source_id)

 

Query timeouts

Author: Alcione Mora

A recurrent subject in Helpdesk requests is support on queries timing out before completion. In this tutorial, some common hints and recipes are provided.

This is an intermediate level tutorial that assumes a basic knowledge of the general interface and workflow. The introductory tutorials White dwarfs exploration and Cluster analysis are recommended in case of difficulties following this exercise.

Be careful with on-the-fly computations

Imagine we are interested in all stars in DR2 with proper motion larger than 1 arcsec/yr. A naive way of implementing the query could be:

select * from gaiadr2.gaia_source
where sqrt(pmra * pmra + pmdec * pmdec) > 1e3

where the proper motion units in gaia_source are mas/yr. This query will most probably time out because it requires computing a complex quantity, the total proper motion, for all rows in the table. This operation is too costly and prevents the request from being completed. The same would apply to a selection of very blue stars (BP – RP < -2)

select * from gaiadr2.gaia_source
where phot_bp_mean_mag - phot_rp_mean_mag < -2

or the highest precision parallaxes (SNR > 1000)

SELECT count (*)
FROM gaiadr2.gaia_source
WHERE parallax / parallax_error > 1000

Alternative formulations will be provided in the following sections for all these queries.

Use indexed fields

One common trick is to use indexed fields when possible. Let's see how two queries in the previous section can be rewritten to improve efficiency

Bluest stars:

select * from gaiadr2.gaia_source
where bp_rp  < -2

The query now produces 8947 records very soon

Highest precision parallaxes

SELECT *
FROM gaiadr2.gaia_source
WHERE parallax_over_error > 1000

Similarly, this query now returns 2892 rows

In both cases, the improvement has been obtained through the use of a pre-existing indexed column: bp_rp and parallax_over_error. In fact, part of the preparation for DR2 has been the identification, creation and indexation of the most common derived quantities to be used in queries.

Note this trick can only be applied to indexed columns. The information on a given field being indexed or not can be retrieved from a number of places. Probably the most convenient is the Archive GUI. Within the Search -> Advanced (ADQL) tab, the left panel shows a tree view of all available tables. When they are expanded, the different columns are shown. Any indexed field will appear in bold face. For example, a quick inspection reveals the Hipparcos new reduction HIP, RA and DEC columns are indexed, while many others (including the parallax) are not.

 

Use additional restrictions

Many times, the quantity of interest is not pre-computed, and no associated column is available for better filtering, e.g. the total proper motion. In those cases, adding additional restrictions based on indexed fields might be the difference between a successful and a failed query. Let us see some examples

High proper motion stars (> 1 arcsec/yr):

select * from gaiadr2.gaia_source
where (
  pmra < -707.1
  or pmra > 707.1
  or pmdec < -707.1
  or pmdec > 707.1
)
and sqrt(pmra * pmra + pmdec * pmdec) > 1e3

which returns 983 records. In this case, the trick has been to additionally exclude stars with either component of the proper motion smaller than 1 arcsec/yr / sqrt(2). Note this condition is trivially satisfied for the sample of interest but helps enormously the data base, because most objects are excluded based on two indexed columns (proper motion in RA and DEC), and only a handful of objects with already large proper motions are subsequently filtered for the costlier condition on the total proper motion modulus.

Geometrical conditions:

Similarly, geometrical restrictions may also simplify complex problems. For instance, the high proper motion stars query works properly if confined to the vicinity of the Large Magellanic Cloud (three degrees radius):

SELECT *
FROM gaiadr2.gaia_source
WHERE CONTAINS(
  POINT('ICRS', ra, dec),
  CIRCLE('ICRS', 80.89417, -69.75611, 3)
)=1
AND sqrt(pmra * pmra + pmdec * pmdec) > 1e3

which returns four objects

Use random samples

Sometimes, a sufficiently representative random sample might be enough to answer a scientific question. Random indices have thus been introduced as columns in gaiadr2.gaia_source, gaiadr1.gaia_source and gaiadr1.tgas_source. In all cases, the column random_index runs from zero to the total number of elements minus one. Total table sizes are 1692919135, 1142679769 and 2057050, respectively.

For example, absolute G magnitude vs G-Ks colour diagrams were constructed for the whole TGAS DR1 sample, as shown in Brown et al. (2016):

select gaia.source_id,
  gaia.phot_g_mean_mag + 5 * log10(gaia.parallax)- 10 as g_mag_abs,
  gaia.phot_g_mean_mag-tmass.ks_m as g_min_ks
from gaiadr1.tgas_source as gaia
inner join gaiadr1.tmass_best_neighbour as xmatch
  on gaia.source_id = xmatch.source_id
inner join gaiadr1.tmass_original_valid as tmass
  on tmass.tmass_oid = xmatch.tmass_oid
where gaia.parallax/gaia.parallax_error >= 5 and
  ph_qual = 'AAA' and
  sqrt(power(2.5/log(10)*gaia.phot_g_mean_flux_error
    /gaia.phot_g_mean_flux,2) ) <= 0.05 and
  sqrt(power(2.5/log(10)*gaia.phot_g_mean_flux_error
    /gaia.phot_g_mean_flux,2)
    + power(tmass.ks_msigcom,2)) <= 0.05

However, building a similar diagram with hundreds of millions of points is impractical for DR2. The following query shows how to retrieve the first random million points for the absolute G magnitude vs BP-RP colour diagram in DR2 (all fields within gaia_source, hence no joins with 2MASS):

select top 1000000
  source_id,
  phot_g_mean_mag + 5 * log10(parallax)- 10 as g_mag_abs,
  bp_rp
from gaiadr2.gaia_source
where parallax_over_error >= 5
and phot_bp_mean_flux_over_error > 0
and phot_rp_mean_flux_over_error > 0
and sqrt(
    power(2.5/log(10) / phot_bp_mean_flux_over_error, 2)
    + power(2.5/log(10) / phot_rp_mean_flux_over_error, 2)
  ) <= 0.05
order by random_index

A quick Topcat plot is presented below. Many prominent features can be easily identified

 

Use histograms

The construction of a DR2 colour-magnitude diagram is a good example of how histograms can help optimizing queries. In many cases, the values for each point are not interesting, but the global trends. The following (fairly advanced) query shows how to construct the equivalent histogram with bin sizes of 0.1 in BP-RP and G absolute magnitude:

select
  bp_rp_index / 10 as bp_rp,
  g_mag_abs_index / 10 as g_mag_abs,
  count(*) as n
from (
  select top 1000000 source_id,
    floor((phot_g_mean_mag+5*log10(parallax)-10) * 10) as g_mag_abs_index,
    floor(bp_rp * 10) as bp_rp_index
  from gaiadr2.gaia_source
  where parallax_over_error >= 5 and
    phot_bp_mean_flux_over_error > 0 and
    phot_rp_mean_flux_over_error > 0 and
    sqrt(
      power(2.5/log(10) / phot_bp_mean_flux_over_error, 2)
      + power(2.5/log(10) / phot_rp_mean_flux_over_error, 2)
    ) <= 0.05
  order by random_index
)as subquery
group by bp_rp_index, g_mag_abs_index

A quick view with Topcat reveals many features are still recognizable, even for such a large bin size

 

Divide the query into pieces

Sometimes a query is simply too large to be executed in one go, and the result of multiple requests needs to be combined afterwards. Using the random index for chopping it into pieces is usually advisable, to control the output size and to equalize the workload for each segment.

For example, imagine we want to retrieve points for the colour-magnitude diagram shown in the previous section, scanning gaia_source in chunks of 10 million sources. The following queries would carry out the first three steps of the job:

select
  source_id,
  phot_g_mean_mag + 5 * log10(parallax)- 10 as g_mag_abs,
  bp_rp
from gaiadr2.gaia_source
where parallax_over_error >= 5
and phot_bp_mean_flux_over_error > 0
and phot_rp_mean_flux_over_error > 0
and sqrt(
    power(2.5/log(10) / phot_bp_mean_flux_over_error, 2)
    + power(2.5/log(10) / phot_rp_mean_flux_over_error, 2)
  ) <= 0.05
and random_index between 0 and 9999999
select
  source_id,
  phot_g_mean_mag + 5 * log10(parallax)- 10 as g_mag_abs,
  bp_rp
from gaiadr2.gaia_source
where parallax_over_error >= 5
and phot_bp_mean_flux_over_error > 0
and phot_rp_mean_flux_over_error > 0
and sqrt(
    power(2.5/log(10) / phot_bp_mean_flux_over_error, 2)
    + power(2.5/log(10) / phot_rp_mean_flux_over_error, 2)
  ) <= 0.05
and random_index between 10000000 and 19999999
select
  source_id,
  phot_g_mean_mag + 5 * log10(parallax)- 10 as g_mag_abs,
  bp_rp
from gaiadr2.gaia_source
where parallax_over_error >= 5
and phot_bp_mean_flux_over_error > 0
and phot_rp_mean_flux_over_error > 0
and sqrt(
    power(2.5/log(10) / phot_bp_mean_flux_over_error, 2)
    + power(2.5/log(10) / phot_rp_mean_flux_over_error, 2)
  ) <= 0.05
and random_index between 20000000 and 29999999

The output contains 778361, 777144 and 775214 records, respectively. Note the between operator includes results at both ends.

Sometimes we are not interested in scanning a fixed number of entries in the catalogue, but a fraction. Imagine we want to scan the first three 1% chunks of the catalogue in search for points for the absolute magnitude vs colour diagram. The random index can still help us when combined with the table size (remember, 1692919135 for gaiadr2.gaia_source):

select
  source_id,
  phot_g_mean_mag + 5 * log10(parallax)- 10 as g_mag_abs,
  bp_rp
from gaiadr2.gaia_source
where parallax_over_error >= 5
and phot_bp_mean_flux_over_error > 0
and phot_rp_mean_flux_over_error > 0
and sqrt(
    power(2.5/log(10) / phot_bp_mean_flux_over_error, 2)
    + power(2.5/log(10) / phot_rp_mean_flux_over_error, 2)
  ) <= 0.05
and random_index between 0 * 0.01 * 1692919135 and 1 * 0.01 * 1692919135 – 1
select
  source_id,
  phot_g_mean_mag + 5 * log10(parallax)- 10 as g_mag_abs,
  bp_rp
from gaiadr2.gaia_source
where parallax_over_error >= 5
and phot_bp_mean_flux_over_error > 0
and phot_rp_mean_flux_over_error > 0
and sqrt(
    power(2.5/log(10) / phot_bp_mean_flux_over_error, 2)
    + power(2.5/log(10) / phot_rp_mean_flux_over_error, 2)
  ) <= 0.05
and random_index between 1 * 0.01 * 1692919135 and 2 * 0.01 * 1692919135 – 1
select
  source_id,
  phot_g_mean_mag + 5 * log10(parallax)- 10 as g_mag_abs,
  bp_rp
from gaiadr2.gaia_source
where parallax_over_error >= 5
and phot_bp_mean_flux_over_error > 0
and phot_rp_mean_flux_over_error > 0
and sqrt(
    power(2.5/log(10) / phot_bp_mean_flux_over_error, 2)
    + power(2.5/log(10) / phot_rp_mean_flux_over_error, 2)
  ) <= 0.05
and random_index between 2 * 0.01 * 1692919135 and 3 * 0.01 * 1692919135 - 1

where the results contain 1316985, 1312230 and 1314951 records, respectively.

Please do NOT include conditions on integral division remainders between the random_index and the fraction to explore. That is, for the previous example of the first three 1% chunks of the catalogue:

where mod(random_index, 100) = 0
where mod(random_index, 100) = 1
where mod(random_index, 100) = 2

Even though they are formally equivalent conditions, the data base would take a very long time and eventually fail, because an operation would be carried out on each row, instead of a quick selection on random_index values.

Ask for longer execution time quota

If everything else fails, and this is the only way your science can be done, please send a motivated request to the Gaia Helpdesk with an estimate on how much additional time or user space quota is needed. Include when possible the offending queries because they can sometimes be optimized.

​​​​​​​

ADQL syntax

 

ADQL (Astronomical Data Query Language) is based on SQL (Structured Query Language) which is a language to retrieve information from tables stored in a database (references: ADQL 2.0 and SQL 92). There are a number of tutorials and resources providing a gentle learning curve for newcomers, specially those without previous experience with SQL or relational databases. A small selection is provided below:

 

The ADQL version implemented in the Gaia ESA Archive contains all the ADQL 2.0 functions plus a number of extra functions developed by the Archive team to facilitate the analysis and exploration of the catalogues hosted by the Archive. They are listed below:

  1. ADDITIONAL FUNCTIONS AVAILABLE
  2. ADQL MATHEMATICAL FUNCTIONS
  3. ADQL TRIGONOMETRIC FUNCTIONS
  4. DATA TYPE CASTING ADQL FUNCTIONS
  5. ADQL EXTENSION: CONDITIONAL EXPRESSIONS
  6. DEVIATIONS OF ADQL FUNCTION IMPLEMENTATIONS FROM STANDARD

 

1. Additional functions available

Apart from the standard ADQL functions, the ESA Gaia TAP+ service offers the following functions:

Table Gaia TAP+ ADQL functions

Function

Return Type

Description

Example

Result

STDDEV(expression)

Numeric

Standard deviation function

STDEV(column)  
GAIA_HEALPIX_INDEX(norder,source_id)

bigint

Returns the healpix index of the given

  • norder [integer]: order of the desired healpix output

  • source_id [bigint]: Gaia source identifier

GAIA_HEALPIX_INDEX(4, 2060294888487267584) 914
GREATEST(v1,v2[,v3,..,vn])

Same as input

Greatest value among the given arguments

GREATEST(10.55, 9.12323, 11.2, 7.8) 11.2
LEAST(v1,v2[,v3,..,vn])

Same as input

Least value among the given arguments

LEAST(10.55, 9.12323, 11.2, 7.8) 7.8
SIGN(x)

Integer

Sign of the argument (-1, 0, +1)

SIGN(-10.55) -1
COALESCE(v1,[]v2,v3,..,vn])

Same as input

Returns first argument that is not null. If all arguments are null, it will return null

COALESCE(NULL, 1, 2) 1
NULLIF(v1,v2)

Same as input

Returns a null value if v1 equals v2, otherwise it returns v1

NULLIF(1, 1) NULL
WIDTH_BUCKET(operand,min,max,buckets)

Integer

Returns the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range min to max; returns 0 or count+1 for an input outside the range

WIDTH_BUCKET(5.35, 0.024, 10.06, 5) 3

 

Also, the ESA Gaia TAP+ service offers functions for array handling:

Table Gaia TAP+ array handling functions

Function

Description

Example

Result

ARRAY_ELEMENT(array_column, index1 [,index2....,indexN])

Returns the requested element inside the array, 1 being the first index value

Given the array [4,5,6]

SELECT array_element(array,2) : 5						
ARRAY_NDIMS(array_column)

Returns the number of dimensions of the array (integer)

Given the array [[1,2,3], [4,5,6]]

SELECT array_ndims(array) : 2
ARRAY_DIMS(array_column)

Returns a text representation of the array's dimensions. Initial and end index for each dimension are given

Given the array [[1,2,3], [4,5,6]]

SELECT array_dims(array) : [1:2][1:3]
ARRAY_LENGTH(array_column, index)

Returns the length of the requested array dimension (integer), 1 being the first index value

Given the array [1,2,3]

SELECT array_length(array,1) : 3
CARDINALITY(array_column)

Returns the total number of elements in the array (integer), or 0 if the array is empty

Given the array [[1,2],[3,4]]

SELECT cardinality(array) : 4

 

2. ADQL mathematical functions

ADQL defines some mathematical functions. Also, other mathematical functions have been defined in our service to support scientific queries.

Table Mathematical ADQL functions

Function

Return Type

Description

Example

Result

ABS(x)

same as input

Absolute value

ABS(-19.4) 19.4
CBRT(x)

numeric

Cube root

CBRT(27.0) 3
DEGREES(x)

numeric

Radians to degrees

DEGREES(0.5) 28.64788975654116
DIV(y,x)

numeric

Integer quotient of y/x

DIV(9,4) 2
EXP(x)

same as input

Exponential

EXP(1.0) 2.718281828459045
FLOOR(x)

same as input

Nearest integer less than or equal to argument

FLOOR(-42.8) -43
LOG(x)

same as input

Natural logarithm

LOG(2.0) 0.6931471805599453
LOG(b,x)

numeric

Logarithm to base b

LOG(2.0, 64.0) 6.0000000000
LOG10(x)

numeric

Base 10 logarithm

LOG10(100.0) 2
MOD(y,x)

same as arguments

Remainder of y/x

MOD(9, 4) 1
PI()

numeric

Pi constant

PI() 3.141592653589793
POWER(x,y)

numeric

x raised to the power of y

POWER(9.0, 3.0) 729
RADIANS(x)

numeric

Degrees to radians

RADIANS(45.0) 0.7853981633974483
RAND(x)

numeric

Random number in the range 0<=x<1

RAND()  
ROUND(x,s)

numeric

Round x to s decimal places, where s is an integer

ROUND(45.2191,2) 45.22
SQRT(x)

numeric

Square root

SQRT(2.0) 1.414213562373095
TRUNCATE(x)

numeric

Truncate towards zero

TRUNCATE(48.8) 48
TRUNCATE(x, s)

numeric

Truncate to s decimal places

TRUNCATE(48.8328, 3) 48.832

 

3. ADQL trigonometric functions

ADQL defines some trigonometric functions considered important for astronomical queries.

Table trigonometric ADQL functions

Function

Return Type

Description

Example

Result

ACOS(x)

Numeric

Inverse cosine or arc cosine

ACOS(0.12582) 1.4446419701843678
ASIN(x)

Numeric

Inverse sine or arc sine

ASIN(0.12582) 0.1261543566105288
ATAN(x)

Numeric

Inverse tangent or arc tangent

ASIN(10.28527) 1.4738745386849255
ATAN2(x,y)

Numeric

Inverse tangent of x/y

ATAN2(10.28527,3.1) 1.2780538751678443
COS(x)

Numeric

Cosine of x

COS(10.28527) -0.6520645009291157
SIN(x)

Numeric

Sine of x

SIN(10.28527) -0.7581634959743598
TAN(x)

Numeric

Tangent of x

TAN(10.28527) 1.1627124232251034
COT(x)

Numeric

Cotangent of x

COT(0.785) 1.000796644031489

 

4. Data type casting ADQL functions

Some User Defined Functions (UDFs) have been implemented in order to allow the casting of values between different data types. The casting functions rely on the underlying PostgreSQL functions so errors received during casting will be, in most of the cases, PosgreSQL errors.

Table Data type casting ADQL functions

Function

Return Type

Description

Example

Result

TO_SMALLINT(x)

int2

Conversion of valid values into smallint. From -2^15 to 2^15-1

TO_SMALLINT(17.4) 17
TO_INTEGER(x)

int4

Conversion of valid values into integer. From -2^31 to 2^31-1

TO_INTEGER(1713112213.4123) 1713112213
TO_BIGINT(x)

int8

Conversion of valid values into bigint. From -2^63 to 2^63-1

TO_BIGINT(1713112213.4123) 1713112213
TO_REAL(x)

float4

Conversion of valid values into real (8 decimal digits precision)

TO_REAL(91323.1231) 91323.125
TO_DOUBLE(x)

float8

Conversion of valid values into double precision (16 decimal digits precision)

TO_DOUBLE(91321213.112212231) 91321213.11221223
TO_CHAR(x)

char

Convert valid values into char data type

TO_CHAR(1123) '1123'
TO_CHAR(v1, v2)

char

Convert valid values into char data type, following the format defined in v2. For a full list of valid format, check Formats

TO_CHAR(-125.8, '999D99S') '125.80-'
TO_BOOLEAN(v1)

boolean

Convert valid values into Boolean data type

TO_BOOLEAN(1) true

 

5. ADQL extension: conditional expressions

Some conditional expressions have been implemented as User Defined Functions.

Note: 'is' and 'in' are reserved ADQL keywords. Conditions including them are possible, but need to be escaped within single quotes for a correct execution. See, for example, the following queries:

SELECT TOP 10 random_index, parallax, if_then_else('parallax is null', 1, 0)
FROM gaiaedr3.gaia_source
ORDER BY random_index

 

SELECT TOP 10 random_index, parallax, if_then_else('random_index in (0, 2, 4, 6, 8)', 1, 0)
FROM gaiaedr3.gaia_source
ORDER BY random_index

 

Table Conditional Expressions ADQL functions

Function

PostgreSQL expression replicated

Example

CASE_CONDITION(default_value, condition1, value1, condition2, value2, ...)
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
case_condition(astrometric_n_obs_al, dec < -40, -astrometric_n_obs_al, dec > 40, astrometric_n_obs_al / 2)
CASE_EXPRESSION(input_column, default_value, target_value1, value1, target_value2, value2, ...)
CASE a
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
case_expression(astrometric_n_obs_al, 'unknown', 85, 'eighty five', 78, 'seventy eight', 228, 'two hundred and twenty eight', 3, 'three', 4, 'four')
IF_THEN_ELSE(condition, value, [default_value])
CASE WHEN condition THEN result
[ELSE result]
END
if_then_else(dec < 0, astrometric_n_obs_al, -astrometric_n_obs_al)

 

 

6. Deviations of ADQL function implementationS from standard

The ADQL standard is implemented in compliance with IVOA ADQL 2.0 plus the ADQL Errata available on 16 April 2018. Some implementation limitations apply for the following functions:

  1. BOX(coordsys, longitudeCentre, latitudeCentre, longitudeExtent, latitudeExtent): this function is interpreted as follows:
    • As defined in the standard when the arguments are fixed values: a cross at the central position with arms extending, parallel to the coordinate axes at the centre position, for half the respective sizes on either side.
    • When the arguments are variable (e.g. table columns): the sides of the box are line segments or great circles intersecting the arms of the cross in its end points at right angles with the arms.

Epoch Propagation

 

The epoch propagation algorithm used is based on the Hipparcos library released with the Hipparcos catalogue. It supports fully reversible transformation of the six elements vector (positions, parallax and proper motions) and their associated errors and correlation coefficients. For more information on the transformations, please refer to the official ESA 1997 SP-1200 reference document "The Hipparcos and Tycho Catalogues".

Note the radial proper motion uncertainty is computed differently, following Michalik et al. (2014), because of its better handling of small radial velocities or parallaxes (as compared to their uncertainties).

It is also worth noting that Butkevich & Lindegren (2014) introduced more advanced epoch propagation transformations which include light-time effects. The more complicated formulae in Appendix B, which take into account light-time effects, are theoretically ideal but in practice not since since they cannot handle general cases of small (or even negative) parallaxes, as is possible with the "classical" formulae (Appendix C). It should be noted that the current implementation is equivalent to that of 1) Sect. 1.5.5, Vol. 1 of The H and Tycho Catalogues (ESA 1997); 2) Michalik et al. (2014), although formulated differently, 3) Appendix C of  Butkevich & Lindegren (2014), and 4) the Gaia EDR3 online documentation.

These routines can be applied both to Gaia and Hipparcos data. Both catalogues use slightly different notation for each field, a comparison table is provided in last section. Most examples are based on Barnard's Star, having the highest proper motion, with Hipparcos and Gaia identifiers HIP 87937 and Gaia DR2 4472832130942575872. They will be based on both catalogues, when possible.
The available ADQL functions described in this section are:

Note zero is used as default value for any missing null input parameter. This is a reasonable choice for most stars because those quantities would be either small (parallax and proper motion) or irrelevant (radial velocity). However, this is not true for very close stars to the Sun, where appropriate values need to be taken from the literature (e.g. average velocity field in the solar neighbourhood).

 

EPOCH_PROP

 

This function returns the full six astrometric parameters (i.e. ra, dec, plx, pmra, pmdec, pmr) propagated from the reference epoch T0 to an arbitrary epoch T1. This function allows two different sets as input:

  • EPOCH_PROP(ra,dec,plx,pmra,pmdec,rv,T0,T1):
    • ra [double]: Right Ascension (deg)
    • dec [double]: Declination (deg)
    • plx [double]: Parallax (mas)
    • pmra [double]: Proper Motion in Right Ascension (mas/yr)
    • pmde [double]: Proper Motion in Declination (mas/yr)
    • rv [double]: Radial Velocity (km/s)
    • T0 [double]: Reference Julian Epoch (yr)
    • T1 [double]: Final Julian Epoch (yr)
     

    The output of this function is an array (double[6]epoch_prop) where:

    • epoch prop[1]: ra, Right Ascension (deg)
    • epoch prop[2]: dec, Declination (deg)
    • epoch prop[3]: plx, Parallax (mas)
    • epoch prop[4]: pmra, Proper Motion in Right Ascension (mas/yr)
    • epoch prop[5]: pmde, Proper Motion in Declination (mas/yr)
    • epoch prop[6]: pmr, Radial Proper Motion (mas/yr)
     
    SELECT EPOCH_PROP(ra, dec, parallax, pmra, pmdec, radial_velocity, 2015.5, 2000)
    FROM gaiadr2.gaia_source
    WHERE source_id = 4472832130942575872
    
    SELECT EPOCH_PROP(ra,de,plx,pmra,pmde,-111.0,1991.25,2000)
    FROM public.hipparcos
    WHERE hip=87937
    
     

    The output radial proper motion is set to the input value in case of null or zero input parallax. The usual transformations are applied otherwise. Note this quantity is defined as pmr (zeta, mu_r) = radial_velocity * parallax/ A, as explained in the Hipparcos reference documentation, Sec. 1.5.5. It is thus different and has different units than the radial velocity. Great care is needed to interpret the results in case of very small but non-zero parallax (below microarcseconds).

     

    The output radial proper motion units are mas/yr, and it is left to the user to decide when the transformation into radial velocities in km/s is applicable, taking into account this transformation is proportional to the inverse of the parallax. To do so a dedicated function RADIAL_VELOCITY is provided as described in the corresponding section.

     

    To access the individual output array elements, ARRAY_ELEMENT can be used, indicating the index of the particular element as follows:

    SELECT array_element(a0,1) as ra_prop,
           array_element(a0,2) as dec_prop,
           array_element(a0,3) as parallax_prop,
           array_element(a0,4) as pmra_prop,
           array_element(a0,5) as pmdec_prop,
           array_element(a0,6) as rv_prop
    FROM
       (
    	SELECT top 50 ra,dec,parallax,pmra,pmdec,radial_velocity,
    	epoch_prop(ra,dec,parallax,pmra,pmdec,radial_velocity,2015.5,2000) as a0
    	FROM gaiadr2.gaia_source
    	WHERE parallax IS NOT NULL
    		AND radial_velocity IS NOT NULL
    	ORDER BY random_index ASC
    	) as p
    
    SELECT ARRAY_ELEMENT(
    	(EPOCH_PROP(ra,de,plx,pmra,pmde,0,1991.25,2000))
    	,1) AS ra
    FROM public.hipparcos
    WHERE hip=87937 
     

    Note the first index in a PostgreSQL array is 1 by default.

  • EPOCH_PROP(double[6]astrometric_parameters,T0,T1):
    • double[6]astrometric parameters: astrometric parameters array, where:
    • astrometric parameters[1]: ra, Right Ascension (deg)
    • astrometric parameters[2]: dec, Declination (deg)
    • astrometric parameters[3]: plx, Parallax (mas)
    • astrometric parameters[4]: pmra, Proper Motion in Right Ascension (mas/yr)
    • astrometric parameters[5]: pmde, Proper Motion in Declination (mas/yr)
    • astrometric parameters[6]: rv, Radial Velocity(km/s)
    • T0 [double]: Reference Julian Epoch (yr)
    • T1 [double]: Final Julian Epoch (yr)

    The output of this function is an array (double[6]epoch_prop) where:

    • epoch prop[1]: ra, Right Ascension (deg)
    • epoch prop[2]: dec, Declination (deg)
    • epoch prop[3]: plx, Parallax (mas)
    • epoch prop[4]: pmra, Proper Motion in Right Ascension (mas/yr)
    • epoch prop[5]: pmde, Proper Motion in Declination (mas/yr)
    • epoch prop[6]: pmr, Radial Proper Motion (mas/yr)

    The astrometric parameters input array can be obtained via the ASTROMETRIC_PARAMETERS ADQL function, described in this section.

    SELECT EPOCH_PROP(
      ASTROMETRIC_PARAMETERS(ra, dec, parallax, pmra, pmdec, radial_velocity),
      2015.5, 2000)
    FROM gaiadr2.gaia_source
    WHERE source_id = 4472832130942575872
    
    SELECT EPOCH_PROP(
    	ASTROMETRIC_PARAMETERS(ra,de,plx,pmra,pmde,-111.0),
    	1991.25,2000)
    FROM public.hipparcos
    WHERE hip=87937
    

 

EPOCH_PROP_POS

 

EPOCH_PROP_POS(ra,dec,plx,pmra,pmdec,rv,T0,T1): Returns an ADQL point with the propagated position (RA, Dec) from the reference epoch T0 to an arbitrary epoch T1, where:

  • ra [double]: Right Ascension (deg)
  • dec [double]: Declination (deg)
  • plx [double]: Parallax (mas)
  • pmra [double]: Proper Motion in Right Ascension (mas/yr)
  • pmde [double]: Proper Motion in Declination (mas/yr)
  • rv [double]: Radial Velocity (km/s)
  • T0 [double]: Reference Julian Epoch (yr)
  • T1 [double]: Final Julian Epoch (yr)

 

This function was originally called EPOCH_PROP in Archive version 1.3.0, but has been renamed to explicitly state only the positions are propagated, and not the full six component vector also including parallax, proper motions and radial velocity.

To retrieve only the Right Ascension or the Declination values in degrees from the output position of the EPOCH_PROP function it would require the use of the ADQL functions COORD1 and COORD2 respectively:

SELECT COORD1(
  EPOCH_PROP_POS(ra, dec, parallax, pmra, pmdec, radial_velocity, 2015.5, 2000))
FROM gaiadr2.gaia_source
WHERE source_id = 4472832130942575872
SELECT COORD2(
  EPOCH_PROP_POS(ra, dec, parallax, pmra, pmdec, radial_velocity, 2015.5, 2000))
FROM gaiadr2.gaia_source
WHERE source_id = 4472832130942575872
SELECT COORD1(
	EPOCH_PROP_POS(ra, de, plx, pmra, pmde, -111.0, 1991.25, 2000))
FROM public.hipparcos
WHERE hip=87937
SELECT COORD2(
	EPOCH_PROP_POS(ra, de, plx, pmra, pmde, -111.0, 1991.25, 2000))
FROM public.hipparcos
 WHERE hip=87937

 

It is possible to use this function as input for other ADQL geometrical functions. For example, to compute the distance between the propagated position for a specific source and another random point:

SELECT DISTANCE(
	EPOCH_PROP_POS(ra, dec, parallax, pmra, pmdec, radial_velocity, 2015.5, 2000),
	POINT('ICRS', 266.41683, -29.00781)) AS dist
FROM gaiadr2.gaia_source
WHERE source_id = 4472832130942575872
SELECT DISTANCE(
	EPOCH_PROP_POS(ra, de, plx, pmra, pmde, -111.0, 1991.25, 2000),
	POINT('ICRS', 266.41683, -29.00781)) AS dist
FROM public.hipparcos
WHERE hip=87937

 

ASTROMETRIC_PARAMETERS

 

ASTROMETRIC_PARAMETERS(ra,de,plx,pmra,pmde,rv): Returns the full six input parameters in the correct format needed by the EPOCH_PROP routine, where:

  • ra [double]: Right Ascension (deg)
  • dec [double]: Declination (deg)
  • plx [double]: Parallax (mas)
  • pmra [double]: Proper Motion in Right Ascension (mas/yr)
  • pmde [double]: Proper Motion in Declination (mas/yr)
  • rv [double]: Radial Velocity (km/s)

The output of this function is an array (double[6]astrometric_parameters) where:

  • astrometric parameters[1]: ra(deg)
  • astrometric parameters[2]: dec(deg)
  • astrometric parameters[3]: plx(mas)
  • astrometric parameters[4]: pmra(mas/yr)
  • astrometric parameters[5]: pmde(mas/yr)
  • astrometric parameters[6]: pmr(mas/yr)
SELECT ASTROMETRIC_PARAMETERS(ra, dec, parallax, pmra, pmdec, radial_velocity)
FROM gaiadr2.gaia_source
WHERE source_id = 4472832130942575872
SELECT ASTROMETRIC_PARAMETERS(ra,de,plx,pmra,pmde,-111.0)
FROM public.hipparcos
WHERE hip=87937

 

EPOCH_PROP_ERROR

 

Returns the errors and correlation coefficients of the propagated position from the reference epoch T0 to the arbitrary epoch T1. The two index correlation matrix can be constructed from those data. Errors and correlation coefficients are used instead for convenience and consistency with the Gaia and Hipparcos catalogues. An exception is raised for input stars without a five parameters astrometric solution, that is lacking parallaxes and proper motions. There are two accepted sets of input for this function:

Note the first index in a PostgreSQL array is 1 by default.

  • EPOCH_PROP_ERROR (ra,dec,plx,pmra,pmdec,rv,e_ra,e_dec,e_plx,e_pmra,e_pmdec,e_vr,de_ra,plx_ra,pmra_ra,pmde_ra,plx_de,pmra_de,pmde_de, pmra_plx, pmde_plx, pmde_pmra, T0,T1):  

    This transformation is only applicable for radial velocities independently determined from the astrometry, such as those obtained with a spectrometer. It is not applicable for the back-transformation of data already propagated to another epoch. The output of this function is an array with the propagated 6 errors and 15 correlation coefficients where:

    SELECT EPOCH_PROP_ERROR(
    	ra, dec, parallax, pmra, pmdec, radial_velocity,
    	ra_error, dec_error, parallax_error, pmra_error, pmdec_error, radial_velocity_error,
    	ra_dec_corr, ra_parallax_corr, ra_pmra_corr, ra_pmdec_corr,
    	dec_parallax_corr, dec_pmra_corr, dec_pmdec_corr,
    	parallax_pmra_corr, parallax_pmdec_corr, pmra_pmdec_corr,
    	2015.5, 1900)
    FROM gaiadr2.gaia_source
    WHERE source_id = 4472832130942575872
    
    SELECT EPOCH_PROP_ERROR(
    	ra, de, plx, pmra, pmde, -111.0,
    	e_radeg, e_dedeg, e_plx, e_pmra, e_pmde, 0.5,
    	dera, plxra, pmrara, pmdera, plxde, pmrade, pmdede, pmraplx, pmdeplx, pmdepmra,
    	1991.25, 2000.0)
    FROM public.hipparcos
    WHERE hip=87937
    

    For example, in order to retrieve the propagated uncertainty of the declination(e_dec) that corresponds to the second element of the array:

    SELECT ARRAY_ELEMENT(
    		(EPOCH_PROP_ERROR(
      			ra, dec, parallax, pmra, pmdec, radial_velocity,
      			ra_error, dec_error, parallax_error, pmra_error, pmdec_error, radial_velocity_error,
      			ra_dec_corr, ra_parallax_corr, ra_pmra_corr, ra_pmdec_corr,
      			dec_parallax_corr, dec_pmra_corr, dec_pmdec_corr,
      			parallax_pmra_corr, parallax_pmdec_corr, pmra_pmdec_corr,
      			2015.5, 1900))
      		, 2)
    FROM gaiadr2.gaia_source
    WHERE source_id = 4472832130942575872
    
    SELECT ARRAY_ELEMENT(
    	(EPOCH_PROP_ERROR(
    		ra, de, plx, pmra, pmde, -111.0,
    		e_radeg, e_dedeg, e_plx, e_pmra, e_pmde, 0.5,
    		dera, plxra, pmrara, pmdera, plxde, pmrade, pmdede, pmraplx, pmdeplx, pmdepmra,
    		1991.25, 1900))
    	,2)
    FROM public.hipparcos
    WHERE hip=87937
    
    • ra [double]: Right Ascension (deg)
    • dec [double]: Declination (deg)
    • plx [double]: Parallax (mas)
    • pmra [double]: Proper Motion in Right Ascension (mas/yr)
    • pmde [double]: Proper Motion in Declination (mas/yr)
    • rv [double]: Radial Velocity (km/s)
    • e_ra [double]: Error in Right Ascension (mas)
    • e_dec [double]:Error in Declination (mas)
    • e_plx [double]: Error in Parallax (mas)
    • e_pmra [double]: Error in Proper Motion in RA (mas/yr)
    • e_pmdec [double]: Error in Proper Motion in Dec (mas/yr)
    • e_vr [double]: Error in Radial Velocity (km/s)
    • de_ra [double]: Correlation Coefficient, RA/Dec
    • plx_ra [double]: Correlation Coefficient, Parallax/RA
    • pmra_ra [double]: Correlation Coefficient, Proper Motion in RA/RA
    • pmde_ra [double]: Correlation Coefficient, Proper Motion in Dec/RA
    • plx_de [double]: Correlation Coefficient, Parallax/Dec
    • pmra_de [double]: Correlation Coefficient, Proper Motion in RA/Dec
    • pmde_de [double]: Correlation Coefficient, Proper Motion in Dec/Dec
    • pmra_plx [double]: Correlation Coefficient, Proper Motion in RA/Parallax
    • pmde_plx [double]: Correlation Coefficient, Proper Motion in Dec/Parallax
    • pmde_pmra [double]:Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
    • astrometric parameter_error[1]: e_ra, Error in Right Ascension (mas)
    • astrometric parameter_error[2]: e_dec, Error in Declination (mas)
    • astrometric parameter_error[3]: e_plx, Error in Parallax (mas)
    • astrometric parameter_error[4]: e_pmra, Error in Proper Motion in RA (mas/yr)
    • astrometric parameter_error[5]: e_pmde, Error in Proper Motion in Dec(mas/yr)
    • astrometric parameter_error[6]: e_pmr, Error in Radial Proper Motion (mas/yr)
    • astrometric parameter_error[7]: de_ra, Correlation Coefficient, Dec/RA
    • astrometric parameter_error[8]: plx_ra, Correlation Coefficient, Parallax/RA
    • astrometric parameter_error[9]: pmra_ra, Correlation Coefficient, Proper Motion in RA/RA
    • astrometric parameter_error[10]: pmde_ra, Correlation Coefficient, Proper Motion in Dec/RA
    • astrometric parameter_error[11]: pmr_ra, Correlation Coefficient, Radial Proper Motion/RA
    • astrometric parameter_error[12]: plx_de, Correlation Coefficient, Parallax/Dec
    • astrometric parameter_error[13]: pmra_de, Correlation Coefficient, Proper Motion in RA/Dec
    • astrometric parameter_error[14]: pmde_de, Correlation Coefficient, Proper Motion in Dec/Dec
    • astrometric parameter_error[15]: pmr_de, Correlation Coefficient, Radial Proper Motion/Dec
    • astrometric parameter_error[16]: pmra_plx, Correlation Coefficient, Proper Motion in RA/Parallax
    • astrometric parameter_error[17]: pmde_plx, Correlation Coefficient, Proper Motion in Dec/Parallax
    • astrometric parameter_error[18]: pmr_plx, Correlation Coefficient, Radial Proper Motion/Parallax
    • astrometric parameter_error[19]: pmde_pmra, Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
    • astrometric parameter_error[20]: pmr_pmra, Correlation Coefficient, Radial Proper Motion/Proper Motion in RA
    • astrometric parameter_error[21]: pmr_pmde, Correlation Coefficient, Radial Proper Motion/Proper Motion in Dec
  • EPOCH_PROP_ERROR(double[6] astrometric_parameters, double[21] astrometric_parameter_error,T0,T1):  

    This function is applicable in all cases, and makes no assumption on the correlation between the different parameters. The output of this function is an array with all the uncertainties and correlation coefficients, where:

    SELECT EPOCH_PROP_ERROR(
    	ASTROMETRIC_PARAMETERS(ra, dec, parallax, pmra, pmdec, radial_velocity),
    	ASTROMETRIC_PARAMETER_ERROR(ra_error, dec_error, parallax_error, pmra_error, pmdec_error, ra_dec_corr, ra_parallax_corr, ra_pmra_corr, ra_pmdec_corr, dec_parallax_corr, dec_pmra_corr, dec_pmdec_corr, parallax_pmra_corr, parallax_pmdec_corr, pmra_pmdec_corr, parallax,radial_velocity,radial_velocity_error),2015.5,1900)
    FROM gaiadr2.gaia_source
    WHERE source_id = 4472832130942575872
    
    SELECT EPOCH_PROP_ERROR(
    	ASTROMETRIC_PARAMETERS(ra,de,plx,pmra,pmde,-111.0),
    	ASTROMETRIC_PARAMETER_ERROR(e_radeg, e_dedeg, e_plx, e_pmra, e_pmde, dera, plxra, pmrara, pmdera, plxde, pmrade, pmdede, pmraplx, pmdeplx, pmdepmra, plx,-111.0,0.5),1991.25,1900)
    FROM public.hipparcos
    WHERE hip=87937
    
     
    • astrometric_parameters[6] (output from ASTROMETRIC_PARAMETERS ADQL function):
      • astrometric parameters[1]: ra, Right Ascension (deg)
      • astrometric parameters[2]: dec, Declination (deg)
      • astrometric parameters[3]: plx, Parallax (mas)
      • astrometric parameters[4]: pmra, Proper Motion in Right Ascension (mas/yr)
      • astrometric parameters[5]: pmde, Proper Motion in Declination (mas/yr)
      • astrometric parameters[6]: pmr, Radial Proper Motion (mas/yr)
    • astrometric_parameter_error[21] (output from ASTROMETRIC_PARAMETER_ERROR ADQL function):
      • astrometric parameter_error[1]: e_ra, Error in Right Ascension (mas)
      • astrometric parameter_error[2]: e_dec, Error in Declination (mas)
      • astrometric parameter_error[3]: e_plx, Error in Parallax (mas)
      • astrometric parameter_error[4]: e_pmra, Error in Proper Motion in RA (mas/yr)
      • astrometric parameter_error[5]: e_pmde, Error in Proper Motion in Dec(mas/yr)
      • astrometric parameter_error[6]: e_pmr, Error in Radial Proper Motion (mas/yr)
      • astrometric parameter_error[7]: de_ra, Correlation Coefficient, Dec/RA
      • astrometric parameter_error[8]: plx_ra, Correlation Coefficient, Parallax/RA
      • astrometric parameter_error[9]: pmra_ra, Correlation Coefficient, Proper Motion in RA/RA
      • astrometric parameter_error[10]: pmde_ra, Correlation Coefficient, Proper Motion in Dec/RA
      • astrometric parameter_error[11]: pmr_ra, Correlation Coefficient, Radial Proper Motion/RA
      • astrometric parameter_error[12]: plx_de, Correlation Coefficient, Parallax/Dec
      • astrometric parameter_error[13]: pmra_de, Correlation Coefficient, Proper Motion in RA/Dec
      • astrometric parameter_error[14]: pmde_de, Correlation Coefficient, Proper Motion in Dec/Dec
      • astrometric parameter_error[15]: pmr_de, Correlation Coefficient, Radial Proper Motion/Dec
      • astrometric parameter_error[16]: pmra_plx, Correlation Coefficient, Proper Motion in RA/Parallax
      • astrometric parameter_error[17]: pmde_plx, Correlation Coefficient, Proper Motion in Dec/Parallax
      • astrometric parameter_error[18]: pmr_plx, Correlation Coefficient, Radial Proper Motion/Parallax
      • astrometric parameter_error[19]: pmde_pmra, Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
      • astrometric parameter_error[20]: pmr_pmra, Correlation Coefficient, Radial Proper Motion/Proper Motion in RA
      • astrometric parameter_error[21]: pmr_pmde, Correlation Coefficient, Radial Proper Motion/Proper Motion in Dec
    • T0: [double]: Reference Julian Epoch (yr)
    • T1: [double]: Final Julian Epoch (yr)
    • astrometric parameter_error[1]: e_ra, Error in Right Ascension (mas)
    • astrometric parameter_error[2]: e_dec, Error in Declination (mas)
    • astrometric parameter_error[3]: e_plx, Error in Parallax (mas)
    • astrometric parameter_error[4]: e_pmra, Error in Proper Motion in RA (mas/yr)
    • astrometric parameter_error[5]: e_pmde, Error in Proper Motion in Dec(mas/yr)
    • astrometric parameter_error[6]: e_pmr, Error in Radial Proper Motion (mas/yr)
    • astrometric parameter_error[7]: de_ra, Correlation Coefficient, Dec/RA
    • astrometric parameter_error[8]: plx_ra, Correlation Coefficient, Parallax/RA
    • astrometric parameter_error[9]: pmra_ra, Correlation Coefficient, Proper Motion in RA/RA
    • astrometric parameter_error[10]: pmde_ra, Correlation Coefficient, Proper Motion in Dec/RA
    • astrometric parameter_error[11]: pmr_ra, Correlation Coefficient, Radial Proper Motion/RA
    • astrometric parameter_error[12]: plx_de, Correlation Coefficient, Parallax/Dec
    • astrometric parameter_error[13]: pmra_de, Correlation Coefficient, Proper Motion in RA/Dec
    • astrometric parameter_error[14]: pmde_de, Correlation Coefficient, Proper Motion in Dec/Dec
    • astrometric parameter_error[15]: pmr_de, Correlation Coefficient, Radial Proper Motion/Dec
    • astrometric parameter_error[16]: pmra_plx, Correlation Coefficient, Proper Motion in RA/Parallax
    • astrometric parameter_error[17]: pmde_plx, Correlation Coefficient, Proper Motion in Dec/Parallax
    • astrometric parameter_error[18]: pmr_plx, Correlation Coefficient, Radial Proper Motion/Parallax
    • astrometric parameter_error[19]: pmde_pmra, Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
    • astrometric parameter_error[20]: pmr_pmra, Correlation Coefficient, Radial Proper Motion/Proper Motion in RA
    • astrometric parameter_error[21]: pmr_pmde, Correlation Coefficient, Radial Proper Motion/Proper Motion in Dec
 

​​​​​​​​​​​​​​ASTROMETRIC_PARAMETER_ERROR

 

ASTROMETRIC_PARAMETER_ERROR(e_ra, e_dec, e_plx, e_pmra, e_pmde, dera, plxra, pmrara, pmdera, plxde, pmrade, pmdede, pmraplx, pmdeplx, pmdepmra, plx, rv,e_rv): Returns the initial uncertainties and correlation coefficients where:

The output of this function is an array (double[21]astrometric_parameters_error) with all the uncertainties and correlation coefficients, where:

SELECT ASTROMETRIC_PARAMETER_ERROR(
	ra_error, dec_error, parallax_error, pmra_error, pmdec_error,
	ra_dec_corr, ra_parallax_corr, ra_pmra_corr, ra_pmdec_corr, dec_parallax_corr, dec_pmra_corr, dec_pmdec_corr, parallax_pmra_corr, parallax_pmdec_corr, pmra_pmdec_corr,
	parallax, radial_velocity, radial_velocity_error)
FROM gaiadr2.gaia_source
WHERE source_id = 4472832130942575872
SELECT ASTROMETRIC_PARAMETER_ERROR(
	e_radeg, e_dedeg, e_plx, e_pmra, e_pmde,
	dera, plxra, pmrara, pmdera, plxde, pmrade, pmdede, pmraplx, pmdeplx, pmdepmra,
	plx, -111.0, 0.5)
FROM public.hipparcos
WHERE hip=87937

 

​​​​​​​​​​​​​​​​​​​​​RADIAL_VELOCITY

 

RADIAL_VELOCITY(radial_velocity,parallax): It provides the transformation of the radial proper motion (mas/yr) into a radial velocity (km/s), where:

 

This transformation is required for computing the output radial proper motion returned from the EPOCH_PROP routines to radial velocity in km/s where: radial_velocity[km/s] = radial_pm[mas/yr] Av / parallax[mas]

It should be noted that this transformation is inversely proportional to the input parallax, so special care should be taken for close to zero values in this parameter.

SELECT array_element(a0,1) as ra_prop,
       array_element(a0,2) as dec_prop,
       array_element(a0,3) as parallax_prop,
       array_element(a0,4) as pmra_prop,
       array_element(a0,5) as pmdec_prop,
       radial_velocity(array_element(a0,6),parallax) as rv_prop
FROM
   (SELECT top 50
	       ra,dec,parallax,pmra,pmdec,radial_velocity,
	       epoch_prop(ra,dec,parallax,pmra,pmdec,radial_velocity,
                      2015.5,2000) as a0
	FROM gaiadr2.gaia_source
    WHERE parallax IS NOT NULL
	  AND radial_velocity IS NOT NULL
	ORDER BY random_index ASC) as p
  • e_ra [double]: Error in Right Ascension (mas)
  • e_dec [double]:Error in Declination (mas)
  • e_plx [double]: Error in Parallax (mas)
  • e_pmra [double]: Error in Proper Motion in RA (mas/yr)
  • e_pmdec [double]: Error in Proper Motion in Dec (mas/yr)
  • e_vr [double]: Error in Radial Velocity (km/s)
  • de_ra [double]: Correlation Coefficient, RA/Dec
  • plx_ra [double]: Correlation Coefficient, Parallax/RA
  • pmra_ra [double]: Correlation Coefficient, Proper Motion in RA/RA
  • pmde_ra [double]: Correlation Coefficient, Proper Motion in Dec/RA
  • plx_de [double]: Correlation Coefficient, Parallax/Dec
  • pmra_de [double]: Correlation Coefficient, Proper Motion in RA/Dec
  • pmde_de [double]: Correlation Coefficient, Proper Motion in Dec/Dec
  • pmra_plx [double]: Correlation Coefficient, Proper Motion in RA/Parallax
  • pmde_plx [double]: Correlation Coefficient, Proper Motion in Dec/Parallax
  • pmde_pmra [double]:Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
  • plx [double]: Parallax (mas)
  • rv [double]: Radial Velocity (km/s)
  • e_vr [double]: Error in Radial Velocity (km/s)
  • astrometric parameter_error[1]: e_ra, Error in Right Ascension (mas)
  • astrometric parameter_error[2]: e_dec, Error in Declination (mas)
  • astrometric parameter_error[3]: e_plx, Error in Parallax (mas)
  • astrometric parameter_error[4]: e_pmra, Error in Proper Motion in RA (mas/yr)
  • astrometric parameter_error[5]: e_pmde, Error in Proper Motion in Dec(mas/yr)
  • astrometric parameter_error[6]: e_pmr, Error in Radial Proper Motion (mas/yr)
  • astrometric parameter_error[7]: de_ra, Correlation Coefficient, Dec/RA
  • astrometric parameter_error[8]: plx_ra, Correlation Coefficient, Parallax/RA
  • astrometric parameter_error[9]: pmra_ra, Correlation Coefficient, Proper Motion in RA/RA
  • astrometric parameter_error[10]: pmde_ra, Correlation Coefficient, Proper Motion in Dec/RA
  • astrometric parameter_error[11]: pmr_ra, Correlation Coefficient, Radial Proper Motion/RA
  • astrometric parameter_error[12]: plx_de, Correlation Coefficient, Parallax/Dec
  • astrometric parameter_error[13]: pmra_de, Correlation Coefficient, Proper Motion in RA/Dec
  • astrometric parameter_error[14]: pmde_de, Correlation Coefficient, Proper Motion in Dec/Dec
  • astrometric parameter_error[15]: pmr_de, Correlation Coefficient, Radial Proper Motion/Dec
  • astrometric parameter_error[16]: pmra_plx, Correlation Coefficient, Proper Motion in RA/Parallax
  • astrometric parameter_error[17]: pmde_plx, Correlation Coefficient, Proper Motion in Dec/Parallax
  • astrometric parameter_error[18]: pmr_plx, Correlation Coefficient, Radial Proper Motion/Parallax
  • astrometric parameter_error[18]: pmde_pmra, Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
  • astrometric parameter_error[20]: pmr_pmra, Correlation Coefficient, Radial Proper Motion/Proper Motion in RA
  • astrometric parameter_error[21]: pmr_pmde, Correlation Coefficient, Radial Proper Motion/Proper Motion in Dec
  • radial_pm [mas/yr]: Radial proper motion
  • parallax [mas]: Parallax
 
Astrometric parameters in Gaia and Hipparcos

The following table provides a list of all astrometric parameters in the Gaia and Hipparcos catalogues. The corresponding tables are gaiadr2.gaia_source and public.hipparcos or public.hipparcos_newreduction.

 

Gaia

Hipparcos

Parameter

Unit

ra

ra

Right Ascension

deg

dec

de

Declination

deg

parallax

plx

Parallax

mas

pmra

pmra

Proper Motion in Right Ascension

mas/yr

pmdec

pmde

Proper Motion in Declination

mas/yr

pmdec

pmde

Proper Motion in Declination

mas/yr

radial_velocity

 

Radial velocity

km/s

ra_error

e_radeg

Error in Right Ascension

mas

dec_error

e_dedeg

Error in Declination

mas

parallax_error

e_plx

Error in Parallax

mas

pmra_error

e_pmra

Error in Proper Motion in RA

mas/yr

pmdec_error

e_pmde

Error in Proper Motion in Dec

mas/yr

radial_velocity_error

 

Error in Radial velocity

km/s

ra_dec_corr

dera

Correlation Coefficient, Dec/RA

 

ra_parallax_corr

plxra

Correlation Coefficient, Parallax/RA

 

ra_pmra_corr

pmrara

Correlation Coefficient, Proper Motion in RA/RA

 

ra_pmdec_corr

pmdera

Correlation Coefficient, Proper Motion in Dec/RA

 

dec_parallax_corr

plxde

Correlation Coefficient, Parallax/Dec

 

dec_pmra_corr

pmrade

Correlation Coefficient, Proper Motion in RA/RA

 

dec_pmdec_corr

pmdede

Correlation Coefficient, Proper Motion in Dec/Dec

 

dec_pmdec_corr

pmdede

Correlation Coefficient, Proper Motion in Dec/Dec

 

parallax_pmra_corr

pmraplx

Correlation Coefficient, Proper Motion in RA/Parallax

 

parallax_pmdec_corr

pmdeplx

Correlation Coefficient, Proper Motion in Dec/Parallax

 

pmra_pmdec_corr

pmdepmra

Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA

 

Using job results within ADQL

 

Gaia Archive TAP+ server provides a mechanism to use job results directly from an ADQL query. The syntax is as follows:

SELECT *
FROM JOB_UPLOAD.job1543248644898D AS a,
     JOB_UPLOAD.job1543248912035D AS b
	

This query will on-the-fly upload results of the jobs with IDs 1543248644898D and 1543248912035D and make them available with the corresponding table names: JOB_UPLOAD.job1543248644898D and JOB_UPLOAD.job1543248912035D. Then those tables can be referenced in the ADQL query.

Any accessible job can be used, both anonymous jobs and private jobs of the currently logged user.

TAP_UPLOAD mechanism effectively loads the job results in the TAP server infrastructure. This can be a heavy operation if the job result is big, and it is carried out each time the query is executed. If the query is to be executed several times, it is much faster to upload the job results persistently as a user table.

 

Example

After execution of the following DR2 query:

SELECT TOP 10 source_id, ra, dec, phot_g_mean_mag
FROM gaiadr2.gaia_source

a job is produced as a result (example job ID 1543325347500D). The results of it could be used in a new query to load and combine those data with DR1 using a cone search:

SELECT gaia1.source_id AS gaia1_id, gaia2.source_id AS gaia2_id,
  DISTANCE(
	  POINT('', gaia2.ra, gaia2.dec),
	  POINT('', gaia1.ra, gaia1.dec)
  ) * 3600e3 AS dist_mas,
  gaia1.ra AS gaia1_ra, gaia1.dec AS gaia1_dec, gaia2.ra AS gaia2_ra, gaia2.dec AS gaia2_dec
FROM job_upload.job1543325347500D AS gaia2
JOIN gaiadr1.gaia_source AS gaia1
  ON 1 = contains(
	  POINT('', gaia2.ra, gaia2.dec),
	  CIRCLE('', gaia1.ra, gaia1.dec, 10. / 3600)
  )