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

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.

A simple 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
Nearest Neighbor Search (Cross-ID): Table-valued Functions without Cursors

This is an example of using a table-valued function (functions that return a table of values rather than a single scalar value) in a query and applying its results to a query on another table. In this case we do a nearest neighbor search, and we use the CROSS APPLY and OUTER APPLY SQL constructs. You can use the same prescription to apply the results of other table-valued functions.

Assume that you have already created or imported a MyDB table called MyRaDecPairs that contains a list of ra,dec pairs for the locations that you want to find neighbors. The table also has an "id" as the first column that is either an enumerator or some other unique id for each point. The first version of the nearest neighbor query returns only the matching SDSS objects, whereas the second version (using the OUTER APPLY instead of CROSS APPLY) also returns non-matches.

Version 1:

SELECT
   m.id, m.ra AS ra1, m.dec AS dec1,
   n.objid, n.distance,
   o.ra AS ra2, o.dec AS dec2
FROM MyDB.MyRaDecPairs AS m
   CROSS APPLY dbo.fGetNearestObjEq( m.ra, m.dec, 0.5) AS n
   JOIN PhotoObj AS o ON n.objid=o.objid

Version 2:

SELECT
   m.id, m.ra AS ra1, m.dec AS dec1,
   n.objid, n.distance,
   o.ra AS ra2, o.dec AS dec2
FROM MyDB.MyRaDecPairs AS m
   OUTER APPLY dbo.fGetNearestObjEq( m.ra, m.dec, 0.5) AS n
   LEFT JOIN PhotoObj AS o ON n.objid=o.objid

The second version will give output of the form:

id ra1 dec1 objid distance ra2 dec2
1 180 -0.5 1237648720693888083 0.188067537809051 180.000328568273 -0.496882808306584
2 170 -0.15 1237648721226367920 0.257270246781359 169.999215045568 -0.15421537671495
3 90 -20.5 null null null null
4 110 30.5 1237673738862461148 0.157046405351005 110.001118266903 30.5024336440048
5 300 -80.5 null null null null
6 230 -70.5 null null null null
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://skyservice.pha.jhu.edu/dr8/ImgCutout/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
On Neighbors Search
NOTE: This is an older way to do neighbors search, see the Nearest Neighbor Search example above using CROSS APPLY for the fastest way to do this type of 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
  JOIN MYDB.MyTable_32 a ON t.up_id = a.search_id
  JOIN specobj s ON 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
  JOIN MYDB.XrayRadii a ON t.up_id = a.cluster

Nearest Neighbor Search

Look at the Nearest Neighbor Search example above to see how to do a nearest neighbor search, i.e. to fetch only the one nearest match for each position in the input list.
Cursors and Table-valued Functions
NOTE: This is the old way to work with table-valued functions, see the example above using CROSS APPLY for the new (and much faster) way to do this.
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).

NADC CasJobs

Help

Using variables in your query

Adding an INT enumerator column to your MyDB table

Compound Queries with GO statement

Nearest Neighbor Search (Cross-ID): Table-valued Functions without Cursors

Putting Thumbnails in your table

On Neighbors Search

Fixed Radius Search

Variable Radius Search

Nearest Neighbor Search

Cursors and Table-valued Functions