Home Help GOHelp Tools Create Account Login   Not Logged in

Help

Variables
Enumerator column
Compound Queries
Neighbors Search
Cursors/Table Functions
Thumbnails
  1. 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


  2. 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.

  3. 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

  4. 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


  5. 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).


  6. 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