SQLTeam.com | Weblogs | Forums

Get near by data base on GPS locations



I have a list of GPS locations stored in my database, i want to get near by stored location from my database

Suppose i have a data base which is contains list of cafeterias of Ahmadabad (INDIA -> Gujarat -> Ahmadabad)

i am standing over a one location (GPS Location (latitude, longitude)) i want to Search base on GPS location Which Cafeteria are surround me in between 5 KM (Kilo Meter)


According to this, you could do:

declare @lon float=72.571362;
declare @lat float=23.022505;
declare @r float=6371;
declare @rad float=5;	/* This is your search radius in km */

select *
      ,acos(sin(@lat)*sin(radians(latitude))+cos(radians(@lat))*cos(radians(latitude))*cos(radians(longitude)-radians(@lon)))*@r as distance
  from yourtable
 where latitude between @lat-degrees(@rad/@r) and @lat+degrees(@rad/@r)
   and longitude between @lon-degrees(@rad/@r/cos(radians(@lat))) and @lon+degrees(@rad/@r/cos(radians(@lat))
   and acos(sin(@lat)*sin(radians(latitude))+cos(radians(@lat))*cos(radians(latitude))*cos(radians(longitude)-radians(@lon)))*@r<=@rad

Btw. you should put index on latitude and longitude fields to gain max performance

Calculate distance in miles between two coordinates