Examples

This example query retrieves stars in the sky region around the globular cluster Messier 4 (M4), filtering by celestial coordinates and parallax to select nearby stars at a similar distance.

SELECT ra, dec, parallax from gaiadr3.gaia_source_lite
  WHERE 1=CONTAINS(POINT(ra, dec), CIRCLE(POINT(245.9, -26.5), 0.5))
  AND parallax BETWEEN 0.5 AND 0.6

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

-- LANG: PostgreSQL
-- QUEUE: 30s
SELECT *
  FROM "gaiadr3"."gaia_source_lite" 
  LIMIT 2000

Select 2000 random sources with a RVS mean spectrum. These spectra can be visualized with help of the spectra viewer. Additionally this table can then be used with the Simple Join Service to retrieve the spectra.

-- SELECT 2000 random sources with RVS Mean Spectrum
--
-- LANG: Postgres
-- QUEUE: 30s
--
SELECT source_id
  FROM gaiadr3.gaia_source_lite
 WHERE has_rvs = True
   AND random_index BETWEEN 500000 AND 1000000
 LIMIT 2000

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 2000 random sources with Xp Continuous Mean Spectrum
--
-- LANG: Postgres
-- QUEUE: 30s
--
SELECT source_id
  FROM gaiadr3.gaia_source_lite
 WHERE has_xp_sampled = True
   AND random_index BETWEEN 500000 AND 1000000
 LIMIT 2000

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.source_id = vs.source_id 
   AND gs.random_index BETWEEN 500000 AND 1000000 
 LIMIT 2000

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

This query selects all sources incl. Gaia DR3 and Gaia FPR in the crowded field 0.8 degrees around the Omega-Cen cluster centre.

SELECT source_id, ref_epoch, ra, ra_error, dec, dec_error, 
       pmra, pmra_error, pmdec, pmdec_error, 
       phot_g_mean_mag, l, b, n_scans, 'fpr' as origin 
  FROM gaiafpr.crowded_field_source 
 UNION 
SELECT source_id, ref_epoch, ra, ra_error, dec, dec_error, 
       pmra, pmra_error, pmdec, pmdec_error, 
       phot_g_mean_mag, l, b, -1 as nscans, 'dr3' as origin
  FROM gaiadr3.gaia_source 
 WHERE pos @ SCIRCLE(SPOINT(RADIANS(201.69399972775088),RADIANS(-47.484610741298994)), RADIANS(0.8))

This query selects all sources incl. Gaia DR3 and Gaia FPR in the crowded field 0.8 degrees around the Omega-Cen cluster centre.

SELECT source_id, ref_epoch, 
       ra, ra_error, dec, dec_error,
       pmra, pmra_error, pmdec, pmdec_error, 
       phot_g_mean_mag, l, b, n_scans, 'fpr' as origin
  FROM gaiafpr.crowded_field_source
 UNION
SELECT source_id, ref_epoch, 
       ra, ra_error, dec, dec_error,
       pmra, pmra_error, pmdec, pmdec_error, 
       phot_g_mean_mag, l, b, -1 as nscans, 'dr3' as origin
  FROM gaiadr3.gaia_source
 WHERE 1=CONTAINS(POINT('ICRS', ra, dec),
       CIRCLE('ICRS', 201.69399972775088,-47.484610741298994, 0.8))

This query counts the number of spectra available for each healpix.

SELECT sub.healpix, COUNT(*) as nbr_spectra 
    FROM 
 (SELECT healpix 
    FROM "gaiafpr"."interstellar_medium_spectra" 
GROUP BY (lc, bc, dc, healpix)) AS sub
GROUP BY sub.healpix 
  HAVING COUNT(*) > 1
ORDER BY sub.healpix ASC

This query retrieves the principal quantity from GaiaDR3 and FPR Long Period Variable (LPV) product.

SELECT gs.source_id, 
           gs.ra, gs.dec,
           fprlpv.frequency_rv, fprlpv.frequency_rv_error,
           fprlpv.amplitude_rv, fprlpv.flag_rv
     FROM gaiadr3.gaia_source_lite as gs
LEFT JOIN gaiafpr.vari_long_period_variable as fprlpv
       ON gs.source_id = fprlpv.source_id
    WHERE fprlpv.source_id IS NOT NULL

Retrieve the radial velocity time series from Gaia FPR.

SELECT fprlpv.source_id, 
          varirv.transit_id,
          varirv.rv_obs_time,
          varirv.radial_velocity,
          varirv.radial_velocity_error,
          varirv.rejected_by_variability
     FROM gaiafpr.vari_long_period_variable as fprlpv
LEFT JOIN gaiafpr.vari_epoch_radial_velocity as varirv
       ON varirv.source_id = fprlpv.source_id
    WHERE fprlpv.source_id IS NOT NULL

Retrieve the state vector and semi-major axis uncertainty for all the valid orbital solutions.

SELECT "number_mp", "denomination", "epoch_state_vector", 
       h_state_vector[1] AS x, 
       h_state_vector[2] AS y, 
       h_state_vector[3] AS z, 
       h_state_vector[4] AS vx, 
       h_state_vector[5] AS vy, 
       h_state_vector[6] AS vz, 
       SQRT(orbital_elements_var_covar_matrix[1]) AS sigma 
  FROM "gaiafpr"."sso_source" 
 WHERE orbital_elements_var_covar_matrix[1] > 0 
 ORDER BY "number_mp" ASC

A Cone Search query for GaiaFPR Interstellar Medium Spectra table (using a helping table)

SELECT ism.healpix as healpix, ism.lc as lc, ism.bc as bc, ism.dc, 
       coord.ra as ra, coord.dec as dec,
       ism.lambda as lambda, ism.flux as flux, ism.flux_uncertainty as flux_uncertainty
  FROM gaiafpr.interstellar_medium_spectra as ism,
       gaiafpr_contrib.ism_gaiagal_to_eq as coord
 WHERE coord.pos @ SCIRCLE(SPOINT(RADIANS(201.69399972775088),RADIANS(-47.484610741298994)), RADIANS(2))
   AND ism.lc = coord.lc
   AND ism.bc = coord.bc

Select the composed ID (healpix, dc) of a spectra in a cone search.

SELECT ism.healpix, ism.dc, COUNT(*) as res
  FROM gaiafpr.interstellar_medium_spectra as ism,
       gaiafpr_contrib.ism_gaiagal_to_eq as coord
 WHERE coord.pos @ SCIRCLE(SPOINT(RADIANS(201.69399972775088),RADIANS(-47.484610741298994)), RADIANS(2))
   AND ism.lc = coord.lc
   AND ism.bc = coord.bc
 GROUP BY ism.healpix, ism.dc
 ORDER BY ism.dc ASC

Count the number of spectra available in a cone search.

SELECT COUNT(*) as nbr_voxels
FROM 
     (SELECT ism.healpix, ism.dc
        FROM gaiafpr.interstellar_medium_spectra as ism,
             gaiafpr_contrib.ism_gaiagal_to_eq as coord
       WHERE coord.pos @ SCIRCLE(SPOINT(RADIANS(201.69399972775088),RADIANS(-47.484610741298994)), RADIANS(2))
         AND ism.lc = coord.lc
         AND ism.bc = coord.bc
       GROUP BY ism.dc, ism.healpix
       ORDER BY ism.dc ASC) as spectra

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

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

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