Help support

Should you have any question, please check the Gaia FAQ section or contact the Gaia Helpdesk

Query examples

Below is a selected sample of ADQL queries that can be executed in the Gaia ESA Archive in less than 1 minute under average user-load conditions. They are representative of different (but common) use cases. Most of them have been extracted from the different articles that accompany the Gaia (E)DR3 releases. This complementary page  contains a list of Gaia Collaboration paper queries targetting all Gaia Data Releases. 

Content:

  1. Cone search sorted by angular separation
  2. Cone search in galactic coordinates + DataLink filter
  3. Selection of rectangular sky region + 2MASS pre-computed cross-match
  4. Bright sources in Gaia DR2 and (E)DR3
  5. Basic positional cross-match: Hipparcos vs. Gaia
  6. Advanced positional cross-match
  7. Positional cross-match + proper-motion propagation
  8. Retrieve average quantities per HEALPix level 8 in the galactic anti-centre
  9. Extinction-corrected CMD diagram (histogram)
  10. Exploration of the interstellar medium with Gaia RVS diffuse interstellar bands


Cone search sorted by angular separation

Use case: I want to retrieve a sample of filtered sources (brighter than G=20.5 mag and with a parallax measurement) in a circular region centred on the Large Magellanic Coud [(R.A., Dec) = (81.28, -69.78) deg] with a search radius of 5 arcmin (adapted from EDR3, Gaia Collaboration, Luri et al., 2021, A&A 649, A7). The output should be ordered by the angular separation from the centre, from small to large.
Notes: Beware of integer divisions in ADQL (e.g., 5/60 = 0 whereas 5.0/60.0 = 0.083)
Target table: gaiadr3.gaia_source
Query:

SELECT *, DISTANCE(81.28, -69.78, ra, dec) AS ang_sep
FROM gaiadr3.gaia_source
WHERE DISTANCE(81.28, -69.78, 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

Use case: I want to retrieve a sample of sources contained in a circular region centred on the Small Magellanic Coud [(l, b) = (302.95, -43.98) deg] with a search radius of 5 arcmin (adapted from EDR3, Gaia Collaboration, Luri et al., 2021, A&A 649, A7) that have DataLink (see the DataLink tutorials in this page) products: epoch photometry and XP spectra.
Notes: Beware of integer divisions in ADQL (e.g., 5/60 = 0 whereas 5.0/60.0 = 0.083)
Target table: gaiadr3.gaia_source
Query:

SELECT *, DISTANCE(302.95, -43.98, l, b) AS ang_sep
FROM gaiadr3.gaia_source
WHERE DISTANCE(302.95, -43.98, l, b) < 5./60.
AND has_epoch_photometry = 'True'
AND has_xp_continuous = 'True'

SELECTION OF rectangular SKY region + 2MASS pre-computed cross-match

Use case: I want to retrieve a set of parameters from a sample of sources contained in a (projected) rectangular region centred on the Small Magellanic Cloud [(l, b) = (302.95, -43.98) deg] in Galactic coordinates, and combine them with the 2MASS Point Source Catalogue. In addition, I want to filter out the sources with lower-quality infrared photometry (according to the 2MASS PSC catalogue).
Notes: The Gaia Archive contains many pre-computed cross-matches against major surveys (see the Pre-computed cross-matches tutorial).
Target tables: gaiadr3.gaia_source, gaiadr1.tmass_original_valid, gaiadr3.tmass_psc_xsc_best_neighbour, gaiadr3.tmass_psc_xsc_join, gaiadr1.tmass_original_valid.
Query:

SELECT gaia.source_id, gaia.ra AS g_ra, gaia.dec AS g_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

Use case: I want to retrieve the targets with G < 5 mag in Gaia DR2 and their Gaia DR3 counterparts (adapted from EDR3, Gaia Collaboration, Brown et al., 2021, A&A 649, A1). This query uses the pre-computed Gaia DR2-DR3 cross-match table. The output is ordered by the Gaia DR2 source_id field.
Target table: gaiadr2.gaia_source, gaiadr3.dr2_neighbourhood.
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

Use case: I want to carry out a positional cross-match between two catalogues using a cone-seach radius of 1 arcsecond (0.00028 degrees).
Notes: When cross-matching catalogues in the Gaia Archive, the smaller and larger catalogues should be used as the first and second pair of arguments of the DISTANCE function, respectively. Otherwise, the Archive response time can be degraded by several orders of magnitude - please see the Catalogue Combination tutorial for details and useful examples. The TOP 1000 introduces a bias in the output and it is only used to ensure that this query is executed in less than a minute in the Archive.
Target table: gaiadr3.gaia_source, public.hipparcos.
Query:

SELECT TOP 1000 gaia.source_id AS g_source_id, gaia.ra AS g_ra, gaia.dec AS g_dec, gaia.pmra AS g_pmra, gaia.pmdec AS g_pmdec, hip.*, 
DISTANCE(hip.ra, hip.de, gaia.ra, gaia.dec) AS ang_sep
FROM public.hipparcos AS hip
JOIN gaiadr3.gaia_source AS gaia ON
DISTANCE(hip.ra, hip.de, gaia.ra, gaia.dec) < 0.00028

Advanced positional cross-match

Use case: I want to extract selected parameters from the gaiadr3.gaia_source and gaiadr3.synthetic_photometry_gspc tables for a sample contained in a circle of radius 1 deg centred on the globular cluster NGC 5139 (adapted from DR3, Gaia Collaboration, Montegriffo et al. 2022, A&A).
Notes: Epoch propagation is applied to the NGC 5139 coordinates - see the Epoch Propagation and the Proper-motion corrected cross-match for details and useful examples.
Target table: gaiadr3.gaia_source, gaiadr3.synthetic_photometry_gspc
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
DISTANCE(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)),
         ra, dec) < 1.

positional cross-match + PROPER-MOTION propagation

Use case: I want to propagate Gaia epoch J2016.0 positions from a sample of targets (the Pleiades) to an epoch in the past for comparison against a catalogue with epoch J2000 astrometry. Matches are defined when objects are closer than 1 arcsec. The Pleiades are defined, for simplicity, using a simple cone search with a radius of 5 degrees plus a simple circular cut in proper-motion space (with a radius of 6 mas yr-1).
Notes: This query applies the built-in ADQL function "EPOCH_PROP_POS" - see the Epoch Propagation and the Proper-motion corrected cross-match for details and useful examples. The "OFFSET 0" clause increases the query performance, as it instructs the Archive PostgreSQL data base backend to respect the logical order (the subquery is executed first and other conditions applied afterwards - for more details, see this tutorial).
Target table: gaiadr3.gaia_source, external.galex_ais
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 DISTANCE(56.75, 24.12, 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 DISTANCE(COORD1(subquery.propagated_position_vector), COORD2(subquery.propagated_position_vector), galex.raj2000, galex.dej2000) < 1./3600.

retrieve average quantities per healpix level 8 in the galactic anticentrE

Use case: I want to retrieve the number of stars and average quantities in all Healpixels of level 8 inside a rectangular patch on the sky (the Galactic anticentre, adapted from EDR3, Gaia Collaboration, Antoja et al., 2021, A&A 649, A8).
Target table: gaiadr3.gaia_source
Notes: The internal TOP 10000 introduces a bias in the output and it is only used to ensure that this query is executed in less than a minute in the Archive. In this example, the SQL COUNT(*) function is used in combination with the GROUP BY clause.
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)

Use case: I want to construct a dereddened CMD diagram for a subset of stars with high-quality parallaxes (adapted from Gaia Collaboration, Andrae et al., 2018, A&A 616, A8; Fig. 19).
Notes: The random_index is an indexed field included in each gaia_source table. In this example, the constraint in the random_index is only used to ensure that the query is executed in less than 1 minute. 
Target table: gaiadr3.gaia_source
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

Use case: I want to retrieve the high-quality sample of DIBs described in the Gaia DR3 Collaboration paper "The exploration of the interstellar medium with Gaia RVS Diffuse Interstellar Bands" (Gaia Collaboration, Schultheis et al. 2022, A&A).
Notes: This query applies the "LIKE" SQL operator that allows to search for patterns (e.g., strings) in a table. The TOP 1000 introduces a bias in the output and it is only used to ensure that this query is executed in less than a minute in the Archive.
Target tables: gaiadr3.astrophysical_parameters
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)

Query speed booster

Authors: Héctor Cánovas, Jos de Bruijne, and Alcione Mora

 

Writing efficient ADQL queries is not trivial and it is relatively common to introduce small mistakes or inefficiencies that significantly delay the query execution time. Fortunately, there are many tricks that boost the query performance and that can be implemented with very little effort. In most cases, these tricks can be combined to further reduce the computing time, as detailed in this intermediate level tutorial. 

Tutorial content:

  1. Use gaia_source_lite
  2. Don't be lazy: don't SELECT *
  3. Use indexed fields + pre-computed fields + constraints
  4. Optimise the cross-matches
  5. Be careful with TOP + ORDER BY 
  6. Randomise
  7. Divide and conquer

 

1. Use gaia_source_lite

The third Gaia data release, Gaia DR3, includes for the first time a column-wise reduced version of its main catalogue: the "gaiadr3.gaia_source_lite" table. This shortened dataset only contains 51 selected fields from the "gaiadr3.gaia_source" catalogue (click on top of the table name in the tables tree to inspect it), but the number of sources (rows) in both tables remains identical. This reduction makes it possible to substantially improve the performance of various types of ADQL queries - obviously as long as the queries do not involve fields that have been excluded from "gaiadr3.gaia_source_lite". Three representative queries that benefit from targeting this table (instead of the full table "gaiadr3.gaia_source") are listed below.

SELECT COUNT(*)
   FROM gaiadr3.gaia_source_lite
   WHERE phot_g_mean_mag <= 18.25
   AND has_mcmc_msc = 'True'
SELECT ROUND(gs.logg_gspphot) AS logg, COUNT(*)
   FROM gaiadr3.gaia_source_lite AS gs
   WHERE gs.phot_g_mean_mag < 19
   GROUP BY logg ORDER BY logg
SELECT COUNT(gaia.source_id)
FROM gaiadr3.gaia_source_lite AS gaia
INNER JOIN gaiaedr3.tmass_psc_xsc_best_neighbour AS tmass_x USING (source_id)
INNER JOIN gaiadr1.tmass_original_valid AS tmass
   ON tmass.tmass_oid = tmass_x.clean_tmass_psc_xsc_oid
INNER JOIN gaiaedr3.allwise_best_neighbour AS wise_x USING (source_id)
INNER JOIN gaiadr1.allwise_original_valid AS wise USING(allwise_oid)
WHERE gaia_healpix_index(5, gaia.source_id) = 1151

2. Don't be lazy: don't select *

