• http://www.techneurons.com/career/
  • experienced programming consultants for hire !!!

    Contact Now

    T-SQL Programming Articles

    KB: Calculate distance between two points on globe from latitude and longitude coordinates

    WEDNESDAY, MARCH 17, 2010

    Calculation of geographical distance is a very important utility function in GPS related procedures. The Haversine formula is used commonly for this. The haversine formula is an equation important in navigation, giving great-circle distances between two points on a sphere from their longitudes and latitudes. This actually gives the shortest distance between two points, assuming the globe as a clean globoid sphere with no obstacles between the points.

    For a large scaled calculation, it would be sufficient to calculate distance between two points alone, but for smaller scales, to get a proper result, the path between the two points should be broken into various mid-points considering all obstacles and roads, etc., and to make a summation of distance between one mid-point to the next one.

    As calculation of distance is mostly a data oriented job, it would be better to have the distance calculation procedure written as a T-SQL function. This function would required four inputs totally - latitude and longitude of both points. An important parameter in the distance calculation is the radius of earth at a given point. This cannot be seen as a constant because earth is not a proper sphere, but a globoid with radius 6357 to 6378 kilometers from poles to the equatorial belt.

    The Haversine formula for calculation of distance is as follows:

    Haversine formula:

    R = earth’s radius (mean radius = 6,371km)
    Δlat = lat2− lat1
    Δlong = long2− long1
    a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2)
    c = 2.atan2(√a, √(1−a))
    d = R.c

    The T-SQL implementation of the code would be as follows: (This includes a formula to calculate the radius of earth, unlike the above contant value)

    ALTER FUNCTION [dbo].[geo_Distance]
    (
    @Lat FLOAT,
    @Long FLOAT,
    @LLat FLOAT,
    @LLong FLOAT
    )
    RETURNS FLOAT
    AS
    BEGIN
    DECLARE @dLat FLOAT, @dLong FLOAT, @A FLOAT, @C FLOAT
    DECLARE @RADIUSE FLOAT, @RADIUSP FLOAT, @RADIUS FLOAT
    DECLARE @NR FLOAT, @DR FLOAT
     
    SET @RADIUSE = 6378135
    SET @RADIUSP = 6356750
     
     
    SET @dLat =(@Lat - @LLat) / 180 * PI()
    SET @dLong =(@Long - @LLong) /180 * PI()
    SET @A =(SIN(@dLat / 2) * SIN(@dLat / 2))
    +( COS(@Lat / 180 * PI()) * COS(@LLat / 180 * PI())
    * SIN(@dLong / 2) * SIN(@dLong / 2))
    SET @C = 2 * ATN2(SQRT(@A), SQRT(1-@A))
     
    /*
     Calculate radius of earth
    */
    SET @NR = POWER(@RADIUSE * @RADIUSE * COS(@Lat/180*PI()), 2)
    + POWER( @RADIUSP * @RADIUSP * SIN(@Lat/180*PI()), 2)
    SET @DR = POWER(@RADIUSE * COS(@Lat/180*PI()), 2)
    + POWER( @RADIUSP * SIN(@Lat/180*PI()), 2)
    SET @RADIUS = SQRT(@NR/@DR)
     
    RETURN(@RADIUS * @C)

          END

     

    ConsultSarath - We provide end to end outsourcing solutions for .net programming requirements- you can hire programmer for hourly rates, for monthly commitments, for short term projects, for long term projects, Contact to know our hourly rates for programmer in India. ConsultSarath - We provide end to end outsourcing solutions for php programming requirements- you can hire programmer for hourly rates, for monthly commitments, for short term projects, for long term projects, Contact to know our hourly rates for programmer in India. ConsultSarath - We provide end to end outsourcing solutions for python programming requirements- you can hire programmer for hourly rates, for monthly commitments, for short term projects, for long term projects, Contact to know our hourly rates for programmer in India.

    Other Popular Articles
    We are experts in Cloud Computing Technologies. We can assist you to build high scalable business applications using Amazon Web Services (Amazon EC2, Amazon S3, Amazon SES, SNS, CloudFront), Windows Azure Platforms - Windows Azure and SQL Server Azure, Google App Engine using Python and Django Framework. We are Expert Programming Consultants available at affordable rates per hour. We work on several technologies - .NET, Python, Google App Engine, PHP, Windows Azure, Amazon Web Services ...