Help support

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

Query examples

 

Cone search sorted by distance

Use case: I want to retrieve all the objects in a catalogue in a circular region centred at (R.A., Dec) = (266.41683,-29.00781) with a search radius of 5 arcmin (0.08333 deg).

Target table: gaiaedr3.gaia_source

Query:
SELECT DISTANCE(
  POINT('ICRS', 266.41683, -29.00781),
  POINT('ICRS', ra, dec)) AS dist, *
FROM gaiaedr3.gaia_source
WHERE 1=CONTAINS(
  POINT('ICRS', 266.41683, -29.00781),
  CIRCLE('ICRS',ra, dec, 0.08333333))
ORDER BY dist ASC

 

Cone search filtered by magnitude, ordered by magnitude Galactic centre, radius 5'

Use case: I want to retrieve all the objects in a catalogue in a circular region and apply an additional selection criterion (magnitude in this case).

Target table: gaiaedr3.gaia_source

Query:
SELECT *
FROM gaiaedr3.gaia_source
WHERE 1=CONTAINS(
  POINT('ICRS',266.41683, -29.00781),
  CIRCLE('ICRS',ra, dec, 0.08333333))
AND phot_g_mean_mag>=10 AND phot_g_mean_mag<15
AND ruwe <1.4
ORDER BY phot_g_mean_mag ASC

 

Gaia EDR3 catalogue filtered by magnitude and parallax

Use case: I want to retrieve all the stars in a close region (shell) around the Sun and apply an additional selection criterion (magnitude in this case).

Target table: gaiaedr3.gaia_source

Query:
SELECT *
FROM gaiaedr3.gaia_source
WHERE parallax >= 15 AND parallax <= 50
AND phot_g_mean_mag >= 9 AND phot_g_mean_mag <= 9.5

 

ADQL positional cross-match: Hipparcos vs Gaia, 1" radius

Use case: I want to carry out a positional cross-match between two catalogues.

Notes: In case of catalogues with different size, the smaller one should be used to define the “point” and the larger to create the “circle”. The performance can be degraded by several orders of magnitude otherwise. Only the first 10 objects are retrieved to make the query fast (remove the “top 10” statement to retrieve the full output). Several pre-computed cross-matches against major surveys are available in the Gaia Archive.

Target tables: gaiaedr3.gaia_source, public.hipparcos

Query:
SELECT TOP 10 * , distance(
  POINT('ICRS', hip.ra, hip.de),
  POINT('ICRS', gaia.ra, gaia.dec)) AS dist
FROM public.hipparcos AS hip
JOIN gaiaedr3.gaia_source AS gaia
  ON 1=CONTAINS(
    POINT('ICRS', hip.ra, hip.de),
    CIRCLE('ICRS', gaia.ra, gaia.dec, 0.000277777777778))

 

Built-in positional cross-match: Hipparcos vs Gaia, 1" radius

Use case: I want to carry out a positional cross-match between two catalogues for further refinement within the Archive.

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 this tutorial). Several pre-computed cross-matches against major surveys are available in the Gaia Archive.

Target tables: gaiaedr3.gaia_source, public.hipparcos

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

 

Proper motion propagation: Pleiades Gaia EDR3 field to 1950

Use case: I want to propagate Gaia positions to an epoch in the past for comparison against photographic plates astrometry.

Note: This query applies the built-in ADQL function "EPOCH_PROP_POS".

Target table: gaiaedr3.gaia_source

Query:
SELECT source_id, ra, dec, coord1(prop) AS ra_1950, coord2(prop) AS dec_1950 FROM (
  SELECT gaia.source_id, ra, dec,
    EPOCH_PROP_POS(ra, dec, parallax, pmra, pmdec, 0, ref_epoch, 1950) AS prop
  FROM gaiaedr3.gaia_source AS gaia
  WHERE contains(
    POINT('ICRS',  56.75, 24.12),
    CIRCLE('ICRS',gaia.ra, gaia.dec, 5)) = 1
  AND sqrt(power(gaia.pmra - 20.5, 2) + power(gaia.pmdec + 45.5, 2)) < 6.0
) AS subquery

 

Proper motion propagation: Pleiades Gaia EDR3 field to 1950

Use case: I want to propagate Gaia positions to an epoch in the past for comparison against photographic plates astrometry.

Note: This query applies a simple linear transformation to the Equatorial coordinates.

Target table: gaiaedr3.gaia_source

Query:
SELECT source_id, ra, dec,
  ra  + 1. / 3600e3 * pmra  * (1950.0 - ref_epoch) / cos(radians(dec)) as ra_1950,
  dec + 1. / 3600e3 * pmdec * (1950.0 - ref_epoch) as dec_1950
FROM gaiaedr3.gaia_source AS gaia
WHERE
  1 = contains(
    POINT('ICRS',  56.75, 24.12),
    CIRCLE('ICRS',gaia.ra, gaia.dec, 5)
  )
  AND sqrt(power(gaia.pmra - 20.5, 2) + power(gaia.pmdec + 45.5, 2)) < 6.0

 

Density distribution of radial velocities

