Moca DB - DB Query (SQL)

This page will allow you to run basic SQL queries on the MOCA database, and export the results in CSV format or copy them to your clipboard. The outputs are limited to 10,000 rows, and the creation of temporary tables is not allowed. For such uses, we recommend using a SQL client or using the mocapy Python package to obtain a better in-code integration with pandas dataframes.

The MOCA database is written in MySQL, and a vast amount of online help is available to learn its basics. Note that we do not plan to support other flavors of SQL such as ADQL currently.

Users should first familiarize themselves with the database schema to undertsand the list of tables and columns available for queries.

Matching a list of custom star names with the MOCA database requires the use of the mocapy Python package, and examples are given in the README documentation of the mocapy GitHub page.

There are 7 types of tables in MOCA, starting with the following prefixes:

• moca_ : Tables listing unique identifiers in the database, such as astrophysical objects, young associations or other quantities, with their relevant informations.
• cat_ : External catalogs from which all columns were transferred in MOCA, usually only for stars that are included in the MOCA database.
• data_ : Tables containing raw data from the literature or from astronomical catalogs.
• calc_ : Tables containing intermediate quantities calculated by MOCA.
• cdata_ : Tables containing a mix of MOCA calculations or raw data from the literature. These tables allow for the adoption of the best available quantity regardless of whether it is a measurement directly from the literature or a MOCA calculation.
• mechanics_ : Tables automatically generated by MOCA as by-products which are not necessarily calculations. One such example would be a list of all designations for a given star, or compiled lists of most likely association members.
• summary_ : Tables that list a large number of properties for a star, a young association, or membership lists.

The usual pipeline for updating MOCA (carried by the admin only) is the inclusion of either new measurements or new claimed memberships in tables starting with data_. Automated batch programs will subsequently pull all available rows in various cat_ tables, propagate relevant measurements from cat_ tables to several data_ tables, and then update all relevant calc_, cdata_, mechanics_ and summary_ tables.

There are several column names in the database which start with the moca_ prefix; those usually indicate unique keys specific to the MOCA database, for example, moca_oid (object ID) is an integer that uniquely refers to a single astrophysical object (e.g., a star, white dwarf or brown dwarf), and moca_aid (association ID) is a short string that refers to an association. These columns are often useful to connect tables together with SQL JOIN statements.

A large number of basic queries can be done using only the summary_all_members table, which lists the most up-to-date lists of members for each young association. This table can list repeated entries for a given star, when it may be a candidate member of more than one association. The summary_all_objects table is similar but only lists each astrophysical object once, with its best membership and other informations.

A user could, for example, list only the candidate members of the beta Pic moving group (moca_aid=BPMG) with many of their basic properties, excluding the low-quality members (membership types not LM or R, i.e. low-likelihood candidates members or rejected members), using the following query (line returns are facultative):

SELECT *
FROM summary_all_members
WHERE moca_aid='BPMG' AND moca_mtid != 'R' AND moca_mtid != 'LM'

Basic filters can be applied easily using the columns available in summary_all_members, for example cuts based on right ascension (ra), declination (dec), or Gaia DR3 G-band magnitudes (gmag). Note that any cut applied to a column that includes missing (NULL) values will automatically reject all rows with a missing value, unless NULLs are specifically allowed for, such as in this example:

SELECT *
FROM summary_all_members
WHERE moca_aid='BPMG' AND moca_mtid != 'R' AND moca_mtid != 'LM' AND (gmag >= 12 OR gmag IS NULL) AND `dec` > 0 AND (ra > 200 OR ra <20)

Note that, in the example above, the ` symbol was required to wrap around the column name "dec" because this name is unfortunately a reserved SQL symbol. The ` symbol specifies that it is wrapped around a column name regardless of its title or any symbols it may contain.

Constraints based on spectral types can be specified using a LIKE comparison (% acts as a wildcard in this case), or using the column sptn (spectral type number), where 0 refers to M0, 10 refers to L0, and -10 refers to K0 etc. These two examples will therefore result in the same selection of all BPMG M dwarfs:

SELECT *
FROM summary_all_members
WHERE moca_aid='BPMG' AND moca_mtid != 'R' AND moca_mtid != 'LM' AND (spt LIKE 'M%' OR spt LIKE '(M%')

SELECT *
FROM summary_all_members
WHERE moca_aid='BPMG' AND moca_mtid != 'R' AND moca_mtid != 'LM' AND sptn >= 0 AND sptn < 10

In the first example, we had to specifically allow for the open parenthesis symbol, because spectral types based on photometric estimates are given between parentheses.

More advanced queries will require the users familiarizing themselves with JOIN or LEFT JOIN statements in SQL. Note that JOIN is equivalent to INNER JOIN, and LEFT JOIN is equivalent to LEFT OUTER JOIN.

One such examples that would require a JOIN statement would be in the query of all available radial velocity measurements for a specific star. In this example, we will query the star AU Mic, by first resolving its name with the mechanics_all_designations table. The table will allow us to obtain the moca_oid of AU Mic, and then we will join the radial velocities through the data_radial_velocities table. Note that the radial_velocities table will contain many entries associated with AU Mic, and therefore the single row returned by the first start of the query (to resolve the AU Mic name) will get duplicated as many times as there are radial velocities. In this example I have assigned table aliases for ease of use, which I always tend to do when I start joining tables together:

SELECT drv.*
FROM mechanics_all_designations AS mdes
JOIN data_radial_velocities AS drv USING(moca_oid)
WHERE mdes.designation='AU Mic'

The USING statement allows the database to know what column should be used to connect the two tables together. It can only be used when the two columns have the same name, or when the match is unambiguous, but a more specific way of doing the exact same thing would be:

SELECT drv.*
FROM mechanics_all_designations AS mdes
JOIN data_radial_velocities AS drv ON(mdes.moca_oid=drv.moca_oid)
WHERE mdes.designation='AU Mic'

Similarly, one may want to obtain the publication details for each radial velocity measurement, and not just the MOCA-specific publication identifier (moca_pid), with a chained JOIN:

SELECT drv.*, mpub.*
FROM mechanics_all_designations AS mdes
JOIN data_radial_velocities AS drv ON(mdes.moca_oid=drv.moca_oid)
JOIN moca_publications AS mpub ON(drv.moca_pid=mpub.moca_pid)
WHERE mdes.designation='AU Mic'

This would also allow to order the table by publication date, for example, from the most recent to oldest papers:

SELECT drv.*, mpub.*
FROM mechanics_all_designations AS mdes
JOIN data_radial_velocities AS drv ON(mdes.moca_oid=drv.moca_oid)
JOIN moca_publications AS mpub ON(drv.moca_pid=mpub.moca_pid)
WHERE mdes.designation='AU Mic'
ORDER BY mpub.pubdate DESC

Or the data could be sorted by measurement epoch instead:

SELECT drv.*, mpub.*
FROM mechanics_all_designations AS mdes
JOIN data_radial_velocities AS drv ON(mdes.moca_oid=drv.moca_oid)
JOIN moca_publications AS mpub ON(drv.moca_pid=mpub.moca_pid)
WHERE mdes.designation='AU Mic'
ORDER BY drv.epoch DESC

Additional examples of advanced database queries can be found on the mocapy GitHub page.