0

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

Positive Navid
  • 2,481
  • 2
  • 27
  • 41
awfulwarlock
  • 1
  • Also, beware of SQL injection in your query. https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – Filip Halaxa Nov 29 '20 at 21:07
  • 1
    @awfulwarlock are you sure your long acos sin function is right, it looks awfully complex, wouldn't be strange if you accidently screwed up the formula... – Yunfei Chen Nov 29 '20 at 21:17

1 Answers1

2

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.

GMB
  • 216,147
  • 25
  • 84
  • 135