0

I have a MySQL database full of locations, and I'm looking to write a php page that accepts latitude and longitude as arguments and query's a MySQL database of locations for results within +/- 0.01 of the lat and lon.

<?php include "../inc/dbinfo.inc"; 

$lat = $_GET['lat'];
$lon  = $_GET['lon'];

$connection = mysqli_connect(DB_SERVER, DB_USERNAME, DB_PASSWORD);
if (mysqli_connect_errno()) echo "Failed to connect to MySQL: " . mysqli_connect_error();
$database = mysqli_select_db($connection, DB_DATABASE);

settype($lat, "double");
settype($lon, "double");

$lat_max = bcadd($lat, 0.01);
$lat_min = bcsub($lat, 0.01);
$lon_max = bcadd($lat, 0.01);
$lon_min = bcsub($lat, 0.01);

if ($lat_min > $lat_max) {
$temp = $lat_min;
$lat_min = $lat_max;
$lat_max = $temp;
}

if ($lon_min > $lon_max) {
$temp = $lon_min;
$lon_min = $lon_max;
$lon_max = $temp;
}



$qstring = "SELECT * FROM toilets WHERE (Latitude BETWEEN " . $lat_min . " AND " . $lat_max. ") AND (Longitude BETWEEN " . $lon_min . " AND " . $lon_max . ")";

$result = mysqli_query($connection, $qstring); 
$myArray = array();
if ($result) {
  $tempArray = array();
 while($row = $result->fetch_object()) {
            $tempArray = $row;
            array_push($myArray, $tempArray);
        }
    echo json_encode($myArray);
}

mysqli_free_result($result);
mysqli_close($connection);
?>

The MySQL select statement generated from this is not the expected result. I believe the problem is with floating point precision in PHP, but I've tried using bcadd and bcsub with no luck. Can someone point out where I've gone wrong in the code or in my understanding of PHP's handling of floating point numbers? Thanks

Mike C
  • 1
  • OK, so show us the schema for `toilets` and also the numbers you are putting into the script for lat and lng – RiggsFolly Sep 08 '18 at 12:31
  • Take a look here: https://stackoverflow.com/questions/2234204/latitude-longitude-find-nearest-latitude-longitude-complex-sql-or-complex-calc it may help. And you should clarify `within +/- 0.01` 0.01 of what? meters, km ? – Jorge Campos Sep 08 '18 at 12:39
  • @JorgeCampos Probably degrees as that is the most commonly used unit for latitude and longitude. 0.01° is approximately 1km. – Mike Sep 08 '18 at 12:46
  • @Mike yeah, maybe. His code though *seems* to look for distance (given his query). When you look for something given a lat and long you usually want to know the distance unless you are using it to calculate something else. – Jorge Campos Sep 08 '18 at 12:53

0 Answers0