Help support

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

Gaia ADQL example queries

ConeSearch sorted by distance

Use case: I want to perform a simple cone search.

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 two additional SELECTion criteria.

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

Example of simple query filtered by logical conditions.

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, selecting only the first 10 cross-matches.

Target table: 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

Use case: I want to carry out a positional cross-match BETWEEN Hipparcos and Gaia eDR3.

NOTE: You must be authenticated to execute this query.

Target table: 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" (see documentation in: https://www.cosmos.esa.int/web/gaia-users/archive/writing-queries/#EpochPropagationFlag)

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.

Reference: DR2. Gaia Collaboration, Katz et al. 2019 A&A 622, A205 Fig. 7 (adapted)

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

Reference: DR2. Gaia Collaboration, Andrae et al. 2018 A&A 616, A8 Fig. 19 (adapted)

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 * 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

Reference: DR2. Gaia Collaboration, Andrae et al. 2018 A&A 616, A8 Fig. 19 (adapted)

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 obtain the parallax-averaged A_G Extinction.

Reference: DR2. Gaia Collaboration, Andrae et al. 2018 A&A 616, A8 Fig. 21 (adapted)

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

Reference: DR2. Gaia Collaboration, Andrae et al. 2018 A&A 616, A8 Fig. 34 (adapted)

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

Reference: DR2. Gaia Collaboration, Holl et al. 2018 A&A 618, A30 Fig. 6 (adapted)

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.

Reference: DR2. Gaia Collaboration, Holl et al. 2018 A&A 618, A30 Fig. A.1 (adapted).

Target table: gaiadr2.gaia_source,gaiadr2.vari_rrlyrae,gaiadr2.vari_long_period_variable,gaiadr2.vari_classifier_result

Query: 
SELECT gaia.source_id, phot_variable_flag, best_class_name, datalink_url, sosrrl.pf, sosrrl.epoch_g, soslpv.frequency
FROM gaiadr2.gaia_source AS gaia
LEFT OUTER JOIN gaiadr2.vari_classifier_result AS variables
  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.

Reference: DR1. Gaia Collaboration, Brown et al. 2016 A&A 595, A2 Fig. 5 (adapted)

Target table: 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.

Reference: DR1. Gaia Collaboration, Brown et al. 2016 A&A 595A, 2G Fig. 5 (adapted)

Target table: 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.

Reference: DR1. Clementini et al. 2016 A&A 595A, A133 (adapted)

NOTE: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial: https://www.cosmos.esa.int/web/gaia-users/archive/ancillary-data#tutorial_datalinklc)

Target table: gaiadr1.cepheid,gaiadr1.phot_variable_time_series_gfov

Query: 
SELECT gaia.*
FROM gaiadr1.phot_variable_time_series_gfov AS gaia
INNER JOIN gaiadr1.cepheid AS cep
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.

Reference: DR1. Gaia Collaboration, Brown et al. 2016 A&A 595A, 2G Fig. 7 (adapted)

NOTE: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial: https://www.cosmos.esa.int/web/gaia-users/archive/ancillary-data#tutorial_datalinklc)

Target table: gaiadr1.rrlyrae,gaiadr1.phot_variable_time_series_gfov

Query: 
SELECT curves.observation_time,
	mod(curves.observation_time - rrlyrae.epoch_g, rrlyrae.p1)/ rrlyrae.p1 AS phase,
	curves.g_magnitude,
	2.5 / log(10) * curves.g_flux_error / curves.g_flux AS g_magnitude_error
FROM gaiadr1.phot_variable_time_series_gfov AS curves
INNER JOIN gaiadr1.rrlyrae AS rrlyrae
	 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. Clementini et al. 2016 A&A 595A, A133 (adapted)

NOTE: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial: https://www.cosmos.esa.int/web/gaia-users/archive/ancillary-data#tutorial_datalinklc)

Target table: gaiadr1.rrlyrae,gaiadr1.phot_variable_time_series_gfov_statistical_parameters

Query: 
SELECT stat.num_observations_processed, rr.*
FROM gaiadr1.phot_variable_time_series_gfov_statistical_parameters AS stat
INNER JOIN gaiadr1.rrlyrae AS rr
ON stat.source_id = rr.source_id