MySQL ORDER BY nearest zone

Question

Basically I have to get the algorithm to show restaurants in the selected zone and in other zones which actually makes sense..

E.g: if my area is in North of some city, I would like to show up restaurants in north, then move to central and then to east and then to west and finally to the extreme opposite i.e., south

In case of east, then central, north, south, finally extreme opposite i.e., west

I have in my db the following

zones table with ids, 1 - North, 2 - East, 3 - West, 4 - South, 5 - Central.

And localities table with locality/area of each city and structure goes as

locality_id | locality_name | zone_id(FK)

I have my model (php/codeigniter)

$this->db->select('menu_item.restaurant_id, menu_item.price, localities.locality_name, restaurant_information.restaurant_name, restaurant_information.restaurant_address, restaurant_information.is_halal, restaurant_information.cuisine, restaurant_information.city, restaurant_information.pincode');
$this->db->from('menu_item');
$this->db->where('menu_item.dish_id', $dish_id);
$this->db->where('menu_item.is_active', 1);
$this->db->where('restaurant_information.is_active', 1);
$this->db->join('restaurant_information', 'menu_item.restaurant_id = restaurant_information.restaurant_id');
$this->db->join('localities', 'restaurant_information.locality_id = localities.locality_id');

Its okay if I have too many joins or whatever.. but definitely not with lat/long or google geo..

Please help me out.. I tried order_by_field.. Its okay and it works but I am not able to give it dynamically..

Is there any solution or am I headed in the wrong direction..? Correct me if I got the structure wrong..!

And I am also ready if the order by part could be done on the result object where I can fetch the result and sort it based on the location.. but I prefer MySQL to do the job. Thanks in advance

I think you could shift the "coordinate" using the current offset to sort it by. — Pierre de LESPINAY, Nov 27 '13 at 12:41
@PierredeLESPINAY : I am really sorry I didnt understand what exactly "coordinate" means.. Can you elaborate please.. — Mohammed Ashiq, Nov 27 '13 at 12:43
If you use lat/long system, it is easy to find nearest places exactly. you can check [here](http://stackoverflow.com/questions/1006654/fastest-way-to-find-distance-between-two-lat-long-points) or [there](http://stackoverflow.com/a/5548877/2613728). — Alireza Amiri, Nov 27 '13 at 12:52
@AlirezaAmiri : True.. I totally agree with it.. But I have some 12,000 odd restaurants in my database, and getting positions for each of them looks to be a tough task.. — Mohammed Ashiq, Nov 27 '13 at 13:03

Pierre de LESPINAY · Accepted Answer · 2013-11-28T06:09:00.320

Basically, if I understand well you want to sort by the closest zone.

So maybe you should translate your ids 1, 2, 3, 4, 5 into coordinates like:

1 North: x:0, y:1
2 East: x:1, y:0
3 West: x:-1, y:0
4 South: x:0, y:-1
5 Center: x:0, y:0

Then sort them by distance calculated.

Example: You are located in West and you have restaurants located in West, North, South and Central

If you have the coordinates you can calculate the distance then sort your results:

West restaurant distance 0
North restaurant distance 1.2xxx (something like that)
South restaurant distance 1.2xxx (something like that)
Central restaurant distance 1

So, assuming you have implemented the functions GET_DISTANCE() & GET_COORD() you would have

SELECT
  menu_item.restaurant_id
, menu_item.price
, localities.locality_name
, restaurant_information.restaurant_name
, restaurant_information.restaurant_address
, restaurant_information.is_halal
, restaurant_information.cuisine
, restaurant_information.city
, restaurant_information.pincode
FROM menu_item
JOIN restaurant_information
  ON (menu_item.restaurant_id = restaurant_information.restaurant_id)
JOIN localities
  ON (restaurant_information.locality_id = localities.locality_id)
WHERE TRUE
  AND menu_item.dish_id = $dish_id
  AND menu_item.is_active = 1
  AND restaurant_information.is_active = 1
ORDER BY
  GET_DISTANCE(
    GET_COORD($my_position)
  , GET_COORD(restaurant_information.locality_id)
  )
;

Thank You @PierredeLespinay.. That definitely makes sense.. thank you.. A SQL statement example will be much more helpful.. I am a newbie!! — Mohammed Ashiq, Nov 27 '13 at 13:01
I'd say what you need are 2 MySQL functions, one returning the coordinate for each point and one to calculate the distance between 2 coordinates. — Pierre de LESPINAY, Nov 27 '13 at 13:04
Works like a charm.. Thanks a lot.. :-) You saved my day.. and probably a night hitting my head to figure out how to do this.... — Mohammed Ashiq, Nov 28 '13 at 13:06

MySQL ORDER BY nearest zone

1 Answers1