set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go



--
ALTER FUNCTION [dbo].[fGetObjFromRect] (@ra1 float, @ra2 float, 
				 @dec1 float, @dec2 float)
---------------------------------------------------------------------------
--/H Returns table of objects inside a rectangle defined by two ra,dec pairs.
--
--/T <br>Assumes dec1<dec2. There is no limit on the number of objects.
--/T <br>Uses level 20 HTM.
--/T <br> returned fields: 
--/T  <li> objID bigint,             -- id of the object
--/T  <li> run int NOT NULL,         -- run that observed the object
--/T  <li> camcol int NOT NULL,      -- camera column in run
--/T  <li> field int NOT NULL,       -- field in run
--/T  <li> rerun int NOT NULL,       -- software rerun that saw the object
--/T  <li> type int NOT NULL,        -- type of object (see DataConstants PhotoType)
--/T  <li> cx float NOT NULL,        -- xyz of object
--/T  <li> cy float NOT NULL,
--/T  <li> cz float NOT NULL,
--/T  <li> htmID bigint              -- hierarchical triangular mesh ID of object
--/T <br>sample call<br>
--/T <samp> select * from dbo.fGetObjFromRect(185,185.1,0,.1) </samp>
---------------------------------------------------------------------------
RETURNS @proxtab TABLE (
    objID bigint,
    cx float NOT NULL,
    cy float NOT NULL,
    cz float NOT NULL,
    htmID bigint
)
AS BEGIN
	declare @d2r float, @cmd varchar(1000), @radius float, 
	    @dot float, @d1 float, @d2 float,
	    @level int, @shift bigint, @ra float, @dec float,
	    @nx1 float, @ny1 float, @nz1 float,
	    @nx2 float, @ny2 float, @nz2 float,
	    @nx float, @ny float, @nz float
	-- calculate approximate center
	set @ra  = (@ra1+@ra2)/2;
	set @dec = (@dec1+@dec2)/2;
	--
	set @d2r  = PI()/180.0;
	-- n1 is the normal vector to the plane of great circle 1
	set @nx1  = SIN(@ra1*@d2r) * COS(@dec1*@d2r);
	set @ny1  = COS(@ra1*@d2r) * COS(@dec1*@d2r);
	set @nz1  = SIN(@dec1*@d2r);
	--
	set @nx2  = SIN(@ra2*@d2r) * COS(@dec2*@d2r);
	set @ny2  = COS(@ra2*@d2r) * COS(@dec2*@d2r);
	set @nz2  = SIN(@dec2*@d2r);
	--
	set @nx  = SIN(@ra*@d2r) * COS(@dec*@d2r);
	set @ny  = COS(@ra*@d2r) * COS(@dec*@d2r);
	set @nz  = SIN(@dec*@d2r);
	--
	set @d1 = @nx1*@nx+@ny1*@ny+@nz1*@nz;
	set @d2 = @nx2*@nx+@ny2*@ny+@nz2*@nz;
	if @d1<@d2 SET @dot=@d1 ELSE SET @dot=@d2
	set @radius = ACOS(@dot)/@d2r*60.0;
	INSERT @proxtab SELECT
	    objID,
	    cx, cy, cz, htmID
	from fGetNearbyObjEq(@ra,@dec,@radius)
	    WHERE (cz>@nz1) AND (cz<@nz2) 
		AND (-cx*@nx1+cy*@ny1>0)
		AND (cx*@nx2-cy*@ny2)>0 
  RETURN
  END 