In many situations, users only need a few fields (columns) from a catalogue for their science. But, admittedly, writing a "SELECT *" statement is way more easy than specifying one by one the list of the required fields. However, when doing so, one is forcing the database to read and store the content of all the catalogue fields per explored row. As a result, the query execution time increases significantly when analysing catalogues with many fields, like gaiadr3.gaia_source or gaiadr3.astrophysical_parameters (both contain more than 150 columns). In addition, the associated job size also increases and, in several cases, it can exceed 1 GB. This will contribute to fill up your user space (the job results quota is large but not unlimited, as explained in this FAQ). For example, the following query:

SELECT source_id, ra, dec, pmra, pmdec, parallax
FROM gaiadr3.gaia_source
WHERE 1 = CONTAINS(
   POINT(81.28, -69.78),
   CIRCLE(ra, dec, 1))

is executed in ~3 minutes, and its job size is 120 MB. The same query but selecting all fields (SELECT * ...) roughly takes 2x longer to finish and its job size is 1.1 GB. In short, we recommend to minimise the use of the SELECT * statement to execute faster queries. 

3. Use indexed fields + pre-computed fields + constraints

Imagine one is interested in retrieving very blue stars in Gaia DR3. A naive way of implementing the query could be:

SELECT *
FROM gaiadr3.gaia_source
WHERE phot_bp_mean_mag - phot_rp_mean_mag < -2

This operation is too costly and prevents the request from being completed within the normal time-out limit. However, the same query while taking advantage of the indexed and pre-computed field bp_rp (= phot_bp_mean_mag - phot_rp_mean_mag) finishes in less than 2 minutes. 

SELECT *
FROM gaiadr3.gaia_source
WHERE bp_rp < -2

Examples of other indexed pre-computed fields are parallax_over_error (= parallax / parallax_error), pm (=SQRT(pmra * pmra + pmdec * pmdec)), phot_g_mean_flux_over_error (= phot_g_mean_flux / phot_g_mean_flux_error), and g_rp (= phot_g_mean_mag - phot_rp_mean_mag).

In fact, part of the preparation for each Gaia data release has been the identification, creation, and indexation of the most common derived quantities to be used in queries. The information on a given field being indexed or not can be retrieved from a number of places. Probably the most convenient way is to inspect the table content using the tables tree in the Archive web interface and looking for the table fields (columns) that are highlighted in bold font, as illustrated by Fig. 2 of the Advanced (ADQL) tutorial

Although several pre-computed fields have been added, the quantity of interest may often not be pre-computed. In those cases, adding additional restrictions based on indexed fields might be the difference between a successful and a failed (timing-out) query. For example, the following query applies constraints in the RUWE and parallax_over_error quality indicators:

SELECT phot_g_mean_mag - 5.0 * LOG10(distance_gspphot) + 5.0 AS phot_g_mean_mag_absolute, bp_rp
FROM gaiadr3.gaia_source
WHERE 1 = CONTAINS(
  POINT(266.41683, -29.00781),
  CIRCLE(ra, dec, 2.0))
-- Extra constraints
AND ruwe < 1.4
AND parallax_over_error > 10.0

Excluding these two constraints delays the execution time by a factor of ~7.

4. optimise the cross-matches

Cross-matching catalogues by coordinates is one of the most common operations executed in the Gaia ESA Archive. As explained in the tutorials included in the "Combine with other data" section of the Archive Help pages, an inefficient setting of the query that launches the cross-match can increase the execution time by a factor of 3 or more. The following query retrieves the first 1000 occurrences of a geometric cross-match between Gaia DR3 and the Hipparcos catalogue, and it is executed in roughly 2.5 minutes:

SELECT TOP 1000 gaia.source_id, gaia.ra, gaia.dec, gaia.parallax, hip.*
FROM public.hipparcos AS hip, gaiadr3.gaia_source AS gaia
WHERE
1 = CONTAINS(
    POINT(gaia.ra, gaia.dec),
    CIRCLE(hip.ra, hip.de, 1./3600))

However, this conceptually similar query:

SELECT TOP 1000 gaia.source_id, gaia.ra, gaia.dec, gaia.parallax, hip.*
FROM public.hipparcos AS hip
JOIN gaiadr3.gaia_source AS gaia
  ON 1 = CONTAINS(
    POINT(hip.ra, hip.de),
    CIRCLE(gaia.ra, gaia.dec, 1./3600))

takes only ~5 seconds to be executed (nearly a factor 30 faster!). This is because in the second query:

  • the coordinates of the smaller catalogue being cross-matched (Hipparcos in this example) are the inputs of the POINT function, and
  • an explicit join (JOIN + ON) is used instead of an implicit one (FROM multiple tables + WHERE).

5. Be careful with top + order by

It is a common mistake to assume that relational databases (like the one behind the Gaia ESA Archive) behave like humans when exploring large catalogues. The last query in the previous example is usually executed in ~5 seconds. Let's imagine that we want to sort its output by increasing parallax, in ascending order. Probably, we would try to execute something like

SELECT TOP 1000 gaia.source_id, gaia.ra, gaia.dec, gaia.parallax, hip.*
FROM public.hipparcos AS hip
JOIN gaiadr3.gaia_source AS gaia
   ON 1 = CONTAINS(
      POINT(hip.ra, hip.de),
      CIRCLE(gaia.ra, gaia.dec, 1./3600))
ORDER BY parallax ASC

However, this query times out (so its execution time exceeds 2 hours) due to the combined effect of the TOP + ORDER BY clauses in the query execution plan. This is because in the backend, the database is trying to first sort the entire catalogue by random_index, and then extract the first 1000 elements. If you really want the output to be sorted by a given field, then we recommend to divide the query in two steps as follows. First, execute the query excluding the "ORDER BY <field>". Then, apply the job_upload mechanism (see the Advanced ADQL features tutorial for details) as follows:

SELECT *
FROM job_upload.job<job_id>
ORDER BY <field>

The <job_id> is the unique code associated to each job (e.g., 1654361201479O) that is listed in the Jobs list area (see the Advanced (ADQL) tab tutorial for details). This last query usually finishes in a few seconds.

 

6. Randomise

Sometimes, using a representative random sample is sufficient to answer a scientific question. Naively, users might think to simply use the first (say) 1,000,000 rows of a table using the TOP function but this is extremely dangerous: a Postgres database does not maintain a fixed (or logical) order of the rows so use of the TOP function may lead to seriously biased results. A fixed, pre-assigned random index has therefore been introduced in a column in the main Gaia catalogues. In all cases, the column random_index runs from zero to the total number of elements (rows) minus one (and the total table size in rows can be easily found in the pop-up window of the web interface of the Archive that opens when clicking on top of the table name in the tables tree). For example, the following query shows how to construct an absolute G magnitude vs BP-RP colour diagram from a random sample extracted from Gaia DR3.

SELECT source_id, phot_g_mean_mag + 5.0 * log10(parallax) - 10.0 as g_mag_abs, bp_rp
FROM gaiadr3.gaia_source_lite
WHERE parallax_over_error >= 5
AND phot_bp_mean_flux_over_error > 0
AND phot_rp_mean_flux_over_error > 0
AND SQRT(POWER(2.5/log(10) / phot_bp_mean_flux_over_error, 2) + POWER(2.5/log(10) / phot_rp_mean_flux_over_error, 2)) <= 0.05
AND random_index BETWEEN 0 AND 10000000

This query usually takes ~20 minutes to be executed, and its output contains 937,877 sources. A quick TOPCAT plot is shown in the left panel of Fig. 1, showing many prominent features.

Warning: mathematical operators (POWER, SIN, COS, etc) are time consuming.

 

Alternatively, you may be interested in constructing a histogram to identify global trends. The following (fairly advanced) query shows how to construct the equivalent histogram with bin sizes of 0.1 in BP-RP and G absolute magnitude in two consequetive steps to minimise the query execution time:

SELECT source_id, FLOOR((phot_g_mean_mag+5.0*log10(parallax)-10.0) * 10) AS g_mag_abs_index, FLOOR(bp_rp * 10) as bp_rp_index
FROM gaiadr3.gaia_source
WHERE parallax_over_error >= 5
AND phot_bp_mean_flux_over_error > 0
AND phot_rp_mean_flux_over_error > 0
AND SQRT(POWER(2.5/log(10) / phot_bp_mean_flux_over_error, 2) + POWER(2.5/log(10) / phot_rp_mean_flux_over_error, 2)) <= 0.05
AND random_index BETWEEN 0 AND 10000000

followed by a query that uses the previous one (which in this example has job_id 1654361201479O) as input:

SELECT bp_rp_index / 10 AS bp_rp, g_mag_abs_index / 10 AS g_mag_abs, COUNT(*) AS n
FROM job_upload.job1654361201479O
GROUP BY bp_rp_index, g_mag_abs_index
ORDER BY bp_rp_index, g_mag_abs_index

Execution time ~0.5 minutes; output = 5928 sources. The output of this query is shown in the right panel of Fig. 1 below.

 

Fig. 1. Left: Colour-magnitude diagram produced with the first query in Sect. 6. Right: Histogram produced with the the second and third queries in Sect. 6. The colour bar indicates the number of sources per bin, in log scale. Both plots have been generated with TOPCAT.

 

7. Divide and conquer

Sometimes, a query is simply too large to be executed in one go, and the results of multiple requests need to be combined afterwards. Using the random index for chopping the query into pieces is usually advisable, to control the output size and to equalise the workload for each segment. For example, imagine we want to retrieve points for the colour-magnitude diagram shown in the previous section but for a final sample that contains ~10 million sources. The first query in Sect. 6 above retrieves roughly 1 million sources and is executed in ~20 minutes. Therefore, trying to retrieve 10 million sources in one single query will take ~200 minutes, which is beyond the query execution time limit (as explained in this FAQ). In those cases, we recommend to split the query in several chunks by random index and combine all the outputs afterwards. For example, you can run consecutive instances of the first query in Sect. 6 by simply doing a first execution using "... BETWEEN 0 AND 10000000" followed by a second one using  "... BETWEEN 10000001 AND 20000000", and so on - note the BETWEEN operator includes results at both limits.

Sometimes, we are not interested in scanning a fixed number of entries in the catalogue, but a fraction. Imagine we want to scan the second 1% chunk of the catalogue in search for points for the absolute magnitude vs colour diagram. The random index can still help us when combined with the table size (remember, 1811709771 rows for gaiadr3.gaia_source):

SELECT source_id, phot_g_mean_mag + 5.0 * log10(parallax)- 10.0 as g_mag_abs, bp_rp
FROM gaiadr3.gaia_source
WHERE parallax_over_error >= 5
AND phot_bp_mean_flux_over_error > 0
AND phot_rp_mean_flux_over_error > 0
AND SQRT(POWER(2.5/log(10) / phot_bp_mean_flux_over_error, 2) + POWER(2.5/log(10) / phot_rp_mean_flux_over_error, 2)) <= 0.05
AND random_index BETWEEN 1 * 0.01 * 1811709771 AND 2 * 0.01 * 1811709771 - 1

