/*

20queries.sql

Benchmarking (and other testing) queries for the SQLServer implementation
of the SSS.

Bob Mann & Nigel Hambly

*/

/*
Q1: Find all galaxies with a pixel brighter than the highest areal profile
threshold in any band within 1 degree [original was 1 arcminute] of a given point (0.0,0.0) 
in the sky.
	[this tests whether the 4th bit of the Quality flag is set]
*/
drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select m.objid
into ##results
from mergedcatalog as m
	join fGetNearbyObjEq(0.0,0.0,60,1) as near
	on m.objid=near.objid
where bestclass=1 -- galaxy
and ( 
	((qualb > 0 and qualb & 16 = 16) or (qualr1 > 0 and qualr1 & 16 = 16))
 or ((qualr2 > 0 and qualr2 & 16 = 16) or (quali > 0 and quali & 16 = 16))
)

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q01', 1, 1, @@RowCount 

-- Q01 cpu:     2.10 sec, elapsed:    22.66 sec, physical_io: 897 row_count: 317

/*
Q2: Find all galaxies with blue band area between 100 and 200 pixels, and
-10 < super galactic latitude (sgb) < 10, and declination less than zero.
	[surface brightness --> area]
*/
-- don't record positions in supergalactic coordinates at the moment, so have to 
-- do this one as a multi-stage process

-- first, perform the area and dec cuts and write results in ##results

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select m.objid,s.area,m.ra,m.dec
into ##results
from mergedcatalog as m, surveycatalog as s
where m.bestclass=1 -- galaxy in mergedcatalog
and s.area between 100 and 200 -- area cut
and s.objid=m.objidb -- matching condition
and m.dec < 0 -- dec cut

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q02a', 1, 1, @@RowCount 

-- Q02a cpu:     0.94 sec, elapsed:    14.26 sec, physical_io: 952 row_count: 3144

-- then compute Galactic longitude and latitute (glon,glat) for these objects,
-- using standard tranformation:
--
-- cos(glat)*cos(glon-lcp)=cos(dgp)*sin(dec)-sin(dgp)*cos(dec)*cos(ra-agp)	[1]
-- cos(glat)*sin(glon-lcp)=cos(dec)*sin(ra-agp)					[2]
-- sin(glat)=sin(dgp)*sin(dec)+cos(dgp)*cos(dec)*cos(ra-agp)			[3]
--
-- where lcp=123.93 deg, agp=192.86 deg, dgp=27.13 deg for J2000--> Gal (1958) coords

-- first add glon & glat columns to ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

alter table ##results add glon float not null default 0.0
alter table ##results add glat float not null default 0.0

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q02b', 1, 1, @@RowCount 

-- Q02b cpu:     0.66 sec, elapsed:     0.68 sec, physical_io: 4 row_count: 3144

-- then compute the (glon,glat) values

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

declare @lcp float
declare @agp float
declare @dgp float
set @lcp=123.93
set @agp=radians(192.86)
set @dgp=radians(27.13)

