8

I'm developing a web app where the user sets a place in a map (Google Maps API) and I store the coordinates in a database.

At another page, I want to do a search which consists in getting my local coordinates and retrieve nearby places previously stored at my database based on a radius.

How can I do this?

Jason Aller
  • 3,541
  • 28
  • 38
  • 38
Everton Mendonça
  • 688
  • 9
  • 26
  • You can use this SQL select statement: http://stackoverflow.com/a/574736/4195406 depends on what radius you want, you can change the line `HAVING distance < 25` to your desired number. – ztan Mar 10 '15 at 20:53

2 Answers2

17

You can use what is called the Haversine formula.

$sql = "SELECT *, ( 3959 * acos( cos( radians(" . $lat . ") ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(" . $lng . ") ) + sin( radians(" . $lat . ") ) * sin( radians( lat ) ) ) ) AS distance FROM your_table HAVING distance < 5";

Where $lat and $lng are the coordinates of your point, and lat / lng are your table columns. The above will list the locations within a 5 nm range. Replace 3959 by 6371 to change to kilometers.

MrUpsidown
  • 21,592
  • 15
  • 77
  • 131
  • May you please explain your formula since I could not find the formula you used in the [Haversine formula link](https://en.wikipedia.org/wiki/Haversine_formula) you gave above. Cheers! :) – Rin Minase Jul 03 '18 at 07:57
  • 2
    This is the formula explained in the wikipedia link. I am no mathematician so it'd be hard to give more insights than what the linked page does. It's just *translated* for MySQL. It basically allows to find points within a certain radius, on the surface of the earth, which, as you might know, is not flat ;) – MrUpsidown Jul 03 '18 at 08:18
1

sounds like a "store locator script", for which google has an intro to here:

https://developers.google.com/maps/articles/phpsqlsearch_v3

what you'll need to do is to create a backend which allows your user to enter each place into the database. It's quite a large task and if I were you, I would take an open source script or similar and adapt it to your needs.

for example:

https://github.com/bjorn2404/jQuery-Store-Locator-Plugin

I would do a search for "free store locator script" and try their demos, see what appeals to you the most.

luke_mclachlan
  • 1,035
  • 1
  • 15
  • 35