In one of my projects, we have a requirement to search near by locations. Our application front is Silverlight and backend is SQL Server 2008. In this article I am sharing my solution from SQL Server point of view. I have used Haversine formula to solve the problem. Click here to see more details on Haversine formula.

I have

In the Nearby search screen, allow user to enter full address of location where he/she wants to search for nearby locations. If it is valid address then we extract latitude and longitude of the address user entered.

Now front end passes latitude, and longitude to business layer. Let us see on how we can extract nearby locations of given address (latitude and longitude). SQL Server Stored proedure accepts maximum distance of search radius. You can make this configurable item (web.config entry) in web service.

create function dbo.IsLocationFallInRadius

(

@Latitude1 float,

@Longitude1 float,

@Latitude2 float,

@Longitude2 float,

@MaxDistance float

)

returns bit

as

/*

Function: IsLocationFallInRadius

Computes the Great Circle distance in miles

between two points on the Earth using the

Haversine formula distance calculation.

Input Parameters:

@Longitude1 - Longitude in degrees of point 1

@Latitude1 - Latitude in degrees of point 1

@Longitude2 - Longitude in degrees of point 2

@Latitude2 - Latitude in degrees of point 2

*/

begin

declare @radius float

declare @lon1 float

declare @lon2 float

declare @lat1 float

declare @lat2 float

declare @a float

declare @distance float

declare @IsInRadius bit

-- Sets average radius of Earth in miles

set @radius = 3959.0E

-- Convert degrees to radians

set @lon1 = radians( @Longitude1 )

set @lon2 = radians( @Longitude2 )

set @lat1 = radians( @Latitude1 )

set @lat2 = radians( @Latitude2 )

set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) + (cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )

set @distance = @radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))

if(@distance > @MaxDistance)

Begin

SET @IsInRadius = 0

End

Else

Begin

SET @IsInRadius = 1

End

return @IsInRadius

end

CREATE PROCEDURE [dbo].[SearchLocationsNearBy]

@Latitude float,

@Longitude float,

@MaxDistance float

AS

Begin

select * from Locations

Where dbo.IsLocationFallInRadius(@Latitude,@Longitude, Latitude, Longitude,@MaxDistance) = 1

End

In the where clause of query we I have called

I have

**Locations**table. This table has below fields:- LocationId
- LocationName
- StreetAddress
- City
- Zip
- State
- Country
- Description
- Logitude
- Latidue

**Latitude**and**Longitude**of the address.In the Nearby search screen, allow user to enter full address of location where he/she wants to search for nearby locations. If it is valid address then we extract latitude and longitude of the address user entered.

Now front end passes latitude, and longitude to business layer. Let us see on how we can extract nearby locations of given address (latitude and longitude). SQL Server Stored proedure accepts maximum distance of search radius. You can make this configurable item (web.config entry) in web service.

**Step #1:**Create a function in SQL Server. The details of the function are:**Function Name:**IsLocationFallInRadius**Purpose:**This function takes**and***latitude***of two locations. Also it takes allowed maximun distance between these two locations. It retuns 1(true) if two locations fall within maximum distance. Returns 0 (false) if if two locations does not fall within maximum distance.***longitude*create function dbo.IsLocationFallInRadius

(

@Latitude1 float,

@Longitude1 float,

@Latitude2 float,

@Longitude2 float,

@MaxDistance float

)

returns bit

as

/*

Function: IsLocationFallInRadius

Computes the Great Circle distance in miles

between two points on the Earth using the

Haversine formula distance calculation.

Input Parameters:

@Longitude1 - Longitude in degrees of point 1

@Latitude1 - Latitude in degrees of point 1

@Longitude2 - Longitude in degrees of point 2

@Latitude2 - Latitude in degrees of point 2

*/

begin

declare @radius float

declare @lon1 float

declare @lon2 float

declare @lat1 float

declare @lat2 float

declare @a float

declare @distance float

declare @IsInRadius bit

-- Sets average radius of Earth in miles

set @radius = 3959.0E

-- Convert degrees to radians

set @lon1 = radians( @Longitude1 )

set @lon2 = radians( @Longitude2 )

set @lat1 = radians( @Latitude1 )

set @lat2 = radians( @Latitude2 )

set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) + (cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )

set @distance = @radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))

if(@distance > @MaxDistance)

Begin

SET @IsInRadius = 0

End

Else

Begin

SET @IsInRadius = 1

End

return @IsInRadius

end

**Step #2:**Create Stored Procedure to retrive records from Locations table.**Stored Procedure Name:**SearchLocationsNearBy**Purpose:**It takes location latitude and longitude of search input. It also takes maximum distance of search. It returns locations which fall within maximum distance (within radius).CREATE PROCEDURE [dbo].[SearchLocationsNearBy]

@Latitude float,

@Longitude float,

@MaxDistance float

AS

Begin

select * from Locations

Where dbo.IsLocationFallInRadius(@Latitude,@Longitude, Latitude, Longitude,@MaxDistance) = 1

End

In the where clause of query we I have called

*IsLocationFallInRadius*function to vaidate if the records falls within the given rage. Business layer can invoke*SearchLocationsNearBy*stored procedure for searching.
## No comments:

## Post a Comment