Sorting MySQL results based on location and date
TL;DR;
SELECT
date,(
6367.45 * acos(
cos( radians(-29.678329467773) ) *
cos( radians( events.latitude ) ) *
cos( radians( events.longitude ) — radians(-51.130561828613) ) +
sin( radians(-29.678329467773) ) *
sin( radians( events.latitude ) )
)
) AS distanceFROM events
WHERE
events.longitude != ‘’
AND date > NOW()
ORDER BY ( POWER( 1/DATEDIFF(date, CURDATE()), 1.5 ) / distance ) DESC
The Problem
I work at a Sports Social Network in Brazil called SPRINTA. And we were facing a problem today, that when the user were searching for events on his region it was returning events, but not the most relevant for this guy.
We were already using the Haversine algorithm to calculate the distance, and ordering the events based first on closest dates and then on the user distance to it. But many times it would show some quite far events way before the closests ones just because of the date.
The Solution
The solution was actually quite simple.
If we take the date difference and divide by the distance (date_difference / distance) we get a ranking score setting the longest date difference and shortest distance. That’s something already…
That we are already getting the shortest distance, we can invert the date difference score in the ranking ( (1/date_difference) / distance). Voilá! Now we got higher scores for the closest date and closest location, but still not very relevant yet.
Just left to do is to calibrate it however you want. For the first tests I’ve set date is more importantant than distance, thus I’m setting the date_difference score to the power of 1.5 ( POW(1/date_difference, 1.5) / distance ).
Seems just fine for now. =)
Hope it helps you too!
PS: On MySQL you can, and you probably should save geolocation coordiantes as Data Type POINT.
PS 2: On MySQL version 5.7.6 you can use function ST_Distance_Sphere to calculate the difference between two geolocation points.