Problem: Find the closest bar that serves the drink(s) I’m looking for.
Here you can find the generated MySQL code http://pastebin.com/5Uc2ewUW
The API Request that interacts with this problem will have these parameters
query, String, ideally the drink name lng, double, the starting longitude lat, double, the starting latitude range, integer, max distance in meters (with a default value)
The query parameter may select more than one drink (think about searching for “Vodka”).
What would be a good strategy to write a SQL query with good performance?
I’m not very expert, but my idea is to
- SELECT bars in the range
- SELECT from drink__bars where bar_id is in the previous select result
- JOIN the drinks table to get drinks data
How do I set the order based on the distance?
Any suggestion is appreciated!
set @x = 15; set @y = 25; select sqrt(pow(b.lat-@y,2) + pow(b.lng-@x,2)) as distance, b.name, d.name from `drinks` d join `drink__bars` db on d.id = db.drink_id join `bars` b on b.id = db.bar_id where d.`name` = 'Beer' order by distance asc;
Full sqlfiddle, with some data. Use your preferred distance function to replace the plain one I used.
I would recommend against having
Id on many-to-many relations. And prefer NOT NULL always, unless you have a very strong case to allow NULLs (you don’t).
Start with a function to calculate de distance between 2 points
and a query like
declare @start_lat float, @start_lng; select b.name, MIN(calculate_distance_function(@start_lat,@start_lng,b.lat,b.lng) Distance from bars b left join drink_bars db on b.id = db.bar_id left join drinks d on d.id = db.drink_id group by b.name order by Distance ASC LIMIT 1
should do the trick.
declare @start_lat float, @start_lng; select b.name bar_name, d.name drink_name, calculate_distance_function(@start_lat,@start_lng,b.lat,b.lng) distance from bars b left join drink_bars db on b.id = db.bar_id left join drinks d on d.id = db.drink_id order by Distance ASC