Implementing a Proximity Search with SQL Server 2000

  • Article

 

IMPORTANT: MapPoint Web Service was retired on November 18, 2011. Please see Bing Maps for a list of current Bing Maps APIs.

Justin Southall
Microsoft Corporation

January 2004

Applies to:
    Microsoft® MapPoint® Web Service
    Microsoft SQL Server™ 2000

Summary: Learn how to create a Microsoft SQL Server 2000 stored procedure that runs proximity searches based on latitude and longitude coordinates. You can then incorporate this stored procedure into your MapPoint Web Service application. (8 printed pages)

Contents

Introduction
Requirements
How a Proximity Search Works
Formatting the Data
Importing the Data into SQL Server
Creating the Stored Procedure
Creating the User Login
Conclusion

Introduction

Microsoft® MapPoint® Web Service provides an effective proximity search solution for all categories of location-based customer data. You load data and icons into a secure hosted environment and then access them through a powerful and flexible Web service that exposes an industry standard Simple Object Access Protocol (SOAP) API. This makes it simple for an organization to create find the nearest applications, such as store or ATM locators.

While the MapPoint Web Service proximity search solution is effective for most scenarios, in some circumstances implementing proximity searches that are independent of MapPoint Web Service may be beneficial or necessary. Three factors might lead you to want to store and query your MapPoint Web Service data locally:

  • Sensitive or confidential information—If your data is sensitive, such as information about crimes, for example, you may not want to upload the data to MapPoint Web Service.
  • Frequent changes—If your data changes frequently, such as sales information that is updated in real time, it may not be practical to upload your data to MapPoint Web Service continually.
  • Large datasets—If you have a database containing millions of locations, you must store and manage it in a local Microsoft SQL Server™ database. MapPoint Web Service allows you to upload approximately 100,000 points of interest.

This article describes how to implement proximity searches locally using a SQL Server 2000 stored procedure. Details about implementing a stored procedure in your application are beyond the scope of this document.

Requirements

This section describes the software that you need and outlines requirements for your data and your SQL Server configuration.

Software Requirements

You'll need the following software:

  • Microsoft Windows® XP or Windows Server, depending on the edition of SQL Server that you use
  • Microsoft SQL Server 2000 (Personal Edition, Standard Edition, or Enterprise Edition)
  • Microsoft Access 2000 or Access 2003
  • Microsoft MapPoint Web Service Account (Evaluation or Standard)

Other Requirements

The procedures in this article assume the following:

  • The data is originally in an Access table, in the format prescribed by the instructions on the MapPoint Web Service Customer Services site (CSS).
  • The data contains fully geocoded addresses, which means that all Latitude and Longitude fields are populated.
  • You either have administrator access or know the superadmin (sa) password for the instance of SQL Server that you want to use.
  • The server is configured to allow Windows authentication and SQL Server authentication (Mixed Mode Authentication).
  • You know how to use Enterprise Manager, which is included with the SQL Server client applications.

How a Proximity Search Works

A 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(lat1)*sin(lat2)

b = cos(lat1)*cos(lat2)*cos(lon2-lon1)

c = arcos(a+b)

d = R*c

where lat equals latitude, lon equals longitude, R equals the Earth's radius, and d equals distance. Latitude and longitude are expressed in radians.

As you can see, the law of cosines takes two latitude and longitude pairs, one for a location that is known in advance, such as the address of a store, and one for a location that is provided by a user, such as the user's address. A third input, the radius in which to search, is also required.

During a proximity search, the distance is calculated between the user's address and each business location within the search radius, and the results are then ordered by distance.

Implementing a stored procedure for proximity searches includes the following steps:

  1. Formatting the source data, which is stored in an Access database
  2. Importing the data into a newly-created SQL Server database
  3. Creating the stored procedure for proximity searches
  4. Creating a SQL Server user login for use with proximity searches

The following sections describe each step in detail.

Formatting the Data

As mentioned earlier, you will be importing data from Access into SQL Server, and your file must meet the requirements listed on the CSS. Additionally, you'll add three fields to the Access table containing your data, and then you'll need to run three queries on those fields.

The queries populate the new fields using a portion of the proximity search algorithm, which performs the calculations for the locations that are known in advance, such as business addresses. This step could have been included in the stored procedure, but then the calculations would run every time a search is conducted. To improve performance, you run the queries run once, before you load the data into SQL Server.

The Access queries are based on the following calculations:

XAxis = cos(radians(Latitude)) * cos(radians(Longitude))
YAxis = cos(radians(Latitude)) * sin(radians(Longitude))
ZAxis = sin(radians(Latitude))

To format the Access data

  1. Add the following fields:

    Name Type
    XAxis Text
    YAxis Text
    ZAxis Text

  2. Build the following three queries in Access. In the queries, [TABLENAME] represents the name of the table in which your data is stored. The following formulas are used in the queries to represent radians and PI:

    • Radians—degrees * (PI/180)
    • PI—4*((4*atn(1/5))-(atn(1/239)))
    UPDATE [TABLENAME] SET XAxis = (cos(((4*((4*atn(1/5))-
  (atn(1/239))))/180)*[TABLENAME].Latitude)*cos(((4*((4*atn(1/5))-
  (atn(1/239))))/180)*[TABLENAME].Longitude));

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

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

  3. Run the queries.

    When the queries run, the XAxis, YAxis, and ZAxis fields are populated. The data is now ready to be loaded into SQL Server

