Gaia ESA archive is available at:

https://gea.esac.esa.int/archive/

ADQL language results from SQL, currently version 2.1, developed, maintained by International Virtual Observatory Alliance

Gaia webapeg host primatily the Gaia data release but also external catalogues under "Other", For example GALEX, RAVE (The Radial Velocity Experiment, DR6 available)


1. Selecting data for all sources from a table

SELECT source_id, ra, dec, parallax, period, inclination, eccentricity FROM gaiadr3.nss_two_body_orbit ORDER BY period DESC

This selects data from table "gaiadr3.nss_two_body_orbit", the rows are sorted in descending order by period. Results in 443205 rows.

Now you can select download format. For example "CSV". CSV table has 43 MB and can be open with Excel/Openoffice spreadsheet.

2. Performing a cone search around some position 

the search is limited to sources closer than 10 arcsec

SELECT *, 3600*DISTANCE(343.42403, +37.938547, ra, dec) AS ang_sep FROM gaiadr3.gaia_source WHERE DISTANCE(343.42403, +37.938547, ra, dec) < 30./3600. AND phot_g_mean_mag < 20.5 AND parallax IS NOT NULL ORDER BY ang_sep ASC

This searches for object with 0.5 arcmin circle from SW Lac (343.42403, +37.938547) from Gaia DR3, with g magnitude brighter than 20.5
and measured parallax; objects are ordered by angular separation in arcseconds

3. Selecting all data from table gaiadr3.gaia_source close to BU CMi in galactic coordinates) which has photometry and spectrum:

SELECT * FROM gaiadr3.gaia_source WHERE DISTANCE(214.01554, +18.08724, l, b) < 5./60. AND has_epoch_photometry = 'True' AND has_xp_continuous = 'True' 

l.b are coordinates of BU CMi, distance from BU CMi is 5 arcmin. Results in 5 sources

see https://gaia.aip.de/metadata/gaiadr3/gaia_source_lite/

4. Doing basic crossmatch of two catalogs Gaia and Hipparcos

SELECT TOP 20 * FROM public.hipparcos AS hip
JOIN gaiadr3.gaia_source AS gaia 
ON 1 = CONTAINS(POINT(hip.ra, hip.de), CIRCLE(gaia.ra, gaia.dec, 0.0001))
   
Shows only first 20 rows. Function CONTAINS determines if the point given in POINT is withing CIRCLE with diameter of 0.0001 degrees. If it is, CONTAINS results in unity if it is not contained it results in zero. TOP command limits the number of output lines. ADDING OFFSET at the and followed by number of rows skips this number of rows:

SELECT TOP 10 * FROM public.hipparcos AS hip
JOIN gaiadr3.gaia_source AS gaia 
ON 1 = CONTAINS(POINT(hip.ra, hip.de), CIRCLE(gaia.ra, gaia.dec, 0.0001))
OFFSET 20

Here first 20 rows are skipped
 

5. Using external sources use "External TAP source" TAP - "Table Access Protocol", then select external source. For example GCVS can be 
queried. Now you can select all 'EW' type binaries from GCVS and sort them by increasing period

SELECT GCVS,raj2000,dej2000,magmax,period,vartype FROM "B/gcvs/gcvs_cat" WHERE vartype = 'EW' AND period < 0.25 ORDER BY period ASC

This selects EW objects with periods shorter than 0.25 days and order them by period in ascending order. There is a symbol aying that the data were downloaded from an external source.

6. One should use pre-computed resources:

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

this will compute colour indices, but the following query is much faster as it will use pre-computed colour indices:

SELECT *
FROM gaiadr3.gaia_source
WHERE bp_rp < -2

7. Correct crossmatching of sources between Hipparcos and Gaia. We should search for every Hipparcos source the Gaia crossmatch to shorten 
the query:

SELECT gaia.source_id, gaia.ra, gaia.dec, gaia.parallax, hip.*
FROM public.hipparcos AS hip
JOIN gaiadr3.gaia_source AS gaia
ON DISTANCE(POINT(hip.ra, hip.de),POINT(gaia.ra, gaia.dec)) < 1./3600

