Using PHP and MySQL to calculate geolocation distance

Question

I have a php script and a database of geolocations.

At the moment my users can enter their postcode, using Yahaa I get their geo location.

Now that I have their geolocation I want to write a script that loads their local businesses in order of distance. I'm guessing to do this I need to load records from the database that have the most similar coordinates to the users current geolocation. I tried this code:

$sql = "SELECT * FROM dirlistings WHERE ABS(latitude - $lat) AND ABS(longitude - $long)";

However it just displays the results in normal order.

Have I missed anything?

**warning** your code is susceptible to sql injection attacks. — Daniel A. White, Jan 31 '12 at 17:54
I am doing something exactly like this, the thing is its stored in home computer and I am not there at this time, I can give all the information you need for this. There is more than just subtraction since the earth is a sphere. — Grigor, Jan 31 '12 at 18:00

score 3 · Answer 1 · edited May 23 '17 at 12:11

3

I think you want an ORDER clause in there somewhere.

But what you really want is the Haversine formula: MySQL Great Circle Distance (Haversine formula)

edited May 23 '17 at 12:11

Community

1
1

answered Jan 31 '12 at 17:54

Tim

6,281
3
39
49

1

Over short distances (anything less than ~50 miles), you can treat the Earth as a flat plane for the purposes of calculating distance (at least in this domain). – Jonathan Rich Jan 31 '12 at 18:02

score 3 · Answer 2 · answered Jan 31 '12 at 17:59

3

It's more complicated than you think.

This is a great article that helped me a lot. Although its written in javascript, you easily change it to php.

http://www.movable-type.co.uk/scripts/latlong.html

answered Jan 31 '12 at 17:59

alex

580
1
4
12

score 2 · Answer 3 · answered Jan 31 '12 at 18:01

You've missed quite a few things. In order to do what you're trying to do, you need to compute (using the Pythagorean theorem) the distance between two points, and then order by that distance.

You can calculate distance ( (lat - $lat)^2 + (lon - $lon)^2 )^0.5 via:

SQRT(
    POW(latitude - ' . $lat . ',2) 
    + 
    POW(longitude - ' . $lon . ',2)
) AS distance

Then it's as simple as:

ORDER BY distance ASC

score 2 · Accepted Answer · answered Jan 31 '12 at 18:05

2

Note that circle distance isn't going to be precise enough if you're talking about large distances (thousands of miles, for example), as the earth's surface isn't flat. If you need a better formula for geo-distance calculation, you can use something like this:

$dlat = ((double)$lat) / 57.29577951;
$dlon = ((double)$lon) / 57.29577951;

$sql = "SELECT *
        FROM dirlistings
        WHERE 3963.0 * acos(sin(latitude / 57.29577951) * sin($dlat) + cos(latitude / 57.29577951) * cos($dlat) * cos($dlon - longitude / 57.29577951)) < MAX_DIST
        ORDER BY acos(sin(latitude / 57.29577951) * sin($dlat) + cos(latitude / 57.29577951) * cos($dlat) * cos($dlon - longitude / 57.29577951))
        ";

The distances here are in miles - make sure to specify correct max distance - and this formula will give very close results for distances of even ten thousand miles. Note though that such computation is quite time- and power-intensive and if you are not dealing with large distances (i.e. nothing more than a couple hundred miles), then you should use a quicker approximation.

answered Jan 31 '12 at 18:05

Aleks G

56,435
29
168
265

I have specified that max distance and nothing is appearing? – Peter Stuart Jan 31 '12 at 18:18
In this case I suggest you actually check your data. We've been using this formula with our (valid) data for several years now with great success. – Aleks G Jan 31 '12 at 23:58
Sorry it took a while to reply. With some messing around I got it working. Can I ask what I should set my max_dist to? It only works when it's at a high value, what should the value be when I am only looking for locations maximum of 40 miles. – Peter Stuart Feb 04 '12 at 16:00
If you're looking for 40 miles radius, then you should set the max_distance to 40. Make sure you do have the data within 40 miles of your test point though. – Aleks G Feb 05 '12 at 15:28

score 1 · Answer 5 · answered Jan 31 '12 at 17:54

1

Try ORDER BY latitude, longitude at the end. That should do it or approximately do it.

answered Jan 31 '12 at 17:54

Tango Bravo

3,221
3
22
44

I don't think this will be any useful. The ordering must be done by a result of a formula that uses latitude and longitude and not by each one separately. – Aleks G Jan 31 '12 at 18:07
You're totally right. Wow. I haven't done this in a couple of years and I forgot about the formula. – Tango Bravo Jan 31 '12 at 18:43

Using PHP and MySQL to calculate geolocation distance

5 Answers5