--
-- Here are the queries from my Skyserver talk at the
-- Princeton 2002 Collaboration Meeting
--
-- If you are reading this in emacs with my skyserver mode loaded,
-- you can place the cursor in a query and type \C-c\C-c (== ^C^C)
-- to submit that query to the skyserver
--
-- These queries have been tested with the skyserver.pha.jhu.edu/collab/v4
-- database.  February 2003.
--
-------------------------------------------------------------------------------

SELECT DISTINCT
	run, rerun,
	field0, field0 + nfields - 1,
	photoVersion
FROM Segment

-------------------------------------------------------------------------------

SELECT DISTINCT
	str(run, 4) AS run, str(rerun, 3) AS rerun,
	str(field0,3) AS field0, str(field0 + nfields - 1,4) AS field1,
	photoVersion
FROM Segment
ORDER BY run

------------------------------------------------------------------------------
-- 
-- What's loaded in skyserver?
-- 
DECLARE @database CHAR   SET @database = BESTDR1
-- 
SELECT DISTINCT
	str(run, 4) AS run, str(rerun, 3) AS rerun,
	str(field0,3) AS field0, str(field0 + nfields - 1,4) AS field1,
	photoVersion
FROM @database..Segment
ORDER BY run

------------------------------------------------------------------------------
-- 
-- What's loaded in skyserver? Just list the first two (but not 745)
-- 
DECLARE @database CHAR   SET @database = 
-- 
SELECT DISTINCT top 2
	str(run, 4) AS run, str(rerun, 3) AS rerun,
	str(field0,3) AS field0, str(field0 + nfields - 1,4) AS field1,
	photoVersion
FROM @database..Segment
WHERE
	run != 745
ORDER BY run

------------------------------------------------------------------------------
-- 
-- What's loaded in skyserver?  Interrogate two databases at once
-- 
declare @database1 char   set @database1 = BESTDR1
declare @database2 char   set @database2 = TARGDR1
--
declare @Segment char    set @Segment = ( \
		  select * from @database1..Segment\
		  -- And @database2 too \
	union all select * from @database2..Segment\
	) as Segment
-- 
SELECT DISTINCT
	str(run, 4) AS run, str(rerun, 3) AS rerun,
	str(field0,3) AS field0, str(field0 + nfields - 1,4) AS field1,
	photoVersion
FROM @Segment
ORDER BY run

------------------------------------------------------------------------------

/*
 * Galaxy target selection
 */
DECLARE @database CHAR	SET @database =
DECLARE @pi NUMERIC     SET @pi = select str(PI(), 18, 16)
--
DECLARE @BLENDED BIGINT	SET @BLENDED = dbo.fPhotoFlags('BLENDED')
DECLARE @BRIGHT BIGINT	SET @BRIGHT = dbo.fPhotoFlags('BRIGHT')
DECLARE @EDGE BIGINT	SET @EDGE = dbo.fPhotoFlags('EDGE')
DECLARE @NODEBLEND BIGINT SET @NODEBLEND = dbo.fPhotoFlags('NODEBLEND')
DECLARE @SATURATED BIGINT SET @SATURATED = dbo.fPhotoFlags('SATURATED')
--
DECLARE @bad_flags BIGINT SET @bad_flags = (@SATURATED | @BRIGHT | @EDGE)
--
DECLARE @maglim NUMERIC	SET @maglim = 17.77
DECLARE @SBlim NUMERIC	SET @SBlim = 24.5
DECLARE @delta_psf_model NUMERIC SET @delta_psf_model = 0.3
--
SELECT top 10
-- Standard fields
run, rerun, camCol, field,
str(rowc,6,1) AS rowc, str(colc,6,1) AS colc,
str(dbo.fObj(ObjId), 4) AS id,
':' AS ':',
--
-- Scientific output
--
str(ra,9,4) AS ra, str(dec,8,4) AS dec
--
FROM
	@database..PhotoPrimary
WHERE
	-- Our star-galaxy separation and target selection
	psfMag_r - modelMag_r >= @delta_psf_model AND
	petroMag_r - extinction_r <= @maglim AND
	petroMag_r - 2.5*log10(2*@pi*petroR50_r*petroR50_r) < @SBlim AND
	-- Check flags
	(flags & @bad_flags) = 0 AND
	(((flags & @BLENDED) = 0) or ((flags & @NODEBLEND) != 0))

