1

Possible Duplicate:
how to do location based search
Getting similar longitude and latitude from database

I have a dating PHP application working with MySQL database where users enter their location and based on it, other profiles are shown.

Structure of table cities:

int id PK
int country_id FK
varchar(50) name
float longitude
float latitude

Example entry in table:

1 | 1 | New York | 23.20323 | 12.32356

And I want to select all cities which have longitude and latitude less then 23.20323 + 50 km, more then 23.20323 - 50km respectively.

The issue here is not a SQL syntax, but the actual radius calculation.

Community
  • 1
  • 1
kudlajz
  • 1,068
  • 8
  • 15

3 Answers3

4

You can do this using Great Circle algorithms. http://en.wikipedia.org/wiki/Great-circle_distance

Here's how to find distance. You would need to solve the equation for lat2.

distance = ((factor * (lat2-lat1)) ^ 2 + (factor * (lng2 - lng1) * cos(lat2 / 57.3)) ^ 2) ^ .5

Note: factor ~ 69.1 for miles ~ 115.1666667 for km

Jimmy Kane
  • 16,223
  • 11
  • 86
  • 117
  • 1
    This is the correct way to calculate accurate distance. However, unless you need either precise distance, or you are calculating distance over a distance of more than several hundred miles, you can use a simpler form of this equation that will calculate distance on a flat plane. – datasage Dec 19 '12 at 16:06
  • Yes, this algorithm is for calculating distance between two long/lat-itudes, but what if I want the opposite? User chooses the radius of 50 km and I want to show him profiles from all the cities which are in this radius of 50 km. – kudlajz Dec 19 '12 at 16:43
  • Calculate the distance, then solve for lat2? Everything is there! – Jimmy Kane Dec 19 '12 at 16:47
2

Take a look at the Haversine function. This is the spherical distance calculation for two latitude / longitude points.

borrible
  • 17,120
  • 7
  • 53
  • 75
1

This should be what you're after.

//Get your base city location e.g. New York
$base_lat = 12.32356;
$base_lng = 23.20323;

//Get target distance in miles
$target_distance = 31; //50km is approx 31 miles

//Select all cities that are closer than 50km (31 miles)
SELECT id, ( 3959 * acos( cos( radians('.$base_lat.') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('.$base_lng.') ) + sin( radians('.$base_lat.') ) * sin( radians( latitude ) ) ) ) AS distance
FROM cities
WHERE latitude IS NOT NULL
AND longitude IS NOT NULL
HAVING distance < $target_distance
ORDER BY distance ASC

//Select all cities that are further than 50km (31 miles)
SELECT id, ( 3959 * acos( cos( radians('.$base_lat.') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('.$base_lng.') ) + sin( radians('.$base_lat.') ) * sin( radians( latitude ) ) ) ) AS distance
FROM cities
WHERE latitude IS NOT NULL
AND longitude IS NOT NULL
HAVING distance > $target_distance
ORDER BY distance ASC
Daniel Greaves
  • 987
  • 5
  • 24