Examples

This is a reduced pre-selection of columns of gaiadr3.gaia_source.

-- A light version of gaia_source
--
-- LANG: POstgres
-- QUEUE: 30s
--
SELECT *
  FROM "gaiadr3"."gaia_source_lite" 
  LIMIT 2000

SELECT `tycho2_id`,SQRT(POW(`pmra`, 2) + POW(`pmdec`, 2)) / 1000 as pm
FROM `GDR1`.`tgas_source`
WHERE `tycho2_id` IS NOT NULL
ORDER BY pm DESC
LIMIT 100;

Select 2000 random sources with a sampled mean spectrum. This table can then be used with the Simple Join Service to retrieve the spectra.

SELECT source_id
  FROM gaiadr3.gaia_source_lite
 WHERE has_xp_sampled = True
   AND random_index BETWEEN 500000 AND 1000000
 LIMIT 2000

SELECT gmag * 0.1 AS gmag_bin, COUNT(gmag) AS number
FROM
(
    SELECT FLOOR(`phot_g_mean_mag` * 10) AS gmag
    FROM `GDR1`.`gaia_source`
) AS gmag_tab
GROUP BY gmag;

Select a random sample of 2000 sources with informations in the vari tables.

-- SELECT 2000 random sources with variable informations
--
-- LANG: Postgres
-- QUEUE: 30s
--
SELECT gs.source_id, 
       vs."in_vari_classification_result", 
       vs."in_vari_rrlyrae", 
       vs."in_vari_cepheid", 
       vs."in_vari_planetary_transit", 
       vs."in_vari_short_timescale", 
       vs."in_vari_long_period_variable", 
       vs."in_vari_eclipsing_binary", 
       vs."in_vari_rotation_modulation", 
       vs."in_vari_ms_oscillator", 
       vs."in_vari_agn", 
       vs."in_vari_microlensing", 
       vs."in_vari_compact_companion" 
  FROM gaiadr3.gaia_source_lite as gs, 
       gaiadr3.vari_summary as vs 
 WHERE gs."phot_variable_flag" = 'VARIABLE' 
   AND gs.random_index BETWEEN 500000 AND 1000000 
 LIMIT 2000

SELECT `hip`, `tycho2_id`, `solution_id`, `source_id`, `random_index`, `ref_epoch`, `ra`, `ra_error`, `dec`, `dec_error`, `parallax`, `parallax_error`, `pmra`, `pmra_error`, `pmdec`, `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`, `astrometric_n_obs_al`, `astrometric_n_obs_ac`, `astrometric_n_good_obs_al`, `astrometric_n_good_obs_ac`, `astrometric_n_bad_obs_al`, `astrometric_n_bad_obs_ac`, `astrometric_delta_q`, `astrometric_excess_noise`, `astrometric_excess_noise_sig`, `astrometric_primary_flag`, `astrometric_relegation_factor`, `astrometric_weight_al`, `astrometric_weight_ac`, `astrometric_priors_used`, `matched_observations`, `duplicated_source`, `scan_direction_strength_k1`, `scan_direction_strength_k2`, `scan_direction_strength_k3`, `scan_direction_strength_k4`, `scan_direction_mean_k1`, `scan_direction_mean_k2`, `scan_direction_mean_k3`, `scan_direction_mean_k4`, `phot_g_n_obs`, `phot_g_mean_flux`, `phot_g_mean_flux_error`, `phot_g_mean_mag`, `phot_variable_flag`, `l`, `b`, `ecl_lon`, `ecl_lat`
FROM `GDR1`.`tgas_source`;

Select all datalink flags columns

-- Query the Datalink flags
--
-- LANG: Potsgres
-- QUEUE: 30s
--
SELECT "source_id", 
	   "phot_variable_flag",
       "in_qso_candidates", "in_galaxy_candidates", 
       "non_single_star", "has_xp_continuous", "has_xp_sampled", "has_rvs", 
       "has_epoch_photometry", "has_epoch_rv", "has_mcmc_gspphot", "has_mcmc_msc", 
       "in_andromeda_survey"
  FROM "gaiadr3"."gaia_source_lite" 
  LIMIT 2000

SELECT COUNT(*) FROM `GDR1`.`tgas_source`;

SELECT FLOOR(`phot_g_mean_mag`) AS gmag, COUNT(FLOOR(`phot_g_mean_mag`)) AS `count`
FROM `GDR1`.`tgas_source`
GROUP BY FLOOR(`phot_g_mean_mag`);

Query for the zero-point correction tutorial. ref: https://gitlab.com/icc-ub/public/gaiadr3_zeropoint/-/tree/master

-- Query for the zero-point correction tutorial

