-- Find objects in my list
-- Steve Howell Galactic Structure 3) and Photometry 5) combined
-- 
-- 3. Find all objects listed in an input file for which the database has the
-- following information/criteria.
-- images in B with int times >100sec, taken in 2007, with seeing of <0.7 arcsec, 
-- etc.
-- 
-- 5. same as three in Galactic structure but with photometric keys allowed.
-- Items such as brightness, amplitude, type of variability, galactic location, 
-- color, apparent mag, etc.
-- 
-- The cross-id query can be done using SkyServer upload page, or CasJobs
-- the following is the CasJobs/MyDB version.  MyList is the input list,
-- MyMatches is the output table containing the matches.

-- Option 1: when the list is not too large, say up to few thousand points

-- Run the create table in the MyDB context (delete the rest of the query) first. 
-- Then reload the sample query, delete the create table part, and run the rest
-- of the query in the PS1 context.

CREATE TABLE MyMatches (
    source_id INT,	-- an id to identify object in original list
    source_ra	FLOAT,
    source_dec FLOAT,
    objID BIGINT,	-- objID of nearest PS object
    ra	FLOAT,
    dec FLOAT,
    distance REAL	
)


-- Run this part in the PS1 context after running the above in MyDB context.

declare @ra float, @dec float, @id int;

DECLARE my_cursor cursor read_only
FOR
SELECT ra,dec,id FROM MYDB.MyList
-- MyList is the uploaded list of ra,decs the person is interested in

OPEN my_cursor
-- NOTE: this can be done with CROSS APPLY in SQL Server 2005
WHILE(1=1)
BEGIN
  FETCH NEXT from my_cursor into @ra, @dec, @id
  IF (@@fetch_status < 0) break
  INSERT MYDB.MyMatches
  SELECT @id as source_id,@ra as source_ra,@dec as source_dec,
	objID, ra,dec, distance
	FROM dbo.fGetNearestObjEq(@ra,@dec,0.1)	-- search 0.1 arcmin radius
END

--------------------
-- close the cursor
--------------------
CLOSE my_cursor
DEALLOCATE my_cursor