POINT commands says to ADQL that a single catalogue point will be used = exact coordinates in this case given by hip.ra, hip.dec, gaia.ra. 
gaia.dec columns. AS defines an alias: the complicated source name e.g. "gaiadr3.gaia_source" is aliased as gaia. Results in 81867 rows where
Hipparcos objects are found in Gaia DR3.

8. Simple selecting of all information from Hipparcos, objects with largest parallaxes selected

SELECT *
FROM public.hipparcos AS HIP
WHERE hip.plx IS NOT NULL
ORDER BY hip.plx DESC

objects with non-existent parallaxes ares skipped. Results in 117955 rows. If we select non-negative parallaxes, we have

SELECT *
FROM public.hipparcos AS HIP
WHERE (hip.plx IS NOT NULL) AND (hip.plx > 0.)
ORDER BY hip.plx DESC

and 113710 objects

9. Reading an external catalog. Use "External TAP search". Use "Search keywords". For example give "gouliermis ngc 346". Select "Photometric catalog of stars...".
Press "Add selected". Now full catalog is queried with:

SELECT * FROM "J/ApJS/166/549/table2"

! In the catalog selection window you must have: tapvizier.cds.unistra.fr !

9. First, let's search for all eclipsing binaries in GCVS which have period shorter than 20 days:

SELECT GCVS,raj2000,dej2000,magmax,period,vartype FROM "B/gcvs/gcvs_cat" WHERE (vartype = 'EW' OR vartype ='EB' OR vartype = 'EA') AND period < 20.0 ORDER BY period ASC

We must have external source connect ("tapvizier.cds.unistra.fr") must be selected instead of "gaia" !!!

this is done and job number is given. We have have7515 rows.

then using crossmatch with gaia looking for gaia parallaxes and their errors:

SELECT gaia.parallax, gaia.parallax_error, gcvs.*
FROM job_upload."job1716454315815O" AS gcvs
JOIN gaiadr3.gaia_source AS gaia
ON DISTANCE(POINT(gcvs.raj2000, gcvs.dej2000),POINT(gaia.ra, gaia.dec)) < 1./3600

Now, do not forget to select "gaia" as the catalogue source ! The query results in 7357 sources with Gaia DR3 parallaxes

or trying to find VIM between GCVS objects:

SELECT gaia.parallax, gaia.parallax_error, gaia.significance, gcvs.*
FROM job_upload."job1716464700007O" AS gcvs
JOIN gaiadr3.nss_vim_fl AS gaia
ON DISTANCE(POINT(gcvs.raj2000, gcvs.dej2000),POINT(gaia.ra, gaia.dec)) < 1./3600

This results in 29 sources which are EBs in GCVS and have VIM in the Gaia astrometric solution

10. Creating an colour-magnitude diagram from stars with high-quality parallaxes:

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

ag_gspphot is extinction in G band from Gaia DR3
parallax_over_error is parallax divided by its error
random_index < 500000 limits the number of quried gaia rows shortening execution time
Plot can be found in https://www.aanda.org/articles/aa/full_html/2018/08/aa32516-17/F19.html

11. Speeding up the quaries. For Gaia, one can use gaiadr3.gaia_source_lite which only contains 51 selected fields but full number of rows:

For example:

SELECT COUNT(*)
   FROM gaiadr3.gaia_source_lite
   WHERE phot_g_mean_mag <= 18.25
   AND has_mcmc_msc = 'True'

  COUNT function does not produce a table with sources but only number of such sources, in this case 348630727.

12. Using random samples saves lot of time when selecting from the wholo 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 >= 10
AND phot_bp_mean_flux_over_error > 0
AND phot_rp_mean_flux_over_error > 0
AND SQRT( means  + POWER(2.5/log(10) / phot_rp_mean_flux_over_error, 2)) <= 0.05
AND random_index BETWEEN 0 AND 1000000

This produces data for colour-magnitude diagram based on 1000000 randomly selected sources with parallax overerror larger than 10, and relative colour index error is lower than 5 percent.

POWER(2.5/log(10) / phot_bp_mean_flux_over_error, 2) means (2.5/log(10)/phot_bp_mean_flux_over_error)^2

