CODEWITHSUNDEEP
phpmysqlgeolocationwhere-clause
Peter Stuart
Peter Stuart

Reputation: 2444

Using PHP and MySQL to calculate geolocation distance

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?

Upvotes: 1

Views: 3207

Answers (5)

Aleks G
Aleks G

Reputation: 57346

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.

Upvotes: 2

Jonathan Rich
Jonathan Rich

Reputation: 1738

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

Upvotes: 2

alex
alex

Reputation: 580

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

Upvotes: 3

Tim
Tim

Reputation: 6441

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)

Upvotes: 3

Tango Bravo
Tango Bravo

Reputation: 3309

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

Upvotes: 1

Related Questions