Sunday, April 5, 2009

Find Distance between two Geographical points Using SQl Server..

Hi Friends...
If you have ever worked with google map you will find this most interesting..
Google map provides API for integrating interactive map in you web Apllication.
You can find out Place on map By providing Address of that Place.for that Google provides Built-In API functions.
Any Place on the earth can Exactly Located By it's Latitude and Lonitude .
if you have this Latitude and Longitude you can Use Trigonomatry to Find out Distance Between Two Places on the earth.....

Depending Your Application's Business Logic You Need to find it In your Javascript Code,SQL Server,or IN C# or Whats ever you like..
For My need i Developed it With Sql Server.

This is My Function..

ALTER function [dbo].[CalculateDistance](@latitudet1 numeric(18,10),@latutudet2 numeric(18,10),@longitude1 numeric(18,10),@longitude2 numeric(18,10))
returns numeric(18,10)
as
Begin


declare @radious numeric(18,8)
set @radious=3963.1

declare @pi numeric(18,15)
set @pi=3.14159265358979323846

declare @lat_1 numeric(18,8)
set @lat_1=cast(@latitudet1 as numeric(18,5)) * ( @pi / 180)

declare @lat_2 numeric(18,8)
set @lat_2= @latutudet2 * (@pi / 180)

declare @lon_1 numeric(18,8)
set @lon_1= @longitude1 * (@pi / 180)

declare @lon_2 numeric(18,8)
set @lon_2= @longitude2 * (@pi / 180)

declare @distance numeric(18,8)
set @distance= (Acos(Cos(@lat_1) * Cos(@lon_1) * Cos(@lat_2) * Cos(@lon_2) + Cos (@lat_1) * Sin(@lon_1) * Cos(@lat_2) * Sin(@lon_2) + Sin(@lat_1) * Sin(@lat_2)) * @radious)

return @distance

END
--Select [dbo].[CalculateDistance] (1,4,7,9)


To Calcuate distance between two point Provide their Latitude and Longitude as argument This function will resurt distance in mile..
you can convert this Distance in kilometer multilyig by 1.6
I also have this function in C# language..Using C# u can calculate distance between two points ..
You can Find this on ..
http://forum.softwebsolutions.com/forum/default.aspx?g=posts&t=49...
Hope this will Help You...
Happy Programming..

5 comments:

  1. this is good approach for calculating distance between two Geo points In SQL Server 2000 and SQL Server 2005 ,But in SQL Server 2008 there is Built in supports for such calculation..
    There is new Datatype in SQl Server 2008 Named geographyThis Spatial datatype supports methods for various Calculation on Geographic points
    you can find details of Calculation distance between to Geo points on

    http://blogs.msdn.com/pekorica/archive/2009/03/16/calculating-a-distance-between-two-point-of-interest-using-sql-server-2008-geography-data-type.aspx

    ReplyDelete
  2. Here used mathematical approach to find out distance, you can find more such mathematical formulas to find different aspect of geography.

    http://www.movable-type.co.uk/scripts/latlong.html

    ReplyDelete
  3. thanks for posting this..

    it works me

    ReplyDelete

Comments posted on ASP.Net Ajax Tutorials Blog are moderated and will be approved only if they are on-topic and not abusive. Please email me or my team for tech-support or blogging related questions. Avoid including website URLs in your comments - Thanks Author