Proximity search works by applying the law of cosines, which calculates the distance between two points on the globe. Because the law of cosines takes into account the curvature of the earth, it is considered better than other methods, such as the Pythagorean Theorem. An abridged version of the law of cosines is as follows:

    a = sin(latitude_1) * sin(latitude_2)

    b = cos(latitude_1) * cos(latitude_2) *
         cos(longitude_2 - longitude_1)

    c = arcos( a + b )

    d = R*c

In this law, the following facts are true:

This formula takes two latitude and longitude pairs, one for a location stored in your SQL data store, such as the address of an ATM, and one for a location provided by your end user, such as the user’s current location around which she wants to find ATMs. During a proximity search, the distance is calculated between the user’s current address and each business location within the specified search radius, and the results are then ordered by distance.

In the following sections, I will go through a step-by-step approach to building your own spatial proximity search using SQL Server.

Let’s say you have business locations for which you want to enable proximity searching; in order to do that, you need to have your business listings and their corresponding location information expressed in latitude and longitude coordinates as radians. Once you have that data, you need to create a SQL table (say, BusinessEntity) to store your business listings:

    CREATE TABLE [BusinessEntity] (
        [ID] [int] IDENTITY (1, 1) NOT NULL,
        [Name] [nvarchar] NOT NULL,
        [Latitude] [float] NOT NULL,
        [Longitude] [float] NOT NULL,
[XAxis] [float] NOT NULL,
        [YAxis] [float] NOT NULL,
        [ZAxis] [float] NOT NULL,
    ) ON [PRIMARY]
    GO

Once you created the table successfully, load your data into it using either SQL Data Transformation Services (DTS) or SQL Server Integration Services (SSIS).

Now that you have your business listings available in the SQL table, run the following SQL query (either using the SQL Server Enterprise Manager or SQL Query Analyzer) to calculate the values for the columns XAxis, YAxis and ZAxis:

    UPDATE [BusinessEntity] SET XAxis = (cos(((4*((4*atn(1/5))-
      (atn(1/239))))/180)*[ BusinessEntity].Latitude)*cos(((4*((4*atn(1/5))-
      (atn(1/239))))/180)*[ BusinessEntity].Longitude));

    UPDATE [BusinessEntity] SET YAxis = (cos(((4*((4*atn(1/5))-
      (atn(1/239))))/180)*[ BusinessEntity].Latitude)*sin(((4*((4*atn(1/5))-
      (atn(1/239))))/180)*[ BusinessEntity].Longitude));

    UPDATE [BusinessEntity] SET ZAxis = (sin(((4*((4*atn(1/5))-
      (atn(1/239))))/180)*[ BusinessEntity].Latitude));

This query creates the values for the XAxis, YAxis, and ZAxis columns in the table. Now your data is ready to be searched for spatial proximity queries.

Create a SQL stored procedure called FindNearby:

    CREATE PROCEDURE FindNearby
       @CenterLat float,
       @CenterLon float,
       @SearchDistance float,
       @Units int
AS
    declare @CntXAxis float
    declare @CntYAxis float
    declare @CntZAxis float
    declare @EarthRadius float

    -- Miles = 0
    if(@Units = 0)
    set @EarthRadius = 3963.0
    else
    set @EarthRadius = 6378.5

    set @CntXAxis = cos(radians(@CenterLat)) * cos(radians(@CenterLon))
    set @CntYAxis = cos(radians(@CenterLat)) * sin(radians(@CenterLon))
    set @CntZAxis = sin(radians(@CenterLat))

    select BusinessEntity.ID,
           BusinessEntity.Name,
           ProxDistance = @EarthRadius * acos( XAxis*@CntXAxis +
                          YAxis*@CntYAxis + ZAxis*@CntZAxis)

    from  [BusinessEntity]

    where  @EarthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis +
           ZAxis*@CntZAxis) <= @SearchDistance

    order by ProxDistance ASC

Now you can execute FindNearby queries using your own SQL implementation.

Now that you have the SQL Server implementation of the FindNearby ready, you can use it from your applications using the following C# code:

    public void FindNearbyBusinessEntities(double latitude,
                                           double longitude,
                                           double distance)
    {
        //Assume miles distance units for now
        //Change to 1 if you want Kilometers
        int units = 0;

        //Create SQL command
        //import System.Data.Sql namespace if you haven't already
        SqlCommand cmd = new SqlCommand("FindNearby");

        //Assign input values to the sql command
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@CenterLat", latitude);
        cmd.Parameters.Add("@CenterLon", longitude);
        cmd.Parameters.Add("@SearchDistance", distance);
        cmd.Parameters.Add("@Units", units);

        //Define a connection
        SqlConnection sqlConn = null;
        try
        {
            //Open the connection
            sqlConn = new SqlConnection("your sql connection string");
            // If the connection is closed, open it
            if(sqlConn.State == ConnectionState.Closed)
                sqlConn.Open( );

            if(sqlConn.State == ConnectionState.Open)
            {
                cmd.Connection = sqlConn;
                SqlDataReader dreader =  cmd.ExecuteReader( );
                if(dreader != null)
                {
                    while(dreader.Read( ))
                    {
                        //Get id of the business entity
                                               int id=0;
                        if(!dreader.IsDBNull(0))
                            id = (int)dreader[0];

                        //Get name of the business entity
                                               string name = string.Empty;
                        if(!dreader.IsDBNull(1))
                            name = (string)dreader[1];

                        //Do something userful with your entity such as
                        //creating a puhspin object to render on map or
                        //something
                        . . .

                    }
                }
            }

        }
        catch(Exception ex)
        {
            //Handle your exception processing here
        }
        finally
        {
            if(sqlConn != null)
            {
                if(sqlConn.State == ConnectionState.Open)
                {
                    //Close sql connection
                    sqlConn.Close( );
                }
            }
        }
    }

Now you have your own FindNearby capability that can be used in conjunction with other MapPoint Web Service features, such as rendering nearby business entities.