Query examples

Below is a selected sample of ADQL queries that can be executed in the Gaia ESA Archive in less than 1 minute under average user-load conditions. They are representative of different (but common) use cases. Most of them have been extracted from the different articles that accompany the Gaia (E)DR3 releases. This complementary page  contains a list of Gaia Collaboration paper queries targetting all Gaia Data Releases. 

Content:

1. Cone search sorted by angular separation
2. Cone search in galactic coordinates + DataLink filter
3. Selection of rectangular sky region + 2MASS pre-computed cross-match
4. Bright sources in Gaia DR2 and (E)DR3
5. Basic positional cross-match: Hipparcos vs. Gaia
6. Advanced positional cross-match
7. Positional cross-match + proper-motion propagation
8. Retrieve average quantities per HEALPix level 8 in the galactic anti-centre
9. Extinction-corrected CMD diagram (histogram)
10. Exploration of the interstellar medium with Gaia RVS diffuse interstellar bands

Cone search sorted by angular separation

Use case: I want to retrieve a sample of filtered sources (brighter than G=20.5 mag and with a parallax measurement) 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 (adapted from 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.
Notes: Beware of integer divisions in ADQL (e.g., 5/60 = 0 whereas 5.0/60.0 = 0.083)
Target table: gaiadr3.gaia_source
Query:

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

Cone search in galactic coordinates + datalink filter

Use case: I want to retrieve a sample of sources contained in a circular region centred on the Small Magellanic Coud [(l, b) = (302.95, -43.98) deg] with a search radius of 5 arcmin (adapted from EDR3, Gaia Collaboration, Luri et al., 2021, A&A 649, A7) that have DataLink (see the DataLink tutorials in this page) products: epoch photometry and XP spectra.
Notes: Beware of integer divisions in ADQL (e.g., 5/60 = 0 whereas 5.0/60.0 = 0.083)
Target table: gaiadr3.gaia_source
Query:

SELECT *, DISTANCE(302.95, -43.98, l, b) AS ang_sep
FROM gaiadr3.gaia_source
WHERE DISTANCE(302.95, -43.98, l, b) < 5./60.
AND has_epoch_photometry = 'True'
AND has_xp_continuous = 'True'

SELECTION OF rectangular SKY region + 2MASS pre-computed 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. In addition, I want to filter out the sources with lower-quality infrared photometry (according to the 2MASS PSC catalogue).
Notes: The Gaia Archive contains many pre-computed cross-matches against major surveys (see the Pre-computed cross-matches tutorial).
Target tables: gaiadr3.gaia_source, gaiadr1.tmass_original_valid, gaiadr3.tmass_psc_xsc_best_neighbour, gaiadr3.tmass_psc_xsc_join, gaiadr1.tmass_original_valid.
Query:

SELECT gaia.source_id, gaia.ra AS g_ra, gaia.dec AS g_dec, gaia.parallax, gaia.pmra, gaia.pmdec, tmass.*
FROM gaiadr3.gaia_source AS gaia
JOIN gaiadr3.tmass_psc_xsc_best_neighbour AS xmatch USING (source_id)
JOIN gaiadr3.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 AND
tmass.ph_qual = 'AAA'

bright sources in Gaia DR2 and (e)dr3

Use case: I want to retrieve the targets with G < 5 mag in Gaia DR2 and their Gaia DR3 counterparts (adapted from EDR3, Gaia Collaboration, Brown et al., 2021, A&A 649, A1). This query uses the pre-computed Gaia DR2-DR3 cross-match table. The output is ordered by the Gaia DR2 source_id field.
Target table: gaiadr2.gaia_source, gaiadr3.dr2_neighbourhood.
Query:

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

BASIC positional cross-match: hipparcos vs gaia

Use case: I want to carry out a positional cross-match between two catalogues using a cone-seach radius of 1 arcsecond (0.00028 degrees).
Notes: When cross-matching catalogues in the Gaia Archive, the smaller and larger catalogues should be used as the first and second pair of arguments of the DISTANCE function, respectively. Otherwise, the Archive response time can be degraded by several orders of magnitude - please see the Catalogue Combination tutorial for details and useful examples. The TOP 1000 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.
Target table: gaiadr3.gaia_source, public.hipparcos.
Query:

SELECT TOP 1000 gaia.source_id AS g_source_id, gaia.ra AS g_ra, gaia.dec AS g_dec, gaia.pmra AS g_pmra, gaia.pmdec AS g_pmdec, hip.*, 
DISTANCE(hip.ra, hip.de, gaia.ra, gaia.dec) AS ang_sep
FROM public.hipparcos AS hip
JOIN gaiadr3.gaia_source AS gaia ON
DISTANCE(hip.ra, hip.de, gaia.ra, gaia.dec) < 0.00028

Advanced positional cross-match

Use case: I want to extract selected parameters from the gaiadr3.gaia_source and gaiadr3.synthetic_photometry_gspc tables for a sample contained in a circle of radius 1 deg centred on the globular cluster NGC 5139 (adapted from DR3, Gaia Collaboration, Montegriffo et al. 2022, A&A).
Notes: Epoch propagation is applied to the NGC 5139 coordinates - see the Epoch Propagation and the Proper-motion corrected cross-match for details and useful examples.
Target table: gaiadr3.gaia_source, gaiadr3.synthetic_photometry_gspc
Query:

SELECT dr3.source_id, dr3.ra, dr3.dec, dr3.pmra, dr3.pmra_error, dr3.pmdec, dr3.pmdec_error, dr3.ruwe, gspc.c_star,
gspc.u_jkc_mag, gspc.u_jkc_flux, gspc.u_jkc_flux_error, gspc.u_jkc_flag,
gspc.b_jkc_mag, gspc.b_jkc_flux, gspc.b_jkc_flux_error, gspc.b_jkc_flag,
gspc.v_jkc_mag, gspc.v_jkc_flux, gspc.v_jkc_flux_error, gspc.v_jkc_flag,
gspc.y_ps1_mag, gspc.y_ps1_flux, gspc.y_ps1_flux_error, gspc.y_ps1_flag,
gspc.f606w_acswfc_mag, gspc.f606w_acswfc_flux, gspc.f606w_acswfc_flux_error, gspc.f606w_acswfc_flag
FROM gaiadr3.gaia_source AS dr3
JOIN gaiadr3.synthetic_photometry_gspc AS gspc USING (source_id)
WHERE
DISTANCE(COORD1(EPOCH_PROP_POS(201.697,-47.479472,0.1368,-3.2400,-6.7300,234.2800,2000,2016.0)),
         COORD2(EPOCH_PROP_POS(201.697,-47.479472,0.1368,-3.2400,-6.7300,234.2800,2000,2016.0)),
         ra, dec) < 1.

positional cross-match + PROPER-MOTION propagation

Use case: I want to propagate Gaia epoch J2016.0 positions from a sample of targets (the Pleiades) to an epoch in the past for comparison against a catalogue with epoch J2000 astrometry. 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" - see the Epoch Propagation and the Proper-motion corrected cross-match for details and useful examples. The "OFFSET 0" clause increases the query performance, as it instructs the Archive PostgreSQL data base backend to respect the logical order (the subquery is executed first and other conditions applied afterwards - for more details, see this tutorial).
Target table: gaiadr3.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, radial_velocity, ref_epoch, 2000) AS propagated_position_vector
  FROM gaiadr3.gaia_source AS gaia
  WHERE DISTANCE(56.75, 24.12, 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 DISTANCE(COORD1(subquery.propagated_position_vector), COORD2(subquery.propagated_position_vector), galex.raj2000, galex.dej2000) < 1./3600.

retrieve average quantities per healpix level 8 in the galactic anticentrE

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 (the Galactic anticentre, adapted from EDR3, Gaia Collaboration, Antoja et al., 2021, A&A 649, A8).
Target table: gaiadr3.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 gaiadr3.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 for a subset of stars with high-quality parallaxes (adapted from Gaia Collaboration, Andrae et al., 2018, A&A 616, A8; Fig. 19).
Notes: The random_index is an indexed field included in each gaia_source table. In this example, the constraint in the random_index is only used to ensure that the query is executed in less than 1 minute. 
Target table: gaiadr3.gaia_source
Query:

SELECT bp_rp_index / 40 AS bp_rp, g_abs_index / 10 AS g_abs, n
FROM (
  SELECT FLOOR((bp_rp - ebpminrp_gspphot) * 40) AS bp_rp_index, FLOOR((phot_g_mean_mag - ag_gspphot + 5 * LOG10(parallax) - 10) * 10) AS g_abs_index, COUNT(*) AS n
  FROM gaiadr3.gaia_source
  WHERE parallax_over_error > 5
  AND ag_gspphot IS NOT NULL
  AND random_index < 500000
  GROUP BY bp_rp_index, g_abs_index
) AS subquery

exploration of the interstellar medium with Gaia RVS Diffuse Interstellar Bands

Use case: I want to retrieve the high-quality sample of DIBs described in the Gaia DR3 Collaboration paper "The exploration of the interstellar medium with Gaia RVS Diffuse Interstellar Bands" (Gaia Collaboration, Schultheis et al. 2022, A&A).
Notes: This query applies the "LIKE" SQL operator that allows to search for patterns (e.g., strings) in a table. The TOP 1000 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.
Target tables: gaiadr3.astrophysical_parameters
Query: 

SELECT TOP 1000 *
FROM gaiadr3.astrophysical_parameters
WHERE ((flags_gspspec LIKE '0%') OR (flags_gspspec LIKE '1%')) AND
((flags_gspspec LIKE '_0%') OR (flags_gspspec LIKE '_1%')) AND
((flags_gspspec LIKE '__0%') OR (flags_gspspec LIKE '__1%')) AND
((flags_gspspec LIKE '___0%') OR (flags_gspspec LIKE '___1%')) AND
((flags_gspspec LIKE '____0%') OR (flags_gspspec LIKE '____1%')) AND
((flags_gspspec LIKE '_____0%') OR (flags_gspspec LIKE '_____1%')) AND
((flags_gspspec LIKE '______0%') OR (flags_gspspec LIKE '______1%')) AND
((flags_gspspec LIKE '_______0%') OR (flags_gspspec LIKE '_______1%')) AND
((flags_gspspec LIKE '________0%') OR (flags_gspspec LIKE '________1%')) AND
((flags_gspspec LIKE '_________0%') OR (flags_gspspec LIKE '_________1%')) AND
((flags_gspspec LIKE '__________0%') OR (flags_gspspec LIKE '__________1%')) AND
((flags_gspspec LIKE '___________0%') OR (flags_gspspec LIKE '___________1%')) AND
((flags_gspspec LIKE '____________0%') OR (flags_gspspec LIKE '____________1%'))AND
(dibqf_gspspec <= 2) AND (dibqf_gspspec >= 0) AND
(dib_gspspec_lambda > 862.0) AND (dib_gspspec_lambda < 862.6) AND
((dibew_gspspec_uncertainty/dibew_gspspec) < 0.35)

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 DPAC ADQL Cookbook
• GAVO ADQL reference card
• GAVO ADQL short course for Gaia
• The Gaia Archive White dwarfs exploration and Cluster analysis tutorials
• Tutorial - ADQL (Advanced) tab
• Tutorial - ADQL Advanced features
• Tutorial - Catalogue combination
• Tutorial - Command line access

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:

SELECT array_element(array,2) : 5						

SELECT array_ndims(array) : 2

SELECT array_dims(array) : [1:2][1:3]

SELECT array_length(array,1) : 3

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.

3. ADQL trigonometric functions

ADQL defines some trigonometric functions considered important for astronomical queries.

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.

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

CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END

CASE a
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END

CASE WHEN condition THEN result
[ELSE result]
END

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 Hipparcos 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:

• EPOCH_PROP

• EPOCH_PROP_POS

• ASTROMETRIC_PARAMETERS

• EPOCH_PROP_ERROR

• ASTROMETRIC_PARAMETER_ERROR

• RADIAL_VELOCITY

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(ra,dec,plx,pmra,pmdec,rv,T0,T1): Returns a string with the propagated position (ra, dec) in radians 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(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(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(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,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

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.