4

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!

Edit:Thanks for the answers till now, but they are mainly focused on calculate distance and that is covered. I don't get how to order result (that are drinks) based on the distance I have from bars.

Think like this meta query

SELECT drink.id, drink.name 
FROM $DATA_POOL
WHERE drink.name LIKE '%MY_QUERY%' 
ORDER BY $ORDER

where

  • $DATA_POOL = a subset of drinks, that are server in the bars near me (I can already compute which bars are near me)
  • $ORDER = the distance I have from the bar, based on the API params lng and lat
Leonardo Rossi
  • 2,922
  • 2
  • 20
  • 28
  • possible duplicate of [Calculate distance between 2 GPS coordinates](http://stackoverflow.com/questions/365826/calculate-distance-between-2-gps-coordinates) – Bulat Sep 02 '14 at 10:26
  • does @TheChaos answer work for you? – Bulat Sep 02 '14 at 11:07

2 Answers2

0

ok, removed the old answer since it was not what you wanted...

is this more what you need?

SELECT
*,
@radius * 2 * ASIN(SQRT(POWER(SIN(( @startlat - abs(b.lat)) * pi() / 180 / 2),2) +
COS( @startlat * pi()/180) * COS(abs(b.lat) * pi() / 180) * POWER(SIN(( @startlng - b.lng) *
pi() / 180 / 2), 2) )) AS distance
FROM 
drinks d
JOIN drink_bars db ON (db.drink_id = d.id)
JOIN bars b ON (b.id_id = db.bar_id)
WHERE d.name LIKE '%mojito%'
ORDER BY distance ASC

So the query looks for a drink and all bars that have this drink and fetchs the data ordered by distance.

Olli
  • 1,708
  • 10
  • 21
  • Thanks for the answer. This is not really about how to compute distance from my starting lat/lng, I edited the question because it wasn't clear. – Leonardo Rossi Sep 02 '14 at 10:35
0

So you know how to calculate the distance, lets say we have a sub-query that does it.

All you need to do is to sort by distance like this:

SELECT d.id, d.name 
FROM 
 drinks d INNER JOIN
 drink_bars db ON db.drink_id = d.id INNER JOIN
 (SELECT id, <formula for distance> as distance FROM bars) b ON b.id = db.bar_id
WHERE d.name = @RequestedDrink AND b.distance < @MaxDistance
ORDER BY b.distance
Bulat
  • 6,869
  • 1
  • 29
  • 52