-- Search near BCG galaxies.
-- For each galaxy in the BCG data set (brightest cluster galaxy), 
-- in 160<right ascension<170, -25<declination<35 count the number of 
-- galaxies within 30" of it that have a photoz within 0.05 
-- of that galaxy.
-- An extremely complex WHERE clause.

SELECT  G.objID, count(N.NeighborObjID) as pop
FROM 	Galaxy   as G,    		-- first gravitational lens candidate 
        Neighbors  as N,        	-- precomputed list of neighbors
  	Galaxy   as U,			-- a neighbor galaxy of G
	photoZ	   as Gpz,		-- photoZ of G.
        photoZ     as Npz		-- Neighbor photoZ
  WHERE G.ra between 160 and 170	
    and G.dec between -5 and 5	      	
    and G.objID = N.objID		-- connect G and U via the neighbors table
    and U.objID = N.neighborObjID 	-- so that we know G and U are within 
    and N.objID < N.neighborObjID 	-- 30 arcseconds of one another.
    and G.objID = Gpz.objID
    and U.objID = Npz.objID
    and abs(Gpz.Z - Npz.Z) < 0.05	-- restrict the photoZ differences
    and (G.flags & (dbo.fPhotoFlags('BINNED1') + dbo.fPhotoFlags('BINNED2') + dbo.fPhotoFlags('BINNED4'))) > 0 
	--and it is detected from at least one of the binned images 

  and (G.flags & ( dbo.fPhotoFlags('BLENDED') + dbo.fPhotoFlags('NODEBLEND') + dbo.fPhotoFlags('CHILD'))) != dbo.fPhotoFlags('BLENDED') 
      --and, if it is blended, it is either a child or not deblended further ??
  and (G.flags & (dbo.fPhotoFlags('EDGE') + dbo.fPhotoFlags('SATURATED'))) = 0
      -- and it is not near a ccd edge or saturated, where measurements may be bad
   -- and apply color cut for a BCG courtesy of James Annis of Fermilab
   and G.petroMag_i > 17.5
   and (G.petroMag_r > 15.5 or G.petror50_r > 2)
   and (G.petroMag_r < 30 and G.g < 30 and G.r < 30 and G.i < 30)
   and  (G.r < 19.2 
       and ( 1=1 or					-- huh?
             (G.r < (13.1 + (7/3)*(G.g - G.r) +  	
	              -- deRed_r < 13.1 + 0.7 / 0.3 * deRed_gr
               4 *(G.r - G.i - 0.18 )) 		
	              -- 1.2 / 0.3 * deRed_ri          
         and (( G.r - G.i - (G.g - G.r)/4 - 0.18) BETWEEN -0.2 and  0.2 )
	 and ((G.r +  	                        
                      -- petSB - deRed_r + 2.5 log10(2Pi*petror50^2)
              2.5 * LOG( 2 * 3.1415 * G.petror50_r * G.petror50_r )) < 24.2 )  
         )
        or 
         (      (G.r < 19.5 )
         and (( G.r - G.i -(G.g - G.r)/4 -.18) >    	
                      -- - deRed_gr/4 - 0.18
        	      -- cperp = deRed_ri             
                         (0.45 - 4*( G.g - G.r)))  		
                      -- 0.45 - deRed_gr/0.25
         and ((G.g - G.r) > ( 1.35 + 0.25 *(G.r - G.i)))
         and ((G.r  + 	
                      -- petSB - deRed_r + 2.5 log10(2Pi*petror50^2)          
                 2.5 * LOG( 2 * 3.1415 * G.petror50_r * G.petror50_r )) < 23.3 )
        ))  )
group by G.objID        