------------------------------------------------------------------------------

/*
 * Galaxy target selection with spectroscopic redshifts
 */
DECLARE @database CHAR	SET @database =
DECLARE @pi NUMERIC	SET @pi = 3.141592654
--
DECLARE @BLENDED BIGINT	SET @BLENDED = dbo.fPhotoFlags('BLENDED')
DECLARE @BRIGHT BIGINT	SET @BRIGHT = dbo.fPhotoFlags('BRIGHT')
DECLARE @EDGE BIGINT	SET @EDGE = dbo.fPhotoFlags('EDGE')
DECLARE @NODEBLEND BIGINT SET @NODEBLEND = dbo.fPhotoFlags('NODEBLEND')
DECLARE @SATURATED BIGINT SET @SATURATED = dbo.fPhotoFlags('SATURATED')
DECLARE @FAILED INT     SET @FAILED = dbo.fSpecZStatus('FAILED')
DECLARE @NOT_MEASURED INT SET @NOT_MEASURED = dbo.fSpecZStatus('NOT_MEASURED')
--
DECLARE @bad_flags BIGINT SET @bad_flags = (@SATURATED | @BRIGHT | @EDGE)
--
DECLARE @maglim NUMERIC	SET @maglim = 17.77
DECLARE @SBlim NUMERIC	SET @SBlim = 24.5
DECLARE @delta_psf_model NUMERIC SET @delta_psf_model = 0.3
--
SELECT top 15
str(gal.ra,9,4) AS ra, str(gal.dec,8,4) AS dec,
'|' as '|', cast(spec.objTypeName AS CHAR(9)) AS type,
str(spec.z,7,4) AS Z,
dbo.fSpecZStatusN(spec.zStatus) AS status,
dbo.fGetUrlSpecImg(spec.specObjID) AS Spectra
--
FROM
	@database..PhotoPrimary AS gal,
	@database..specObj AS spec
WHERE
	gal.objID = spec.bestObjID AND
	-- Our star-galaxy separation AND target selection
	psfMag_r - modelMag_r >= @delta_psf_model AND
	petroMag_r - extinction_r <= @maglim AND
	petroMag_r - 2.5*log10(2*@pi*petroR50_r*petroR50_r) < @SBlim AND
	-- Check flags
	(flags & @bad_flags) = 0 AND
	(((flags & @BLENDED) = 0) OR ((flags & @NODEBLEND) != 0)) AND
	-- Check spectro flags
	NOT spec.zStatus IN (@FAILED, @NOT_MEASURED)

------------------------------------------------------------------------------

/*
 * Galaxy target selection with spectroscopic redshifts
 * (Using an explicit JOIN)
 */
DECLARE @database CHAR	SET @database =
DECLARE @pi NUMERIC	SET @pi = 3.141592654
--
DECLARE @BLENDED BIGINT	SET @BLENDED = dbo.fPhotoFlags('BLENDED')
DECLARE @BRIGHT BIGINT	SET @BRIGHT = dbo.fPhotoFlags('BRIGHT')
DECLARE @EDGE BIGINT	SET @EDGE = dbo.fPhotoFlags('EDGE')
DECLARE @NODEBLEND BIGINT SET @NODEBLEND = dbo.fPhotoFlags('NODEBLEND')
DECLARE @SATURATED BIGINT SET @SATURATED = dbo.fPhotoFlags('SATURATED')
DECLARE @FAILED INT     SET @FAILED = dbo.fSpecZStatus('FAILED')
DECLARE @NOT_MEASURED INT SET @NOT_MEASURED = dbo.fSpecZStatus('NOT_MEASURED')
--
DECLARE @bad_flags BIGINT SET @bad_flags = (@SATURATED | @BRIGHT | @EDGE)
--
DECLARE @maglim NUMERIC	SET @maglim = 17.77
DECLARE @SBlim NUMERIC	SET @SBlim = 24.5
DECLARE @delta_psf_model NUMERIC SET @delta_psf_model = 0.3
--
SELECT top 15
str(gal.ra,9,4) AS ra, str(gal.dec,8,4) AS dec,
'|' as '|', cast(spec.objTypeName AS CHAR(9)) AS type,
str(spec.z,7,4) AS Z,
dbo.fSpecZStatusN(spec.zStatus) AS status,
dbo.fGetUrlSpecImg(spec.specObjID) AS Spectra
--
FROM
		@database..PhotoPrimary AS gal
	JOIN
		@database..specObj AS spec
	ON
		gal.objID = spec.bestObjID