-- LANGUAGE = PostgreSQL
-- QUEUE = 30s

SELECT * 
FROM "gaiaedr3"."gaia_source"
WHERE "phot_g_mean_mag" BETWEEN 17.9 AND 18.1
  AND "random_index" BETWEEN 700000000 AND 701000000

Query for G-band magnitude/flux corrections for 6-p Gaia EDR3 sources. ref: https://github.com/agabrown/gaiaedr3-6p-gband-correction

-- Query for G-band magnitude/flux corrections for 6-p Gaia EDR3 sources

-- LANGUAGE = PostgreSQL
-- QUEUE = 30s

SELECT "source_id", "astrometric_params_solved", "bp_rp", "phot_g_mean_mag", "phot_g_mean_flux"
FROM "gaiaedr3"."gaia_source"
WHERE "random_index" BETWEEN 5000000 AND 5999999

Query for the calculation of the corrected flux excess factor. ref: https://github.com/agabrown/gaiaedr3-flux-excess-correction

-- Query for the calculation of the corrected flux excess factor

-- LANGUAGE = PostgreSQL
-- QUEUE = 30s

SELECT "source_id", "bp_rp", "phot_bp_rp_excess_factor"
FROM "gaiaedr3"."gaia_source"
WHERE "random_index" BETWEEN 1000000 AND 1999999

Select 6p stars with pseudo-colour.

-- Select 6p stars with pseudo-colour

-- LANGUAGE = PostgreSQL
-- QUEUE = 30s

-- Note: astrometric_params_solved 2p: 3 - 5p: 31 - 6p: 95

SELECT "source_id", "astrometric_params_solved", "pseudocolour", "pseudocolour_error"
FROM "gaiaedr3"."gaia_source" 
WHERE "astrometric_params_solved" = 95 
LIMIT 10

Select stars which show a 20% improvement in the parallax precision from DR2 to EDR3. ref: Documentation (7.1)

-- parallax typically improves to 20% from DR2 to EDR3

-- LANGUAGE = PostgreSQL
-- QUEUE = 30s

SELECT edr3."source_id" AS edr3_source_id, 
       dr2."source_id" AS dr2_source_id,

       -- parallax of EDR3
       edr3."parallax" AS edr3_parallax, 
       edr3."parallax_error" AS edr3_parallax_error, 
       edr3."parallax_over_error" AS edr3_parallax_over_error,
       
       -- parallax of DR2
       dr2."parallax_error" AS dr2_parallax_error, 
       dr2."parallax" AS dr2_parallax, 
       dr2."parallax_over_error" AS dr2_parallax_over_error,
       
       -- ratio of the error
       edr3.parallax_error/dr2.parallax_error AS ratio
       
FROM "gaiaedr3"."dr2_neighbourhood" AS "dr2_neigh",
     "gaiaedr3"."gaia_source" AS "edr3",
     "gaiadr2"."gaia_source" AS "dr2"
     
