/* 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=-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