WHERE
	-- Our star-galaxy separation and target selection
	psfMag_r - modelMag_r >= @delta_psf_model AND
	petroMag_r - extinction_r <= @maglim AND
	petroMag_r - 2.5*log10(2*@pi*petroR50_r*petroR50_r) < @SBlim AND
	-- Check flags
	(flags & @bad_flags) = 0 AND
	(((flags & @BLENDED) = 0) OR ((flags & @NODEBLEND) != 0)) AND
	-- Check spectro flags
	NOT spec.zStatus IN (@FAILED, @NOT_MEASURED)

------------------------------------------------------------------------------

/*
 * Galaxy target selection including spectroscopic redshifts where available,
 * but including object for which no spectrum was taken
 */
DECLARE @database CHAR	SET @database =
DECLARE @pi NUMERIC	SET @pi = 3.141592654
--
DECLARE @BLENDED BIGINT	SET @BLENDED = dbo.fPhotoFlags('BLENDED')
DECLARE @BRIGHT BIGINT	SET @BRIGHT = dbo.fPhotoFlags('BRIGHT')
DECLARE @EDGE BIGINT	SET @EDGE = dbo.fPhotoFlags('EDGE')
DECLARE @NODEBLEND BIGINT SET @NODEBLEND = dbo.fPhotoFlags('NODEBLEND')
DECLARE @SATURATED BIGINT SET @SATURATED = dbo.fPhotoFlags('SATURATED')
DECLARE @FAILED INT     SET @FAILED = dbo.fSpecZStatus('FAILED')
DECLARE @NOT_MEASURED INT SET @NOT_MEASURED = dbo.fSpecZStatus('NOT_MEASURED')
--
DECLARE @bad_flags BIGINT SET @bad_flags = (@SATURATED | @BRIGHT | @EDGE)
--
DECLARE @maglim NUMERIC	SET @maglim = 17.77
DECLARE @SBlim NUMERIC	SET @SBlim = 24.5
DECLARE @delta_psf_model NUMERIC SET @delta_psf_model = 0.3
--
SELECT top 15
str(gal.ra,9,4) AS ra, str(gal.dec,8,4) AS dec,
'|' AS '|', cast(ISNULL(spec.objTypeName, 'NULL') AS CHAR(9)) AS type,
ISNULL(str(spec.z,7,4), 'NULL') AS Z,
ISNULL(dbo.fSpecZStatusN(spec.zStatus), 'NULL') AS status,
case when spec.specObjID is NULL then 'NULL' else dbo.fGetUrlSpecImg(spec.specObjID) end as Spectra
--
FROM
		@database..PhotoPrimary AS gal
	LEFT OUTER JOIN
		@database..specObj AS spec
	ON
		gal.objID = spec.bestObjID
WHERE
	-- Our star-galaxy separation and target selection
	psfMag_r - modelMag_r >= @delta_psf_model AND
	petroMag_r - extinction_r <= @maglim AND
	petroMag_r - 2.5*log10(2*@pi*petroR50_r*petroR50_r) < @SBlim AND
	-- Check flags
	(flags & @bad_flags) = 0 AND
	(((flags & @BLENDED) = 0) or ((flags & @NODEBLEND) != 0))
	-- Check spectro flags
        AND (spec.zStatus IS NULL OR
	     NOT spec.zStatus IN (@FAILED, @NOT_MEASURED))

------------------------------------------------------------------------------
--
-- Make a temporary table
--
DECLARE @database CHAR	 SET @database = BESTDR1
--
SELECT
	*
FROM
	(SELECT
		run,
		rerun,
		COUNT(*) AS nobj
	FROM
		@database..photoObj
	GROUP BY run, rerun
	) AS tmpTable

