4

In my application, we require the user to enter a zipcode to query for nearby locations. We use the zipcode's LatLng object to act as an origin, and then we draw an imaginary "bounding box" to query the database for all locations within that range. These locations would show up as markers on the map that the user will be able to see. We want to limit the results to about 10-15.

Example of the database:

database

I was thinking of using MySQL's BETWEEN operator to query within ±2 degrees vertically and horizontally and then LIMIT n those results, OR I could pull the entire database into PHP and then abstract it using range().

I'm not really sure what's the best way to approach this, so I'd appreciate it if any of you have dealt with this scenario before and could provide some insight on how to solve it.

Jake Bellacera
  • 884
  • 1
  • 9
  • 18
  • 1
    I would strongly advise not pulling an entire table into PHP. As the table grows larger it will impact performance down the road. I would suggest trying to limit the data set as much as possible before pulling it to the front end. – afuzzyllama Feb 06 '12 at 21:28

1 Answers1

6

Do it in the sql

SELECT 
    id, 
    ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance 
FROM 
    markers 
HAVING 
    distance < 25 
ORDER BY 
    distance 
LIMIT 
    0 , 20;

http://code.google.com/apis/maps/articles/phpsqlsearch.html

afuzzyllama
  • 6,538
  • 5
  • 47
  • 64
thenetimp
  • 9,487
  • 5
  • 29
  • 42
  • Unfortunatly, that does a calculation on every single row and therefore might hit your DB hard.. See this question: http://stackoverflow.com/questions/5236921/geo-search-distance-in-php-mysql-performance – konsolenfreddy Feb 06 '12 at 19:36
  • Not if you have your indexes done properly. We ran a website with a store locator that operated just like this. Never had a problem with it. – thenetimp Feb 06 '12 at 19:38
  • I'm sure you can make some estimations about the location so you could eliminate some of the records with a `WHERE` clause? – afuzzyllama Feb 06 '12 at 21:29
  • This is actually working pretty well, the only problem is that most LatLng objects are floats. I have to round off my location to perform a search. Other than that this is a great start. – Jake Bellacera Feb 09 '12 at 22:20
  • @thenetimp can you please explain this statement please ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance . – Muhammad Usama Mashkoor Aug 10 '17 at 19:03
  • @usama This is based on some mathematic calculation that I myself do not understand. It is from the link that I shared along with the solution. I have used it before and know it to work. Please follow the link I shared where it explains how it works. – thenetimp Aug 14 '17 at 15:17
  • Thanks @thenetimp – Muhammad Usama Mashkoor Aug 16 '17 at 10:40