-- Counts of galaxies in HTM triangles
-- This query is a sequential scan of galaxies with predicates for the color
-- magnitude. It group the results by a specified level in the HTM hierarchy
-- (obtained by shifting the HTM key) and return a count of galaxies in each
-- triangle together with the key of the triangle.

SELECT (o.htmID / power(2,24)) as htm_8 ,
    -- group by 8-deep HTMID (right shift HTM by 12) 
    avg(o.ra) as ra, 
    avg(o.[dec]) as [dec], 
    count(*) as pop -- return center point and count for display 
FROM Galaxy o
	-- for a full scan of the galaxy table, remove the JOIN constraint
  JOIN dbo.fGetNearbyObjEq(148,	0.5, 120) n		-- 2 degree cone search
  ON o.objId = n.objId	-- to get galaxy object params
WHERE (0.7*gMag - 0.5*rMag - 0.2*iMag) < 1.25 -- meeting this color cut
    and rMag < 21.75 -- brighter than 21.75 magnitude in red band. 
GROUP BY (o.htmID /power(2,24)) -- group into 8-deep HTM buckets.
ORDER BY (o.htmID /power(2,24))	-- ordered by 8-deep HTMID 
