SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
CREATE FUNCTION dbo.GreatCircleDistance
(
    @Latitude1 float = NULL,
    @Longitude1 float = NULL,
    @Latitude2 float = NULL,
    @Longitude2 float = NULL
)
RETURNS float
AS
BEGIN
    IF @Latitude1 IS NULL RETURN 0.0
    IF @Longitude1 IS NULL RETURN 0.0
    IF @Latitude2 IS NULL RETURN 0.0
    IF @Longitude2 IS NULL RETURN 0.0

    DECLARE @sin1 float
        ,@sin2 float
        ,@sind float
        ,@cos1 float
        ,@cos2 float
        ,@cosd float

    SELECT @sin1 = SIN(RADIANS(@Latitude1))
        ,@sin2 = SIN(RADIANS(@Latitude2))
        ,@sinD = SIN(RADIANS(@Longitude2 - @Longitude1))
        ,@cos1 = COS(RADIANS(@Latitude1))
        ,@cos2 = COS(RADIANS(@Latitude2))
        ,@cosD = COS(RADIANS(@Longitude2 - @Longitude1))

    RETURN ATN2     (SQRT(SQUARE(@cos2 * @sinD) + SQUARE(@cos1 * @sin2 - @sin1 * @cos2 * @cosD))
                ,@sin1 * @sin2 + @cos1 * @cos2 * @cosD
            ) * 3959.871
END
GO

quote:

---

but why 2 latitudes & longitudes?

---

SELECT *
    ,dbo.GreatCircleDistance(C.Latitude, C.Longitude, S.Latitude, S.Longitude) AS Miles
FROM City C
    JOIN CompanySite S
        ON dbo.GreatCircleDistance(C.Latitude, C.Longitude, S.Latitude, S.Longitude) <= 25.0
WHERE C.State = 'CA'

SELEFT companysite, Lat, Long
FROM MyTable
WHERE     Lat >= @MinLat
      AND Lat <= @MaxLat
      AND Long >= @MinLong
      AND Long <= @MaxLong

quote:

---

Originally posted by Kristen

"The problem of finding the limits of the square around the circle are not as simple as it seems"

Didn't know that ...

---