ATLAS-REFCAT2

The below examples show SQL commands you can enter in the "Query" tab once you sign into CasJobs.

Tutorial 1: Converting "Contrib" Integers To BitMask Values

You can see the contents of the look-up table by issuing this query:

select * from grizContribFlags

This query should result in a table that looks like the following:

name                             hexadecimal      value       description
-------------------------------- ---------------- ----------- ----------------------------------------------
Gaia DR2                          0x00000000      0           Gaia DR2 contributed to griz magnitude
GMP                               0x00000001      1           GMP contributed to griz magnitude.
Pan-STARRS                        0x00000002      2           Pan-STARRS contributed to griz magnitude.
SkyMapper                         0x00000004      4           SkyMapper contributed to griz magnitude.
Pathfinder                        0x00000008      8           Pathfinder contributed to griz magnitude.
APASS                             0x00000010      16          APASS contributed to griz magnitude.
APASS DR9                         0x00000020      32          APASS DR9 contributed to griz magnitude.
Tycho-2/BSC                       0x00000040      64          Tycho-2/BSC contributed to griz magnitude.

You can translate the various "Contrib" values to see which bits were set with the following function:

;WITH cteExample
as
(
select top 100 
dbo.fGetgrizContribFlagsName(gContrib) as gContribs, 
dbo.fGetgrizContribFlagsName(rContrib) as rContribs, 
dbo.fGetgrizContribFlagsName(iContrib) as iContribs, 
dbo.fGetgrizContribFlagsName(zContrib) as zContribs,
gContrib, rContrib, iContrib, zContrib,
objid, RA, Dec, plx, dplx, pmra,
dpmra, pmdec, dpmdec, Gaia, dGaia, BP,
dBP, RP, dRP, Teff, AGaia, dupvar,
Ag, rp1, r1, r10, g, dg,
gchi, r, dr, rchi, i, di,
ichi, z, dz, zchi, nstat, 
J, dJ, H, dH, K, dK
from refcat2
)
select * from cteExample
order by zContrib desc

You can select sources that have a given bit set in a given "Contrib" column by doing something like the following, which uses a stored procedure called "fGetgrizContribFlagsValue" available in the ATLAS-REFCAT2 Context:

select top 10 dbo.fGetgrizContribFlagsName(gContrib) as gContribs,* 
from refcat2 
where gContrib & dbo.fGetgrizContribFlagsValue('GMP') > 0

Tutorial 2: Box Search Using RA and Dec

This search conducts a box search between an RA of 0.0 and 0.2 degrees and a Declination between +0.0 and +0.2 degrees on the sky.

select * from refcat2
where ra between 0 and 0.2
and dec between 0 and 0.2

Tutorial 3: Box Search Using RA and Dec. Via Stored Procedure

This search conducts a box search between an RA of 0.0 and 0.2 degrees and a Declination between +0.0 and +0.2 degrees on the sky using a stored procedure called "fGetObjFromRect". In most situations, it is likely faster to do the query directly. The parameter list for "fGetObjFromRect" is (ra1, ra2, dec1, dec2), all in degrees.

select r.*
from refcat2 as r
inner join fGetObjFromRect(0,0.2,0,0.2) as nb on r.objid=nb.objid

Tutorial 4: Cone Search Using RA and Dec. Via Stored Procedure

This search conducts a cone search centered on an (RA, Dec) coorindate of (0.0, 0.0) degrees using a 5 arcminute search radius. This makes use of the "fGetNearbyObjEq" stored procedure, which accepts as parameters the RA and Dec. of the search center in degrees, and a search radius (also in degrees). This query also sorts the return results by distance from the search center, so that objects closest to the cone search center are returned first.

select n.distance as dstDegrees, r.*
from fGetNearbyObjEq(0,0,5.0/60.0) as n
inner join refcat2 as r on n.objid=r.objid
order by n.distance

Tutorial 5: Cross-Match Against An Uploaded Table

If you upload a table containing targets with positions, you can perform a spatial cross-match (or any other cross-match on columns that contain the same info). This example shows how to perform a spatial cross-match. First, create an upload table that looks like this:

ID, RA, DEC
1, 1.58851, -0.07845
2, 5.53825, -1.51730
3, 6.33772, -12.34491

Upload this file into MyDB (for this tutorial, we will call it "MySources") using the Import tool in CasJobs. Then, run the following query to obtain the ATLAS-REFCAT2 catalog entries within a 1 arcminute radius of each source in your table (remember "fGetNearbyObjEq" uses a search radius in degrees):

select nb.distance as dstDegrees, nb.distance*3600.0 as dstArcSec,r.*
from MyDB.MySources s
cross apply fGetNearbyObjEq(s.ra,s.dec,1.0/60.0) nb
inner join refcat2 as r on nb.objid=r.objid

Tutorial 6: Cross-Match Against GALEX GR7

You can cross-match tables with any other database table available in MAST CasJobs. For instance, we show here how to cross-match ATLAS-REFCAT2 with GALEX GR7 (the last data release) to get candidate matches to add UV flux information. Note that this example requires setting the Context drop-down menu in the top left to "GALEX_GR6Plus7", because one can not call "fGetNearbyObjEq" by adding the Context to the function call. Also note that the "fGetNearbyObjEq" stored procedure in the GALEX Context accepts the search radius in arcminutes, not degrees. In this example, the match radius is set to 10 arcseconds in the call to fGetNearbyObjEq (converted to arcminutes as required by the stored procedure).

select top 100 r.*, p.objid as gobjid, p.nuv_mag, p.fuv_mag, nb.distance*60 as dstArcSec
from hlsp_atlas_refcat2.refcat2 as r
cross apply fGetNearbyObjEq(ra, dec, 10.0/60.0) as nb
inner join photoobjall as p on nb.objid=p.objid
order by objid, dstArcSec