Sorting zip code proximity search by distance (php/mysql)

Question

I have a table (user_zip_codes) with the users' zip_code, latitude and longitude. I have found a function here on stackoverflow that finds zip codes within a specific radius:

function zipcodeRadius($lat, $lon, $radius) {
    global $mysqli;
    $zipcodeList = array();
    $radius = ($radius ? $radius : 20);
    $sql = "SELECT userID,city,zip_code,country FROM user_zip_codes  WHERE (3958*3.1415926*sqrt((lat-$lat)*(lat-$lat) + cos(lat/57.29578)*cos($lat/57.29578)*(lon-$lon)*(lon-$lon))/180) <= $radius GROUP BY zip_code";
    if($stmt = $mysqli->prepare($sql)) {
        $stmt->execute();
        $stmt->bind_result($userID,$city,$zip_code,$country);
        while($stmt->fetch()) {
            $zipcodeList[] = array('userID'=>$userID,'city'=>$city,'zip_code'=>$zip_code,'country'=>$country);
        }
    }
    return $zipcodeList;
}

It works perfectly. However, I would like the function to sort the array by distance (either by ASC og DESC). How should I adjust my query in order for this to happen?

Thanks in advance.

UPDATE: The word 'distance' might appear ambiguous (thanks to Jorge). I simply wish to sort the zip_codes by distance meant as the distance between two points.

The ´distance´ word is a bit trick. Thats because if you mean distance based on two points or the distance based on a road from the first and the second point. Which one you want? You should update your question with this information. — Jorge Campos, Jul 14 '14 at 11:57
@JorgeCampos, I have updated my question. I mean the distance based on two points. — hskrijelj, Jul 14 '14 at 12:02
In the absence of any aggregating functions, you should not be GROUPing anything! — Strawberry, Jul 14 '14 at 12:24
possible duplicate of [Calculate distance between 2 GPS coordinates](http://stackoverflow.com/questions/365826/calculate-distance-between-2-gps-coordinates) — Bulat, Sep 02 '14 at 10:30

score 4 · Accepted Answer · answered Jul 14 '14 at 14:01

You could use something like

$iDistance = 20;
$iRadius = 6371; // earth radius in km
$iRadius = 3958; // earth radius in miles
$fLat = x.y; // Your position latitude
$fLon = x.y; // Your position longitude

$strQuery = "
SELECT 
  *, 
  $iRadius * 2 * ASIN(SQRT(POWER(SIN(( $fLat - abs(pos.lat)) * pi() / 180 / 2),2) +
COS( $fLat * pi()/180) * COS(abs(pos.lat) * pi() / 180) * POWER(SIN(( $fLon - pos.lon) *
pi() / 180 / 2), 2) )) AS distance
FROM user_zip_codes pos
HAVING distance < $iDistance 
ORDER BY distance";

where you have to fetch your lat/lon value before using the SQL. This works for me

Thumbs up for this solution, which I actually ended up using. It sorts the zip_codes correctly and even makes it possible easily to switch between miles and km. Thank you! — hskrijelj, Jul 14 '14 at 14:13

score 1 · Answer 2 · answered Jul 14 '14 at 12:25

1

ORDER BY (3958*3.1415926*sqrt((lat-$lat)*(lat-$lat) + cos(lat/57.29578)*cos($lat/57.29578)*(lon-$lon)*(lon-$lon))/180) DESC

answered Jul 14 '14 at 12:25

Strawberry

33,750
13
40
57

score 0 · Answer 3 · answered Jul 14 '14 at 12:30

MySQL has geometric functions to calculate distance. However, you would need to manage your coordinate pairs as Point fields instead of separate float fields. A workaround would be to cast both columns into a coordinate pair.

$sql = "SELECT userID,
            city,
            zip_code,
            country, 
            GeometryFromText( CONCAT( 'POINT(', lon, ' ', lat, ')' ) ) as coord
        FROM user_zip_codes  ";

If instead of points you use your $lat and $lon variables to cast a LineString, yo could use GLength to get the distance

$sql = "SELECT userID,
                city,
                zip_code,
                country, 
                GLength(GeomFromText(CONCAT('LineString(',lon,' ', lat1,',$lon $lat)'))) as distance
            FROM user_zip_codes  
            where GLength(GeomFromText(CONCAT('LineString(',lon,' ', lat1,',$lon $lat)')))<=$radius
            ORDER BY distance DESC";

It still looks messy. There should be a way to declare a LineString without using its WKT representation. I'll keep looking.

This is perfect, thank you! I used your second example with $lat and $lon to cast a LineString. — hskrijelj, Jul 14 '14 at 13:56

Sorting zip code proximity search by distance (php/mysql)

3 Answers3

Linked