Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • Finding Distance on basis of Longitude and Latitude from Database

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 242
    Comment on it

    Below is an example of finding all the user who are stored in db and lies in the given range of Distance on basis of Longitude and Latitude.

    CREATE proc [dbo].[SP_UserByDistance]
    (
    @UserID uniqueidentifier,--UserId
    @Date datetime, --Date
    @Longitude float, --Longitude of Location
    @Latitude float, --Latitude of Location
    @Distance float  --Distance in KM
    )
    as
    Begin
    SELECT * FROM
    (
    SELECT UCF.UserID,UCF.CategoryId,UCF.FlirtingNo AS FlirtingNo,Convert(nvarchar(11),UCF.DATE,101) AS Date,
           UCF.TIME AS Time,UCF.Longitude,UCF.Latitude,UCF.StateOfReg as Location,u.EmailID,u.ImagePath,
            3956 * 2 * ASIN(SQRT( POWER(SIN((UCF.Latitude - abs(@Latitude)) * pi()/180 / 2), 2) 
            + COS(UCF.Latitude * pi()/180 ) * COS(abs(@Latitude) * pi()/180)  
            * POWER(SIN((UCF.Longitude - @Longitude) * pi()/180 / 2), 2) )) 
            AS distance  FROM UserCategoryFlirting UCF 
            INNER JOIN  dbo.[User] AS U ON U.UserId = UCF.UserId 
     WHERE ucf.categoryid=3
     ) t
    
      WHERE distance <= @Distance and (cast(Date as Date) = cast(@Date as date) and UserID<>@UserID )
    
    End
    
    
    GO
    
    

    Note:-Latitude Longitude of Users are save in UserCategoryFlirting table

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: