Archive Historical Help content

This page contains FAQs and tutorials that have become obsolete. It can be useful for users who store obsolete content (like tables or files) in their computers.

Gaia Collaboration queries 

This section contains useful ADQL queries extracted from the DPAC documentation, including DPAC articles.

Contents:

5.1 Gaia DR2 selected queries
5.2 Gaia DR1 selected queries

Gaia DR2 selected queries

Light Curves / epoch photometry

Use case: I want to retrieve the light curves for two variable stars (DR2, Gaia Collaboration, Holl et al. 2018 A&A 618, A30; adapted Fig. A1).
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 USING (source_id)
LEFT OUTER JOIN gaiadr2.vari_rrlyrae AS sosrrl USING (source_id)
LEFT OUTER JOIN gaiadr2.vari_long_period_variable AS soslpv USING (source_id)
WHERE gaia.source_id IN (1372970619261887104, 5368939678153068288)

Density distribution of radial velocities

Use case:  I want to show the average distribution of Gaia DR2 radial velocities in Galactic Coordinates (DR2, Gaia Collaboration, Katz et al. 2019 A&A 622, A205; adapted Fig. 7).
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 (DR2, Gaia Collaboration, Andrae et al. 2018 A&A 616, A8; adapted Fig. 19).
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

Average Extinction distribution

Use case:  I want to obtain the parallax-averaged A_G Extinction (DR2, Gaia Collaboration, Andrae et al. 2018 A&A 616, A8; adapted Fig. 21).
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 (DR2, Gaia Collaboration, Andrae et al. 2018 A&A 616, A8; adapted Fig. 34).
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 (DR2, Gaia Collaboration, Holl et al. 2018 A&A 618, A30; adapted Fig. 6).
Target table: gaiadr2.gaia_source
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

Gaia DR1 selected queries

Gaia DR1-2MASS HR diagram

Use case:  I want to construct the Gaia DR1-TMASS HR diagram (DR1, Gaia Collaboration, Brown Holl et al. 2016 A&A 595, A2; adapted Fig. 5).
Target table: gaiadr1.gaia_source, gaiadr1.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 gaiadr1.gaia_source AS gaia
INNER JOIN gaiadr1.tmass_best_neighbour AS xmatch USING (source_id)
INNER JOIN gaiadr1.tmass_original_valid AS tmass  USING (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 DR1-2MASS HR diagram

Use case:   I want to construct the Gaia DR2 HR diagram. (DR1, Gaia Collaboration, Brown Holl et al. 2016 A&A 595, A2; adapted Fig. 5).
Target table: gaiadr1.gaia_source
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 USING (source_id)
  INNER JOIN gaiadr1.tmass_original_valid AS tmass  USING (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 complete epoch photometry of a given object class for offline analysis (DR1, Gaia Collaboration, Clementini et al. 2016 A&A 595, A133).
Notes: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial).
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 USING (source_id)

RR Lyrae: phase-folded light curve reconstruction, including errors

Use case: I want to construct a phase-folded light curve for comparison to stars with different periods (DR1, Gaia Collaboration, Brown et al. 2016 A&A 595, A2; adapted Fig. 7).
Notes: After Gaia DR1, light curves are being provided via the DataLink interface (see dedicated Tutorial).
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 USING (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, Gaia Collaboration, Clementini et al. 2016 A&A 595, A133).
Notes: After Gaia DR1, light curves are being provided via the DataLink interface (see dedicated Tutorial).
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 USING (source_id)

FAQs

Tutorials

DR3 EPOCH PHOTOMETRY (prior to GACS 3.6)

The serialisation of the epoch photometry in INDIVIDUAL data structure served before GACS 3.6 was based on the IVOA Time series cube data model. No metadata was added into the file header, and all the information was repeated through the output table so each row was self-contained. The table fields and their associated metadata is summarised in the table below, where the fields added by the Archive (and not included in the DPAC/RAW data model) are highlighted with bold fonts:

Descriptions for the added fields:

band: Photometric band. Values: G (per-transit combined SM-AF flux), BP (blue photometer integrated flux), and RP (red photometer integrated flux).

rejected_by_photometry: Rejected by DPAC photometric processing. Unavailable or rejected by DPAC photometric processing, or negative (unphysical) flux.

other flags: Additional processing flags. This field contains extra information on the data used to compute the fluxes and their quality. It provides debugging information that may be safely ignored for many general purpose applications. The field is a collection of binary flags, whose values can be recovered by applying bit shifting and masking operations. Each band has different binary flags in different positions, as shown below. Bit numbering is as follows: least significant bit = 1 and most significant bit = 64.

• G band:
  • Bit 1: SM transit rejected by photometric processing.
  • Bit 2 to 10: AF1 to AF9 transit rejected by photometric processing.
  • Bit 13: G band flux scatter larger than expected (all CCDs considered).
  • Bit 14: SM transit unavailable by photometric processing.
  • Bit 15 to 23: AF1 to AF9 transit unavailable by photometric processing.
• BP band:
  • Bit 11: BP transit rejected by photometric processing.
  • Bit 24: BP transit photometry rejected by variability processing.
• RP band:
  • Bit 12: RP transit rejected by photometric processing.
  • Bit 25: RP transit photometry rejected by variability processing.

Figure 1: excerpt from the DR3 Epoch Photometry table serialised in INDIVIDUAL structure as displayed by TOPCAT.