Please do NOT include conditions on integer division remainders between the random_index and the fraction to explore. That is, for the previous example:

WHERE MOD(random_index, 100) = 1

Even though they are formally equivalent conditions, the data base would take a very long time and eventually fail, because an operation would be carried out on each row, instead of a quick selection on random_index values.

 

Alternatively, for downloads of large fractions of the dataset, we strongly recommend to make use of the bulk download repository that we offer, as explained in this tutorial. Whereas it is reasonable to launch up to a few dozen queries to collect results in chunks, as explained above, users facing the need to launch hundreds or thousands of queries are kindly advised to offload the TAP+ service and use the bulk download repository.

 

 

Ask for longer execution time quota

If all of the above advise has been tried and everything fails, and if this is the only way your science can be done, please send a motivated request to the Gaia Helpdesk with an estimate on how much additional time or user space quota is needed. Include, when possible, the offending queries because they can sometimes be optimised.

ADQL syntax

 

ADQL (Astronomical Data Query Language) is based on SQL (Structured Query Language) which is a language to retrieve information from tables stored in a database (references: ADQL 2.0 and SQL 92). There are a number of tutorials and resources providing a gentle learning curve for newcomers, specially those without previous experience with SQL or relational databases. A small selection is provided below:

 

The ADQL version implemented in the Gaia ESA Archive contains all the ADQL 2.0 functions plus a number of extra functions developed by the Archive team to facilitate the analysis and exploration of the catalogues hosted by the Archive. They are listed below:

  1. ADDITIONAL FUNCTIONS AVAILABLE
  2. ADQL MATHEMATICAL FUNCTIONS
  3. ADQL TRIGONOMETRIC FUNCTIONS
  4. DATA TYPE CASTING ADQL FUNCTIONS
  5. ADQL EXTENSION: CONDITIONAL EXPRESSIONS
  6. DEVIATIONS OF ADQL FUNCTION IMPLEMENTATIONS FROM STANDARD

 

1. Additional functions available

Apart from the standard ADQL functions, the ESA Gaia TAP+ service offers the following functions:

Table Gaia TAP+ ADQL functions

Function

Return Type

Description

Example

Result

STDDEV(expression)

Numeric

Standard deviation function

STDDEV(column)  
GAIA_HEALPIX_INDEX(norder,source_id)

bigint

Returns the healpix index of the given

  • norder [integer]: order of the desired healpix output

  • source_id [bigint]: Gaia source identifier

GAIA_HEALPIX_INDEX(4, 2060294888487267584) 914
GREATEST(v1,v2[,v3,..,vn])

Same as input

Greatest value among the given arguments

GREATEST(10.55, 9.12323, 11.2, 7.8) 11.2
LEAST(v1,v2[,v3,..,vn])

Same as input

Least value among the given arguments

LEAST(10.55, 9.12323, 11.2, 7.8) 7.8
SIGN(x)

Integer

Sign of the argument (-1, 0, +1)

SIGN(-10.55) -1
COALESCE(v1,[]v2,v3,..,vn])

Same as input

Returns first argument that is not null. If all arguments are null, it will return null

COALESCE(NULL, 1, 2) 1
NULLIF(v1,v2)

Same as input

Returns a null value if v1 equals v2, otherwise it returns v1

NULLIF(1, 1) NULL
WIDTH_BUCKET(operand,min,max,buckets)

Integer

Returns the bucket number to which operand would be assigned in a histogram having count equal-width buckets spanning the range min to max; returns 0 or count+1 for an input outside the range

WIDTH_BUCKET(5.35, 0.024, 10.06, 5) 3

 

Also, the ESA Gaia TAP+ service offers functions for array handling:

Table Gaia TAP+ array handling functions

Function

Description

Example

Result

ARRAY_ELEMENT(array_column, index1 [,index2....,indexN])

Returns the requested element inside the array, 1 being the first index value

Given the array [4,5,6]

SELECT array_element(array,2) : 5						
ARRAY_NDIMS(array_column)

Returns the number of dimensions of the array (integer)

Given the array [[1,2,3], [4,5,6]]

SELECT array_ndims(array) : 2
ARRAY_DIMS(array_column)

Returns a text representation of the array's dimensions. Initial and end index for each dimension are given

Given the array [[1,2,3], [4,5,6]]

SELECT array_dims(array) : [1:2][1:3]
ARRAY_LENGTH(array_column, index)

Returns the length of the requested array dimension (integer), 1 being the first index value

Given the array [1,2,3]

SELECT array_length(array,1) : 3
CARDINALITY(array_column)

Returns the total number of elements in the array (integer), or 0 if the array is empty

Given the array [[1,2],[3,4]]

SELECT cardinality(array) : 4

 

2. ADQL mathematical functions

ADQL defines some mathematical functions. Also, other mathematical functions have been defined in our service to support scientific queries.

Table Mathematical ADQL functions

Function

Return Type

Description

Example

Result

ABS(x)

same as input

Absolute value

ABS(-19.4) 19.4
CBRT(x)

numeric

Cube root

CBRT(27.0) 3
DEGREES(x)

numeric

Radians to degrees

DEGREES(0.5) 28.64788975654116
DIV(y,x)

numeric

Integer quotient of y/x

DIV(9,4) 2
EXP(x)

same as input

Exponential

EXP(1.0) 2.718281828459045
FLOOR(x)

same as input

Nearest integer less than or equal to argument

FLOOR(-42.8) -43
LOG(x)

same as input

Natural logarithm

LOG(2.0) 0.6931471805599453
LOG(b,x)

numeric

Logarithm to base b

LOG(2.0, 64.0) 6.0000000000
LOG10(x)

numeric

Base 10 logarithm

LOG10(100.0) 2
MOD(y,x)

same as arguments

Remainder of y/x

MOD(9, 4) 1
PI()

numeric

Pi constant

PI() 3.141592653589793
POWER(x,y)

numeric

x raised to the power of y

POWER(9.0, 3.0) 729
RADIANS(x)

numeric

Degrees to radians

RADIANS(45.0) 0.7853981633974483
RAND(x)

numeric

Random number in the range 0<=x<1

RAND()  
ROUND(x,s)

numeric

Round x to s decimal places, where s is an integer

ROUND(45.2191,2) 45.22
SQRT(x)

numeric

Square root

SQRT(2.0) 1.414213562373095
TRUNCATE(x)

numeric

Truncate towards zero

TRUNCATE(48.8) 48
TRUNCATE(x, s)

numeric

Truncate to s decimal places

TRUNCATE(48.8328, 3) 48.832

 

3. ADQL trigonometric functions

ADQL defines some trigonometric functions considered important for astronomical queries.

Table trigonometric ADQL functions

Function

Return Type

Description

Example

Result

ACOS(x)

Numeric

Inverse cosine or arc cosine

ACOS(0.12582) 1.4446419701843678
ASIN(x)

Numeric

Inverse sine or arc sine

ASIN(0.12582) 0.1261543566105288
ATAN(x)

Numeric

Inverse tangent or arc tangent

ASIN(10.28527) 1.4738745386849255
ATAN2(x,y)

Numeric

Inverse tangent of x/y

ATAN2(10.28527,3.1) 1.2780538751678443
COS(x)

Numeric

Cosine of x

COS(10.28527) -0.6520645009291157
SIN(x)

Numeric

Sine of x

SIN(10.28527) -0.7581634959743598
TAN(x)

Numeric

Tangent of x

TAN(10.28527) 1.1627124232251034
COT(x)

Numeric

Cotangent of x

COT(0.785) 1.000796644031489

 

4. Data type casting ADQL functions

Some User Defined Functions (UDFs) have been implemented in order to allow the casting of values between different data types. The casting functions rely on the underlying PostgreSQL functions so errors received during casting will be, in most of the cases, PosgreSQL errors.

Table Data type casting ADQL functions

Function

Return Type

Description

Example

Result

TO_SMALLINT(x)

int2

Conversion of valid values into smallint. From -2^15 to 2^15-1

TO_SMALLINT(17.4) 17
TO_INTEGER(x)

int4

Conversion of valid values into integer. From -2^31 to 2^31-1

TO_INTEGER(1713112213.4123) 1713112213
TO_BIGINT(x)

int8

Conversion of valid values into bigint. From -2^63 to 2^63-1

TO_BIGINT(1713112213.4123) 1713112213
TO_REAL(x)

float4

Conversion of valid values into real (8 decimal digits precision)

TO_REAL(91323.1231) 91323.125
TO_DOUBLE(x)

float8

Conversion of valid values into double precision (16 decimal digits precision)

TO_DOUBLE(91321213.112212231) 91321213.11221223
TO_CHAR(x)

char

Convert valid values into char data type

TO_CHAR(1123) '1123'
TO_CHAR(v1, v2)

char

Convert valid values into char data type, following the format defined in v2. For a full list of valid format, check Formats

TO_CHAR(-125.8, '999D99S') '125.80-'
TO_BOOLEAN(v1)

boolean

Convert valid values into Boolean data type

TO_BOOLEAN(1) true

 

5. ADQL extension: conditional expressions

Some conditional expressions have been implemented as User Defined Functions.

Note: 'is' and 'in' are reserved ADQL keywords. Conditions including them are possible, but need to be escaped within single quotes for a correct execution. See, for example, the following queries:

SELECT TOP 10 random_index, parallax, if_then_else('parallax is null', 1, 0)
FROM gaiaedr3.gaia_source
ORDER BY random_index

 

SELECT TOP 10 random_index, parallax, if_then_else('random_index in (0, 2, 4, 6, 8)', 1, 0)
FROM gaiaedr3.gaia_source
ORDER BY random_index

 

Table Conditional Expressions ADQL functions

Function

PostgreSQL expression replicated

Example

CASE_CONDITION(default_value, condition1, value1, condition2, value2, ...)
CASE WHEN condition THEN result
[WHEN ...]
[ELSE result]
END
case_condition(astrometric_n_obs_al, dec < -40, -astrometric_n_obs_al, dec > 40, astrometric_n_obs_al / 2)
CASE_EXPRESSION(input_column, default_value, target_value1, value1, target_value2, value2, ...)
CASE a
WHEN 1 THEN 'one'
WHEN 2 THEN 'two'
ELSE 'other'
END
case_expression(astrometric_n_obs_al, 'unknown', 85, 'eighty five', 78, 'seventy eight', 228, 'two hundred and twenty eight', 3, 'three', 4, 'four')
IF_THEN_ELSE(condition, value, [default_value])
CASE WHEN condition THEN result
[ELSE result]
END
if_then_else(dec < 0, astrometric_n_obs_al, -astrometric_n_obs_al)

 

 