Table contains source_id, g_mag_abs, bp_rp

13. Selecting from and external source and using UDF (User Defined Functions)

SELECT * FROM external.ravedr6 WHERE COORD2(IVO_GEOM_TRANSFORM('ICRS', 'GALACTIC', POINT(ra_input, dec_input))) BETWEEN -5.0 AND 5.0

Selects data from RAVE survey DR6

Here transformation from RA,DEC to galactic coordinates is done. We only select objects with galactic latitude between -5 and 5 all along galactic equator.

14. Selecting from Gaia DR3 but using Simbad name resolver "IVO_SIMBADPOINT"

SELECT * FROM gaiadr3.gaia_source_lite
WHERE DISTANCE(POINT(COORD1(IVO_SIMBADPOINT('pleiades')), COORD2(IVO_SIMBADPOINT('pleiades'))), POINT(ra, dec)) < 1.0
AND parallax > 5 AND parallax < 10
ORDER BY parallax DESC

This selects objects which are within one degree of Simbad-resolved coordinates of Plaiades. COORD1 means re, COORD2 means dec.
Only objects with parallaxes between 5 and 10 mas are selected, which excludes very distand or close objects (knowing that Plaiades are about 136 pc away = with pi = 7.3 mas).

15. Using transoformation from normal date YYYY-MM-DD to JD and sleecting Gaia observations after JD 2455197.5

SELECT * FROM gaiafpr.sso_observation WHERE epoch > (IVO_TO_JD('2020-01-01') - 2455197.5)

2455197.5 corresponds to 2010-01-01 this is Gaia-centric epoch TCB(Gaia)

16. We want to retrieve all astromtric measurements for some minor planet

SELECT * FROM gaiafpr.sso_observation
WHERE number_mp=704

or using denomination:

SELECT * FROM gaiafpr.sso_observation
WHERE denomination = 'interamnia'

It is minor planet Interamnia: object between a minor plet and small asteroid. Be careful denomination is string and it is case-sensitive.

17. Interestion Gaia Focused Product Releases. For example interstellar diffuse bands

SELECT *
FROM gaiafpr.interstellar_medium_spectra AS gaia
WHERE (gaia.lc > 10.0 AND gaia.lc < 15.0 AND gaia.bc > 2.0 AND gaia.bc < 6.0)


This results in a stacked interstellar medium spectrom from galactic longitudes 10-15 and latitudes 2-6 degrees
The stacked spectra are given for different distances and slightly different field centers. Thus we have many spectra
in the result.

18. EXTENDED RADIAL VELOCITY EPOCH DATA FOR LONG PERIOD VARIABLES

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

It is not clear which objects are included in the sample

19. Considering proper motion in cone searches

select source_id, ra, dec, parallax, pmra, pmdec,
sqrt(pmra * pmra + pmdec * pmdec) as pm, radial_velocity, ref_epoch
from gaiadr2.gaia_source
where (pmra  < -50 / sqrt(2) or pmra  > 50 / sqrt(2))
  and (pmdec < -50 / sqrt(2) or pmdec > 50 / sqrt(2))
  and sqrt(pmra * pmra + pmdec * pmdec) > 50

20. Proper motion cros-match Gaia vs. USNO B1

First search using TAP "USNO B1". Then select "I/284/out"

select "USNO-B1.0", "Tycho-2", raj2000, dej2000, epoch, pmra, pmde, mupr,
  b1mag, r1mag, b2mag, r2mag
from "I/284/out"
where 1 = contains(
  point('ICRS', raj2000, dej2000),
  circle('ICRS', 245.89675000000003, -26.52575, 0.5)
)

cirle gives coordinates of center of M4. In the TAP query 'ICRS; must be the first argument, not only ra,dec.
uery results in 43060 rows. 

One can do statistics on the last query but do not forget to swith to "gaia" as the source ! The result is only one line containing min, max and average

The simplest way to fight the proper motion is to enlarge the tolerance. The cross-match radius between Gaia and M4 can be estimated as follows: 2 arcsec (tolerance) + 50 mas/yr (maximum proper motion for slow stars) * (2015.5 – 1954.2) yr = 5.0 arcsec.

