CasJobs Advanced Queries

Using variables in your query

You can declare variables in your query buffer and use them in your query using the SQL declare statement. All variable names must be preceded by the '@' character. An example of a query using variables is shown below (courtesy of Dimitri Pourbaix).

declare @BRIGHT bigint set @BRIGHT=dbo.fPhotoFlags('BRIGHT')
declare @EDGE bigint set @EDGE=dbo.fPhotoFlags('EDGE')
declare @SATURATED bigint set @SATURATED=dbo.fPhotoFlags('SATURATED')
declare @NODEBLEND bigint set @NODEBLEND=dbo.fPhotoFlags('NODEBLEND')
--
declare @bad_flags bigint set
@bad_flags=(@SATURATED|@BRIGHT|@EDGE|@NODEBLEND)

select run,rerun,camcol,field,obj,colc,rowc,parentID,nChild,ra,dec,
    extinction_r,psfMag_u,psfMag_g,psfMag_r,psfMag_i,psfMag_z,
    psfMagErr_u,psfMagErr_g,psfMagErr_r,psfMagErr_i,psfMagErr_z,rowv,
    colv,rowvErr,colvErr,rowc_u,colc_u,rowc_g,colc_g,rowc_r,colc_r,
    rowc_i,colc_i,rowc_z,colc_z,offsetRa_u,offsetDec_u,offsetRa_g,
    offsetDec_g,offsetRa_r,offsetDec_r,offsetRa_i,offsetDec_i,
    offsetRa_z,offsetDec_z
into MyDB.CID
from Star
where (flags & @bad_flags) = 0 and nChild=0
    and psfMag_u>0 and psfMag_u<21 and psfMag_g>0 and psfMag_g<21
    and abs(psfMagErr_u)<=0.1 and abs(psfMagErr_r)<=0.1
    and abs(psfMagErr_g)<=0.05 and abs(psfMagErr_i)<=0.05
    and abs(psfMagErr_z)<=0.05
    and sqrt((offsetRa_u-offsetRa_z)*(offsetRa_u-offsetRa_z)
        *cos(dec*0.01745)*cos(dec*0.01745)
        +(offsetDec_u-offsetDec_z)*(offsetDec_u-offsetDec_z))>=0.5
Adding an INT enumerator column to your MyDB table

You can add an int enumerator easily as follows, by making a new table to copy your existing table into along with the new enumerator column:
create table table2 (
    int_id int identity(1,1),
    ra float,
    dec float,
    objid bigint
)
insert table2( ra,dec,objid)
select ra,dec,objid
from table1

You shd run this in the MyDB context. table2 will then have an int_id field filled with consecutive integers.
Compound Queries with GO statement

Starting with v2_9_1, you can submit compound queries in the query buffer, i.e., more than one query in the buffer, separating individual queries with the GO statement. The GO statement tells the server to immediately execute that query before going on to the next one. This allows, for example, a table to be created or deleted before running a query that writes data to it.

The first example of a compound query is shown below. It must be run in the MYDB context because it is not possible to drop a table remotely in another database, so the drop table cannot be run in the non-MYDB context.

drop table mytable_2
go

select top 10 objid, ra, dec into mytable_2
from dr5.photoobj -- remotely gets data from DB mapped to DR5 context
go

A more complex compound query is shown in the example below (courtesy of Robert Lupton). This query should be run in the DRx context (DR4, DR5 etc.).

SELECT DISTINCT
   matchHead objId
INTO
   MYDB.tmp1
FROM
   match
WHERE
   objId1 IN (select objId from
   dbo.fGetObjFromRect(50.427362,50.527362,-0.710444,-0.610444))
GO -- this tells CasJobs to run this query separately first

SELECT TOP 1
   dbo.fSDSS(MH.objId) AS UID,
   dbo.fSDSS(M.objId2) AS ID,
   ltrim(str(60*distance, 12, 2)) AS distance,
   mjd_r,
   psfMag_r, psfMagErr_r,
   dbo.fPhotoTypeN(obj.type) AS type
INTO MYDB.tmp2
FROM
   MYDB.tmp1 MH
   JOIN (
      SELECT objId1, objId1 AS objID2,
         -1/60. AS distance, matchHead FROM match
      UNION all
      SELECT objId1, objID2, distance, matchHead FROM match
    ) AS M ON (MH.objID = M.matchHead)
   JOIN photoObj AS Obj ON (obj.objId = M.objID2)
   JOIN field AS F ON (F.fieldId = obj.fieldId)
WHERE
   0 = (flags & 0x800000040006)
ORDER BY MH.objId
On Neighbors Search
The neighbors search query is a macro from the MYDB page that searches for objects around every object in a table, given a certain radius. It is accessable by clicking on any table in your MYDB with columns named 'ra' and 'dec', then clicking the search button. By default, it returns only the objid of each object matched. Below are a few examples of modifying this query to return more information.
These examples must be followed as closely as possible. You can substitute your own names for the MyDB tables involved, but everything else, especially the sequence, contexts etc. must be followed exactly as described below.

Fixed Radius Search

This example shows how to do a fixed radius (same search radius for all objects) neighbors search and add extra columns to the result.

