Accessing the CasJobs Tables

The best way to use the BCS catalogs is via MAST's GALEX CasJobs page. After registering with a free account, users can perform SQL queries on MAST's database tables, including the GALEX source catalogs. These queries, and the resultant output tables, can be stored in CasJobs for future use and retrieval, you can export tables out of CasJobs in CSV or FITS table format, and you can execute much larger and more powerful search queries than are possible with the standard MAST web search interfaces.

You can find documentation and examples in the Help section from within CasJobs. Here we provide a quick explanation of the available BCSCAT tables and how you might interact with them. First and foremost, the relevant tables are all found under the GALEX_Catalogs Context in CasJobs. This is one of the most common mistakes for new users, so make sure your Context drop-down menu has "GALEX_Catalogs" selected when in the Query tab before running your SQL commands.

The main source catalogs are stored (from within the GALEX_Catalogs Context) in tables called "bcscat_ais" and "bcscat_mis". These tables have many columns, including the NUV and FUV fluxes of the sources, coordinates, flux uncertainties, etc. The first column in these tables is a unique ID within CasJobs called the "casjobsID". It is a negative integer, and is important when using the built-in CasJobs "neighbors" function to do spatial cross-matching. More on that below. The second column, "objid", is the GALEX ID for that source, and can be used to refer to specific objects in publications, websites, etc. MAST does not recommend using the "casjobsID" as a source identification, because it is an internal reference specific to CasJobs.

The following query from CasJobs will give the objID, RA, DEC, FUV mag, and NUV mag for the 10 brightest NUV sources in the BCS MIS catalog, and runs in about a second:

	select top 10 objid, ra, dec, fuv_mag as fuv, nuv_mag as nuv
	from bcscat_mis
	order by nuv_mag
	

	objid		     ra		       dec		  fuv	    nuv
	2938952937106317944  229.637734037928  2.07963861057808	  -999	    9.843625
	6400109988228895130  259.284172689432  43.1365818054315	  -999	    9.903237
	2863095430882787329  323.364510629287  -0.822028042642919 -999	    10.09208
	6404191375393292289  71.507041425674   11.7052595237515	  13.68527  10.81764
	6402784000507643670  312.312678571981  14.6079848702796	  -999	    10.87608
	2935927081106678724  217.459741584368  0.832047995775364  -999	    10.98587
	2928327256735489068  134.534500051645  1.54361639753197	  -999	    10.99865
	2917771945108839284  17.0928832178547  5.65036279127825	  -999	    11.01779
	6407393153253376001  118.337503704343  28.0516450137997	  11.34516  11.02875
	2906337024179964349  326.100077319661  -4.7313179680575	  -999	    11.0446
	

CasJobs has a built-in, spatial cross-match function called "Neighbors" that allows you to quickly cross-match a table in your database area ("MyDB" Context) against MAST tables in other Contexts. In this section, we will present step-by-step instructions on how to upload a table of targets into MyDB, cross-match against GALEX catalogs, and then interact with the resulting tables.

Step 1: Upload your table of targets.

Upload your table of targets into CasJobs. First, create a comma-separated text file with at least three columns: "id", "ra", and "dec". The column labels should be lowercase, and the coordinates must be in decimal degrees. In our example, we will use a partial list of white dwarf stars from the McCook-Sion catalog, where the first column is the star's "WD" ID. Note that you can include other columns in addition to the RA and DEC (here our fourth column consists of strings representing the white dwarf type). You can download a copy of this file yourself if you'd like to follow along.

After signing into CasJobs, click on the "Import" link on the top menu. Make sure "Import into..." is set to "New Table". Below is a text box where you can enter the name of the table you'd like to create on import. By default this is "MyTable". It is suggested you change the name to something more descriptive. In our example, we will change it from "MyTable" to "McCookSion_WDSample". Make sure "Format" is set to "Comma/Space/Tab Separated". Make sure "Data Type" is set to "File Import" (if you have a file on disk ready to upload), or choose "Copy & Paste" if you want to copy a table directly into a text editor. In our example we will use the "File Import" option, and press "Choose File" to select the file on our local machine's disk. When ready, press the "Import" button.

Step 2: Perform a cross-match and create a new "neighbors" table.

