Use case: I want to perform a simple cone search.
Target table: gaiaedr3.gaia_source
SELECT DISTANCE( POINT('ICRS', 266.41683, -29.00781), POINT('ICRS', ra, dec)) AS dist, * FROM gaiaedr3.gaia_source WHERE 1=CONTAINS( POINT('ICRS', 266.41683, -29.00781), CIRCLE('ICRS',ra, dec, 0.08333333)) ORDER BY dist ASC
Use case: I want to retrieve all the objects in a catalogue in a circular region and apply two additional SELECTion criteria.
Target table: gaiaedr3.gaia_source
SELECT * FROM gaiaedr3.gaia_source WHERE 1=CONTAINS( POINT('ICRS',266.41683, -29.00781), CIRCLE('ICRS',ra, dec, 0.08333333)) AND phot_g_mean_mag>=10 AND phot_g_mean_mag<15 AND ruwe <1.4 ORDER BY phot_g_mean_mag ASC
Example of simple query filtered by logical conditions.
Use case: I want to retrieve all the stars in a close region (shell) around the Sun and apply an additional selection criterion (magnitude in this case)
Target table: gaiaedr3.gaia_source
SELECT * FROM gaiaedr3.gaia_source WHERE parallax >= 15 AND parallax <= 50 AND phot_g_mean_mag >= 9 AND phot_g_mean_mag <= 9.5
Use case: I want to carry out a positional cross-match BETWEEN two catalogues, selecting only the first 10 cross-matches.
Target table: gaiaedr3.gaia_source,public.hipparcos
SELECT TOP 10 * , distance( POINT('ICRS', hip.ra, hip.de), POINT('ICRS', gaia.ra, gaia.dec)) AS dist FROM public.hipparcos AS hip JOIN gaiaedr3.gaia_source AS gaia ON 1=CONTAINS( POINT('ICRS', hip.ra, hip.de), CIRCLE('ICRS', gaia.ra, gaia.dec, 0.000277777777778))
Use case: I want to carry out a positional cross-match BETWEEN Hipparcos and Gaia eDR3.
NOTE: You must be authenticated to execute this query.
Target table: gaiaedr3.gaia_source,public.hipparcos
SELECT crossmatch_positional( 'public','hipparcos', 'gaiaedr3','gaia_source', 1.0, 'xmatch_hipparcos_gaia') FROM dual;
Use case: I want to propagate Gaia positions to an epoch in the past for comparison against photographic plates astrometry.
NOTE: This query applies the built-in ADQL function "EPOCH_PROP_POS" (see documentation in: https://www.cosmos.esa.int/web/gaia-users/archive/writing-queries/#EpochPropagationFlag)
Target table: gaiaedr3.gaia_source
SELECT source_id, ra, dec, coord1(prop) AS ra_1950, coord2(prop) AS dec_1950 FROM ( SELECT gaia.source_id, ra, dec, EPOCH_PROP_POS(ra, dec, parallax, pmra, pmdec, 0, ref_epoch, 1950) AS prop FROM gaiaedr3.gaia_source AS gaia WHERE contains( POINT('ICRS', 56.75, 24.12), CIRCLE('ICRS',gaia.ra, gaia.dec, 5)) = 1 AND sqrt(power(gaia.pmra - 20.5, 2) + power(gaia.pmdec + 45.5, 2)) < 6.0 ) AS subquery
Use case: I want to propagate Gaia positions to an epoch in the past for comparison against photographic plates astrometry.
NOTE: This query applies a simple linear transformation to the Equatorial coordinates.
Target table: gaiaedr3.gaia_source
SELECT source_id, ra, dec, ra + 1. / 3600e3 * pmra * (1950.0 - ref_epoch) / cos(radians(dec)) as ra_1950, dec + 1. / 3600e3 * pmdec * (1950.0 - ref_epoch) as dec_1950 FROM gaiaedr3.gaia_source AS gaia WHERE 1 = contains( POINT('ICRS', 56.75, 24.12), CIRCLE('ICRS',gaia.ra, gaia.dec, 5) ) AND sqrt(power(gaia.pmra - 20.5, 2) + power(gaia.pmdec + 45.5, 2)) < 6.0
Use case: I want to show the average distribution of Gaia DR2 radial velocities in Galactic Coordinates.
Reference: DR2. Gaia Collaboration, Katz et al. 2019 A&A 622, A205 Fig. 7 (adapted)
Target table: gaiadr2.gaia_source
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
Use case: I want to construct an observed CMD diagram
Reference: DR2. Gaia Collaboration, Andrae et al. 2018 A&A 616, A8 Fig. 19 (adapted)
Target table: gaiadr2.gaia_source
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
Use case: I want to construct a dust-corrected CMD diagram
Reference: DR2. Gaia Collaboration, Andrae et al. 2018 A&A 616, A8 Fig. 19 (adapted)
Target table: gaiadr2.gaia_source
SELECT bp_rp_index / 40 AS bp_rp, g_abs_index / 10 AS g_abs, n FROM ( SELECT floor((bp_rp - e_bp_min_rp_val) * 40) AS bp_rp_index, floor((phot_g_mean_mag - a_g_val + 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 BETWEEN 1000000 AND 1799999 GROUP BY bp_rp_index, g_abs_index ) AS subquery
Use case: I want to obtain the parallax-averaged A_G Extinction.
Reference: DR2. Gaia Collaboration, Andrae et al. 2018 A&A 616, A8 Fig. 21 (adapted)
Target table: gaiadr2.gaia_source
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
Use case: I want to obtain the extinction towards Orion for a distance slice between 0.6 AND 1.0 kpc
Reference: DR2. Gaia Collaboration, Andrae et al. 2018 A&A 616, A8 Fig. 34 (adapted)
Target table: gaiadr2.gaia_source
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
Use case: I want to retrieve the sky density distribution of RR Lyrae stars
Reference: DR2. Gaia Collaboration, Holl et al. 2018 A&A 618, A30 Fig. 6 (adapted)
Target table: gaiadr2.vari_classifier_result
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
Use case: I want to retrieve the light curves for two variable stars.
Reference: DR2. Gaia Collaboration, Holl et al. 2018 A&A 618, A30 Fig. A.1 (adapted).
Target table: gaiadr2.gaia_source,gaiadr2.vari_rrlyrae,gaiadr2.vari_long_period_variable,gaiadr2.vari_classifier_result
SELECT gaia.source_id, phot_variable_flag, best_class_name, datalink_url, epoch_photometry_url, sosrrl.pf, sosrrl.epoch_g, soslpv.frequency FROM gaiadr2.gaia_source AS gaia LEFT OUTER JOIN gaiadr2.vari_classifier_result AS variables ON gaia.source_id = variables.source_id LEFT OUTER JOIN gaiadr2.vari_rrlyrae AS sosrrl ON gaia.source_id = sosrrl.source_id LEFT OUTER JOIN gaiadr2.vari_long_period_variable AS soslpv ON gaia.source_id = soslpv.source_id WHERE gaia.source_id IN (1372970619261887104, 5368939678153068288)
Use case: I want to construct the Gaia DR2-TMASS HR diagram.
Reference: DR1. Gaia Collaboration, Brown et al. 2016 A&A 595, A2 Fig. 5 (adapted)
Target table: gaiadr2.gaia_source,gaiadr2.tmass_best_neighbour,gaiadr1.tmass_original_valid
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 gaiadr2.gaia_source AS gaia INNER JOIN gaiadr2.tmass_best_neighbour AS xmatch ON gaia.source_id = xmatch.source_id INNER JOIN gaiadr1.tmass_original_valid AS tmass ON tmass.tmass_oid = xmatch.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
Use case: I want to construct the Gaia DR2 HR diagram.
Reference: DR1. Gaia Collaboration, Brown et al. 2016 A&A 595A, 2G Fig. 5 (adapted)
Target table: gaiadr2.gaia_source,gaiadr2.tmass_best_neighbour,gaiadr1.tmass_original_valid
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 ON gaia.source_id = xmatch.source_id INNER JOIN gaiadr1.tmass_original_valid AS tmass ON tmass.tmass_oid = xmatch.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
Use case: I want to retrieve all light curves of a given object class for offline analysis.
Reference: DR1. Clementini et al. 2016 A&A 595A, A133 (adapted)
NOTE: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial: https://www.cosmos.esa.int/web/gaia-users/archive/ancillary-data#tutorial_datalinklc)
Target table: gaiadr1.cepheid,gaiadr1.phot_variable_time_series_gfov
SELECT gaia.* FROM gaiadr1.phot_variable_time_series_gfov AS gaia INNER JOIN gaiadr1.cepheid AS cep on gaia.source_id = cep.source_id
Use case: I want to construct a phase-folded light curve for easier analysis and comparison to stars with different period.
Reference: DR1. Gaia Collaboration, Brown et al. 2016 A&A 595A, 2G Fig. 7 (adapted)
NOTE: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial: https://www.cosmos.esa.int/web/gaia-users/archive/ancillary-data#tutorial_datalinklc)
Target table: gaiadr1.rrlyrae,gaiadr1.phot_variable_time_series_gfov
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 ON rrlyrae.source_id = curves.source_id WHERE rrlyrae.source_id = 5284240582308398080
Use case: I want high level information for a given class of variable objects.
DR1. Clementini et al. 2016 A&A 595A, A133 (adapted)
NOTE: After DR1 light curves are provided via DataLink interface (see dedicated Tutorial: https://www.cosmos.esa.int/web/gaia-users/archive/ancillary-data#tutorial_datalinklc)
Target table: gaiadr1.rrlyrae,gaiadr1.phot_variable_time_series_gfov_statistical_parameters
SELECT stat.num_observations_processed, rr.* FROM gaiadr1.phot_variable_time_series_gfov_statistical_parameters AS stat INNER JOIN gaiadr1.rrlyrae AS rr ON stat.source_id = rr.source_id