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