6. Deviations of ADQL function implementationS from standard

The ADQL standard is implemented in compliance with IVOA ADQL 2.0 plus the ADQL Errata available on 16 April 2018. Some implementation limitations apply for the following functions:

  1. BOX(coordsys, longitudeCentre, latitudeCentre, longitudeExtent, latitudeExtent): this function is interpreted as follows:
    • As defined in the standard when the arguments are fixed values: a cross at the central position with arms extending, parallel to the coordinate axes at the centre position, for half the respective sizes on either side.
    • When the arguments are variable (e.g. table columns): the sides of the box are line segments or great circles intersecting the arms of the cross in its end points at right angles with the arms.

Epoch Propagation

 

The epoch propagation algorithm used is based on the Hipparcos library released with the Hipparcos catalogue. It supports fully reversible transformation of the six elements vector (positions, parallax and proper motions) and their associated errors and correlation coefficients. For more information on the transformations, please refer to the official ESA 1997 SP-1200 reference document "The Hipparcos and Tycho Catalogues".

Note the radial proper motion uncertainty is computed differently, following Michalik et al. (2014), because of its better handling of small radial velocities or parallaxes (as compared to their uncertainties).

It is also worth noting that Butkevich & Lindegren (2014) introduced more advanced epoch propagation transformations which include light-time effects. The more complicated formulae in Appendix B, which take into account light-time effects, are theoretically ideal but in practice not since since they cannot handle general cases of small (or even negative) parallaxes, as is possible with the "classical" formulae (Appendix C). It should be noted that the current implementation is equivalent to that of 1) Sect. 1.5.5, Vol. 1 of The Hipparcos and Tycho Catalogues (ESA 1997); 2) Michalik et al. (2014), although formulated differently, 3) Appendix C of  Butkevich & Lindegren (2014), and 4) the Gaia EDR3 online documentation.

These routines can be applied both to Gaia and Hipparcos data. Both catalogues use slightly different notation for each field, a comparison table is provided in last section. Most examples are based on Barnard's Star, having the highest proper motion, with Hipparcos and Gaia identifiers HIP 87937 and Gaia DR2 4472832130942575872. They will be based on both catalogues, when possible.
The available ADQL functions described in this section are:

Note zero is used as default value for any missing null input parameter. This is a reasonable choice for most stars because those quantities would be either small (parallax and proper motion) or irrelevant (radial velocity). However, this is not true for very close stars to the Sun, where appropriate values need to be taken from the literature (e.g. average velocity field in the solar neighbourhood).

 

EPOCH_PROP

 

This function returns the full six astrometric parameters (i.e. ra, dec, plx, pmra, pmdec, pmr) propagated from the reference epoch T0 to an arbitrary epoch T1. This function allows two different sets as input:

  • EPOCH_PROP(ra,dec,plx,pmra,pmdec,rv,T0,T1):
    • ra [double]: Right Ascension (deg)
    • dec [double]: Declination (deg)
    • plx [double]: Parallax (mas)
    • pmra [double]: Proper Motion in Right Ascension (mas/yr)
    • pmde [double]: Proper Motion in Declination (mas/yr)
    • rv [double]: Radial Velocity (km/s)
    • T0 [double]: Reference Julian Epoch (yr)
    • T1 [double]: Final Julian Epoch (yr)
     

    The output of this function is an array (double[6]epoch_prop) where:

    • epoch prop[1]: ra, Right Ascension (deg)
    • epoch prop[2]: dec, Declination (deg)
    • epoch prop[3]: plx, Parallax (mas)
    • epoch prop[4]: pmra, Proper Motion in Right Ascension (mas/yr)
    • epoch prop[5]: pmde, Proper Motion in Declination (mas/yr)
    • epoch prop[6]: pmr, Radial Proper Motion (mas/yr)
     
    SELECT EPOCH_PROP(ra, dec, parallax, pmra, pmdec, radial_velocity, 2015.5, 2000)
    FROM gaiadr2.gaia_source
    WHERE source_id = 4472832130942575872
    
    SELECT EPOCH_PROP(ra,de,plx,pmra,pmde,-111.0,1991.25,2000)
    FROM public.hipparcos
    WHERE hip=87937
    
     

    The output radial proper motion is set to the input value in case of null or zero input parallax. The usual transformations are applied otherwise. Note this quantity is defined as pmr (zeta, mu_r) = radial_velocity * parallax/ A, as explained in the Hipparcos reference documentation, Sec. 1.5.5. It is thus different and has different units than the radial velocity. Great care is needed to interpret the results in case of very small but non-zero parallax (below microarcseconds).

     

    The output radial proper motion units are mas/yr, and it is left to the user to decide when the transformation into radial velocities in km/s is applicable, taking into account this transformation is proportional to the inverse of the parallax. To do so a dedicated function RADIAL_VELOCITY is provided as described in the corresponding section.

     

    To access the individual output array elements, ARRAY_ELEMENT can be used, indicating the index of the particular element as follows:

    SELECT array_element(a0,1) as ra_prop,
           array_element(a0,2) as dec_prop,
           array_element(a0,3) as parallax_prop,
           array_element(a0,4) as pmra_prop,
           array_element(a0,5) as pmdec_prop,
           array_element(a0,6) as rv_prop
    FROM
       (
    	SELECT top 50 ra,dec,parallax,pmra,pmdec,radial_velocity,
    	epoch_prop(ra,dec,parallax,pmra,pmdec,radial_velocity,2015.5,2000) as a0
    	FROM gaiadr2.gaia_source
    	WHERE parallax IS NOT NULL
    		AND radial_velocity IS NOT NULL
    	ORDER BY random_index ASC
    	) as p
    
    SELECT ARRAY_ELEMENT(
    	(EPOCH_PROP(ra,de,plx,pmra,pmde,0,1991.25,2000))
    	,1) AS ra
    FROM public.hipparcos
    WHERE hip=87937 
     

    Note the first index in a PostgreSQL array is 1 by default.

  • EPOCH_PROP(double[6]astrometric_parameters,T0,T1):
    • double[6]astrometric parameters: astrometric parameters array, where:
    • astrometric parameters[1]: ra, Right Ascension (deg)
    • astrometric parameters[2]: dec, Declination (deg)
    • astrometric parameters[3]: plx, Parallax (mas)
    • astrometric parameters[4]: pmra, Proper Motion in Right Ascension (mas/yr)
    • astrometric parameters[5]: pmde, Proper Motion in Declination (mas/yr)
    • astrometric parameters[6]: rv, Radial Velocity(km/s)
    • T0 [double]: Reference Julian Epoch (yr)
    • T1 [double]: Final Julian Epoch (yr)

    The output of this function is an array (double[6]epoch_prop) where:

    • epoch prop[1]: ra, Right Ascension (deg)
    • epoch prop[2]: dec, Declination (deg)
    • epoch prop[3]: plx, Parallax (mas)
    • epoch prop[4]: pmra, Proper Motion in Right Ascension (mas/yr)
    • epoch prop[5]: pmde, Proper Motion in Declination (mas/yr)
    • epoch prop[6]: pmr, Radial Proper Motion (mas/yr)

    The astrometric parameters input array can be obtained via the ASTROMETRIC_PARAMETERS ADQL function, described in this section.

    SELECT EPOCH_PROP(
      ASTROMETRIC_PARAMETERS(ra, dec, parallax, pmra, pmdec, radial_velocity),
      2015.5, 2000)
    FROM gaiadr2.gaia_source
    WHERE source_id = 4472832130942575872
    
    SELECT EPOCH_PROP(
    	ASTROMETRIC_PARAMETERS(ra,de,plx,pmra,pmde,-111.0),
    	1991.25,2000)
    FROM public.hipparcos
    WHERE hip=87937
    

 

EPOCH_PROP_POS

 

EPOCH_PROP_POS(ra,dec,plx,pmra,pmdec,rv,T0,T1): Returns a string with the propagated position (ra, dec) in radians from the reference epoch T0 to an arbitrary epoch T1, where:

  • ra [double]: Right Ascension (deg)
  • dec [double]: Declination (deg)
  • plx [double]: Parallax (mas)
  • pmra [double]: Proper Motion in Right Ascension (mas/yr)
  • pmde [double]: Proper Motion in Declination (mas/yr)
  • rv [double]: Radial Velocity (km/s)
  • T0 [double]: Reference Julian Epoch (yr)
  • T1 [double]: Final Julian Epoch (yr)

 

This function was originally called EPOCH_PROP in Archive version 1.3.0, but has been renamed to explicitly state only the positions are propagated, and not the full six component vector also including parallax, proper motions and radial velocity.

To retrieve only the Right Ascension or the Declination values in degrees from the output position of the EPOCH_PROP function it would require the use of the ADQL functions COORD1 and COORD2 respectively:

SELECT COORD1(
  EPOCH_PROP_POS(ra, dec, parallax, pmra, pmdec, radial_velocity, 2015.5, 2000))
FROM gaiadr2.gaia_source
WHERE source_id = 4472832130942575872
SELECT COORD2(
  EPOCH_PROP_POS(ra, dec, parallax, pmra, pmdec, radial_velocity, 2015.5, 2000))
FROM gaiadr2.gaia_source
WHERE source_id = 4472832130942575872
SELECT COORD1(
	EPOCH_PROP_POS(ra, de, plx, pmra, pmde, -111.0, 1991.25, 2000))
FROM public.hipparcos
WHERE hip=87937
SELECT COORD2(
	EPOCH_PROP_POS(ra, de, plx, pmra, pmde, -111.0, 1991.25, 2000))
FROM public.hipparcos
 WHERE hip=87937

 

It is possible to use this function as input for other ADQL geometrical functions. For example, to compute the distance between the propagated position for a specific source and another random point:

SELECT DISTANCE(
	EPOCH_PROP_POS(ra, dec, parallax, pmra, pmdec, radial_velocity, 2015.5, 2000),
	POINT(266.41683, -29.00781)) AS dist
FROM gaiadr2.gaia_source
WHERE source_id = 4472832130942575872
SELECT DISTANCE(
	EPOCH_PROP_POS(ra, de, plx, pmra, pmde, -111.0, 1991.25, 2000),
	POINT(266.41683, -29.00781)) AS dist
FROM public.hipparcos
WHERE hip=87937

 

ASTROMETRIC_PARAMETERS

 

ASTROMETRIC_PARAMETERS(ra,de,plx,pmra,pmde,rv): Returns the full six input parameters in the correct format needed by the EPOCH_PROP routine, where:

  • ra [double]: Right Ascension (deg)
  • dec [double]: Declination (deg)
  • plx [double]: Parallax (mas)
  • pmra [double]: Proper Motion in Right Ascension (mas/yr)
  • pmde [double]: Proper Motion in Declination (mas/yr)
  • rv [double]: Radial Velocity (km/s)

