0

I have a table of users, and the geo-location of each user is stored as a pair of longitude and latitude.

I would like to show all the users within a circular area, with known center and radius. The following code is what I have so far, but it does not give the correct answer:

$lat = 54.635214;
$lon = -3.551677;
$bounding_distance = 10;

$sql = "SELECT *
            ,((ACOS(SIN(:find_lat * PI() / 180) * SIN(`latitude` * PI() / 180) + COS(:find_lat * PI() / 180) * COS(`latitude` * PI() / 180) * COS((:find_long - `longitude`) * PI() / 180)) * 180 / PI()) * 60 * 1.15078) AS `distance` 
            FROM `user` 
            WHERE
                `latitude` BETWEEN (:find_lat - :bounding_distance) AND (:find_lat + :bounding_distance)
                AND `longitude` BETWEEN (:find_long - :bounding_distance) AND (:find_long + :bounding_distance)
                AND `distance` <= 100
            ORDER BY `distance` ASC"; 

$stmt = $this->pdo->prepare($sql);

$stmt->bindValue(':find_lat', $lat);
$stmt->bindValue(':find_long', $lon);
$stmt->bindValue(':bounding_distance', $bounding_distance);

$stmt->execute();
return $stmt->fetchAll();
Peter Pei Guo
  • 7,770
  • 18
  • 35
  • 54
Daniel Le
  • 199
  • 4
  • 16
  • "Not working" means what, exactly? It's also worth noting that if you're doing a lot of GIS-type work, Postgres and [PostGIS](http://postgis.net/) are hard to beat. MySQL's support for this is really sub-optimal. – tadman Mar 05 '15 at 11:34
  • its showing no results, im not getting any errors or anything – Daniel Le Mar 05 '15 at 11:36
  • Have you turned on [PDO exception reporting](http://stackoverflow.com/questions/8992795/set-pdo-to-throw-exceptions-by-default)? Does the query run independently of PHP with test data? Is this even the right formula? – tadman Mar 05 '15 at 11:44
  • First of all do you need bounding box? ie throwing up too many results. If you do `$bounding_distance` should be greater than distance. – david strachan Mar 05 '15 at 12:28

0 Answers0