0

I'm using the following query to get the next local events using their location. My database is growing and I've got events from all over Europe. So basically, the query calculates distances from events of two different countries (like Spain and Germany) which is really not useful since I'm looking for the next events within 20 KM. 

# latitude & longitude are the fields in DB
# 43.57 & 3.85 are given values representing the local test point
SELECT ( 6366 * ACOS( COS( RADIANS( 43.57 ) ) * COS( RADIANS( latitude ) ) * COS( RADIANS( longitude ) - RADIANS( 3.85 ) ) + SIN( RADIANS( 43.57 ) ) * SIN( RADIANS( latitude ) ) ) ) dist
FROM events
HAVING dist >0 AND dist <=20
ORDER BY event_time ASC 
LIMIT 0 , 5

So basically, this query is going to get all the distances of all the events before being able to use the HAVING.

Is there a better way?

halfer
  • 19,824
  • 17
  • 99
  • 186
Alain Zelink
  • 889
  • 2
  • 15
  • 33
  • 2
    @OllieJones The accepted answer on the question you've linked to is exactly what the OP is already using. This question is looking for an improvement on that. –  Feb 07 '15 at 22:00
  • @HoboSapiens, true, but my answer to that question, not the accepted one, addresses his question precisely. I didn't feel like typing it again. It's written up here. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/ – O. Jones Feb 07 '15 at 23:46

1 Answers1

2

Calculate the longitude and latitude of the four points of a box that encloses the radius surrounding the test point. Over distances of a few kilometres you can ignore errors due to the curvature of the Earth.

We can use a rough approximation to limit an initial search, then refine things later. At 50 degrees North (roughly Paris) a degree of latitude (North/South) is approximately 111.229km. A degree of longitude (East/West) is approximately 71.695km (I got these numbers from this page From this 20Km is approximately 10.8 seconds of latitude, and 16.74 seconds of longitude. Calculate a bounding box by adding and subtracting these numbers from the latitude and longitude of your test point.

Query for locations in that box using longitude and latitude.

If you're worried that your bounding box may be too large, or that an event in a corner of the box is outside the radius, calculate an accurate distance for those points you've already identified and filter out the few you don't want.

Note These figures approximate for 50deg North. As you move north of that your value for km of longitude will become too long, and south of that it will be too short. You could use a small table of lookup values, or calculate the actual distance from the latitude, or simply increase the initial bounding box in the longitude direction. The box is just a first approximation, so as long as it's bigger than the target area it's exact size doesn't matter too much.

  • I searched for the four points box and it seems that I've found a PHP library which would do that. After having called boundingCoordinates(), I should be able to use latitude and longitude boundaries and therefore have a much faster query. https://github.com/anthonymartin/GeoLocation.php – Alain Zelink Feb 07 '15 at 22:24
  • @AlainZelink I added an approximation method based on empirical data, but I see you've found a library. Good luck! –  Feb 07 '15 at 22:41