# Find the closest bar, when searching for a drink

Posted on

### 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!

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

``````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
``````