The output of this function is an array (double[6]astrometric_parameters) where:

  • astrometric parameters[1]: ra(deg)
  • astrometric parameters[2]: dec(deg)
  • astrometric parameters[3]: plx(mas)
  • astrometric parameters[4]: pmra(mas/yr)
  • astrometric parameters[5]: pmde(mas/yr)
  • astrometric parameters[6]: pmr(mas/yr)
SELECT ASTROMETRIC_PARAMETERS(ra, dec, parallax, pmra, pmdec, radial_velocity)
FROM gaiadr2.gaia_source
WHERE source_id = 4472832130942575872
SELECT ASTROMETRIC_PARAMETERS(ra,de,plx,pmra,pmde,-111.0)
FROM public.hipparcos
WHERE hip=87937

 

EPOCH_PROP_ERROR

 

Returns the errors and correlation coefficients of the propagated position from the reference epoch T0 to the arbitrary epoch T1. The two index correlation matrix can be constructed from those data. Errors and correlation coefficients are used instead for convenience and consistency with the Gaia and Hipparcos catalogues. An exception is raised for input stars without a five parameters astrometric solution, that is lacking parallaxes and proper motions. There are two accepted sets of input for this function:

Note the first index in a PostgreSQL array is 1 by default.

  • EPOCH_PROP_ERROR (ra,dec,plx,pmra,pmdec,rv,e_ra,e_dec,e_plx,e_pmra,e_pmdec,e_vr,de_ra,plx_ra,pmra_ra,pmde_ra,plx_de,pmra_de,pmde_de, pmra_plx, pmde_plx, pmde_pmra, T0,T1):  

    This transformation is only applicable for radial velocities independently determined from the astrometry, such as those obtained with a spectrometer. It is not applicable for the back-transformation of data already propagated to another epoch. The output of this function is an array with the propagated 6 errors and 15 correlation coefficients where:

    SELECT EPOCH_PROP_ERROR(
    	ra, dec, parallax, pmra, pmdec, radial_velocity,
    	ra_error, dec_error, parallax_error, pmra_error, pmdec_error, radial_velocity_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,
    	2015.5, 1900)
    FROM gaiadr2.gaia_source
    WHERE source_id = 4472832130942575872
    
    SELECT EPOCH_PROP_ERROR(
    	ra, de, plx, pmra, pmde, -111.0,
    	e_radeg, e_dedeg, e_plx, e_pmra, e_pmde, 0.5,
    	dera, plxra, pmrara, pmdera, plxde, pmrade, pmdede, pmraplx, pmdeplx, pmdepmra,
    	1991.25, 2000.0)
    FROM public.hipparcos
    WHERE hip=87937
    

    For example, in order to retrieve the propagated uncertainty of the declination(e_dec) that corresponds to the second element of the array:

    SELECT ARRAY_ELEMENT(
    		(EPOCH_PROP_ERROR(
      			ra, dec, parallax, pmra, pmdec, radial_velocity,
      			ra_error, dec_error, parallax_error, pmra_error, pmdec_error, radial_velocity_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,
      			2015.5, 1900))
      		, 2)
    FROM gaiadr2.gaia_source
    WHERE source_id = 4472832130942575872
    
    SELECT ARRAY_ELEMENT(
    	(EPOCH_PROP_ERROR(
    		ra, de, plx, pmra, pmde, -111.0,
    		e_radeg, e_dedeg, e_plx, e_pmra, e_pmde, 0.5,
    		dera, plxra, pmrara, pmdera, plxde, pmrade, pmdede, pmraplx, pmdeplx, pmdepmra,
    		1991.25, 1900))
    	,2)
    FROM public.hipparcos
    WHERE hip=87937
    
    • ra [double]: Right Ascension (deg)
    • dec [double]: Declination (deg)
    • plx [double]: Parallax (mas)
    • pmra [double]: Proper Motion in Right Ascension (mas/yr)
    • pmde [double]: Proper Motion in Declination (mas/yr)
    • rv [double]: Radial Velocity (km/s)
    • e_ra [double]: Error in Right Ascension (mas)
    • e_dec [double]:Error in Declination (mas)
    • e_plx [double]: Error in Parallax (mas)
    • e_pmra [double]: Error in Proper Motion in RA (mas/yr)
    • e_pmdec [double]: Error in Proper Motion in Dec (mas/yr)
    • e_vr [double]: Error in Radial Velocity (km/s)
    • de_ra [double]: Correlation Coefficient, RA/Dec
    • plx_ra [double]: Correlation Coefficient, Parallax/RA
    • pmra_ra [double]: Correlation Coefficient, Proper Motion in RA/RA
    • pmde_ra [double]: Correlation Coefficient, Proper Motion in Dec/RA
    • plx_de [double]: Correlation Coefficient, Parallax/Dec
    • pmra_de [double]: Correlation Coefficient, Proper Motion in RA/Dec
    • pmde_de [double]: Correlation Coefficient, Proper Motion in Dec/Dec
    • pmra_plx [double]: Correlation Coefficient, Proper Motion in RA/Parallax
    • pmde_plx [double]: Correlation Coefficient, Proper Motion in Dec/Parallax
    • pmde_pmra [double]:Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
    • astrometric parameter_error[1]: e_ra, Error in Right Ascension (mas)
    • astrometric parameter_error[2]: e_dec, Error in Declination (mas)
    • astrometric parameter_error[3]: e_plx, Error in Parallax (mas)
    • astrometric parameter_error[4]: e_pmra, Error in Proper Motion in RA (mas/yr)
    • astrometric parameter_error[5]: e_pmde, Error in Proper Motion in Dec(mas/yr)
    • astrometric parameter_error[6]: e_pmr, Error in Radial Proper Motion (mas/yr)
    • astrometric parameter_error[7]: de_ra, Correlation Coefficient, Dec/RA
    • astrometric parameter_error[8]: plx_ra, Correlation Coefficient, Parallax/RA
    • astrometric parameter_error[9]: pmra_ra, Correlation Coefficient, Proper Motion in RA/RA
    • astrometric parameter_error[10]: pmde_ra, Correlation Coefficient, Proper Motion in Dec/RA
    • astrometric parameter_error[11]: pmr_ra, Correlation Coefficient, Radial Proper Motion/RA
    • astrometric parameter_error[12]: plx_de, Correlation Coefficient, Parallax/Dec
    • astrometric parameter_error[13]: pmra_de, Correlation Coefficient, Proper Motion in RA/Dec
    • astrometric parameter_error[14]: pmde_de, Correlation Coefficient, Proper Motion in Dec/Dec
    • astrometric parameter_error[15]: pmr_de, Correlation Coefficient, Radial Proper Motion/Dec
    • astrometric parameter_error[16]: pmra_plx, Correlation Coefficient, Proper Motion in RA/Parallax
    • astrometric parameter_error[17]: pmde_plx, Correlation Coefficient, Proper Motion in Dec/Parallax
    • astrometric parameter_error[18]: pmr_plx, Correlation Coefficient, Radial Proper Motion/Parallax
    • astrometric parameter_error[19]: pmde_pmra, Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
    • astrometric parameter_error[20]: pmr_pmra, Correlation Coefficient, Radial Proper Motion/Proper Motion in RA
    • astrometric parameter_error[21]: pmr_pmde, Correlation Coefficient, Radial Proper Motion/Proper Motion in Dec
  • EPOCH_PROP_ERROR(double[6] astrometric_parameters, double[21] astrometric_parameter_error,T0,T1):  

    This function is applicable in all cases, and makes no assumption on the correlation between the different parameters. The output of this function is an array with all the uncertainties and correlation coefficients, where:

    SELECT EPOCH_PROP_ERROR(
    	ASTROMETRIC_PARAMETERS(ra, dec, parallax, pmra, pmdec, radial_velocity),
    	ASTROMETRIC_PARAMETER_ERROR(ra_error, dec_error, parallax_error, pmra_error, 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, parallax,radial_velocity,radial_velocity_error),2015.5,1900)
    FROM gaiadr2.gaia_source
    WHERE source_id = 4472832130942575872
    
    SELECT EPOCH_PROP_ERROR(
    	ASTROMETRIC_PARAMETERS(ra,de,plx,pmra,pmde,-111.0),
    	ASTROMETRIC_PARAMETER_ERROR(e_radeg, e_dedeg, e_plx, e_pmra, e_pmde, dera, plxra, pmrara, pmdera, plxde, pmrade, pmdede, pmraplx, pmdeplx, pmdepmra, plx,-111.0,0.5),1991.25,1900)
    FROM public.hipparcos
    WHERE hip=87937
    
     
    • astrometric_parameters[6] (output from ASTROMETRIC_PARAMETERS ADQL function):
      • astrometric parameters[1]: ra, Right Ascension (deg)
      • astrometric parameters[2]: dec, Declination (deg)
      • astrometric parameters[3]: plx, Parallax (mas)
      • astrometric parameters[4]: pmra, Proper Motion in Right Ascension (mas/yr)
      • astrometric parameters[5]: pmde, Proper Motion in Declination (mas/yr)
      • astrometric parameters[6]: pmr, Radial Proper Motion (mas/yr)
    • astrometric_parameter_error[21] (output from ASTROMETRIC_PARAMETER_ERROR ADQL function):
      • astrometric parameter_error[1]: e_ra, Error in Right Ascension (mas)
      • astrometric parameter_error[2]: e_dec, Error in Declination (mas)
      • astrometric parameter_error[3]: e_plx, Error in Parallax (mas)
      • astrometric parameter_error[4]: e_pmra, Error in Proper Motion in RA (mas/yr)
      • astrometric parameter_error[5]: e_pmde, Error in Proper Motion in Dec(mas/yr)
      • astrometric parameter_error[6]: e_pmr, Error in Radial Proper Motion (mas/yr)
      • astrometric parameter_error[7]: de_ra, Correlation Coefficient, Dec/RA
      • astrometric parameter_error[8]: plx_ra, Correlation Coefficient, Parallax/RA
      • astrometric parameter_error[9]: pmra_ra, Correlation Coefficient, Proper Motion in RA/RA
      • astrometric parameter_error[10]: pmde_ra, Correlation Coefficient, Proper Motion in Dec/RA
      • astrometric parameter_error[11]: pmr_ra, Correlation Coefficient, Radial Proper Motion/RA
      • astrometric parameter_error[12]: plx_de, Correlation Coefficient, Parallax/Dec
      • astrometric parameter_error[13]: pmra_de, Correlation Coefficient, Proper Motion in RA/Dec
      • astrometric parameter_error[14]: pmde_de, Correlation Coefficient, Proper Motion in Dec/Dec
      • astrometric parameter_error[15]: pmr_de, Correlation Coefficient, Radial Proper Motion/Dec
      • astrometric parameter_error[16]: pmra_plx, Correlation Coefficient, Proper Motion in RA/Parallax
      • astrometric parameter_error[17]: pmde_plx, Correlation Coefficient, Proper Motion in Dec/Parallax
      • astrometric parameter_error[18]: pmr_plx, Correlation Coefficient, Radial Proper Motion/Parallax
      • astrometric parameter_error[19]: pmde_pmra, Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
      • astrometric parameter_error[20]: pmr_pmra, Correlation Coefficient, Radial Proper Motion/Proper Motion in RA
      • astrometric parameter_error[21]: pmr_pmde, Correlation Coefficient, Radial Proper Motion/Proper Motion in Dec
    • T0: [double]: Reference Julian Epoch (yr)
    • T1: [double]: Final Julian Epoch (yr)
    • astrometric parameter_error[1]: e_ra, Error in Right Ascension (mas)
    • astrometric parameter_error[2]: e_dec, Error in Declination (mas)
    • astrometric parameter_error[3]: e_plx, Error in Parallax (mas)
    • astrometric parameter_error[4]: e_pmra, Error in Proper Motion in RA (mas/yr)
    • astrometric parameter_error[5]: e_pmde, Error in Proper Motion in Dec(mas/yr)
    • astrometric parameter_error[6]: e_pmr, Error in Radial Proper Motion (mas/yr)
    • astrometric parameter_error[7]: de_ra, Correlation Coefficient, Dec/RA
    • astrometric parameter_error[8]: plx_ra, Correlation Coefficient, Parallax/RA
    • astrometric parameter_error[9]: pmra_ra, Correlation Coefficient, Proper Motion in RA/RA
    • astrometric parameter_error[10]: pmde_ra, Correlation Coefficient, Proper Motion in Dec/RA
    • astrometric parameter_error[11]: pmr_ra, Correlation Coefficient, Radial Proper Motion/RA
    • astrometric parameter_error[12]: plx_de, Correlation Coefficient, Parallax/Dec
    • astrometric parameter_error[13]: pmra_de, Correlation Coefficient, Proper Motion in RA/Dec
    • astrometric parameter_error[14]: pmde_de, Correlation Coefficient, Proper Motion in Dec/Dec
    • astrometric parameter_error[15]: pmr_de, Correlation Coefficient, Radial Proper Motion/Dec
    • astrometric parameter_error[16]: pmra_plx, Correlation Coefficient, Proper Motion in RA/Parallax
    • astrometric parameter_error[17]: pmde_plx, Correlation Coefficient, Proper Motion in Dec/Parallax
    • astrometric parameter_error[18]: pmr_plx, Correlation Coefficient, Radial Proper Motion/Parallax
    • astrometric parameter_error[19]: pmde_pmra, Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
    • astrometric parameter_error[20]: pmr_pmra, Correlation Coefficient, Radial Proper Motion/Proper Motion in RA
    • astrometric parameter_error[21]: pmr_pmde, Correlation Coefficient, Radial Proper Motion/Proper Motion in Dec
 

