CODEWITHSUNDEEP
phpmysqlsqlwhere-clausespatial
awfulwarlock
awfulwarlock

Reputation: 1

Selecting MySQL results within a distance (in miles); incorrect results

Evening,

I have a PHP script that is selecting results and attempting to filter those results based on a certain distance in miles. I have a database of results, which includes longitude and latitude data collected from Google Maps.

My query currently looks like this...

    $stmt = $pdo->prepare("SELECT *, 
                (((acos(sin(('$latitude'*pi()/180)) 
                * sin((latitude*pi()/180)) 
                + cos(('$latitude'*pi()/180)) 
                * cos((latitude*pi()/180)) 
                * cos((('$longitude' - longitude)*pi()/180)))) 
                * 180/pi()) * 60 * 1.1515) AS distance 
            FROM directory 
            HAVING distance <= '$distance' 
            ORDER by distance ASC");

The query is returning data. However, it does not seem to filter accurately. My $distance variable is currently set to 10, so I am hoping only results within 10 miles will be returned.

Does anyone know what is going wrong here?

Thanks, Luke

Upvotes: 0

Views: 279

Answers (1)

GMB
GMB

Reputation: 222432

MySQL supports spatial functions since version 5.7, which relief you from the burden of typing such complicated formula. You can, for example, use st_distance_sphere() like so:

select d.*, 
    st_distance_sphere(point(latitude, longitude), point(:latitude, :longitude)) as distance
from directory d
having distance <=  :distance * 1609.344
order by distance

st_distance_sphere() returns a value in meters, so this uses an approximation to convert the input miles value to meters.

Note that this uses named query parameters (denoted by the starting :) rather than concatenating variables in the query string: this is both safer and more efficient.

Upvotes: 2

Related Questions