Importing the Data into SQL Server

In this step, you use the Data Transformation Services Import/Export Wizard to import your Access data into SQL Server.

To import data

  1. Open Enterprise Manager: On the Start menu, point to All Programs, point to Microsoft SQL Server, and then click Enterprise Manager.

  2. In the left pane, expand Microsoft SQL Servers, expand SQL Server Group, and then expand the group containing the instance of SQL Server that you want to use.

    If the instance of SQL Server is on your local computer, expand the Local group. If you want to use an instance of SQL Server on a different computer, you must add that server to the SQL Server group. For more information, see SQL Server Books Online.

  3. Right-click Databases, point to All Tasks, and then click Import Data. When the first screen of the Data Transformation Services Import/Export Wizard appears, click Next.

  4. On the next screen, in the Data Source drop-down list, select Microsoft Access, specify a file name for the Access database, and then click Next.

  5. In the Destination drop-down list, ensure that Microsoft OLE DB Provider for SQL Server is selected, and in the Server drop-down list, choose the instance of SQL Server to which you want to connect.

  6. Select either Use Windows Authentication or Use Server Authentication (depending on how the instance of SQL Server is configured).

  7. In the Database drop-down list, select <new>. Give your database a name, accept the defaults, click OK, and then click Next.

  8. On the next page, leave the radio button Copy table(s) and view(s) from the source database selected and click Next.

  9. On the next screen, select the check box next to the table that contains your location data. Leave the default value in the Destination column (it will look something like [dbname].[dbo].[tablename]), and then click Next.

  10. On the next screen accept the defaults, click Next, and then click Finish to exit the wizard.

You should see the status of the import operation as your data is imported into SQL Server.

After the import operation is finished, your new database will be listed in the Enterprise Manager Databases node (you may have to refresh the list first).

Creating the Stored Procedure

The next step is to create the stored procedure, which is called sp_FindNearby. The stored procedure applies the remainder of the law of cosines algorithm. It takes the address the user provides, calculates the distance between the address and each business location within the specified search radius, and then orders the results by distance.

When you run sp_FindNearby, you must supply four input parameters:

  • @CenterLat—latitude of the search center point.
  • @CenterLon—longitude of the search center point.
  • @SearchDistance—the proximity search distance. The search will not return records in which the distance is greater than this value.
  • @EarthRadius—the radius of the Earth, specified in miles or kilometers.

To create the stored procedure

  1. In the left pane of Enterprise Manager, find your database, and then expand it.

  2. Right-click Stored Procedures, and then click New Stored Procedure.

  3. In the text box that opens, type following procedure. In the procedure, [TABLENAME] refers to the name of your database table. Ensure sure that the list of fields in the SELECT statement match the fields in your table.

    CREATE PROCEDURE dbo.sp_FindNearby
   @CenterLat float, 
   @CenterLon float, 
   @SearchDistance float, 
   @EarthRadius float
AS
declare @CntXAxis float
declare @CntYAxis float
declare @CntZAxis float

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

select *,  ProxDistance = @EarthRadius * acos( XAxis*@CntXAxis + 
  YAxis*@CntYAxis + ZAxis*@CntZAxis)
from  [TABLENAME]
where  @EarthRadius * acos( XAxis*@CntXAxis + YAxis*@CntYAxis + 
  ZAxis*@CntZAxis) <= @SearchDistance
order by ProxDistance ASC
GO

  4. Click OK.

    You should now see the sp_FindNearby stored procedure listed in the right pane.

Creating the User Login

Creating a user specifically for the purposes of proximity searches is not required, but it is recommended. Because a stored procedure is a snippet of code that executes on your server, limiting access to it and giving users only the permissions they need is a good idea. This step creates a new user, gives the user read-only permissions on the data, and permission to execute the sp_FindNearby stored procedure.

To create a new login

  1. Right-click the server node, and then click Properties.
  2. Click the Security tab, and under Authentication, ensure that SQL Server and Windows is selected, and then click OK.
  3. In the left pane, expand the server, expand the Security node, right-lick Logins, and then click New Login.
  4. On the General tab, do the following:
    • In Name, type a name for the new user account.
    • Select the SQL Server Authentication radio button.
    • In Password, type a password for the new user account.
    • In the Database drop-down list, select the database you created earlier.
  5. Click the Database Access tab, and then do the following:
    • In the Permit column select the check box next to the newly-created database.
    • Under Permit in Database Role, select db_datareader.
    • Click OK, confirm the password when prompted, and then click OK again.
  6. In the left pane, and expand the database, expand Stored Procedures, right-click the new stored procedure, and then click Properties.
  7. Click Permissions, and then select the check box next to the new user name under EXEC to give your new user execute permissions on your stored procedure, and then click OK twice to close all windows.

Conclusion

Storing and managing your data locally can be beneficial if you have sensitive or frequently-changing data or very large datasets. By implementing proximity searches through SQL Server, you can maintain your data in a secure, local environment while taking advantage of the wide variety of location-based services that MapPoint Web Service offers.

Justin Southall is a Web Development Engineer in the Microsoft MapPoint Business Unit, and is a member of the Professional Services team.