MARVELS Catalogs and Examples

This page gives some brief instructions on how to use the CasJobs webpage to retrieve some commonly requested sets of data from the CAS database. Example SQL queries for both DR11 and DR12 are given below.

Accessing CasJobs

Go to the CasJobs webpage (https://skyserver.sdss.org/casjobs/), login with your username and password, and then click on the Query tab on the menu bar.  This allows you to type in SQL queries to search the database for information on MARVELS targets.

In the Query tab, pick the Context drop-down menu, and select the appropriate data set (e.g., DR11Collab  or DR12Collab).

Type in a query.  Some frequently used examples are shown below.  To get the results to appear at the bottom of the page, just press the Quick button in the upper right.  To get the results to appear in a database table of your own, click the Submit button in the upper right (then you will have to access your results under the MyDB tab).

Note that since the DR11 and DR12 data are loaded in different tables in the database, the instructions for the two data releases are slightly different. For each type of request in the list below, syntax is shown to perform the request for DR12, then for DR11.

DR12 SQL Examples

In order to query the DR12 data, be sure that in the Query tab of CasJobs you have selected the DR12Collab from the Context drop-down menu. In each example, there will be a query you can paste this into the query area at https://skyserver.sdss.org/CasJobs/SubmitJob.aspx.  You can use the “Quick” button near the right-hand side to return the results at the bottom of the screen.

How to get the RVs for a single star if you know the star’s name already.

Here is a query to find the RV’s for all beams and all years for GSC-00122-00066.  [Recall that there are two simultaneous spectra taken for each star, which are referred to as beams 1 and 2.]

Query:

SELECT starname,fcjd,rv,photonerr,staterror,offseterror,specno,survey
FROM marvelsVelocityCurveUF1D
WHERE starname LIKE 'GSC-00122-00066_%'

Results:

starname fcjd rv photonerr staterror offseterror specno survey
GSC-00122-00066_BEAM1 2454842.814583 -1134.15 130.88 390.84 245.04 89 year12
GSC-00122-00066_BEAM1 2455105.928588 -189.38 100.83 348.96 245.04 89 year12
GSC-00122-00066_BEAM1 2455106.944271 -318.54 80.61 0.62 245.04 89 year12
GSC-00122-00066_BEAM1 2455116.983935 42.49 81.74 129.01 245.04 89 year12
GSC-00122-00066_BEAM1 2455135.822986 -1021.2 96.51 186.11 245.04 89 year12

In the above query, the % symbol means to find any pattern (including BEAM1 or BEAM2);  note the % symbol only works with the LIKE command, but not if you used the “=” operator instead of LIKE.

Search for stars with certain star properties (e.g., RA DEC/magnitude/Teff) (return a list of starnames)

Here is a query to find the stars with 4000<Teff<4500 from the targets monitored during the first two years of the survey.  Note that the query uses a JOIN command between the table containing the RV’s and the table containing the star properties.

SELECT DISTINCT s.twomass_name,s.starname,s.teff,uf1d.survey
FROM marvelsVelocityCurveUF1D AS uf1d
JOIN marvelsStar AS s ON s.starname = uf1d.starname
WHERE s.teff BETWEEN 4000 AND 4500
      AND uf1d.survey = 'year12' AND uf1d.beam = 1

Search for all stars which are in a certain catalog (e.g., give me all the HIP stars)

Here is a query to find all the stars with Hipparcos catalogue numbers from years 1 and 2 of the survey:

SELECT DISTINCT hip_name,starname
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND hip_name LIKE 'HIP%'

To find all the stars in the monitored sample with other catalogue numbers, instead of selecting hip_name, we could SELECT the twomass_name, gsc_name, or tyc_name instead, and adjust the corresponding WHERE statement.  Note that NOMAD names and UCAC names (used in years 3-4) are inside the GSC_NAME column.  Example queries for finding the stars in other catalogues are below.

SELECT DISTINCT gsc_name,starname
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND gsc_name LIKE 'GSC%'
SELECT DISTINCT tyc_name,starname
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND tyc_name LIKE 'TYC%'
SELECT DISTINCT twomass_name,starname
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND twomass_name LIKE 'TWOMASS%'
SELECT DISTINCT gsc_name,starname 
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND gsc_name LIKE 'NMD1%'
SELECT DISTINCT gsc_name,starname
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND gsc_name LIKE 'UCAC%'

These queries for stars within a certain catalog only involve a database table which is not specific to DR11 or DR12, so no specific DR12 syntax is required.

Get the RVs within a certain date range for one or multiple stars

SELECT starname,fcjd,rv,photonerr,staterror,offseterror,beam,survey
FROM marvelsVelocityCurveUF1D
WHERE fcjd BETWEEN 2454842 AND 2455521 AND
starname LIKE 'GSC-00122-00066_%'

For multiple stars typed manually into the query box:

SELECT starname,fcjd,rv,photonerr,staterror,offseterror,beam,survey
FROM marvelsVelocityCurveUF1D
WHERE fcjd BETWEEN 2455100 AND 2455220 AND
      (starname LIKE 'GSC-00122-00066_%' OR
starname LIKE 'GSC-02265-00167_%' OR
starname LIKE 'HIP-45384_%')

Get RVs for a list of stars’ names

If you have a list of starnames that you would like to get RVs for it is a fairly straightforward query. Just continue to add OR LIKE statements for each star.

SELECT starname,fcjd,rv,photonerr,staterror,offseterror,beam,survey
FROM marvelsVelocityCurveUF1D
WHERE fcjd BETWEEN 2455100 AND 2455220 AND
(starname LIKE 'GSC-00122-00066_%' OR
starname LIKE 'GSC-02265-00167_%' OR
starname LIKE 'HIP-45384_%'

For a list of multiple stars from a text file:

Create a text file with a star name on each row. The first line of the text file should be the header for that column (e.g. name1). See the example text file below. Go to the import tab in CAS. Type a name for your table (e.g. myStars) and select your file to import it into a new table.

Example text file for input list of star names:

name1
GSC-00122-00066
GSC-02265-00167
HIP-45384

Now that your star list is in a table you can select your stars from it.  The following query shows an example of getting all the beam 1 and beam 2 RV results, using a wildcard character.

SELECT a.starname,fcjd,rv,photonerr,staterror,offseterror,beam,survey
FROM marvelsVelocityCurveUF1D a
CROSS JOIN MyDB.myStars b
WHERE a.starname like b.name1+'_BEAM%'

Get RVs for stars within a certain range of properties

Suppose you want the RVs for a large number of stars where they all have certain properties.  Then, one can get the starnames and RV’s all in a single query, rather than obtaining a list of stars in one query, then getting the RV’s for those stars in a separate query.

SELECT DISTINCT s.twomass_name,s.starname,s.teff,uf1d.survey,uf1d.fcjd,uf1d.rv,uf1d.photonerr,
uf1d.staterror,uf1d.offseterror
FROM marvelsVelocityCurveUF1D AS uf1d
JOIN marvelsStar AS s ON s.starname = uf1d.starname
WHERE s.teff BETWEEN 4000 AND 4500
AND uf1d.survey = 'year12' AND uf1d.beam = 1

To associate PLATES, RADECID, and field names.

SELECT DISTINCT radecid,plateid,object
FROM marvelsVelocityCurveUF1D
ORDER BY radecid

DR11 SQL Examples

In order to query the DR11 data, be sure that in the Query tab of CasJobs you have selected the DR11Collab from the Context drop-down menu.In each example, there will be a query you can paste this into the query area at https://skyserver.sdss.org/CasJobs/SubmitJob.aspx.  You can use the “Quick” button near the right-hand side to return the results at the bottom of the screen. Note that the starname column in the DR11 table named marvelsVelocityCurveDFDI includes _BEAM1 or _BEAM2 appended to the starname.

How to get the RVs for a single star if you know the star’s name already.

Query:

SELECT starname,fcjd,rv,rverr,beam,survey
FROM marvelsVelocityCurveDFDI
WHERE starname LIKE 'GSC-00122-00066_%'

Results:

starname fcjd rv rverr beam survey
GSC-00122-00066_BEAM1 2454842.814583 3183.1 90.09 1 year12
GSC-00122-00066_BEAM1 2455105.928588 3331.73 79.28 1 year12
GSC-00122-00066_BEAM1 2455106.944271 3248.4 46.85 1 year12
GSC-00122-00066_BEAM1 2455116.983935 3349.11 51.85 1 year12
GSC-00122-00066_BEAM1 2455135.822986 3317.73 69.85 1 year12

In the above query, the % symbol means to find any pattern (including BEAM1 or BEAM2);  note the % symbol only works with the LIKE command, but not if you used the “=” operator instead of LIKE.

Search for stars with certain star properties (e.g., RA DEC/magnitude/Teff) (return a list of starnames)

Here is a query to find the stars with 4000<Teff<4500 from the targets monitored during the first two years of the survey.  Note that the query uses a JOIN command between the table containing the RV’s and the table containing the star properties.

SELECT DISTINCT  s.twomass_name,s.starname,s.teff,dfdi.survey
FROM marvelsVelocityCurveDFDI AS dfdi
JOIN marvelsStar AS s ON s.starname = dfdi.starname
WHERE s.teff BETWEEN 4000 AND 4500
      AND dfdi.survey = 'year12' AND dfdi.beam = 1

Search for all stars which are in a certain catalog (e.g., give me all the HIP stars)

Here is a query to find all the stars with Hipparcos catalogue numbers from years 1 and 2 of the survey:

SELECT DISTINCT hip_name,starname
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND hip_name LIKE 'HIP%'

To find all the stars in the monitored sample with other catalogue numbers, instead of selecting hip_name, we could SELECT the twomass_name, gsc_name, or tyc_name instead, and adjust the corresponding WHERE statement.  Note that NOMAD names and UCAC names (used in years 3-4) are inside the GSC_NAME column.  Example queries for finding the stars in other catalogues are below.

SELECT DISTINCT gsc_name,starname
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND gsc_name LIKE 'GSC%'
SELECT DISTINCT tyc_name,starname
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND tyc_name LIKE 'TYC%'
SELECT DISTINCT twomass_name,starname
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND twomass_name LIKE 'TWOMASS%'
SELECT DISTINCT gsc_name,starname 
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND gsc_name LIKE 'NMD1%'
SELECT DISTINCT gsc_name,starname
FROM marvelsStar
WHERE starname LIKE '%BEAM1' AND gsc_name LIKE 'UCAC%'

These queries for stars within a certain catalog only involve a database table which is not specific to DR11 or DR12, so no specific DR12 syntax is required.

Get the RVs within a certain date range for one or multiple stars 

SELECT starname,fcjd,rv,rverr,beam,survey
FROM marvelsVelocityCurveDFDI
WHERE fcjd BETWEEN 2454842 AND 2455521 AND
starname LIKE 'GSC-00122-00066_%'

For multiple stars typed manually into the query box:

SELECT starname,fcjd,rv,rverr,beam,survey
FROM marvelsVelocityCurveDFDI
WHERE fcjd BETWEEN 2455100 AND 2455220 AND
(starname LIKE 'GSC-00122-00066_%' OR
starname LIKE 'GSC-02265-00167_%' OR
starname LIKE 'HIP-45384_%')

Get RVs for a list of stars’ names

If you have a list of starnames that you would like to get RVs for it is a fairly straightforward query. Just continue to add OR LIKE statements for each star.

SELECT starname,fcjd,rv,rverr,beam,survey
FROM marvelsVelocityCurveDFDI
WHERE starname LIKE 'GSC-00122-00066_%' OR
      starname LIKE 'GSC-02265-00167_%' OR
starname LIKE 'HIP-45384_%'

For a list of multiple stars from a text file:

Create a text file with a star name on each row. The first line of the text file should be the header for that column (e.g. name1). See the example text file below. Go to the import tab in CAS. Type a name for your table (e.g. myStars) and select your file to import it into a new table.

Example text file for input list of star names:

name1
GSC-00122-00066
GSC-02265-00167
HIP-45384

Now that your star list is in a table you can select your stars from it.  The following query shows an example of getting all the beam 1 and beam 2 RV results, using a wildcard character.

SELECT a.starname,fcjd,rv,rverr,beam,survey
FROM marvelsVelocityCurveDFDI a
CROSS JOIN MyDB.myStars b
WHERE a.starname like b.name1+'_BEAM%'

Get RVs for stars within a certain range of properties 

Suppose you want the RVs for a large number of stars where they all have certain properties.  Then, one can get the starnames and RV’s all in a single query, rather than obtaining a list of stars in one query, then getting the RV’s for those stars in a separate query.

SELECT DISTINCT  s.twomass_name,s.starname,s.teff,dfdi.survey,dfdi.fcjd,dfdi.rv,dfdi.rverr
FROM marvelsVelocityCurveDFDI AS dfdi
JOIN marvelsStar AS s ON s.starname = dfdi.starname
WHERE s.teff BETWEEN 4000 AND 4500
AND dfdi.survey = 'year12' AND dfdi.beam = 1

To associate PLATES, RADECID, and field names.

SELECT DISTINCT radecid,plateid,object
FROM marvelsVelocityCurveDFDI
ORDER BY radecid