-- Gridded galaxy counts and masks. Actually consists of TWO queries:
-- 1) Create a gridded count of galaxies with u-g > 1 and r < 21.5 over -1 < dec < 1,
-- and 179 < R.A. < 181, on a grid, and create a map of masks over the same grid.
-- Scan the table for galaxies and group them in cells 2 arc-minuteson a side. Provide
-- predicates for the color restrictions on u-g and r and to limit the search to the
-- portion of the sky defined by the right ascension and declination conditions. Return
-- the count of qualifying galaxies in each cell.
-- 2) Run another query with the same grouping, but with a predicate to include only
-- objects such as satellites, planets, and airplanes that obscure the cell. The second
-- query returns a list of cell coordinates that serve as a mask for the first query.

--- First find the gridded galaxy count (with the color cut)
--- In local tangent plane, ra/cos(dec) is a linear degree.

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 
FROM Galaxy as G
    JOIN dbo.fHTMCoverRegion('CHULL J2000 179 -1 179 1 181 1 181 -1') AS T 
       ON G.htmID BETWEEN T.HTMIDstart AND T. HTMIDend 
WHERE ra between 179 and 181
    and dec between -1 and 1 
    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

-- now build mask grid.
-- This is a separate query if no temp tables can be made

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 
FROM PhotoObj as PO
    JOIN dbo.fHTMCoverRegion('CHULL J2000 179 -1 179 1 181 1 181 -1') AS T 
       ON PO.htmID BETWEEN T.HTMIDstart AND T.HTMIDend 
    JOIN PhotoType as PT ON PO.type = PT.value 
WHERE
    ra between 179 and 181 
    and dec between -1 and 1 
    and PT.name in ('COSMIC_RAY', 'DEFECT', 'GHOST', 'TRAIL', 'UNKNOWN') 
GROUP BY cast((ra/cos(cast(dec*30 as int)/30.0))*30 as int)/30.0,
    cast(dec*30 as int)/30.0 