-- Correct the T-SQL function for the bounding box.
ALTER FUNCTION [dbo].[GetNeighbors]
(
    @Lat FLOAT,
    @Lon FLOAT,
    @MaxDist FLOAT,
    @Unit CHAR(2) = 'km'
)
RETURNS @Neighbors TABLE
(
    PlaceName VARCHAR(100) NOT NULL,
    State CHAR(2) NOT NULL,
    Dist FLOAT NOT NULL
)
AS
BEGIN
    DECLARE
        @LatMin FLOAT,
        @LatMax FLOAT,
        @LonMin FLOAT,
        @LonMax FLOAT,
        @Lat1R FLOAT,
        @Lon1R FLOAT,
        @Lat2R FLOAT,
        @Lon2R FLOAT,
        @Lat3R FLOAT,
        @DLonR FLOAT,
        @MaxDistR FLOAT,
        @DirR  FLOAT;

    -- Convert from degrees to radians
    SET @Lat1R = RADIANS(@Lat);
    SET @Lon1R = RADIANS(@Lon);

    IF @Unit = 'km'
        SET @MaxDistR = @MaxDist * PI() / 20001.6;
    ELSE
        SET @MaxDistR = @MaxDist * PI() / 12428.418038654259126700071581961;

    -- Determine minimum and maximum latitude and longitude
    -- Calculate latitude of north boundary
    SET @DirR  = RADIANS(0e0);
    SET @Lat2R = ASIN( SIN(@Lat1R) * COS(@MaxDistR)
                     + COS(@Lat1R) * SIN(@MaxDistR) * COS(@DirR));
    -- Convert back to degrees
    SET @LatMax = DEGREES(@Lat2R);
    -- On northern hemisphere, go east/west from northernmost point
    IF @Lat > 0
        SET @Lat3R = @Lat2R;

    -- Calculate latitude of south boundary
    SET @DirR  = RADIANS(180e0);
    SET @Lat2R = ASIN( SIN(@Lat1R) * COS(@MaxDistR)
                     + COS(@Lat1R) * SIN(@MaxDistR) * COS(@DirR));
    -- Convert back to degrees
    SET @LatMin = DEGREES(@Lat2R);
    -- On southern hemisphere, go east/west from southernmost point
    IF @Lat <= 0
        SET @Lat3R = @Lat2R;

    -- Calculate longitude of west boundary
    SET @DirR  = RADIANS(90e0);
    -- Need latitude first
    SET @Lat2R = ASIN( SIN(@Lat3R) * COS(@MaxDistR)
                     + COS(@Lat3R) * SIN(@MaxDistR) * COS(@DirR));
    -- Calculate longitude difference.
    SET @DLonR = ATN2(SIN(@DirR)  * SIN(@MaxDistR) * COS(@Lat3R),
                      COS(@MaxDistR) - SIN(@Lat3R) * SIN(@Lat2R));
    -- Calculate longitude of new point - ensure result is between -PI and PI.
    SET @Lon2R = ( CAST(@Lon1R - @DLonR + PI() AS DECIMAL(38,37))
                 % CAST(2*PI() AS DECIMAL(38,37)))
                 - PI();
    -- Convert back to degrees
    SET @LonMin = DEGREES(@Lon2R);

    -- Calculate longitude of west boundary
    SET @DirR  = RADIANS(-90e0);
    -- Need latitude first
    SET @Lat2R = ASIN( SIN(@Lat3R) * COS(@MaxDistR)
                     + COS(@Lat3R) * SIN(@MaxDistR) * COS(@DirR));
    -- Calculate longitude difference.
    SET @DLonR = ATN2(SIN(@DirR)  * SIN(@MaxDistR) * COS(@Lat3R),
                      COS(@MaxDistR) - SIN(@Lat3R) * SIN(@Lat2R));
    -- Calculate longitude of new point - ensure result is between -PI and PI.
    SET @Lon2R = ( CAST(@Lon1R - @DLonR + PI() AS DECIMAL(38,37))
                 % CAST(2*PI() AS DECIMAL(38,37)))
                 - PI();
    -- Convert back to degrees
    SET @LonMax = DEGREES(@Lon2R);

    -- Search neighborhood within boundaries
    WITH PlacePlusDistance
    AS
    (
        SELECT
            PlaceName,
            State,
            dbo.DistCLR (Lat, Lon, @Lat, @Lon, @Unit) AS Dist
        FROM Place
        WHERE
            Lat BETWEEN @LatMin AND @LatMax
            AND Lon BETWEEN @LonMin AND @LonMax
    )
    INSERT INTO @Neighbors
    (
        PlaceName,
        State,
        Dist
    )
    SELECT
        PlaceName,
        State,
        Dist
    FROM PlacePlusDistance
    WHERE Dist < @MaxDist;
    RETURN;