ASTROMETRIC_PARAMETER_ERROR

 

ASTROMETRIC_PARAMETER_ERROR(e_ra, e_dec, e_plx, e_pmra, e_pmde, dera, plxra, pmrara, pmdera, plxde, pmrade, pmdede, pmraplx, pmdeplx, pmdepmra, plx, rv,e_rv): Returns the initial uncertainties and correlation coefficients where:

The output of this function is an array (double[21]astrometric_parameters_error) with all the uncertainties and correlation coefficients, where:

SELECT ASTROMETRIC_PARAMETER_ERROR(
	ra_error, dec_error, parallax_error, pmra_error, 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,
	parallax, radial_velocity, radial_velocity_error)
FROM gaiadr2.gaia_source
WHERE source_id = 4472832130942575872
SELECT ASTROMETRIC_PARAMETER_ERROR(
	e_radeg, e_dedeg, e_plx, e_pmra, e_pmde,
	dera, plxra, pmrara, pmdera, plxde, pmrade, pmdede, pmraplx, pmdeplx, pmdepmra,
	plx, -111.0, 0.5)
FROM public.hipparcos
WHERE hip=87937

 

RADIAL_VELOCITY

 

RADIAL_VELOCITY(radial_velocity,parallax): It provides the transformation of the radial proper motion (mas/yr) into a radial velocity (km/s), where:

 

This transformation is required for computing the output radial proper motion returned from the EPOCH_PROP routines to radial velocity in km/s where: radial_velocity[km/s] = radial_pm[mas/yr] Av / parallax[mas]

It should be noted that this transformation is inversely proportional to the input parallax, so special care should be taken for close to zero values in this parameter.

SELECT array_element(a0,1) as ra_prop,
       array_element(a0,2) as dec_prop,
       array_element(a0,3) as parallax_prop,
       array_element(a0,4) as pmra_prop,
       array_element(a0,5) as pmdec_prop,
       radial_velocity(array_element(a0,6),parallax) as rv_prop
FROM
   (SELECT top 50
	       ra,dec,parallax,pmra,pmdec,radial_velocity,
	       epoch_prop(ra,dec,parallax,pmra,pmdec,radial_velocity,
                      2015.5,2000) as a0
	FROM gaiadr2.gaia_source
    WHERE parallax IS NOT NULL
	  AND radial_velocity IS NOT NULL
	ORDER BY random_index ASC) as p
  • e_ra [double]: Error in Right Ascension (mas)
  • e_dec [double]:Error in Declination (mas)
  • e_plx [double]: Error in Parallax (mas)
  • e_pmra [double]: Error in Proper Motion in RA (mas/yr)
  • e_pmdec [double]: Error in Proper Motion in Dec (mas/yr)
  • e_vr [double]: Error in Radial Velocity (km/s)
  • de_ra [double]: Correlation Coefficient, RA/Dec
  • plx_ra [double]: Correlation Coefficient, Parallax/RA
  • pmra_ra [double]: Correlation Coefficient, Proper Motion in RA/RA
  • pmde_ra [double]: Correlation Coefficient, Proper Motion in Dec/RA
  • plx_de [double]: Correlation Coefficient, Parallax/Dec
  • pmra_de [double]: Correlation Coefficient, Proper Motion in RA/Dec
  • pmde_de [double]: Correlation Coefficient, Proper Motion in Dec/Dec
  • pmra_plx [double]: Correlation Coefficient, Proper Motion in RA/Parallax
  • pmde_plx [double]: Correlation Coefficient, Proper Motion in Dec/Parallax
  • pmde_pmra [double]:Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
  • plx [double]: Parallax (mas)
  • rv [double]: Radial Velocity (km/s)
  • e_vr [double]: Error in Radial Velocity (km/s)
  • astrometric parameter_error[1]: e_ra, Error in Right Ascension (mas)
  • astrometric parameter_error[2]: e_dec, Error in Declination (mas)
  • astrometric parameter_error[3]: e_plx, Error in Parallax (mas)
  • astrometric parameter_error[4]: e_pmra, Error in Proper Motion in RA (mas/yr)
  • astrometric parameter_error[5]: e_pmde, Error in Proper Motion in Dec(mas/yr)
  • astrometric parameter_error[6]: e_pmr, Error in Radial Proper Motion (mas/yr)
  • astrometric parameter_error[7]: de_ra, Correlation Coefficient, Dec/RA
  • astrometric parameter_error[8]: plx_ra, Correlation Coefficient, Parallax/RA
  • astrometric parameter_error[9]: pmra_ra, Correlation Coefficient, Proper Motion in RA/RA
  • astrometric parameter_error[10]: pmde_ra, Correlation Coefficient, Proper Motion in Dec/RA
  • astrometric parameter_error[11]: pmr_ra, Correlation Coefficient, Radial Proper Motion/RA
  • astrometric parameter_error[12]: plx_de, Correlation Coefficient, Parallax/Dec
  • astrometric parameter_error[13]: pmra_de, Correlation Coefficient, Proper Motion in RA/Dec
  • astrometric parameter_error[14]: pmde_de, Correlation Coefficient, Proper Motion in Dec/Dec
  • astrometric parameter_error[15]: pmr_de, Correlation Coefficient, Radial Proper Motion/Dec
  • astrometric parameter_error[16]: pmra_plx, Correlation Coefficient, Proper Motion in RA/Parallax
  • astrometric parameter_error[17]: pmde_plx, Correlation Coefficient, Proper Motion in Dec/Parallax
  • astrometric parameter_error[18]: pmr_plx, Correlation Coefficient, Radial Proper Motion/Parallax
  • astrometric parameter_error[18]: pmde_pmra, Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA
  • astrometric parameter_error[20]: pmr_pmra, Correlation Coefficient, Radial Proper Motion/Proper Motion in RA
  • astrometric parameter_error[21]: pmr_pmde, Correlation Coefficient, Radial Proper Motion/Proper Motion in Dec
  • radial_pm [mas/yr]: Radial proper motion
  • parallax [mas]: Parallax
 
Astrometric parameters in Gaia and Hipparcos

The following table provides a list of all astrometric parameters in the Gaia and Hipparcos catalogues. The corresponding tables are gaiadr2.gaia_source and public.hipparcos or public.hipparcos_newreduction.

 

Gaia

Hipparcos

Parameter

Unit

ra

ra

Right Ascension

deg

dec

de

Declination

deg

parallax

plx

Parallax

mas

pmra

pmra

Proper Motion in Right Ascension

mas/yr

pmdec

pmde

Proper Motion in Declination

mas/yr

pmdec

pmde

Proper Motion in Declination

mas/yr

radial_velocity

 

Radial velocity

km/s

ra_error

e_radeg

Error in Right Ascension

mas

dec_error

e_dedeg

Error in Declination

mas

parallax_error

e_plx

Error in Parallax

mas

pmra_error

e_pmra

Error in Proper Motion in RA

mas/yr

pmdec_error

e_pmde

Error in Proper Motion in Dec

mas/yr

radial_velocity_error

 

Error in Radial velocity

km/s

ra_dec_corr

dera

Correlation Coefficient, Dec/RA

 

ra_parallax_corr

plxra

Correlation Coefficient, Parallax/RA

 

ra_pmra_corr

pmrara

Correlation Coefficient, Proper Motion in RA/RA

 

ra_pmdec_corr

pmdera

Correlation Coefficient, Proper Motion in Dec/RA

 

dec_parallax_corr

plxde

Correlation Coefficient, Parallax/Dec

 

dec_pmra_corr

pmrade

Correlation Coefficient, Proper Motion in RA/RA

 

dec_pmdec_corr

pmdede

Correlation Coefficient, Proper Motion in Dec/Dec

 

dec_pmdec_corr

pmdede

Correlation Coefficient, Proper Motion in Dec/Dec

 

parallax_pmra_corr

pmraplx

Correlation Coefficient, Proper Motion in RA/Parallax

 

