The MapPoint Web Service provides a comprehensive set of find nearby (proximity search) APIs and custom data storage hosted in a secure environment on Microsoft servers. This setting works seamlessly for most business needs. However, while the MapPoint Web Service proximity search solution is effective for most scenarios, in some circumstances, implementing proximity searches independent of MapPoint Web Service may be necessary. There are three scenarios that might lead you to want to store and query your points of interest data locally within your enterprise network:
If your data is sensitive, such as information about crimes, for example, you may not want to upload the data to MapPoint Web Service.
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.
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 up to approximately 100,000 points of interest.
This section describes how to implement simple proximity searches locally within your enterprise using a SQL Server.
This appendix assumes that you have a basic understanding of SQL Server, Stored Procedures, and ADO.NET.
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:
latitude_1
, longitude_1
, latitude_2,
and longitude_2
are the points between which
we want to measure the distance (latitude and longitude are
expressed in radians)
R
is the Earth’s radius
(3,963.0 in miles or 6,378.5 in kilometers)
d
is the distance between
the two points
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
For the sake of simplicity, I’m only showing one property
(Name
) on the business entity; in
reality, you can have as many properties as you want.
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.
3.142.133.54