Use case: I want to show the average distribution of Gaia DR2 radial velocities in Galactic Coordinates (DR2, Gaia Collaboration, Katz et al. 2019 A&A 622, A205; adapted Fig. 7).

Target table: gaiadr2.gaia_source

Query:
SELECT gaia_healpix_index(6, source_id) AS healpix6, avg(radial_velocity) AS avg_radial_velocity
FROM gaiadr2.gaia_source
WHERE radial_velocity IS NOT NULL
GROUP BY healpix6

 

Observed CMD diagram

Use case: I want to construct an observed CMD diagram (DR2, Gaia Collaboration, Andrae et al. 2018 A&A 616, A8; adapted Fig. 19).

Target table: gaiadr2.gaia_source

Query:
Query:
SELECT bp_rp_index / 40 AS bp_rp, g_abs_index / 10 AS g_abs, n FROM (
SELECT
	floor(bp_rp * 40) AS bp_rp_index,
	floor((phot_g_mean_mag + 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 < 800000
GROUP BY bp_rp_index, g_abs_index
) AS subquery

 

Extinction-Corrected CMD diagram

Use case: I want to construct a dust-corrected CMD diagram (DR2, Gaia Collaboration, Andrae et al. 2018 A&A 616, A8; adapted Fig. 19).

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

 

Average Extinction distribution

Use case: I want to construct a dust-corrected CMD diagram (DR2, Gaia Collaboration, Andrae et al. 2018 A&A 616, A8; adapted Fig. 21).

Target table: gaiadr2.gaia_source

Query:
SELECT gaia_healpix_index(8, source_id) AS healpix8, avg(a_g_val) AS avg_a_g_val
FROM gaiadr2.gaia_source
WHERE a_g_val IS NOT NULL
AND random_index < 800000
GROUP BY healpix8

 

Extinction towards Orion

Use case: I want to obtain the extinction towards Orion for a distance slice between 0.6 AND 1.0 kpc (DR2, Gaia Collaboration, Andrae et al. 2018 A&A 616, A8; adapted Fig. 34).

Target table: gaiadr2.gaia_source

Query:
SELECT l_index / 2 AS l, b_index / 4 AS b, extinction, n FROM (
  SELECT
   floor((l - 360) * 2) AS l_index,
   floor(b * 4) AS b_index, avg(a_g_val) AS extinction, count(*) AS n
  FROM gaiadr2.gaia_source
  WHERE l BETWEEN 180 AND 230
    AND b BETWEEN -25 AND 0
    AND parallax_over_error > 5
    AND a_g_val IS NOT NULL
  AND parallax BETWEEN 1. AND 1. / 0.6
  AND rANDom_index < 800000
  GROUP BY l_index, b_index
) AS subquery

 

RR Lyrae sky density distribution.

Use case: I want to retrieve the sky density distribution of RR Lyrae stars (DR2, Gaia Collaboration, Holl et al. 2018 A&A 618, A30; adapted Fig. 6).

Target table: gaiadr2.vari_classifier_result

Query:
SELECT gaia_healpix_index(6, source_id) AS healpix6,
  count (*)/ 0.9161 AS stars_per_sq_deg
FROM gaiadr2.vari_classifier_result
WHERE best_class_name='RRAB'
  OR best_class_name='RRC'
  OR best_class_name='RRD'
  OR best_class_name='ARRD'
GROUP BY healpix6

 

Light Curves

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 tables: 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, epoch_photometry_url, sosrrl.pf, sosrrl.epoch_g, soslpv.frequency
FROM gaiadr2.gaia_source AS gaia
LEFT OUTER JOIN gaiadr2.vari_classifier_result AS variables
  ON gaia.source_id = variables.source_id
LEFT OUTER JOIN gaiadr2.vari_rrlyrae AS sosrrl
  ON gaia.source_id = sosrrl.source_id
LEFT OUTER JOIN gaiadr2.vari_long_period_variable AS soslpv
  ON gaia.source_id = soslpv.source_id
WHERE gaia.source_id IN (1372970619261887104, 5368939678153068288)

 

Gaia DR2-2MASS HR diagram

Use case: I want to construct the Gaia DR2-TMASS HR diagram (DR1, Gaia Collaboration, Brown et al. 2016 A&A 595, A2; adapted Fig. 5).

Target tables: gaiadr2.gaia_source, gaiadr2.tmass_best_neighbour, gaiadr1.tmass_original_valid

Query:
SELECT TOP 100 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 gaiadr2.gaia_source AS gaia
INNER JOIN gaiadr2.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

 

Gaia DR2-2MASS HR diagram

Use case: I want to construct the Gaia DR2 HR diagram (DR1, Gaia Collaboration, Brown et al. 2016 A&A 595, A2; adapted  Fig. 5).

Target tables: gaiadr2.gaia_source, gaiadr2.tmass_best_neighbour, gaiadr1.tmass_original_valid

Query:
SELECT g_min_ks_index / 10 AS g_min_ks, g_mag_abs_index / 10 AS g_mag_abs, count(*) AS n
FROM (
  SELECT TOP 100 gaia.source_id,
    floor((gaia.phot_g_mean_mag+5*log10(gaia.parallax)-10) * 10) AS g_mag_abs_index,
    floor((gaia.phot_g_mean_mag-tmass.ks_m) * 10) AS g_min_ks_index
  FROM gaiadr2.gaia_source AS gaia
  INNER JOIN gaiadr2.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
  )AS subquery
GROUP BY g_min_ks_index, g_mag_abs_index

Cepheids: light curve retrieval for all stars

Use case: I want to retrieve all light curves of a given object class for offline analysis (DR1, Gaia Collaboration, Clementini et al. 2016 A&A 595, A133).

Note: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial).

Target tables: 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
ON gaia.source_id = cep.source_id

 

RR Lyrae phase folded light curve reconstruction, including errors

Use case: I want to construct a phase-folded light curve for easier analysis and comparison to stars with different period (DR1, Gaia Collaboration, Brown et al. 2016 A&A 595, A2; adapted Fig. 7).

Note: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial).