parallax_pmdec_corr

pmdeplx

Correlation Coefficient, Proper Motion in Dec/Parallax

 

pmra_pmdec_corr

pmdepmra

Correlation Coefficient, Proper Motion in Dec/Proper Motion in RA

 

Gaia Collaboration queries

 

This page contains a number of ADQL queries extracted from the DPAC documentation, including DPAC articles.

Content:

  1. Integrated extragalactic tables
  2. Gaia (E)DR3 selected queries
  3. Gaia DR2 selected queries
  4. Gaia DR1 selected queries

 

Integrated extragalactic tables

The integrated extragalactic tables (see Chapter 12 in the Gaia DR3 documentation) are composed of sources stemming from various DPAC processing modules, with overlaps between the corrresponding sub-samples. We indicate here how these can be selected based on ADQL queries on the qso_candidates and galaxy_candidates tables.

 

DSC and DSC-joint samples

The DSC sources used in the samples described in this chapter can be obtained with the following queries in the respective qso_candidates and galaxy_candidates tables:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE MOD(source_selection_flags,POWER(2,6+1))-MOD(source_selection_flags,POWER(2,6)) != 0
SELECT source_id FROM gaiadr3.galaxy_candidates
WHERE MOD(source_selection_flags,POWER(2,3+1))-MOD(source_selection_flags,POWER(2,3)) != 0

Similarly, the following provides the sources from the DSC-Joint samples of quasars and galaxies:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE classlabel_dsc_joint = 'quasar'
SELECT source_id FROM gaiadr3.galaxy_candidates
WHERE classlabel_dsc_joint = 'galaxy'

Vari-Classification samples

The respective 'AGN' and 'GALAXY' samples from the Vari-Classification module are selected as follows:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE vari_best_class_name = 'AGN'
SELECT source_id FROM gaiadr3.galaxy_candidates
WHERE vari_best_class_name = 'GALAXY'

Vari-AGN sample

The Vari-AGN sample is simply obtained by:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE qso_variability IS NOT NULL

Surface brightness samples

The sources considered eligible from the surface brightness samples are selected as:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE host_galaxy_flag < 6
SELECT source_id FROM gaiadr3.galaxy_candidates
WHERE radius_sersic IS NOT NULL

QSOC sample

The sources considered eligible from the QSOC processing are selected as:

SELECT source_id FROM gaiadr3.qso_candidates
WHERE source_selection_flags >= 128

UGC sample

The sources considered eligible from the UGC processing are selected as:

SELECT source_id FROM gaiadr3.galaxy_candidates
WHERE redshift_ugc IS NOT NULL

OA samples

Although not used to define which sources should enter into the integrated extragalactic tables, the OA sources used in this chapter can be obtained by simply selecting those sources where field classlabel_oa is not null.

 

Gaia (E)DR3 selected queries

The celestial reference frame  (Gaia-CRF3)

Use case: I want to retrieve the astrometric data for all the 1,614,173 Gaia-CRF3 sources (Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3)).
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT agn.source_name_in_catalogue, agn.catalogue_name, edr3.*
FROM gaiaedr3.agn_cross_id AS agn
INNER JOIN gaiaedr3.gaia_source AS edr3 USING (source_id)

The celestial reference frame (Gaia-CRF3 5: parameter solutions)

Use case: I want to retrieve the astrometric data for the Gaia-CRF3 sources with five-parameter astrometric solutions (Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3)).
Notes: Replacing '31' by '95' one gets the data for the 398,231 sources with six-parameter solutions.
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT agn.source_name_in_catalogue, agn.catalogue_name, edr3.*
FROM gaiaedr3.agn_cross_id AS agn
INNER JOIN gaiaedr3.gaia_source AS edr3 USING (source_id)
WHERE edr3.astrometric_params_solved = 31

The celestial reference frame (ORIENTATION sources)

Use case: I want to retrieve the data for the 2007 sources that were used to fix the orientation of Gaia-CRF3 (Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3)).
Notes: The complexity of the query results from the fact that gaiaedr3.frame_rotator_source can contain sources that are NOT in gaiaedr3.agn_cross_id.
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT sub.source_name_in_catalogue, sub.catalogue_name, edr3.*
FROM (
SELECT frame.source_id, agn.source_name_in_catalogue, agn.catalogue_name
FROM gaiaedr3.frame_rotator_source AS frame
LEFT OUTER JOIN gaiaedr3.agn_cross_id AS agn USING (source_id)
WHERE frame.used_for_reference_frame_orientation = 'True'
) AS sub
INNER JOIN gaiaedr3.gaia_source AS edr3 USING (source_id)

The celestial reference frame  (SPIN sources)

Use case: I want to retrieve the data for the 428,034 sources used to define the rotation (spin) of Gaia-CRF3 (Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3)).
Notes: The complexity of the query results from the fact that gaiaedr3.frame_rotator_source can contain sources that are NOT in gaiaedr3.agn_cross_id.
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT sub.source_name_in_catalogue, sub.catalogue_name, edr3.*
FROM (
SELECT frame.source_id, agn.source_name_in_catalogue, agn.catalogue_name
FROM gaiaedr3.frame_rotator_source AS frame
LEFT OUTER JOIN gaiaedr3.agn_cross_id AS agn USING (source_id)
WHERE frame.used_for_reference_frame_spin = 'True'
) AS sub
INNER JOIN gaiaedr3.gaia_source AS edr3 USING (source_id)

The celestial reference frame (catalogue selection)

Use case: I want to retrieve the Gaia-CRF3 sources from a given external catalog: "$$$" should be replaced by the catalogue code given in Table 1 in Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3).
Notes: This query involves EDR3 and DR3 tables.
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT xm.$$$_name, edr3.*
FROM gaiadr3.gaia_source AS edr3
INNER JOIN gaiadr3.gaia_crf3_xm AS xm USING (source_id)
WHERE xm.$$$ = 'True'

The celestial reference frame (confusion sources)

Use case: I want to retrieve the data for the 30,723,995 confusion sources in GEDR3 with five-parameter solutions (adapted from Gaia Early Data Release 3: The celestial reference frame (Gaia-CRF3)).
Notes: Replacing '31' by '95' gives the 182,814,959 confusion sources with six-parameter solutions. This query is somewhat dangerous since taken verbatim it returns huge tables.
Target tables: gaiaedr3.gaia_source, gaiaedr3.agn_cross_id
Query:

SELECT * FROM gaiaedr3.gaia_source AS g
WHERE g.astrometric_params_solved = 31
  AND ABS((g.parallax + 0.017)/g.parallax_error) < 5
  AND ( POWER(g.pmra/g.pmra_error,2) + POWER(g.pmdec/g.pmdec_error,2) -2*g.pmra_pmdec_corr*g.pmra/g.pmra_error*g.pmdec/g.pmdec_error)/
      (1-POWER(g.pmra_pmdec_corr,2)) < 25

Synthetic photometry from Gaia XP spectra - PVP

Use case: I want to retrieve SDSS synthetic photometery for the sources that have an absolute corrected BP/RP flux excess factor smaller than the 1-sigma relation suggested in Gaia Early Data Release 3: Photometric content and validation.
Notes: This query runs on a 0.001 random selection of the Gaia source catalogue thanks to the additional constraint on random_index.
Target tables: gaiadr3.gaia_source, gaiadr3.synthetic_photometry_gspc
Query: 

SELECT dr3.source_id, dr3.ra, dr3.dec, dr3.parallax, dr3.parallax_over_error, dr3.ruwe, gspc.g_sdss_mag, gspc.i_sdss_mag
FROM gaiadr3.gaia_source AS dr3
INNER JOIN gaiadr3.synthetic_photometry_gspc AS gspc USING (source_id)
WHERE
ABS(gspc.c_star)<(0.0059898 + 8.817481e-12 * POWER(dr3.phot_g_mean_mag, 7.618399)) AND dr3.random_index < 1811709

Synthetic photometry from Gaia XP spectra - PVP (II)

Use case: I want to retrieve some selected parameters from the main Gaia DR3 table and all the parameters available in the GSPC for a given set of stars, i.e. those having |b|> 50 deg (Galactic Caps). Selections in RUWE and in c_star (following the criterion suggested by Gaia Early Data Release 3: Photometric content and validation) are also adopted.
Target tables: gaiadr3.gaia_source_lite, gaiadr3.synthetic_photometry_gspc
Notes: Execution time ~25 mins. Job output size = 1.3 GB.
Query:

SELECT dr3.source_id, dr3.ra, dr3.dec, dr3.parallax, dr3.parallax_error, dr3.pmra, dr3.pmra_error, dr3.pmdec, dr3.pmdec_error, dr3.phot_g_mean_mag, 
dr3.phot_bp_mean_mag, dr3.phot_rp_mean_mag, dr3.bp_rp, dr3.radial_velocity, dr3.radial_velocity_error, dr3.ruwe, dr3.phot_variable_flag, 
dr3.non_single_star, dr3.teff_gspphot, dr3.logg_gspphot, dr3.mh_gspphot, dr3.distance_gspphot, dr3.ag_gspphot,
gspc.c_star, gspc.u_sdss_mag, gspc.g_sdss_mag,gspc.r_sdss_mag, gspc.i_sdss_mag, gspc.z_sdss_mag, 
gspc.u_jkc_mag, gspc.b_jkc_mag,gspc.v_jkc_mag, gspc.r_jkc_mag, gspc.i_jkc_mag, gspc.y_ps1_mag, f606w_acswfc_mag, f814w_acswfc_mag,
1.086*(gspc.u_sdss_flux_error/gspc.u_sdss_flux) AS du_sdss, 1.086*(gspc.g_sdss_flux_error/gspc.g_sdss_flux) AS dg_sdss, 
1.086*(gspc.r_sdss_flux_error/gspc.r_sdss_flux) AS dr_sdss, 1.086*(gspc.i_sdss_flux_error/gspc.i_sdss_flux) AS di_sdss, 
1.086*(gspc.z_sdss_flux_error/gspc.z_sdss_flux) AS dz_sdss, 1.086*(gspc.u_jkc_flux_error/gspc.u_jkc_flux)   AS du_jkc, 
1.086*(gspc.b_jkc_flux_error/gspc.b_jkc_flux) AS db_jkc,    1.086*(gspc.v_jkc_flux_error/gspc.v_jkc_flux)   AS dv_jkc, 
1.086*(gspc.r_jkc_flux_error/gspc.r_jkc_flux) AS dr_jkc,    1.086*(gspc.i_jkc_flux_error/gspc.i_jkc_flux)   AS di_jkc, 
1.086*(gspc.y_ps1_flux_error/gspc.y_ps1_flux) AS dy_ps1,    1.086*(gspc.f606w_acswfc_flux_error/gspc.f606w_acswfc_flux) AS df606w_acswfc, 
1.086*(gspc.f814w_acswfc_flux_error/gspc.f814w_acswfc_flux) AS df814w_acswfc,
gspc.u_sdss_flag, gspc.g_sdss_flag, gspc.r_sdss_flag, gspc.i_sdss_flag, gspc.z_sdss_flag,
gspc.u_jkc_flag, gspc.b_jkc_flag, gspc.v_jkc_flag, gspc.r_jkc_flag, gspc.i_jkc_flag,
gspc.y_ps1_flag, gspc.f606w_acswfc_flag, gspc.f606w_acswfc_flag
FROM gaiadr3.gaia_source_lite AS dr3
INNER JOIN gaiadr3.synthetic_photometry_gspc AS gspc USING (source_id)
WHERE ABS(dr3.b)>50.0
AND dr3.ruwe<1.3
AND ABS(gspc.c_star)<(0.0059898 + 8.817481e-12 * POWER(dr3.phot_g_mean_mag,7.618399))