------------------------------------------------------------------------------
--
-- Make a temporary table and use it
--
DECLARE @database CHAR	 SET @database = BESTDR1
--
SELECT
	sum(nobj) total, avg(nobj) as nbar
FROM
	(SELECT
		run,
		rerun,
		COUNT(*) AS nobj
	FROM
		@database..photoObj
	GROUP BY run, rerun
	) AS tmpTable

------------------------------------------------------------------------------

-- 
-- What's loaded in skyserver?
-- 
DECLARE @database CHAR   SET @database = BESTDR1
-- 
SELECT DISTINCT
	str(seg.run, 4) AS run, str(seg.rerun, 3) AS rerun,
	str(field0,3) AS field0, str(field0 + nfields - 1,4) AS field1,
	str(nobj, 7) as nobj,
	photoVersion
FROM
	@database..Segment as seg,
	(select
		COUNT(*) as nobj, run, rerun
	FROM
		@database..photoObj
	GROUP BY run, rerun
	) as fieldSummary
WHERE
	seg.run = fieldSummary.run AND seg.rerun = fieldSummary.rerun
ORDER BY run

------------------------------------------------------------------------------

-- 
-- What's loaded in skyserver?
-- 
DECLARE @database CHAR   SET @database = BESTDR1
-- 
SELECT DISTINCT
	str(seg.run, 4) AS run, str(seg.rerun, 3) AS rerun,
	-- str(field0,3) as field0, str(field0 + nfields - 1,4) as field1,
	str(field0_rhl,3) AS field0,
	str(field1_rhl,3) AS field1,
	photoVersion
FROM
	@database..Segment as seg,
	(SELECT
		MIN(field) as field0_rhl,
		MAX(field) as field1_rhl,
		run, rerun
	FROM
		@database..field
	GROUP BY run, rerun
	) AS runSummary
WHERE
	seg.run = runSummary.run AND seg.rerun = runSummary.rerun
ORDER BY run

------------------------------------------------------------------------------
--
-- Calculate the zhed point by run/camCol/field from the skyserver data
--
-- N.b. See also zhed.sm
--
declare @database char   set @database = -- BESTDR1
--
declare @maglim numeric	set @maglim = 18
declare @star int	set @star = dbo.fPhotoType('star')
declare @bad_flags bigint  set @bad_flags = (dbo.fPhotoFlags('SATURATED') | \
		dbo.fPhotoFlags('BRIGHT') | dbo.fPhotoFlags('EDGE'))
-- Possible restriction on runs to be processed
declare @select_run char set @select_run = run = 1336 and camCol = 1 and
--
-- Our Query
--
select
field.pspStatus,
blue.run, blue.camCol, blue.field,
N,
gr as zhed_gr,
C0 + gr*C1 as zhed_ri,
C0, C1
from
	@database..field as field,
	(select			-- Fit straight line
		run as run, camCol as camCol, field as field, fieldId, N,
		(-sum_x*sum_xy + sum_xx*sum_y)/(n*sum_xx - sum_x*sum_x) as C0,
		(N*sum_xy - sum_x*sum_y)/(n*sum_xx - sum_x*sum_x) as C1
	from
	(select			-- Blue part of locus
		run as run, camCol as camCol, field as field, fieldId,
		count(*) as N,
		sum(g - r) as sum_x,
		sum((g - r)*(g - r)) as sum_xx,
		sum(r - i) as sum_y,
		sum((g - r)*(r - i)) as sum_xy
	from
		(select
			 run, camCol, field, fieldId,
			(psfMag_g - extinction_g) as g,
			(psfMag_r - extinction_r) as r,
			(psfMag_i - extinction_i) as i
		from @database..PhotoPrimary
		where
			@select_run
			(flags & @bad_flags) = 0 and nchild = 0 and
			type = @star and
			-- Not too faint
			psfMag_i < @maglim
		) as obj1
	where
		-- Choose stars in nearly horizontal part of g-r-i diagram
		g - r between 0.3 and 1.1 and
		r - i between -0.1 and 0.6
	group by run, camCol, field, fieldId
	) as _blue
	) as blue,
	(
	select			-- Red part of locus
		fieldId,
		avg(g - r) as gr-- I'd prefer the median
	from
		(select
			fieldId,
			(psfMag_g - extinction_g) as g,
			(psfMag_r - extinction_r) as r,
			(psfMag_i - extinction_i) as i
		from @database..PhotoPrimary
		where
			@select_run
			(flags & @bad_flags) = 0 and nchild = 0 and
			type = @star and
			-- Not too faint
			psfMag_i < @maglim
		) as obj2
	where
		-- Choose stars in vertical part of g-r-i diagram
		g - r between 1.1 and 1.6 and
		r - i between 0.8 and 1.4
	group by fieldId
	) as red
