Gaia ADQL example queries
Cone search sorted by angular separation
Query:
SELECT *, DISTANCE(
POINT(81.28, -69.78),
POINT(ra, dec)) AS ang_sep
FROM gaiadr3.gaia_source
WHERE 1 = CONTAINS(
POINT(81.28, -69.78),
CIRCLE(ra, dec, 5./60.))
AND phot_g_mean_mag < 20.5
AND parallax IS NOT NULL
ORDER BY ang_sep ASC
Cone search in galactic coordinates + DataLink filter
Query:
SELECT *, DISTANCE(
POINT(302.95, -43.98),
POINT(l, b)) AS ang_sep
FROM gaiadr3.gaia_source
WHERE 1 = CONTAINS(
POINT(302.95, -43.98),
CIRCLE(l, b, 5./60.))
AND has_epoch_photometry = 'True'
AND has_xp_continuous = 'True'
Selection of rectangular sky region + 2MASS pre-computed cross-match
Query:
SELECT gaia.source_id, gaia.ra, gaia.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
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
Query:
SELECT TOP 1000 *, DISTANCE(
POINT(hip.ra, hip.de),
POINT(gaia.ra, gaia.dec)) AS ang_sep
FROM public.hipparcos AS hip
JOIN gaiadr3.gaia_source AS gaia
ON 1 = CONTAINS(
POINT(hip.ra, hip.de),
CIRCLE(gaia.ra, gaia.dec, 0.00028))
Advanced positional cross-match
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
CONTAINS(
POINT(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))),
CIRCLE(ra, dec, 1.))=1
Positional cross-match + proper-motion propagation
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 1 = CONTAINS(
POINT(56.75, 24.12),
CIRCLE(gaia.ra, gaia.dec, 5.0))
AND SQRT(POWER(gaia.pmra - 20.5, 2) + POWER(gaia.pmdec + 45.5, 2)) < 6.0 OFFSET 0) AS subquery
JOIN external.galex_ais AS galex
ON 1 = CONTAINS(
POINT(
COORD1(subquery.propagated_position_vector),
COORD2(subquery.propagated_position_vector)),
CIRCLE(galex.raj2000, galex.dej2000, 1./3600.))
Retrieve average quantities per HEALPix level 8 in the Galactic anticentre
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)
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
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)