8

In PHP, I have the following code for calculating the distance between two locations:

<?php
function distance($lat1, $long1, $lat2, $long2) {
    // DEGREE TO RADIAN
    $latitude1 = $lat1/180*pi();
    $longitude1 = $long1/180*pi();
    $latitude2 = $lat2/180*pi();
    $longitude2 = $long2/180*pi();
    // FORMULA: e = ARCCOS ( SIN(Latitude1) * SIN(Latitude2) + COS(Latitude1) * COS(Latitude2) * COS(Longitude2-Longitude1) ) * EARTH_RADIUS
    $distance = acos(sin($latitude1)*sin($latitude2)+cos($latitude1)*cos($latitude2)*cos($longitude2-$longitude1))*6371;
    return $distance;
}
echo distance(9.9921962, 53.5534074, 9.1807688, 48.7771056); // Hamburg, DE - Stuttgart, DE
?>

But now, I want to select locations close to a given location via PHP from my MySQL database:

  • The user enters his hometown
  • My script gets the latitude/longitude values via the Google API
  • In my database, I have about 200 locations with a field for the latitude value and a field for the longitude value
  • I need a code for PHP and MySQL to select the 10 locations which are closest to the user's hometown

I hope you can help me. Thanks in advance!

caw
  • 30,999
  • 61
  • 181
  • 291

7 Answers7

8

MySQL Great Circle Distance (Haversine formula) does exactly what you need.

With only 200 records however you may as well just load them all and check them with code. The data set is really way too small to be worrying too much about database vs code or any other such optimizations.

Calculating distance between zip codes in PHP has a couple of PHP implementations of this algorithm.

Geo Proximity Search is pretty much the exact same problem you have.

Community
  • 1
  • 1
cletus
  • 616,129
  • 168
  • 910
  • 942
  • Thanks, as nOw2 said, it's the Haversine formula. I have the PHP implemention yet, see the code in my question. – caw May 24 '09 at 12:35
4

Maybe something like

SELECT field1, field2, ...,
    ACOS(SIN(latitude / 180 * PI()) * SIN(:1) + COS(latitude / 180 * PI()) * COS(:2) * COS(:2 - longtidude)) * 6371 AS distance
    ORDER BY distance ASC;

or

SELECT field1, field2, ...,
    ACOS(SIN(RADIANS(latitude)) * SIN(:1) + COS(RADIANS(latitude)) * COS(:2) * COS(:2 - longtidude)) * 6371 AS distance
    ORDER BY distance ASC;

(directly translated from the PHP code)

:1 and :2 is $lat2/180*pi() and $long2/180*pi() respectively.

itsbth
  • 41
  • 4
  • Thanks, it's the same as nOw2's answer, isn't it? You write "$lat2/180*pi()". Why not just "RADIANS($lat2)"? You used it in the rest of the query, so why not here, too? – caw May 24 '09 at 12:32
  • :1 and :2 comes from PHP, which doesn't have RADIANS. http://us2.php.net/deg2rad could be used though. – itsbth May 24 '09 at 15:28
1

That's the Haversine formula. You can translate the PHP directly into SQL so you can query the database spatially (the alternative being to pull every record out of the DB and run the data through PHP). MySQL provides all the maths functions that you need.

I did this for a commercial website which provided post/zipcode based distance lookups, so its certainly possible without specific GIS functions.

nOw2
  • 656
  • 7
  • 13
0

Just came a cross this topic. Maybe someone will be interested in this function, tested in MySql 5.7:

    create function GetDistance(lat1 real, lng1 real, lat2 real, lng2 real) returns real no sql
    return ATAN2(SQRT(POW(COS(RADIANS(lat2)) * SIN(RADIANS(lng1 - lng2)), 2) + 
                POW(COS(RADIANS(lat1)) * SIN(RADIANS(lat2)) - SIN(RADIANS(lat1)) *
                COS(RADIANS(lat2)) *  COS(RADIANS(lng1 - lng2)), 2)), 
                (SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)) + COS(RADIANS(lat1)) *
                COS(RADIANS(lat2)) * COS(RADIANS(lng1 - lng2)))) * 6372.795;

Calling:

select GetDistance(your_latitude, your_longitude, table_field_lat,
                   table_field_lng) as dist from [your_table] limit 5;
Mattias Backman
  • 927
  • 12
  • 25
Irina S.
  • 1
0

There are MySQL functions available for doing exactly this.

http://dev.mysql.com/doc/refman/5.0/en/gis-introduction.html

Frank Farmer
  • 38,246
  • 12
  • 71
  • 89
0

MySQL has the ability to index rows geospatially. You might not need to do this math by yourself (you can just ask MySQL to compute the distance between two geo objects and sort by that..).

See: http://forums.mysql.com/read.php?23,159205,159205

Assaf Lavie
  • 73,079
  • 34
  • 148
  • 203
-1

Why don't you use MySQL's geospatial features...? No, just kidding.

If the 200 records are actual places like towns, etc then as an alternative could you use GeoNames' API?

The following webservice will provide the 10 closest locations to the lat and lng provided:

http://ws.geonames.org/findNearby?lat=47.3&lng=9

Source: http://www.geonames.org/export/web-services.html#findNearbyPlaceName

Full list: http://www.geonames.org/export/ws-overview.html

mr-euro
  • 2,732
  • 5
  • 23
  • 27
  • Thanks! I have the data in my database so I needn't use GeoNames' API. – caw May 24 '09 at 12:27
  • Yes I understand, but you could match those 200 records with the responses from Geonames and from that have the 10 closest matches. This is again assuming that your 200 records are somehow known places and not some random positions. – mr-euro May 24 '09 at 12:54