Extragalactic objects in Gaia DR3 (QSOS PURER SUBSET)

Use case: Selects the purer subset of quasars recommended by Gaia Data Release 3: The extragalactic content.
Target table: gaiadr3.qso_candidates
Query: 

SELECT *
FROM gaiadr3.qso_candidates
WHERE (gaia_crf_source = 'True' OR host_galaxy_flag<6 OR classlabel_dsc_joint = 'quasar' OR vari_best_class_name = 'AGN')

Extragalactic objects in Gaia DR3 (GALAXIES PURER SUBSET)

Use case: Selects the purer subset of galaxies recommended by Gaia Data Release 3: The extragalactic content.
Target table: gaiadr3.galaxy_candidates
Query: 

SELECT *
FROM gaiadr3.galaxy_candidates
WHERE (radius_sersic IS NOT NULL OR classlabel_dsc_joint = 'galaxy' OR vari_best_class_name = 'GALAXY')

Extragalactic objects in Gaia DR3

Use case: Shows how to combine three tables to select sources with higher classification probabilities that exclude the LMC and SMC (extracted from Gaia Data Release 3: The extragalactic content).
Target tables: gaiadr3.qso_candidates, gaiadr3.astrophysical_parameters
Query: 

SELECT source_id
FROM (SELECT *
	  FROM gaiadr3.qso_candidates
	  JOIN gaiadr3.astrophysical_parameters USING (source_id)
      WHERE classprob_dsc_specmod_quasar>0.9 AND classprob_dsc_allosmod_quasar>0.9
     ) AS temp
JOIN gaiadr3.gaia_source USING (source_id)
WHERE 1! = CONTAINS(
  POINT(81.3, -68.7), CIRCLE(ra, dec, 9.0)) AND
  1! = CONTAINS(
  POINT(16.0, -72.8), CIRCLE(ra, dec, 6.0))

Golden sample of Astrophysical Parameters

Use case: The query used iteratively for the analysis and selection of the FGKM sample of stars. The iteration runs over random_index in steps of 10,000,000 sources (Gaia Data Release 3: A golden sample of astrophysical parameters).
Target tables: gaiadr3.gaia_source, gaiadr3.astrophysical_parameters, gaiadr3.astrophysical_parameters_supp
Query: 

SELECT gs.source_id, phot_bp_mean_mag, phot_rp_mean_mag, phot_bp_mean_flux_over_error, phot_rp_mean_flux_over_error, 
phot_g_mean_mag, parallax_error, parallax, parallax_over_error, astrometric_excess_noise, astrometric_excess_noise_sig, 
ruwe, ipd_gof_harmonic_amplitude, rvs_spec_sig_to_noise, rv_expected_sig_to_noise,
ap.ebpminrp_gspphot, ap.radius_gspphot, ap.teff_gspphot, ap.logg_gspphot, ap.mg_gspphot,
ap.abp_gspphot, gs.ag_gspphot, ap.arp_gspphot, ap.teff_gspphot_upper, gs.mh_gspphot,
ap.teff_gspphot_lower, mg_gspphot_lower, mg_gspphot_upper, ap.ebpminrp_gspphot_lower,ap.ebpminrp_gspphot_upper, 
ap.ag_gspphot_upper, ap.ag_gspphot_lower, ap.mh_gspphot_upper, ap.mh_gspphot_lower, ap.logg_gspphot_upper, ap.logg_gspphot_lower, 
teff_gspphot_marcs, teff_gspphot_phoenix, logg_gspphot_marcs, logg_gspphot_phoenix, 
radius_flame, mass_flame, age_flame, evolstage_flame, lum_flame, bc_flame,
radius_flame_upper, radius_flame_lower, lum_flame_upper, lum_flame_lower, 
mass_flame_upper, mass_flame_lower, age_flame_upper, age_flame_lower, flags_flame,
spectraltype_esphs, flags_esphs, 
teff_gspspec, mh_gspspec, logg_gspspec, alphafe_gspspec, 
teff_gspspec_upper, teff_gspspec_lower, logg_gspspec_upper, logg_gspspec_lower, mh_gspspec_upper, mh_gspspec_lower, 
radius_flame_spec, lum_flame_spec, age_flame_spec, mass_flame_spec, evolstage_flame_spec, 
radius_flame_spec_upper, radius_flame_spec_lower, lum_flame_spec_lower, lum_flame_spec_upper, mass_flame_spec_upper, 
mass_flame_spec_lower, age_flame_spec_lower, age_flame_spec_upper, bc_flame_spec
FROM gaiadr3.gaia_source AS gs 
INNER JOIN gaiadr3.astrophysical_parameters_supp AS aps USING (source_id)
INNER JOIN gaiadr3.astrophysical_parameters AS ap USING (source_id)
WHERE ipd_frac_multi_peak < 6 AND parallax_over_error >10 AND gs.teff_gspphot > 2500 AND gs.teff_gspphot < 7500 AND 
gs.distance_gspphot < 1e3/(parallax-4*parallax_error) AND gs.distance_gspphot > 1e3/(parallax +4*parallax_error) AND
gs.libname_gspphot='MARCS' AND gs.classprob_dsc_combmod_star > 0.9 AND gs.mh_gspphot > -0.8 AND
ABS(teff_gspphot_marcs - teff_gspphot_phoenix + 65) < 150 AND radius_gspphot < 100 and mg_gspphot < 12 
AND random_index >= 1600000000 AND random_index < 1610000000

RETRIEVE SAMPLE OF OB STARS IN THE MILKY WAY SPIRAL ARMS

Use case: I want to retrieve the sample of OB stars used in the Mapping the spiral arms of the Milky Way (Gaia Data Release 3: Mapping the asymmetric disc of the Milky Way).
Target table: gaiadr3.gaia_source, gaiadr3.astrophysical_parameters
Query:

SELECT g.*, ap.*
FROM gaiadr3.gaia_source AS g
INNER JOIN gaiadr3.astrophysical_parameters AS ap
ON g.source_id = ap.source_id
WHERE (
(ap.teff_gspphot > 10000 AND (ap.spectraltype_esphs = 'O' OR ap.spectraltype_esphs = 'B' OR ap.spectraltype_esphs = 'A') AND ap.teff_esphs IS NULL) OR
(ap.teff_esphs > 10000 AND ap.teff_gspphot > 8000) OR (ap.teff_esphs > 10000 AND ap.teff_esphs < 50000 AND ap.teff_gspphot IS NULL))
AND power(g.parallax/100.,5) < power(10.,2.-g.phot_g_mean_mag+1.8*g.bp_rp)

RETRIEVE SAMPLE OF RGB STARS IN THE MILKY WAY SPIRAL ARMS

Use case: I want to retrieve the sample of RGB stars used in the Mapping the spiral arms of the Milky Way (Gaia Data Release 3: Mapping the asymmetric disc of the Milky Way).
Target table: gaiadr3.gaia_source
Query:

SELECT g.*
FROM gaiadr3.gaia_source AS g
WHERE (g.teff_gspphot<5500 AND g.teff_gspphot>3000) AND (g.logg_gspphot<3.)

Chemical cartography of the Milky Way disc

Use case: I want to retrieve the "medium quality sample" of stars with GSPspec parameters described in (Gaia Data Release 3: Chemical cartography of the Milky Way - see their Sect. 2.5)
Target table: gaiadr3.astrophysical_parameters
Query:

SELECT source_id
FROM gaiadr3.astrophysical_parameters
WHERE (teff_gspspec>3500) AND (logg_gspspec>0) AND (logg_gspspec<5) AND ((teff_gspspec_upper-teff_gspspec_lower)<750) AND ((logg_gspspec_upper-logg_gspspec_lower)<1.) AND
((mh_gspspec_upper-mh_gspspec_lower)<.5) AND (teff_gspspec>=3800 OR logg_gspspec<=3.5) AND (teff_gspspec>=4150 OR logg_gspspec<=2.4 OR logg_gspspec>=3.6 ) 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%') OR (flags_gspspec LIKE '______2%') OR (flags_gspspec LIKE '______3%')) AND ((flags_gspspec LIKE '_______0%') OR (flags_gspspec LIKE '_______1%') OR (flags_gspspec LIKE '_______2%'))

Reflectance spectra of Main Belt small bodies

Use case: I want to retrieve the wavelengths, reflectances, and reflectance errors for the asteroid numbered 21 by the Minor Planet Center (Reflectance spectra of Main Belt small bodies, Gaia Data Release 3: Reflectance spectra of solar system small bodies).
Target table: gaiadr3.gaia_source, gaiadr3.astrophysical_parameters
Query:

SELECT wavelength, reflectance_spectrum, reflectance_spectrum_err
FROM gaidr3.sso_reflectance_spectrum
WHERE number_mp = 21

Radial-velocity variable stars

Use case: I want to identify radial-velocity variable stars applying the criteria described in Gaia Data Release 3: Properties and validation of the radial velocities.
Target table: gaiadr3.gaia_source
Query:

SELECT * FROM gaiadr3.gaia_source
WHERE
rv_nb_transits >= 10 AND
rv_template_teff BETWEEN 3900 AND 8000 AND
rv_chisq_pvalue <= 0.01 AND
rv_renormalised_gof > 4

Stellar multiplicity, a teaser for the hidden treasure

Use case: I want to compare orbital periods from the "gaiadr3.nss_two_body_orbit" table against photometric periods from the "gaiadr3.vari_long_period_variable" table. Adapted from Gaia Data Release 3: Stellar multiplicity, a teaser for the hidden treasure.
Target tables: gaiadr3.nss_two_body_orbit, gaiadr3.vari_long_period_variable
Query:

SELECT *
FROM gaiadr3.nss_two_body_orbit AS TBO, gaiadr3.vari_long_period_variable AS LPV
WHERE LPV.source_id = TBO.source_id AND LPV.frequency IS NOT NULL

 

 

 

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)