-- add glat values to ##results using eqn [3] above and then glon using eqn [2] above
update  ##results
set glat = degrees(asin(sin(@dgp)*sin(radians(##results.dec))+cos(@dgp)*cos(radians(##results.dec))*cos(radians(##results.ra)-@agp))),
glon = @lcp - degrees(asin((cos(radians(##results.dec))*sin(radians(##results.ra)-@agp))/cos(radians(##results.glat))))

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q02c', 1, 1, @@RowCount 

-- Q02c cpu:     0.06 sec, elapsed:     0.41 sec, physical_io: 1 row_count: 3144

-- now do the same for the (slon,slat) values, using the relationships:
--
-- cos(slat)*cos(slon)=cos(glat)*cos(glon-lp)				[4]
-- cos(slat)*sin(slon)=cos(glat)*sin(bp)*sin(glon-lp)+cos(bp)*sin(glat)	[5]
-- sin(slat)=sin(bp)*sin(slat)-cos(slat)*cos(bp)*sin(glon-lp)		[6]
--
-- where lp=137.37 degrees and bp=6.32 degrees

-- first add slon & slat columns to ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

alter table ##results add slon float not null default 0.0
alter table ##results add slat float not null default 0.0

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q02d', 1, 1, @@RowCount 

-- Q02d cpu:     0.58 sec, elapsed:     0.58 sec, physical_io: 10 row_count: 3144

-- then compute the (slon,slat) values

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

declare @lp float
declare @bp float
set @lp=radians(137.37)
set @bp=radians(6.32)

-- add slat values to ##results using eqn [5] above and then slon using eqn [4] above
update  ##results
set slat = degrees(asin(sin(@bp)*sin(radians(##results.glat))-cos(@bp)*cos(radians(##results.glat))*sin(radians(##results.glon)-@lp))),
slon = degrees(acos(cos(radians(##results.glat))*cos(radians(##results.glon)-@lp)/cos(radians(##results.slat))))

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q02e', 1, 1, @@RowCount 

-- Q02e cpu:     0.07 sec, elapsed:     0.07 sec, physical_io: 0 row_count: 3144


-- now perform the final selection

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select * from ##results where slat between -10 and 10

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q02f', 1, 1, @@RowCount 

-- Q02f cpu:     0.04 sec, elapsed:     0.20 sec, physical_io: 0 row_count: 3144


/*
Q3: Find all galaxies brighter than magnitude 20, where the local
extinction is >0.75.
	[we don't currently store an extinction map, but presumably
we can readily general one from the Schlegel et al. data in the same
format as was done with the SDSS]
*/

-- we don't store the extinction, so have to compute it from the extinction table: not sure what ax is?
-- don't understand extinction table - doesn't seem to be given to fixed HTM level
select max(htmid), min(htmid),count(*) from extinction -- 1048575, 524288, 524288 (agrees with SkyServer)
select dbo.spHTM_To_String(524288) --  8,0,0,0,0,0,0,0,0 -- Level 9?
select dbo.spHTM_To_String(1048575) -- 3,3,3,3,3,3,3,3,3,3 -- Level 10?
-- SkyServer says that extinction is averaged over an HTM8 triangle

drop table ##results

-- if I run 
select top 10  *
from ##results as m
where left(dbo.spHTM_To_String(dbo.spHTM_Lookup('J2000 8 ' +str(ra,15,7) +' ' +str(dec,15,7) )),15) not in (select left(dbo.spHTM_To_String(htmid),15) from extinction)
-- then no objects are returned: i.e. if I truncate the HTMIDs of the objects at Level 8 I can match all of them to entries in the extinction table so truncated

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select objid,m.ra as ra,m.dec as dec, ax  
into ##results
from mergedcatalog m, extinction e
where m.bestclass=1 -- galaxy in merged catalogue
and m.gcormagb < 20 -- magnitude cut 
and left(dbo.spHTM_To_String(dbo.spHTM_Lookup('J2000 8 ' +str(m.ra,15,7) +' ' +str(m.dec,15,7) )),15) = left(dbo.spHTM_To_String(e.htmid),15) -- matching htmids to extinction map
and e.ax >0.75 

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q03', 1, 1, @@RowCount 

--Q03 cpu:    76.47 sec, elapsed:    82.21 sec, physical_io: 93 row_count: 0

-- finds 0 objects, and that holds if the bestclass and mag cuts are removed - i.e. this field it at too high a latitude for such a high extinction coefficient
-- but don't know what units ax is in


/*
Q4: Find all galaxies with an area greater than 250 pixels and a major
axis 30 arcsec < d < 1 arcmin in the red band and with an ellipticity>0.5.
	[surface brightness --> area]
*/

-- N.B. plate scale is 67.14 arcsec/mm, so 1 micron=0.067 arcsec
drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select objid --m.objid
into ##results
from surveycatalog --,mergedcatalog as m
where (surveyid=2 or surveyid=4 or surveyid=5) -- one of the red surveys
and area > 250 -- area cut
and au*0.067 between 30 and 60 -- major axis cut
and (1.0-bu/au) > 0.5 -- ellipicity cut
--and ((m.objidr1=s.objid) or (m.objidr2=s.objid)) -- join with mergedcatalogue to eliminate duplicates in the overlap regions

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q04', 1, 1, @@RowCount 

-- Q04 cpu:     0.39 sec, elapsed:     0.39 sec, physical_io: 1 row_count: 97

/*
-- Query 5
--Q5: Find all galaxies with a profile statistic > 10 and the photometric
--    colours consistent with an elliptical galaxy.
*/

-- SDSS colour cuts for low (z<0.37) redshift ellipticals is
--
-- 0.2 > (r-i) + 0.25*(g-r) + 0.18 > -0.2 \ from Eisenstein et al. (2001)
-- r < 13.1 + 2.33*(g-r) + 4 [(r-i)-0.18] /
--
-- Fukugita et al. (1996) gives the SDSS colour equations as:
-- g-r=1.05*(B-V)-0.23
-- r=V-0.49*(B-V)+0.11=V-0.84*(V-R)+0.13
-- r-i= / 0.98*(R-I)-0.23, R-I<1.15
--      \ 1.40*(R-I)-0.72, R-I>=1.15
--
--Blair and Gilmore (1982) give photographic-photoelectric colours as
-- B-BJ=0.28*(B-V) and R,I not significantly different from Cousins R,I
--
-- From there, the colour and colour-mag cuts for SSS mags become:
--
-- (a) R-I<1.15:
--	0.2 > 0.98*(R-I)+0.21*(B-R)-0.11 > -0.2
--      R < 10.77+1.81*(B-R)+3.92(R-I)
--
-- (b) R-I>=1.15:
--	0.2 > 1.40*(R-I)+0.21*(B-R)-0.6 > -0.2
--      R < 8.81+1.81*(B-R)+5.6(R-I)

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select objid 
into ##results
from mergedcatalog
where (classB=1 and prfStatB>10 and bestmagB>0.0 and bestmagB<30.0) -- large galaxy in B
and (classR1=1 and prfStatR1>10 and bestmagR1>0.0 and bestmagR1<30.0) -- large galaxy in R1
and (classR2=1 and prfStatR2>10 and bestmagR2>0.0 and bestmagR2<30.0) -- large galaxy in R2
and (classI=1 and prfStatI>10 and bestmagI>0.0 and bestmagI<30.0) -- large galaxy in I
and
(-- R-I<1.15
	(0.98*(bestmagR1-bestmagI)+0.21*(bestmagB-bestmagR1)-0.11 between -0.2 and 0.2)
and     (0.98*(bestmagR2-bestmagI)+0.21*(bestmagB-bestmagR2)-0.11 between -0.2 and 0.2)
and 	(bestmagR1<10.77+1.81*(bestmagB-bestmagR1)+3.92*(bestmagR1-bestmagI))
and     (bestmagR2<10.77+1.81*(bestmagB-bestmagR2)+3.92*(bestmagR2-bestmagI))
) or
(-- R-I>=1.15
	(1.40*(bestmagR1-bestmagI)+0.21*(bestmagB-bestmagR1)-0.6 between -0.2 and 0.2)
and     (1.40*(bestmagR2-bestmagI)+0.21*(bestmagB-bestmagR2)-0.6 between -0.2 and 0.2)
and 	(bestmagR1<8.81+1.81*(bestmagB-bestmagR1)+5.6*(bestmagR1-bestmagI))
and     (bestmagR2<8.81+1.81*(bestmagB-bestmagR2)+5.6*(bestmagR2-bestmagI))
)

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q05', 1, 1, @@RowCount 

-- Q05 cpu:     1.53 sec, elapsed:     1.54 sec, physical_io: 1 row_count: 323

--
--Q6: Find galaxies that are blended with a star, output the deblended
--    magnitudes.

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select g.objID as gid,g.gMag,s.objID as sid,s.sMag
into ##results
from surveyCatalog g, surveyCatalog s
where g.class = 1      	-- galaxy
  and s.class=2  	-- star
  and g.parentid = s.parentid -- common parent
  and g.parentID != 0	-- not the null parent
  and g.gMag > 0 and g.gMag <30.0 -- sensible magnitude 
  and s.sMag > 0 and s.sMag < 30.0 -- sensible magnitude
  and s.seam = 0 and g.seam=0 -- remove duplicates in overlap regions
 
exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q06', 1, 1, @@RowCount 

-- Q06 cpu:     2.39 sec, elapsed:     2.43 sec, physical_io: 0 row_count: 91900

-- this is for any band --> multiple results if blended in different bands
-- can have multiple stars blended with one galaxy, or vice versa 
-- so could prune this further, if desired. 

/*	
Q7: Provide a list of star-like objects that are 1% rare for the 3-colour
attributes.
*/
drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select  cast(round((bestmagB-bestmagR2),0) as int) as RI, 
	cast(round((bestmagR2-bestmagI),0) as int) as IZ,
	count(*) as pop
into ##results
from mergedcatalog
where 
    (classB=2 and bestmagB>0.0 and bestmagB<30.0) -- star with sensible mags in B
and (classR2=2 and bestmagR2>0.0 and bestmagR2<30.0) -- star with sensible mags in R2
and (classI=2 and bestmagI>0.0 and bestmagI<30.0) -- star with sensible mags in I
group by cast(round((bestmagB-bestmagR2),0) as int), cast(round((bestmagR2-bestmagI),0) as int) 
order by count(*)

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q07', 1, 1, @@RowCount 

-- Q07 cpu:     0.55 sec, elapsed:     0.57 sec, physical_io: 0 row_count: 50

-- 50 buckets selected
select sum(pop) from ##results -- 36332 objects
-- so 1% of objects is 363 and this is reached in the 41st bucket 

/*
Q8: Find a list of stars with Sloan 5-band colours and SSS proper
motions which are consistent with being subdwarfs.
	[this is a new query and it's what Nige and his student are doing
by hand at the moment. To do it really properly requires mapping out the
systematic errors in the proper motions, which is probably too hard to
tackle in SQL, but even computing a naive proper motion estimate from the
SDSS and SSS data combined would be a nice query.]
*/
-- Nige gave me data for subdwarf selection in a reduced proper motion (RPM) diagram,
-- which I fitted (roughly) as a pair of straight lines, so that the selection
-- becomes
-- Hr =r + 5*log10(mu/arcsec per year) + 5 -- definition of reduced proper motion
-- Hr > 13.3 + 6.5 * (r-i) and Hr < 15.3 + 6.35 * (r-i)
--
-- really need SDSS-SSS matching before we complete this one, but start to write it now


drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

declare @star int
set @star = skyserverv3.dbo.fPhotoType('Star')

select sss.objid
into ##results
from sss.dbo.mergedcatalog as sss, skyserverv3.dbo.photoobj as sdss
where 
sdss.type = @star -- star in SDSS
and	sss.bestclass=2 -- star in SSS
and	sdss.psfmag_r + 5 * log10(sqrt((square(sss.muacosd/1000.0))+square(sss.mud/1000.0))) + 5 > 13.3 + 6.5 * (sdss.psfmag_r - sdss.psfmag_i) -- 1st RPM cut
and	sdss.psfmag_r + 5 * log10(sqrt((square(sss.muacosd/1000.0))+square(sss.mud/1000.0))) + 5 < 15.3 + 6.35 * (sdss.psfmag_r - sdss.psfmag_i) -- 2nd RPM cut
-- add sss to sdss matching line here


exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q8', 1, 1, @@RowCount 
/*
Q9: Provide a list of galaxies whose Sloan and SSS magnitudes are
consistent with there having been a supernova in the galaxy at one of
its epochs of observation.
	[another new query, and again something that we've looked into
by hand: one of our colleagues, Mike Read, has found a few new supernovae
by looking for discrepant SDSS-SSS magnitudes, and this could be extended
to yield an estimate of the mean supernova rate in the universe]
*/
-- this requires conversion between the SDSS gri and SSS BRI bands, as follows:
-- g=B-0.24(B-R)+0.36, r=R+0.08(B-R)+0.13
-- i=I+0.02(R-I)+0.08(B-R)+0.36, R-I<1.15
--  =I-0.4(R-I)+0.08(B-R)+0.85, R-I>=1.15
--
-- try a magnitude difference of 5 mags for supernova detection
--
-- also needs SSS-SDSS matching, so leave that for the time being

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

declare @galaxy int
set @galaxy = skyserverv3.dbo.fPhotoType('Galaxy')

select sss.objid as sssid, sdss.objid as sdssid
into ##results
from sss.dbo.mergedcatalog as sss, skyserverv3.dbo.photoobj as sdss
where 
     (sss.classB=1 and sss.classR2=1 and sss.classI=1) -- SSS galaxy
and  (sss.qualB<128 and sss.qualR2<128 and sss.qualI<128) -- good SSS quality
and  (sdss.type = @galaxy) -- SDSS galaxy
-- and  (insert SSS-SDSS matching line here)
and  (
   (abs(sdss.modelmag_g - (sss.bestmagB-0.24*(sss.bestmagB-sss.bestmagR2)+0.36))>5) -- g/B band difference >5 mags
or (abs(sdss.modelmag_r - (sss.bestmagR2+0.08*(sss.bestmagB-sss.bestmagR2)+0.13))>5) -- r/R band difference >5 mags
or  (-- R-I<1.15 option 
 (sss.bestmagR2-sss.bestmagI)<1.15 and		
 abs(sdss.modelmag_i - (sss.bestmagI-0.02*(sss.bestmagR2-sss.bestmagI)+0.08*(sss.bestmagB-sss.bestmagR2)+0.36))>5 -- g/B band difference >5 mags
)
or  (-- R-I>=1.15 option
(sss.bestmagR2-sss.bestmagI)>=1.15 and		
 abs(sdss.modelmag_i - (sss.bestmagI-0.4*(sss.bestmagR2-sss.bestmagI)+0.08*(sss.bestmagB-sss.bestmagR2)+0.85))>5 -- g/B band difference >5 mags
)
)

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q9', 1, 1, @@RowCount 
/*
Q10: Provide a list of high-quality star-like sources brighter than 
16th magnitude which are in either the SSS or SDSS but not both.
	[a third new query, and a somewhat speculative search for
bright optical transients - hey, if it's an alien species shining a 
torch at us, we're famous! The reason for the "high-quality" qualifier
is that any selection of SSS objects without SDSS counterparts will
produce lots of junk - dust, noise spikes, etc - and we want to try
and minimise their number]
*/
-- again, needs SSS-SDSS matching, but fill in the rest for now

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

declare @star int
set @star = skyserverv3.dbo.fPhotoType('Star')

select sss.objid as sssid, sdss.objid as sdssid
into ##results
from sss.dbo.mergedcatalog as sss, skyserverv3.dbo.photoobj as sdss
where 
     (sss.classB=2 and sss.classR2=2 and sss.classI=2) -- SSS star
and  (sss.qualB<128 and sss.qualR2<128 and sss.qualI<128) -- good SSS quality
and  (sdss.type = @star) -- SDSS galaxy
-- and  (insert SSS-SDSS non-matching line here)
and ((sss.bestmagB between 0 and 16) or (sss.bestmagR2 between 0 and 16) or (sss.bestmagI between 0 and 16)) -- brighter than 16th in at least one SSS band
and ((sdss.psfmag_u < 16) or (sdss.psfmag_g < 16) or (sdss.psfmag_r < 16) or (sdss.psfmag_i < 16) or (sdss.psfmag_z < 16)) -- brighter than 16th in at least one SDSS band
 
exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q10', 1, 1, @@RowCount 
/*
Q11: Provide a list of star-like objects with SDSS colours consistent
with being a quasar and positions consistent with not having moved.
	[4th new query. The time baseline in the SSS is *nearly* long
enough that pretty much all stars will have moved appreciably between
the first observation and the last, so one speculative idea was to 
select quasar candidates as objects that look stellar but which have
not moved. In fact, the SSS data aren't quite good enough to do that,
hence the additional SDSS colour selection: not specifying a redshift
range will mean this constraint traces out a pretty weird locus in
colour space, so I'm not sure if this is practicable as it stands.]
-- use objects with proper motions measured using at least three plates
*/
-- needs SSS-SDSS matching, so just write the rest of the query for now
-- only low-redshift quasars will be detected in the SSS, so take low-z QSO colour cuts from Query SX11:
--  (g <= 22) AND
--  (u-g >= -0.27) AND (u-g < 0.71) AND
--  (g-r >= -0.24) AND (g-r < 0.35) AND
--  (r-i >= -0.27) AND (r-i < 0.57) AND
--  (i-z >= -0.35) AND (i-z < 0.70) 
-- with selection from Galaxy -- low-z quasars are resolved in the SDSS(?)

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select sdss.objid as sdssid, sss.objid as sssid
into ##results
from sss.dbo.mergedcatalog as sss, skyserverv3.dbo.galaxy as sdss
where 
    (sdss.modelmag_g <= 22) -- SDSS mag cut
and ((sdss.modelmag_u-sdss.modelmag_g) between -0.27 and 0.71) -- 1st SDSS colour cut
and ((sdss.modelmag_g-sdss.modelmag_r) between -0.24 and 0.35) -- 2nd SDSS colour cut
and ((sdss.modelmag_r-sdss.modelmag_i) between -0.27 and 0.57) -- 3rd SDSS colour cut
and ((sdss.modelmag_i-sdss.modelmag_z) between -0.35 and 0.70) -- 4th SDSS colour cut
and (sss.bestclass = 2) -- SSS star
and (sss.sigmuacosd > abs(sss.muacosd)) -- SSS RA direction proper motion consistent with zero
and (sss.sigmud > abs(sss.mud)) -- SSS Dec direction proper motion consistent with zero
-- and (SSS-SDSS matching line)

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q11', 1, 1, @@RowCount 
/*
Q12: Create a gridded count of galaxies with B-R>1.2 and R<19 over
-70<dec<-60 and 200<RA<210, on a grid of 2 arcmin and create a map
of masks over the same grid.
	[this translates pretty easily to the SSS, provided that 
we understand what is meant by a "map of masks over the same grid" -
are we right in thinking that this is the list of grid cells containing
an object which is flagged as being affected by a satellite trail,
aeroplane, etc?]
*/
-- don't do map of masks at this stage and just do grid in area of this plate
-- tricky for this one plate, because of wrap-around in RA, so split into two with or statement
select max(dec),min(dec) from mergedcatalog where dec >=-100 -- min=-2.755 max=3.275
select min(ra) from mergedcatalog where ra > 300 -- 357.42
select max(ra) from mergedcatalog where ra>=0.0 and ra<300 -- 3.67 

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select  cast((ra*cos(radians(cast(dec*30 as int)/30.0)))*30 as int)/30.0 as raCosDec, 
       cast(dec*30 as int)/30.0                                as griddec, 
       count(*)                                                as pop
into ##results
from mergedcatalog
where ((ra between 0.0 and 4.0) or (ra between 356.0 and 360.0))
and   dec between -4.0 and 4.0
group by cast((ra*cos(radians(cast(dec*30 as int)/30.0)))*30 as int)/30.0,
       cast(dec*30 as int)/30.0
order by racosdec,griddec

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q12', 1, 1, @@RowCount 

-- Q12 cpu:     7.30 sec, elapsed:    33.65 sec, physical_io: 1055 row_count: 33026

-- the following is Jim's original query: 
-- don't understand a few things about it
--	(i) shouldn't the cos have a radians function with it?
--      (ii) why selection on both htmID and (ra and dec)?
--  	(iii) shouldn't it be ra*cos(dec)? 
--declare @LeftShift16 bigint;
--set     @LeftShift16 = power(2,28);
--
--select cast((ra/cos(cast(dec*30 as int)/30.0))*30 as int)/30.0 as raCosDec, 
--      cast(dec*30 as int)/30.0                                as dec, 
--       count(*)                                                as pop
--into ##GalaxyGrid
--from   Galaxy as G, 
--       fHTM_Cover('CONVEX J2000 6 175 -5 175 5 185 5 185 -5') as T
--where  htmID  between T.HTMIDstart*@LeftShift16 and T. HTMIDend*@LeftShift16 
--  and  ra between 175 and 185
--  and  dec between -5 and 5
--  and  u-g > 1
--  and  r < 21.5
--group by  cast((ra/cos(cast(dec*30 as int)/30.0))*30 as int)/30.0, 
--         cast(dec*30 as int)/30.0    

/*
Q13: Create a count of galaxies for each of the HTM triangles which 
satisfy a certain colour cut, like 0.7B-0.5R-0.2I<0.8 and R<19, output in
a form adequate for visualization.
	[we'll have to check the colour cut is sensible, but otherwise
this translates very readily]
*/

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

declare @RightShift12 bigint
set @RightShift12 = power(2,24)
select (htmID/@RightShift12) as htm8, avg(ra) as meanra, avg(dec) as meandec, count(*) as pop
into ##results
from mergedcatalog
where (classB=1 and bestmagB>0.0 and bestmagB<30.0) -- galaxy in B
and (classR2=1 and bestmagR2>0.0 and bestmagR2<30.0) -- galaxy in R2
and (classI=1 and bestmagI>0.0 and bestmagI<30.0) -- galaxy in I
and (0.7*bestmagB-0.5*bestmagR2-0.2*bestmagI<0.8) and bestmagR2<19
group by (htmID/@RightShift12)

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q13', 1, 1, @@RowCount 

-- Q13 cpu:     0.38 sec, elapsed:     8.56 sec, physical_io: 673 row_count: 532

/*
Q14: Provide a list of stars with multiple epoch measurements, which have
light variations >0.1 mag.
	[goes straight to the SSS]
*/
-- easiest just to use R1 and R2 data in mergedcatalog

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select objid
into ##results
from mergedcatalog
where (classR1=1 and classR2=1 and qualR1<128 and qualR2<128) -- good galaxies at both epochs
and abs (bestmagR1-bestmagR2) > 5

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q14', 1, 1, @@RowCount 

-- Q14 cpu:     0.27 sec, elapsed:     0.27 sec, physical_io: 0 row_count: 13258

-- could extend this to all bands by using overlap regions between plates:
-- can start with mergedcatalog, look for objects there with seam!=0 in surveycatalog (i.e. there is at least 
-- one overlap pair object) and then search for it in surveycatalog by proximity

/*
Q15: Provide a list of moving objects with colours and proper motions
consistent with being a white dwarf.
	[we can't flag asteroids in the same way as the Sloan, as we
don't have the contemporaneous observations in different bands, but 
this is broadly similar in spirit]
*/
-- Nige provided the following cuts in reduced proper motion (RPM) and colour 
-- space for white dwarfs in SSS colours
--
-- HR=R2+5log10(mu)+ 5 
-- HR > 3.75 *(B-R2) + 13.75
-- HR > 9.091 * (R2-I) + 14.09
--

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select objid
into ##results
from mergedcatalog
where (classB=2 and bestmagB>0 and bestmagB<30.0 and qualB=0) -- good star in B band
and   (classR2=2 and bestmagR2>0 and bestmagR2<30.0 and qualR2=0) -- good star in R2 band
and   (classI=2 and bestmagI>0 and bestmagI<30.0 and qualI=0) -- good star in I band
and   (bestmagR2 + 5*log10(sqrt(square(muacosd/1000.0)+square(mud/1000.0))) + 5 > 3.75 * (bestmagB-bestmagR2) + 13.75) -- 1st RPM cut
and   (bestmagR2 + 5*log10(sqrt(square(muacosd/1000.0)+square(mud/1000.0))) + 5 > 9.091 * (bestmagR2-bestmagI) + 14.09) -- 2nd RPM cut

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q15', 1, 1, @@RowCount 

-- Q15 cpu:     0.39 sec, elapsed:     8.62 sec, physical_io: 669 row_count: 36

/*
Q16: Find all star-like objects within delta mag of 0.2 of the colours
of a quasar of redshift 2.5 < z < 3.5.
	[have to check this is a sensible redshift range, but otherwise
it translates readily]
*/
-- the lack of U band data means that any quasar selection in the SSS will 
-- produce a number of false positives, so this query isn't really science-worthy,
-- but take rough lowish-z colour cut from Richards et al. (2001, AJ, 121, 2308)
-- - i.e. (g-r) between -0.2 and 0.35 and (r-i) between -0.4 and 0.4 - and translate
-- that to the SSS bands, to read 0.05<(B-R)<0.80 and -0.17<(R-I)<0.64, which is in
-- reasonable agreement with Irwin et al. (1991, in "The Space Distribution of Quasars",
-- ASP Conf Series no. 21), who say that z<3 quasars are to be found in an ellipse
-- centred on BJ-R~0.4 and R-I~0.2

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select objid
into ##results
from mergedcatalog
where (classB=2 and classR2=2 and classI=2) -- star in all three bands
and  (qualb=0 and qualR2=0 and qualI=0) -- good quality in all three bands
and  (bestmagB between 0 and 30) and (bestmagR2 between 0 and 30) and (bestmagI between 0 and 30) -- sensible mags
and (bestmagB-bestmagR2 between 0.05 and 0.80) -- first colour cut
and (bestmagR2-bestmagI between -0.17 and 0.64) -- second colour cut

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q16', 1, 1, @@RowCount 

-- Q16 cpu:     0.31 sec, elapsed:     0.31 sec, physical_io: 0 row_count: 527

/*
Q17: Find binary stars where at least one of them has the colours of
a white dwarf
	[the implementation of this may be different from that in 
the SDSS, but the query translates without modification]
*/

-- the original white dwarf selection used four colour cuts, while we can only
-- use two, so we'd get more junk...but that's not important here.
--
-- the original SDSS white dwarf colour cuts were
--
-- u - g < 0.4, g - r < 0.7, r - i > 0.4, i - z > 0.4     
--
-- which we can transform into the following cuts on SSS B-R and R-I 
--
-- B-R < 0.73 and (R-I) > 0.64 for (R-I)<1.15, or (R-I) > 1.00 for (R-I)>=1.15
--
-- ...using only two colour cuts, rather than four means that we'd get more 
-- false positives, but that's not important here
--

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select m1.objid as objid
into ##results
from mergedcatalog as m1, mergedcatalog as m2
where 
    (m1.bestclass=2 and m1.qualB=0 and m1.qualR2=0 and m1.qualI=0) -- good quality star in m1
and (m2.bestclass=2 and m2.qualB=0 and m2.qualR2=0 and m2.qualI=0) -- good quality star in m2
and ((m1.bestmagB between 0 and 30) and (m1.bestmagR2 between 0 and 30) and (m1.bestmagI between 0 and 30)) -- good mags for m1
and (m1.bestmagB-m1.bestmagR2<0.73) -- first white dwarf colour cut
and (
-- R-I < 1.15 option
(m1.bestmagR2-m1.bestmagI between 0.64 and 1.15) 
or
--R-I >=1.15 option
(m1.bestmagR2-m1.bestmagI > 1.15)
)
and m1.objid > m2.objid -- don't double count
and dbo.fGreatCircleDist(radians(m1.ra),radians(m1.dec),radians(m2.ra),radians(m2.dec)) < 3.0 -- closer than 3 arcsec

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q17', 1, 1, @@RowCount 

-- Q17 cpu:   397.75 sec, elapsed:   398.46 sec, physical_io: 4 row_count: 115

-- no doubt could be run much faster using pre-computed neighbours table

/*
Q18: Find all objects within 10 arcsec of another than has very similar
colours; that is, with the colour ratios B-R, R-I less than 0.1m.
	[the accuracy of colour ratios in the SSS varies significantly
with magnitude, so a real gravitational lens search would vary the
tolerance with mag, but that's probably too much of a detail to bother
about now]
*/

-- start just with colour comparison and then compute distance between them using fGreatCircleDist function
-- much better to use pre-computed neighbours table, no doubt, but don't have that yet

-- try to write a function to compute great circle distance
--
--IF EXISTS (SELECT name 
--	   FROM   sysobjects 
--	   WHERE  name = N'fGreatCircleDist' )
--DROP FUNCTION fGreatCircleDist
--
--
--CREATE FUNCTION fGreatCircleDist (@ra1 float, @dec1 float, @ra2 float, @dec2 float)
---------------------------------------------------------------
----/H Returns float holding great circle distance [in arcsec] 
----/H between two points (ra1,dec1) & (ra2,dec2) [ras,decs in radians] 
---------------------------------------------------------------
--
--RETURNS float
--AS 
--begin
--return 3600.0*2.0*degrees(asin(sqrt(square(sin(0.5*(@dec1-@dec2))) + cos(@dec1)*cos(@dec2)* square(sin(0.5*(@ra1-@ra2))))))
--END
--GO

drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT

select m1.objid as objid, 
	abs((m1.bestmagB-m1.bestmagR2)-(m2.bestmagB-m2.bestmagR2))as BR_diff,
	abs((m1.bestmagR2-m1.bestmagI)-(m2.bestmagR2-m2.bestmagI)) as RI_diff,
        dbo.fGreatCircleDist(radians(m1.ra),radians(m1.dec),radians(m2.ra),radians(m2.dec)) as dist
into ##results
from mergedcatalog as m1, mergedcatalog as m2 
where (m1.bestmagB>0.0 and m1.bestmagB<30.0) -- good B mags in m1
and (m1.bestmagR2>0.0 and m1.bestmagR2<30.0) -- good R2 mags in m1
and (m1.bestmagI>0.0 and m1.bestmagI<30.0) -- good I mags in m1
and (m2.bestmagB>0.0 and m2.bestmagB<30.0) -- good B mags in m2
and (m2.bestmagR2>0.0 and m2.bestmagR2<30.0) -- good R2 mags in m2
and (m2.bestmagI>0.0 and m2.bestmagI<30.0) -- good I mags in m2
and abs((m1.bestmagB-m1.bestmagR2)-(m2.bestmagB-m2.bestmagR2)) < 0.1
and abs((m1.bestmagR2-m1.bestmagI)-(m2.bestmagR2-m2.bestmagI)) < 0.1
and m1.objid>m2.objid -- don't double-count
and dbo.fGreatCircleDist(radians(m1.ra),radians(m1.dec),radians(m2.ra),radians(m2.dec)) < 10.0 -- closer than 10 arcsec

exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q18', 1, 1, @@RowCount 

-- this takes too long! - I aborted it after an hour!

/*	
Q19: Provide a list of SSS objects within a magnitude of their respective 
nominal plate limit which are unpaired in the SSS and have no SDSS
counterpart.
	[our last new query is designed specifically to identify junk
objects as we get down to the noise level in the SSS. We've got 
collaborators in Edinburgh's Division of Informatics, who are interested
in using machine-learning techniques to identify which faint SSS
objects are spurious on the basis of training sets constructed from
the deeper SDSS data. A query like this is needed to produce such a
training set.]
*/

-- needs SSS-SDSS matching
drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT


exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q19', 1, 1, @@RowCount 
/*
Q20:....oops - I can't count!....I thought we had replaced all the 
queries in Alex's list that used spectroscopic data, but I miscounted -
we'll think of one more.
*/
drop table ##results

declare @cpu int, @physical_io int, @clock datetime, @elapsed int;
exec dbo.InitTimeX  @clock OUTPUT, @cpu OUTPUT, @physical_io OUTPUT



exec dbo.EndTimeX @clock, @elapsed OUTPUT, @cpu OUTPUT, @physical_io OUTPUT,
     'Q20', 1, 1, @@RowCount 