select usno.*,
  gaia.source_id, gaia.ra, gaia.dec, gaia.parallax,
  gaia.pmra as pmra_gaia, gaia.pmdec as pmdec_gaia,
  radial_velocity, phot_g_mean_mag, phot_bp_mean_mag, phot_rp_mean_mag
from job_upload."job1564407875966O" as usno
join gaiadr2.gaia_source as gaia
  on 1 = contains(
    point(usno.raj2000, usno.dej2000),
    circle(gaia.ra, gaia.dec, 5. / 3600.)
  )

Be careful to have correct job given ! The result gives more matches than original lines from USNO B1 and needs to be refined...

21. Proper-motion corrected cross-match.

There are two ways to propagate proper motion:

-approximate

RA_propagated_approx = RA_catalogue + pm_RA * (epoch_propagated – epoch_catalogue) / cos(DEC)
DEC_propagated_approx = DEC_catalogue + pm_DEC * (epoch_propagated – epoch_catalogue)

-reltively exact using built-in functions 

RA_propagated = SELECT COORD1(
  EPOCH_PROP_POS(ra, dec, parallax, pmra, pmdec, radial_velocity, epoch_catalogue, epoch_propagated))
DEC_propagated = SELECT COORD2(
  EPOCH_PROP_POS(ra, dec, parallax, pmra, pmdec, radial_velocity, epoch_catalogue, epoch_propagated))

The whole recipe is then:

select *,
distance(
  point(ra_orig, dec_orig),
  point(ra_gaia, dec_gaia)
) * 3600 as dist_arcsec
from (
  select *,
  raj2000 + 1. / 3600e3 * pmra * (epoch - 2000.0) / cos(radians(dej2000)) as ra_orig,
  dej2000 + 1. / 3600e3 * pmde * (epoch - 2000.0) as dec_orig,
  ra  + 1. / 3600e3 * pmra_gaia  * (epoch - 2015.5) / cos(radians(dec)) as ra_gaia,
  dec + 1. / 3600e3 * pmdec_gaia * (epoch - 2015.5) as dec_gaia
  from job_upload."job1564413301477O"
) as subquery
where
  distance(
    point(ra_orig, dec_orig),
    point(ra_gaia, dec_gaia)
  ) * 3600 <= 2
  or (
    pmra_gaia is null and
    distance (
        point(ra_orig, dec_orig),
        point(ra, dec)
	  ) * 3600 <= 2
  )

22. Selecting ligh curve for one particular system

N.B., time in Gaia series is TCB in Julians dates from 2010-01-01T00:00:00

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,
  rejected_by_variability_processing AS rejected
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

Now we can download it as FITS

24. Producing RR Lyrae period histogram.

SELECT floor(p1 * 50) / 50 AS period, count(*) AS n
FROM gaiadr1.rrlyrae
GROUP BY period
ORDER BY period

Produces histogram of periods of RR Lyrae stars . The bins are 0.02 wide in period

***********************************************************************************************************************************************

N.B.


a) Results can be downloaded in different formats, e.g. CSV = Comma-separated values, which can easily be imported to an Excel or Openoffice spreadsheet

b) Left clicking on the table gives "Name", "Description", "Size(rows)", "Size" (KB.MB,GB), and there is a link to show first 20 rows,
   the result can be downloaded as e.g. CSV, or FITS

c) SELECT TOP: means selecting 10 rows

d) Gaia already contains many pre-computed cross-match catalogues

e) ADQL: Astronomical Data Query Language

f) JOIN is the primary command for cross-matching using coordinates

g) Tab Query results contains results for individual queries, only 2000 rows can be displayed.

h) Using job upload mechanism to save time

i) AS makes an alias for a longer name of the database which is queried, e.g. "FROM public.hipparcos AS hip" means that public.hipparcos is further referred as "hip"

j) How to speed up the quaries: Query speed booster

k) TAP - Table Access Protocol

l) One can create Gaia account

m) Simple query cross-match using built-in interface under "Search" => "Basic" => "File"
   Now we have Gaia results for our sample of objects.