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 retrieve all the objects in a catalogue in a circular region centered at (266.41683,-29.00781) with a search radius of 5 arcmin (0.08333 deg).

Target table: gaiadr2.gaia_source

Query:
SELECT DISTANCE(
  POINT('ICRS', ra, dec),
  POINT('ICRS', 266.41683, -29.00781)) AS dist, *
FROM gaiadr2.gaia_source
WHERE 1=CONTAINS(
  POINT('ICRS', ra, dec),
  CIRCLE('ICRS', 266.41683, -29.00781, 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: gaiadr2.gaia_source

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

TGAS 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: gaiadr1.tgas_source

Query:
SELECT *
FROM gaiadr1.tgas_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

In case of catalogues with dissimilar 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.

Note only the first 10 objects are retrieved to make the query fast.

Remove the “top 10” statement to retrieve the full diagram

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

Note that several pre-computed cross-matches against major surveys is available in the Gaia Archive.

Query:
SELECT TOP 10 * , distance(
  POINT('ICRS', hip.ra, hip.de),
  POINT('ICRS', gaia.ra, gaia.dec)) AS dist
FROM gaiadr2.gaia_source AS gaia, public.hipparcos AS hip
WHERE 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

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 Help -> ADQL syntax for further details)

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

Note that several pre-computed cross-matches against major surveys is available in the Gaia Archive.

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

TGAS Healpix maps: source density, average good observations and excess noise

DR1. Gaia Collaboration, Lindegren et al. 2016 A&A 595A, 4L Figs. 5, 6 (adapted)

Note only the first 10 objects are used to make the query fast.

Remove the “top 10” statement to retrieve the full diagram.

Use case: I want the representation on the sky of some survey property.

Query:
SELECT TOP 10
  gaia_healpix_index(6, source_id) AS healpix_6,
  count(*) / 0.83929 as sources_per_sq_deg,
  avg(astrometric_n_good_obs_al) AS avg_n_good_al,
  avg(astrometric_n_good_obs_ac) AS avg_n_good_ac,
  avg(astrometric_n_good_obs_al + astrometric_n_good_obs_ac) AS avg_n_good,
  avg(astrometric_excess_noise) as avg_excess_noise
FROM gaiadr1.tgas_source
GROUP BY healpix_6

Red clump stars. Absolute magnitudes within Hipparcos colour bin

DR1. Gaia Collaboration, Brown et al. 2016 A&A 595A, 2G Fig. 4

Use case: I want to estimate whether the Red clump absolute magnitude distribution is sharper (and thus a better define quantity) in TGAS compared to Hipparcos.

Query:
SELECT gaia.source_id, gaia.hip,
	gaia.phot_g_mean_mag + 5 * log10(gaia.parallax) - 10 AS g_mag_abs_gaia,
	gaia.phot_g_mean_mag + 5 * log10(hip.plx) - 10 AS g_mag_abs_hip
FROM gaiadr1.tgas_source AS gaia
INNER JOIN public.hipparcos_newreduction AS hip
	ON gaia.hip = hip.hip
WHERE gaia.parallax/gaia.parallax_error >= 5 AND
	hip.plx/hip.e_plx >= 5 AND
	hip.e_b_v > 0.0 and hip.e_b_v <= 0.05 AND
	hip.b_v >= 1.0 AND hip.b_v <= 1.1 AND
	2.5 / log(10) * gaia.phot_g_mean_flux_error / gaia.phot_g_mean_flux <= 0.05

Red clump stars

Red clump stars. Absolute magnitudes within Hipparcos colour bin

1D histogram computed within the Archive, bin = 0.2 mag

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

Use case: I want to estimate whether the Red clump absolute magnitude distribution is sharper (and thus a better define quantity) in TGAS compared to Hipparcos.

Query:
SELECT g_mag_abs_hip_index / 5. AS g_mag_abs_hip, count(g_mag_abs_hip_index) AS freq FROM (
  SELECT floor((gaia.phot_g_mean_mag + 5 * log10(hip.plx) - 10) * 5) AS g_mag_abs_hip_index
  FROM gaiadr1.tgas_source AS gaia
  INNER JOIN public.hipparcos_newreduction AS hip
    ON gaia.hip = hip.hip
  WHERE gaia.parallax/gaia.parallax_error >= 5 AND
    hip.plx/hip.e_plx >= 5 AND
    hip.e_b_v > 0.0 and hip.e_b_v <= 0.05 AND
    hip.b_v >= 1.0 and hip.b_v <= 1.1 AND
    2.5 / log(10) * gaia.phot_g_mean_flux_error / gaia.phot_g_mean_flux <= 0.05
) AS subquery
GROUP BY g_mag_abs_hip_index
ORDER BY g_mag_abs_hip

TGAS-2MASS HR diagram

DR1. Gaia Collaboration, Brown et al. 2016 A&A 595A, 2G Fig. 5

Note only the first 10 objects are retrieved to make the query fast.

Remove the “top 10” statement to retrieve the full diagram.

Use case: I want to construct the TGAS HR diagram.

Query:
SELECT TOP 10 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

TGAS-2MASS HR diagram

2D histogram computed within the Archive, 0.1 mag bin size

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

Note only the first 10 objects are used to make the query fast.

Remove the “top 10” statement to retrieve the full diagram.

Use case: I want to construct the TGAS HR diagram.

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 10 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 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
	)AS subquery
GROUP BY g_min_ks_index, g_mag_abs_index

RR Lyrae phase folded light curve reconstruction, including errors

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

Use case: I want to construct a phase-folded light curve for easier analysis and comparison to stars with different period.

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 

Cepheids: light curve retrieval for all stars

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

Use case: I want to retrieve all light curves of a given object class for offline analysis.

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: light curve retrieval for all stars

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

Use case: I want to retrieve all light curves of a given object class for offline analysis.

Query:
SELECT gaia.*
FROM gaiadr1.phot_variable_time_series_gfov AS gaia
INNER JOIN gaiadr1.rrlyrae AS rr
ON gaia.source_id = rr.source_id

Cepheids: number of data points and estimated parameters

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

Use case: I want high level information for a given class of variable objects.

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

RR Lyrae: number of data points and estimated parameters

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

Use case: I want high level information for a given class of variable objects.

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

Proper motion propagation: Pleiades TGAS field in 1950

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

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 gaiadr1.tgas_source AS gaia
  WHERE contains(
    POINT('ICRS', gaia.ra, gaia.dec),
    CIRCLE('ICRS', 56.75, 24.12, 5)) = 1
  AND sqrt(power(gaia.pmra - 20.5, 2) + power(gaia.pmdec + 45.5, 2)) < 6.0 
) AS subquery