where
	blue.fieldId = red.fieldId and
	blue.fieldId = field.fieldId
order by blue.run, blue.camCol, blue.field

------------------------------------------------------------------------------
--
-- Calculate the \v{z}hed point from the skyserver data
--
-- This version calculates the per run/camCol zhed points
--
-- N.b. This may take some time...
--
DECLARE @database CHAR  SET @database = -- BESTDR1
--
DECLARE @maglim NUMERIC	SET @maglim = 18
DECLARE @Nmin INT       SET @Nmin = 15 -- Minimum number of stars/field
--
DECLARE @star INT	SET @star = dbo.fPhotoType('star')
DECLARE @bad_flags BIGINT SET @bad_flags = (dbo.fPhotoFlags('SATURATED') | \
			dbo.fPhotoFlags('BRIGHT') | dbo.fPhotoFlags('EDGE'))
DECLARE @good CHAR	SET @good = field.pspStatus = 0 and blue.N > @Nmin
-- Possible restriction on runs to be processed
DECLARE @select_run CHAR SET @select_run = -- run = 1336 and camCol = 1 and
--
-- Our Query
--
SELECT
blue.run, blue.camCol,
sum(case when @good then blue.N     else 0 end) as N,
avg(case when @good then gr         else NULL end) as zhed_gr,
avg(case when @good then C0 + gr*C1 else NULL end) as zhed_ri
FROM
	@database..field AS field,
	(SELECT			-- Fit straight line
		run AS run, camCol AS camCol, field AS field, fieldId, N,
		(-sum_x*sum_xy + sum_xx*sum_y)/(n*sum_xx - sum_x*sum_x) AS C0,
		(N*sum_xy - sum_x*sum_y)/(n*sum_xx - sum_x*sum_x) AS C1
	FROM
	(SELECT			-- Blue part of locus
		run AS run, camCol AS camCol, field AS field, fieldId,
		COUNT(*) AS N,
		SUM(g - r) AS sum_x,
		SUM((g - r)*(g - r)) AS sum_xx,
		SUM(r - i) AS sum_y,
		SUM((g - r)*(r - i)) AS sum_xy
	FROM
		(SELECT
			 run, camCol, field, fieldId,
			(psfMag_g - extinction_g) AS g,
			(psfMag_r - extinction_r) AS r,
			(psfMag_i - extinction_i) AS i
		FROM @database..PhotoPrimary
		WHERE
			@select_run
			(flags & @bad_flags) = 0 and nchild = 0 and
			type = @star and
			-- Not too faint
			psfMag_i < @maglim
		) AS obj1
	WHERE
		-- Choose stars in nearly horizontal part of g-r-i diagram
		g - r between 0.3 and 1.1 and
		r - i between -0.1 and 0.6
	GROUP BY run, camCol, field, fieldId
	) AS _blue
	) AS blue,
	(
	SELECT			-- Red part of locus
		fieldId,
		avg(g - r) AS gr-- I'd prefer the median
	FROM
		(SELECT
			fieldId,
			(psfMag_g - extinction_g) AS g,
			(psfMag_r - extinction_r) AS r,
			(psfMag_i - extinction_i) AS i
		FROM @database..PhotoPrimary
		WHERE
			@select_run
			(flags & @bad_flags) = 0 and nchild = 0 and
			type = @star and
			-- Not too faint
			psfMag_i < @maglim
		) AS obj2
	WHERE
		-- Choose stars in vertical part of g-r-i diagram
		g - r between 1.1 and 1.6 and
		r - i between 0.8 and 1.4
	GROUP BY fieldId
	) AS red
WHERE
	blue.fieldId = red.fieldId and
	blue.fieldId = field.fieldId
GROUP BY blue.run, blue.camCol
ORDER BY blue.run, blue.camCol