Your table is now uploaded into CasJobs and available from the "MyDB" Context. To see your uploaded table, go to the "MyDB" tab in the top menu, and make sure the drop-down menu on the top left of the page is set to "MyDB". Your table should be available on the left side of the page. Next, click on the uploaded table's name. This provides some additional content on the right side of the page: The name of the table, a count on the total number of rows, some additional options like "Sample", "Job", "Download", etc. For this specific tutorial, we are interested in the "Neighbors" option. This option is in small, white text (along with several others) above the larger "Table Schema" text on the right side of the page (see figure below). Click on "Neighbors" and the Neighbor Search options will appear below. Here, you can specify which Context you would like to cross-match to (in our example, we will be selecting the "GALEX_Catalogs" Context) and a search radius in arcminutes (here, we use 0.05 arcminutes). Finally, you can enter the name of the output table into which you want to store your results. In our example, we will enter "McCookSion_WDSample_NB" as the name of our output table, where "NB" just stands for "neighbors". Hit "Go" when ready.

The Neighbors function is accessed from MyDB after clicking on the name of one of your tables. You must have columns called "ra" and "dec" (in lowercase), with coordinates in decimal degrees, for the Neighbors function to work.

Step 3: Examine the resultant neighbors table.

There are two important actions done by the Neighbors function to be aware of. First, your original input table will have a new column added to it, called "search_id". This new column is added to the right of the table, and is simply the row number of your uploaded table. The Neighbors function needs this "search_id" column in your table to be able to link the output table's rows to your input table's rows. The second important action is that it creates an output "neighbors table" in MyDB. In our example, we used the output name of "McCookSion_WDSample_NB", and this table is now available in the MyDB list of tables on the left side of the page. The "neighbors table" contains the columns from your original input table, the "search_id" column it added, and a "matched_id". This "matched_id" corresponds to the "casjobsID" in the various catalog tables in the "GALEX_Catalogs" Context. They are unique, negative integers across all catalogs in the "GALEX_Catalogs" Context. For example, BCS AIS sources will be in the range of -4000000000 ≤ casjobsID < -5000000000, and BCS MIS sources will range from -5000000000 ≤ casjobsID < -6000000000. Sources from the GCAT catalogs of Seibert et al. will have similar sets of allowed "casjobsID" values. This makes it easy for users to get all flux measurements for their given sources, or, to select flux measurements from specific source catalogs by applying some very basic filters.

In our example, we see that the "McCookSion_WDSample_NB" neighbors table has 87 rows, while the original input catalog had 998 rows. Clearly there were some targets with no match in the GALEX catalogs, which we might have suspected. If we click on the "Sample" option to preview the first few rows (see figure below), we can see the first four columns are our original input columns, then the "search_id" added by the Neighbors function, and finally the "matched_id" equal to the "casjobsID" in the GALEX catalogs. We can see some targets have matches in multiple catalogs (BCS and GCAT; for example, WD_2321-549). The inclusion/exclusion in the various catalogs could be due to the different UV brightness of the targets (for AIS vs MIS), or the sky coverage of the different catalogs.

The resultant "neighbors table" includes the "search_id" and "matched_id" added by the Neighbors function. Some targets will be in multiple catalogs, for example, WD_2321-549 is in BCS and GCAT catalogs.

Step 4: Use the resultant neighbors table.

The "neighbors table" you've just created ("McCookSion_WDSample_NB" in our example) provides the link between your input table and the catalogs in the "GALEX_Catalogs" Context. You can now interact with this table via SQL commands as you would any other table in CasJobs. Probably the most popular next step would be to write some simple SQL queries to pull some (or all) of the columns from one (or more) of the GALEX source catalogs for your targets that had matches. Below we provide two examples of how one might do this. The first gives all columns from all the GALEX source catalogs for the matching white dwarfs. The second gives only a few critical columns from just the BCS AIS catalog.

In this first example, we write a SQL query that will get all columns from all BCS GALEX source catalogs for our matches. To do that, we perform an Inner Join on the "matched_id" column from a View called "bcscat". This View is just a combination of all the BCS catalogs (BCS AIS and BCS MIS) in the "GALEXCatalog" Context. Since the full query would take up too much memory to run in the "Quick" option, we only get the first 10 rows from this query and output them into MyDB as a new table. The SQL command is:

	select top 10 bcscat.*
	from mydb.McCookSion_WDSample_NB as nb
	into MyDB.McCookSion_WDSample_First10
	inner join bcscat on nb.matched_id=bcscat.casjobsid
	

This results in the table shown below. You will notice that, in addition to the "casjobsID", a column containing the catalog each row comes from ("catalogSource") is included in the "bcscat" View, and, consequently, in our output table (for our example, it is in the first column).

This sample query performs an Inner Join between the McCookSion_WDSample_NB "neighbors table" and the "bcscat" View, giving all columns from all BCS source catalogs. Only the first 10 rows are returned in the example query, and the output table is made in our MyDB context. One usually applies additional filters on the catalog columns to extract specific targets of interest.

