Getting started – Examples of Gaia DR1 MySQL queries

Here are a few examples of MySQL queries that will get you started with using the DR1 dataset. We will be working with the TGAS subset because it is significantly smaller than the whole catalog and therefore queries are executed faster. Very similar queries can be done on the whole catalog, the only difference being the lack of the TYCHO-2 identifier.

For a start, let’s just see how many stars are in the TGAS table:

SELECT COUNT(*) FROM GDR1.tgas_source;

MySQL reserved words (SELECT, FROM…) are case-insensitive, however it is better practice to keep them capitalized since the syntax is clearer this way. Other words such as table and column names are case sensitive.

Highest proper motion stars

To get the 100 stars with the highest total proper motion we can write the following query:

SELECT tycho2_id,SQRT(POW(pmra, 2) + POW(pmdec, 2)) / 1000 as pm
FROM GDR1.tgas_source
LIMIT 100;


All entries also come with an index that can be easily converted to a HEALpix indexes of various levels:

HEALpix level 12 = source_id / 34359738368
HEALpix level 11 = source_id / 137438953472
HEALpix level 10 = source_id / 549755813888
HEALpix level n = source_id / 2 ^ 35 * 4 ^ (12 - n)

Let’s get the all the stars in a HEALpixel around the center of SMC. The equatorial coordinates are RA=13.1866 d, DEC=-72.8286 d. The way the HEALpix is encoded in the source_id is:

HEALpix = ang2pix(2 ** level, PI/2 - DEC[rad], RA[rad], nest=True)

Function ang2pix is a standard HEALpix function that converts angles to HEALpixels. The query for a level 6 HEALpix (roughly 1 degree on the sky) is:

SELECT ra,`dec`
FROM GDR1.tgas_source
WHERE FLOOR(source_id / (POW(2, 35) * POW(4, 12 - 6))) = 3824;

The benefit of using this over queries that directly involve RA and DEC constraints is that this type of query is much faster.

We need to use the FLOOR function since the division does not return a whole number so otherwise the comparison with the HEALpix index (= 3824) would not yield anything. Also, not that the declination column has to be backticked. DEC is a reserved word in MySQL so the query would not be understood properly if the column name is not backticked.

Histogram of the G-band magnitudes

We can also produce a histogram of the G-band magnitudes directly from the query:

SELECT FLOOR(phot_g_mean_mag) AS gmag, COUNT(FLOOR(phot_g_mean_mag)) AS `count`
FROM GDR1.tgas_source
GROUP BY FLOOR(phot_g_mean_mag);

or using a subquery:

SELECT gmag, COUNT(gmag)
    SELECT FLOOR(phot_g_mean_mag) AS gmag
    FROM GDR1.tgas_source
) AS gmag_tab
GROUP BY gmag;

Proudly powered by Daiquiri
©2016 GAIA@AIPImprint and Data Protection Statement