Target tables: 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
	 ON rrlyrae.source_id = curves.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).

Note: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial).

Target tables: 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
ON stat.source_id = rr.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.0and SQL 92)

A very concise introduction to ADQL is provided in the next sections. There are a number of tutorials and resources providing a gentler learning curve for newcomers, specially those without previous experience with SQL or relational data bases. A small selection is provided below

 

1. Basic syntax

In ADQL/SQL you write queries to the database. A query is compound of the table columns you want to retrieve (the SELECT part), the table or tables that store the data (the FROM part) and the conditions to restrict the data you obtain (the WHERE part). E.g.

SELECT <columns> FROM <tables> WHERE <conditions>

So, if you want to obtain Right Ascension and Declination of all items from the table gaia_source, you may write:

SELECT ra, dec FROM gaiadr1.gaia_source

ra is the column name of Right Ascension in gaia_source table.
dec is the column name of Declination in gaia_source table.
gaiadr1 is the database schema name where gaia_source table belongs to. It is a good practice to add schema names to tables to avoid name clashes.

Probably, you want to obtain also the object identification, so you can modify the query as follows:

SELECT source_id, ra, dec FROM gaiadr1.gaia_source

If you want to know all the column names associated to a table, you may use GACS GUI, clicking on the plus sign next to a table name.

Or, in TAP+, you may obtain all the columns and descriptions of a table using the following syntax:

curl "https://gea.esac.esa.int/tap-server/tap/tables?tables=gaiadr1.gaia_source"

Now, suppose you are interested in an specific region (e.g. ra=266.41683, dec=-29.00781, radius=0.083333 arc.min.). So you want to restrict the results to that region. In order to do that, you may execute a 'cone search' to obtain all the objects where ra,dec are inside a cone:

SELECT source_id, ra, dec FROM gaiadr1.gaia_source
   WHERE 1=CONTAINS(POINT('ICRS',ra,dec),
                    CIRCLE('ICRS',266.41683,-29.00781, 0.08333333))

(You may read the ADQL recommendation to obtain the list of functions that can be used).

One way to create a complex query could be to use the 'Simple Form' page to create the basic query graphically. Then you should press 'Show query' button to show the query as ADQL in the 'ADQL Form'. And then, you can modify it.

 

2. Selecting complex data

You are not restricted to obtain column names only. You can obtain complex values too.

For instance, you may want to obtain the distance of each source to the center of an specific region. Then, you may type:

SELECT source_id, ra, dec, DISTANCE(POINT('ICRS',ra,dec),
       POINT('ICRS',266.41683,-29.00781)) AS dist,
   FROM gaiadr1.gaia_source
   WHERE 1=CONTAINS(POINT('ICRS',ra,dec),CIRCLE('ICRS',266.41683,-29.00781, 0.08333333))

source_id, ra and dec are the Source Identifier, Right ascension and Declination of each item of the gaia_source table.

'DISTANCE(POINT('ICRS',ra,dec), POINT('ICRS',266.41683,-29.00781)) AS dist' is a created on the fly column, with the name 'dist' that contains the distance of the item to the specified point.

 

3. Additional functions available

Apart from the standard ADQL functions, ESA Gaia TAP+ service offers the next 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]: norder of the desirede 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 to 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. Indexes beginning in 1

Example. 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)

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

SELECT array_ndims(array) : 2
ARRAY_DIMS(array_column)

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

Example. 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

Example. 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

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

SELECT cardinality(array) : 4

 

4. 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 toward zero

TRUNCATE(48.8) 48
TRUNCATE(x, s)

numeric

Truncate to s decimal places

TRUNCATE(48.8328, 3) 48.832

 

5. 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

 

6. Data type casting ADQL functions

Some User Defined Functions have been implemented in order to allow the casting of values between different data types. The casting functions relies in 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

 

7. ADQL extension: conditional expressions

Some conditional expressions have been implemented as User Defined Functions.

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)

 

8. Deviations of ADQL functions implementation from standard

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

  1. BOX(coordsys, longitudeCentre, latitudeCentre, longitudeExtent, latitudeExtent): it is interpreted as follows:
    • As defined into the standard when arguments are fixed values (A cross at the central position with arms extending, parallel to the coordinate axes at the center 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)
  )