In this second example, we write a SQL query that will get our original input columns (WD ID, RA, DEC, WD type), along with the objid, mag_fuv, and mag_nuv for our matched white dwarfs from just the BCS_AIS catalog matches. We apply a sorting filter that sorts in order of descending FUV mag, so that targets with both FUV and NUV fluxes will come first in the table (missing FUV fluxes are given a value of -999 in the BCS catalogs). There are several ways one could do this, for example, you could run the same query as above, and then implement a "where" filter to select only those with catalogSource == "AIS", or apply a limit on the range of casjobsID values. Instead, for this example, we will edit our query to perform the Inner Join only on the BCS AIS table, as opposed to the combined View as before:

	select nb.wd_id, ra=nb.ra, dec=nb.dec, nb.wd_type, objid, fuv_mag as fuv, nuv_mag as nuv
	from mydb.McCookSion_WDSample_NB as nb
	inner join bcscat_ais on nb.matched_id=bcscat_ais.casjobsid
	order by fuv_mag DESC
	

This query is fairly complex for beginners, so we'll break down each line a bit:

• Line 1: We specify the columns we want to retrieve. To make it clear to the query that we want to retrieve the WD ID, RA, DEC, and WD TYPE from our input table ("McCookSion_WDSample_NB") rather than columns in the BCS catalog tables, we use the assignment operator (e.g., ra=nb.ra, as opposed to ra=gcat_asc.ra). This is especially important if your input table has columns with the same name as the table you are joining, in this example, both tables have columns called "ra" and "dec". We do not bother with the assignment operator for "objid", "fuv_mag", or "nuv_mag", since those columns are only present in the "bcscat_ais" table, and SQL can determine which table to use in those cases. Finally, we employ a little column renaming so that our results table will have "fuv_mag" and "nuv_mag" appear as just "fuv" and "nuv" in the column names.
• Line 2: To improve readability and handle some quirks with CasJobs SQL interpretation, we assign "mydb.McCookSion_WDSample_NB" to the alias "nb". This allows us to just use "nb" where we would have used the full table name. Think of this as just shorthand notation, and everywhere you see "nb", it's referring to our "McCookSion_WDSample_NB" neighbors table..
• Line 3: This line contains the Inner Join command. Here, we specify that we want to join with the BCS AIS catalog table ("bcscat_ais"). We want to use the "matched_id" and "casjobsid" columns as the common column between both tables (i.e., "matched_id" in our neighbors table is the same as "casjobsid" in the bcscat_ais table, so use that for identifying matched rows).
• Line 4: We apply a sorting filter on our results. In this example, we want those white dwarfs that have both an FUV and NUV flux measurement to appear at the top of the table. Since the BCS AIS table uses -999 to refer to missing fluxes, we achieve our desired effect by sorting in descending order on the FUV magnitude column.

With just a bit more specificity, you can create tables that are ready to immediately ingest into your science scripts, no time wasted on writing your own catalog matching and table output routines. These queries can be done in seconds once you have a basic understanding of SQL and the MAST tables in CasJobs.

Calculating Distances

There is a built-in CasJobs function that allows for calculation of (spherical) distances between sources. The example below shows how to calculate angular separations between matches of the input McCook-Sion white dwarf database with the BCSCat, as well as those input targets that do not have a BCSCat match. Make sure your Context menu is set to "GALEX_Catalogs".

	select McCookSion_WDSample.search_id,
	cast((dbo.fDistanceArcMinEq(nb.ra, nb.dec, bcscat.ra, bcscat.dec)*60.0) as numeric(20,6))
	as dstArcSec,bcscat.*
	from mydb.McCookSion_WDSample as McCookSion_WDSample 
	left outer join mydb.McCookSion_WDSample_NB as nb
	on McCookSion_WDSample.search_id=nb.search_id
	left outer join bcscat on nb.matched_id=bcscat.casjobsid
	

In contrast, this query returns only those white dwarf targets that have BCSCat matches.

	select McCookSion_WDSample.search_id,
	cast((dbo.fDistanceArcMinEq(nb.ra, nb.dec, bcscat.ra, bcscat.dec)*60.0) as numeric(20,6))
	as dstArcSec,bcscat.*
	from mydb.McCookSion_WDSample as McCookSion_WDSample
	inner join mydb.McCookSion_WDSample_NB as nb
	on McCookSion_WDSample.search_id=nb.search_id
	inner join bcscat on nb.matched_id=bcscat.casjobsid