Find the closest bar, when searching for a drink

Question

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!

Answer 1

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).

Answer 2

Start with a function to calculate de distance between 2 points

calculate_distance_function(start_lat,start_lng,end_lat,end_lng)

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.

Edit:

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