0

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!

  • 3
    You should just find the nearest bar and order whatever drinks they serve. – Remus Rusanu Sep 2 '14 at 12:09
  • Thanks for the comment, I'd like to let people consume what they like within the minimum walking distance possible. – Leonardo Rossi Sep 2 '14 at 13:38
  • @leorossi MySQL has some particularities regarding spatial data, which may make these queries relatively slow (depending on the number of bars). Check if that is a problem for you. – jynus Sep 2 '14 at 15:46
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).

0

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
  • Thanks for the reply. Probably is not clear, but I'm looking for drink names, ordered by bar distance. – Leonardo Rossi Sep 2 '14 at 10:41
  • the edit query should be what you need – mxix Sep 2 '14 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.