a) First, create a new table in your MYDB (select context MYDB and run a command like the following), in this example it is called MyTable_34, but you can call it whatever you want:

CREATE TABLE MyTable_34 (
objid bigint,
ra float,
dec float,
search_id int,
matched_id bigint,
z real
);

Note that this table includes the extra spec column "z". Add whatever spec columns you want to the end of this table.

b) Then get the neighbor query by running the neighbor search with your upload file and copying the query in the Query window. Paste that query in a new query buffer in context DR3. Then modify it as in the following example:

CREATE TABLE #UPLOAD(
up_ra FLOAT,
up_dec FLOAT,
up_id int
)
INSERT INTO #UPLOAD
SELECT RA AS UP_RA,DEC AS UP_DEC,search_id AS UP_ID
FROM MYDB.MyTable_32

CREATE TABLE #tmp (
up_id int,
objid bigint
)

INSERT INTO #tmp
EXEC spgetneighbors 1
INSERT INTO MYDB.MyTable_34
select a.*,t.objid as matched_id, s.z from #tmp t, MYDB.MyTable_32 a,
specobj s
where t.up_id = a.search_id and s.bestobjid=t.objid

Note that the MyTable name has been manually set to MyTable_34, added "s.z" to the select list (again, add more columns here if you need), "specobj s" to the from, and "and s.bestobjid=t.objid" to the where. You need to run this query in the DR3 context using the Submit button (wont work with Quick).

Variable Radius Search

Here is a way to do a proximity search in casjobs on a list of ra,dec pairs with variable search radius. You should have an ra,dec table (called xrayradii in this example, but you can call it what you want). For spGetNeighborsRadius, you have to add another column up_rad to the #upload table. Your ra,dec MyDB table needs to have at least the columns that are in the first SELECT statement in b) below (ra,dec,xrayradius and cluster in this example). Note that this requires that xrayradii.cluster is an int id that identifies the cluster.

a) Create the table to hold the results:

CREATE TABLE MyTable_45 (
ra float,
dec float,
rad float,
cluster int,
objid bigint,
);

b) Run the neighbors search for variable radius, saving results into table created in a):

CREATE TABLE #UPLOAD(
up_ra FLOAT,
up_dec FLOAT,
up_rad FLOAT,
up_id int
)
INSERT INTO #UPLOAD
SELECT ra AS UP_RA,
dec AS UP_DEC,
xrayradius as UP_RAD,
cluster AS UP_ID
FROM MYDB.XrayRadii

CREATE TABLE #tmp (
up_id int,
objid bigint
)
INSERT INTO #tmp
EXEC spGetNeighborsRadius

INSERT INTO MYDB.MyTable_45
SELECT a.*, t.objid
FROM #tmp t, MYDB.XrayRadii a
WHERE
t.up_id = a.cluster
Cursors and Table-valued Functions
In order to use variables with table-valued functions like fGetNearestObjEq, you need to define a cursor and call the function from within the cursor body. A cursor is basically a loop on each row that matches a given SELECT statement. Here MyTable_71 is created in a separate casjobs query in the MYDB context as:

CREATE MyTable_71 (
regionid bigint not null,
type varchar(16) not null
)

MyTable_70 contains ra,dec columns and can be imported or created with another casjobs query, to get the ra,dec from phototag, for example. You can even use a table in the non-MyDB context instead of a MyDB table, i.e., you can define the cursor directly on the DR5 phototag table, for instance. fGetNearestObjEq is a table-valued function that returns certain properties of the nearest object to the given ra,dec as a table. Finally, you run your cursor query in the DR5 context:

declare @ra float, @dec float;

DECLARE my_cursor cursor read_only
FOR
SELECT ra,dec FROM MYDB.MyTable_70
-- this could be something like:
--    "SELECT TOP 100 ra,dec FROM Star", for instance
OPEN my_cursor
--
WHILE(1=1)
BEGIN
  FETCH NEXT from my_cursor into @ra, @dec
  IF (@@fetch_status < 0) break
  INSERT MYDB.MyTable_71
  SELECT * FROM
dbo.fGetNearestObjEq(@ra,@dec,1.5)
END
--------------------
-- close the cursor
--------------------
CLOSE my_cursor
DEALLOCATE my_cursor

You will need to run this in the long queue (with the Submit button, not Quick).
Putting Thumbnails in your table
Since casjobs is all on the web you may link up the image tools to colums in your table. The following query adds thumbnail links to the objects in specobjall .
select top 10 s.specobjid, s.z as sdssz ,s.ra,s.dec,
'<a href=http://cas.sdss.org/dr3/en/tools/chart/navi.asp?ra='+
cast(s.ra as varchar(10))+
'&dec='+cast( s.dec as varchar(10)) +
'>'+
'<img src="http://casjobs.sdss.org/ImgCutoutDR3/getjpeg.aspx?ra='
+cast(s.ra as varchar(15))+
'&dec='+cast(s.dec as varchar(15))+
'&scale=0.40&width=120&height=120&opt="/> '
as pic
from specphotoall s

Help

Using variables in your query

Adding an INT enumerator column to your MyDB table

Compound Queries with GO statement

On Neighbors Search

Fixed Radius Search

Variable Radius Search

Cursors and Table-valued Functions

Putting Thumbnails in your table