END;



using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections;
using System.Collections.Generic;

public partial class UserDefinedFunctions
{
    public struct BBox
    {
        public SqlString _PlaceName;
        public SqlString _State;
        public SqlDouble _Dist;
        public BBox(SqlString PlaceName, SqlString State, SqlDouble Dist)
        {
            _PlaceName = PlaceName;
            _State = State;
            _Dist = Dist;
        }
    }

    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true,
      DataAccess = DataAccessKind.Read, FillRowMethodName = "FillRow",
      TableDefinition = "PlaceName NVARCHAR(100), State NCHAR(2), Dist FLOAT")]
    public static IEnumerable CLRNeighbors(SqlDouble LatIn, SqlDouble LonIn,
                                           SqlDouble MaxDistIn, SqlString Unit)
    {
        double Lat = LatIn.Value, Lon = LonIn.Value, MaxDist = MaxDistIn.Value;
        double LatMax, LatMin, LonMax, LonMin, Dummy;
        bool Miles = (Unit.Value.Equals("mi"));
        // Calculate minimum and maximum longitude and latitude
        StoredProcedures.SpatialMove(Lat, Lon, MaxDist, 0,   // North
                                     Miles, out LatMax, out Dummy);
        StoredProcedures.SpatialMove(Lat, Lon, MaxDist, 180, // South
                                     Miles, out LatMin, out Dummy);
        // Go east/west from north or south, depending on hemisphere
        double LatHelp = (Lat > 0 ? LatMax : LatMin);
        StoredProcedures.SpatialMove(LatHelp, Lon, MaxDist, 90,  // West
                                     Miles, out Dummy, out LonMin);
        StoredProcedures.SpatialMove(LatHelp, Lon, MaxDist, -90, // East
                                     Miles, out Dummy, out LonMax);

        List<BBox> _BBdata = new List<BBox>();

        using (SqlConnection conn =
            new SqlConnection("context connection = true"))
        {
            SqlCommand comm = 
                new SqlCommand("SELECT PlaceName, State, Lat, Lon "
                             + "FROM dbo.Place "
                             + "WHERE Lat BETWEEN @LatMin AND @LatMax "
                             + "AND Lon BETWEEN @LonMin AND @LonMax", conn);
            comm.Parameters.Add("@LatMin", SqlDbType.Float);
            comm.Parameters[0].Value = LatMin;
            comm.Parameters.Add("@LatMax", SqlDbType.Float);
            comm.Parameters[1].Value = LatMax;
            comm.Parameters.Add("@LonMin", SqlDbType.Float);
            comm.Parameters[2].Value = LonMin;
            comm.Parameters.Add("@LonMax", SqlDbType.Float);
            comm.Parameters[3].Value = LonMax;

            conn.Open();
            SqlDataReader reader = comm.ExecuteReader();

            while (reader.Read())
            {
                double Lat2 = reader.GetDouble(2);
                double Lon2 = reader.GetDouble(3);
                double Dist = SpatialDist(Lat, Lon, Lat2, Lon2, Miles);
                if (Dist <= MaxDist)
                {
                    SqlString PlaceName = reader.GetSqlString(0);
                    SqlString State = reader.GetSqlString(1);
                    BBox BBnew = new BBox(PlaceName,
                                            State,
                                            (SqlDouble)Dist);
                    _BBdata.Add(BBnew);
                }
            }
        }

        return (IEnumerable)_BBdata;
    }

    [Microsoft.SqlServer.Server.SqlFunction(IsDeterministic = true,
      DataAccess = DataAccessKind.Read, FillRowMethodName = "FillRow",
      TableDefinition = "PlaceName NVARCHAR(100), State NCHAR(2), Dist FLOAT")]
    public static IEnumerable CLRDynamicBB(SqlDouble LatIn, SqlDouble LonIn,
                                           SqlString Unit)
    {
        double Lat = LatIn.Value, Lon = LonIn.Value, MaxDist = 100000;
        double LatMax, LatMin, LonMax, LonMin, Dummy;
        bool Miles = (Unit.Value.Equals("mi"));
        double Lat2, Lon2, Dist;
        BBox[] BBdata = new BBox[1];

        // Find MaxDist to use; try Tries locations east and west,
        //                      but stop at Threshold.
        using (SqlConnection conn = new SqlConnection("context connection = true"))
        {
            conn.Open();

            // Sample some places
            SqlCommand comm1 = new SqlCommand("SELECT Lat, Lon FROM "
                          + "(SELECT TOP(26) Lat, Lon "
                          + "FROM dbo.Place WHERE Lon > @Lon "
                          + "ORDER BY Lon ASC) AS East "
                          + "UNION ALL SELECT Lat, Lon FROM "
                          + "(SELECT TOP(26) Lat, Lon "
                          + "FROM dbo.Place WHERE Lon < @Lon "
                          + "ORDER BY Lon DESC) AS West", conn);
            comm1.Parameters.Add("@Lon", SqlDbType.Float);
            comm1.Parameters[0].Value = Lon;

            using (SqlDataReader reader = comm1.ExecuteReader())
            {
                // Bail out when below threshold
                while ((MaxDist > 8.5) && (reader.Read()))
                {
                    Lat2 = reader.GetDouble(0);
                    Lon2 = reader.GetDouble(1);
                    Dist = SpatialDist(Lat, Lon, Lat2, Lon2, Miles);
                    if (Dist <= MaxDist)
                        MaxDist = Dist;
                }
            }

            // Add tiny bit to MinDist to fence off rounding errors
            MaxDist += 0.001;
            // Calculate minimum and maximum longitude and latitude for MaxDist
            StoredProcedures.SpatialMove(Lat, Lon, MaxDist, 0,   // North
                                         Miles, out LatMax, out Dummy);
            StoredProcedures.SpatialMove(Lat, Lon, MaxDist, 180, // South
                                         Miles, out LatMin, out Dummy);
            // Go east/west from north or south, depending on hemisphere
            double LatHelp = (Lat > 0 ? LatMax : LatMin);
            StoredProcedures.SpatialMove(LatHelp, Lon, MaxDist, 90,  // West
                                         Miles, out Dummy, out LonMin);
            StoredProcedures.SpatialMove(LatHelp, Lon, MaxDist, -90, // East
                                         Miles, out Dummy, out LonMax);

            // Fetch rows within the dynamic bounding box
            SqlCommand comm2 = new SqlCommand("SELECT PlaceName, State, Lat, Lon "
                                   + "FROM dbo.Place "
                                   + "WHERE Lat BETWEEN @LatMin AND @LatMax "
                                   + "AND Lon BETWEEN @LonMin AND @LonMax", conn);
            comm2.Parameters.Add("@LatMin", SqlDbType.Float);
            comm2.Parameters[0].Value = LatMin;
            comm2.Parameters.Add("@LatMax", SqlDbType.Float);
            comm2.Parameters[1].Value = LatMax;
            comm2.Parameters.Add("@LonMin", SqlDbType.Float);
            comm2.Parameters[2].Value = LonMin;
            comm2.Parameters.Add("@LonMax", SqlDbType.Float);
            comm2.Parameters[3].Value = LonMax;

            using (SqlDataReader reader = comm2.ExecuteReader())
            {
                // Find place with lowest non-zero distance
                double MinDist = MaxDist;

                while (reader.Read())
                {
                    Lat2 = reader.GetDouble(2);
                    Lon2 = reader.GetDouble(3);
                    if ((Lat2 != Lat) || (Lon2 != Lon))
                    {
                        Dist = SpatialDist(Lat, Lon, Lat2, Lon2, Miles);
                        if (Dist < MinDist)
                        {
                            MinDist = Dist;
                            BBdata[0]._PlaceName = reader.GetSqlString(0);
                            BBdata[0]._State = reader.GetSqlString(1);
                        }
                    }
                }
                BBdata[0]._Dist = MinDist;
            }
        }

        return (IEnumerable)BBdata;
    }

    public static void FillRow(Object obj, out SqlString PlaceName,
                               out SqlString State, out SqlDouble Dist)
    {
        BBox bb = (BBox)obj;
        PlaceName = bb._PlaceName;
        State = bb._State;
        Dist = bb._Dist;
    }
};