CODEWITHSUNDEEP
sqllaravelgeolocationelasticsearchquery-optimization
Igor
Igor

Reputation: 855

Geo location in mySQL: large array to Where In, How to do it right?

Good day, friends!

Sorry, if i wrote it too complex... thank you for the help.

I have an event (with some options) that starts in one location and ends in other location (other city).

Database: mySQL

Structure:

events.start_loc_id, events.end_loc_id - Foreign key to locations.id

Locations from: https://developers.google.com/maps/articles/phpsqlsearch_v3#findnearsql

So the problem:

Need to search an event with (for example)

  1. 100 km radius around start location
  2. 80 km around end location
  3. Rating > 10

So to look around location I using: https://gist.github.com/goosechaser/07dbf28818cedc1c9bb5

(btw: PHP framewok: Laravel)

How it goes now:

  1. We found 24 locations for start
  2. We found 33 locations for end

Then "WHERE IN" arrays to event's start and end location.

Laravel code:

$Events = Events::whereIn(
    'start_loc_id',
    Location::distance(
    Input::get('start_location_lat'),
    Input::get('start_location_lng'),
    Input::get('start_location_radius'),
    "km")
->orderBy("distance")->lists('id'))
->whereIn(
    'end_loc_id',
    Location::distance(
    Input::get('end_location_lat'),
    Input::get('end_location_lng'),
    Input::get('end_location_radius'),
    "km")
 ->orderBy("distance")->lists('id'))
 ->get();

So what if we have 1000 locations found? Obviously will work too slow.

  1. What is the better way to do it with php/mysql?
  2. Temporary table?
  3. Switch to elasticsearch?
  4. Add start_location_lat, start_location_lng and end_location_lat, end_location_lng to events table?

Thank you for the help!

Upvotes: 0

Views: 160

Answers (1)

Issam Zoli
Issam Zoli

Reputation: 2774

I recommend using an indexation method like MongoDB's geospatial commands http://docs.mongodb.org/manual/reference/command/nav-geospatial/, I think elasticsearch can do too http://www.elasticsearch.org/blog/geo-location-and-search/

Upvotes: 1

Related Questions