-- Cross-match source_id
WHERE "dr2_neigh"."dr3_source_id" = "edr3"."source_id" 
  AND "dr2_neigh"."dr3_source_id" = "dr2"."source_id"
  
  -- When parallax are positive
  AND dr2.parallax > 0.
  AND edr3.parallax > 0.
  
  -- Preselect good parallax (from EDR3
  AND edr3.parallax_over_error > 10.
  
  -- Look for the 20% improvement claim
  AND edr3.parallax_error/dr2.parallax_error < 0.8 -- 20 % improvement
       
LIMIT 10

Histogram of the magnitude difference between DR2 and EDR3. ref: Documentation (9.3)

-- Histogram of the magnitude difference between DR2 and EDR3

-- LANGUAGE = ADQL
-- QUEUE = 2h
SELECT FLOOR(magnitude_difference * 1000.) / 1000. AS magnitude_difference_bin, 
       count(*) AS n
       
FROM gaiaedr3.dr2_neighbourhood

WHERE angular_distance < 100.

GROUP BY magnitude_difference_bin
ORDER BY magnitude_difference_bin

Compare coordinates between DR2 and EDR3

-- Compare coordinates between DR2 and EDR3

-- LANGUAGE = PostgreSQL
-- QUEUE = 5m

SELECT dr2.ra as dr2_ra, 
       dr2.dec as dr2_dec, 
       edr3.ra as edr3_ra, 
       edr3.dec as dr3_dec
       
FROM gaiadr2.gaia_source AS dr2,
     gaiaedr3.gaia_source AS edr3, 
     gaiaedr3.dr2_neighbourhood AS xm
     
WHERE dr2.source_id = xm.dr2_source_id
  AND edr3.source_id = xm.dr3_source_id
  
LIMIT 100000

Combine Gaia information with 2MASS J, H and Ks photometric measurements.

-- Gaia + 2MASS photometry

SELECT TOP 10 gaia.ra, gaia.dec,
       gaia.phot_g_mean_mag, gaia.phot_bp_mean_mag, gaia.phot_rp_mean_mag,
       tmass.j_m, tmass.h_m, tmass.k_m, tmass.ph_qual
FROM gaiadr2.gaia_source AS gaia,
	 gaiadr2.tmass_best_neighbour AS xm,
     catalogs.tmass AS tmass
WHERE gaia.source_id = xm.source_id
AND xm.tmass_oid = tmass.tmass_oid;

Select orbital elements of all available SSOs.

SELECT osc_epoch, orb_m, omega, node_omega, inclination, eccentricity, a
FROM gaiadr2.aux_sso_orbits;

Select first 10 light curves that have more than 100 points. This query operates on data that is packed into arrays and must therefore be run with PostgreSQL.

-- Run with PostgreSQL

SELECT gaia.ra, gaia.dec,
	   ep.source_id, ep.g_transit_time, ep.g_transit_flux
FROM gaiadr2.gaia_source AS gaia, gaiadr2.epoch_photometry AS ep
WHERE array_length(ep.g_transit_time, 1) > 100
AND ep.source_id = gaia.source_id
LIMIT 10;

-- Replace COUNT(*) with requested columns

SELECT COUNT(*)
FROM gaiadr2.gaia_source
WHERE phot_g_mean_mag < 12
AND radial_velocity IS NOT NULL;

SELECT gaia.source_id, gaia.ra, gaia.dec, gd.r_est
FROM gaiadr2.gaia_source gaia, gaiadr2_contrib.geometric_distance gd
WHERE 1 = CONTAINS(POINT('ICRS', gaia.ra, gaia.dec), 
                   CIRCLE('ICRS',245.8962, -26.5222, 0.5))
AND gaia.phot_g_mean_mag < 15
AND gd.r_est > 1500 AND gd.r_est < 2300
AND gaia.source_id = gd.source_id

Select stars with radial velocity measurements, use the source_id to compute the HEALPix index and uniformly limit the number of stars using random_index.

-- Compute HEALPix value from source_id

SELECT FLOOR(source_id / (POW(2, 35) * POW(4, 6))) AS hpix, radial_velocity AS rv
FROM gaiadr2.gaia_source
WHERE random_index < 10000000
AND radial_velocity IS NOT NULL;

SELECT TOP 1000 dr1.ra as dr1_ra, dr1.dec as dr1_dec, dr2.ra as dr2_ra, dr2.dec as dr2_dec
FROM gaiadr1.gaia_source AS dr1, gaiadr2.gaia_source AS dr2, gaiadr2.dr1_neighbourhood AS xm
WHERE dr1.source_id = xm.dr1_source_id
AND dr2.source_id = xm.dr2_source_id;

Hertzsprung–Russell diagram of the famous globular cluster 47Tuc.

SELECT bp_rp, phot_g_mean_mag AS gp
FROM gaiadr2.gaia_source
WHERE 1 = CONTAINS(POINT('ICRS', ra, dec),
                   CIRCLE('ICRS', 6.0223292, -72.0814444, 0.2))
AND phot_g_mean_flux_over_error > 50
AND phot_rp_mean_flux_over_error > 20
AND phot_bp_mean_flux_over_error > 20
AND phot_bp_rp_excess_factor < 1.3 + 0.06 * POW(bp_rp, 2)
AND phot_bp_rp_excess_factor > 1.0 + 0.015 * POW(bp_rp, 2)
AND visibility_periods_used > 8
AND astrometric_chi2_al / (astrometric_n_good_obs_al - 5) <
    1.44 * greatest(1, exp(-0.4 * (phot_g_mean_mag - 19.5)));

Count the number of stars in the TGAS catalog with parallax / parallax_error > 10;

SELECT COUNT(*)
FROM gaiadr1.tgas_source
WHERE parallax / parallax_error > 10;

Compute the histogram of TGAS G magnitudes.

SELECT gmag * 0.1 AS gmag_bin, COUNT(gmag) AS number
FROM
(
    SELECT FLOOR(phot_g_mean_mag * 10) AS gmag
    FROM gaiadr1.tgas_source
) AS gmag_tab
GROUP BY gmag;

-- Run with PostgreSQL
-- Input of function spoint() must be in radians!

SELECT ra, dec, phot_g_mean_mag AS gmag
FROM gaiadr1.gaia_source
WHERE pos @ scircle(spoint(RADIANS(245.8962), RADIANS(-26.5222)), RADIANS(0.5))
AND phot_g_mean_mag < 15