Joe P Joe P - 3 months ago 9
MySQL Question

Check if Latitiude & Longitude exist in database

I would like to check to see the Latitude & Longitude already exist in a database. I have a website where people can enter their physical address and it will be displayed on a map. I do want to prevent though a physical address from being entered twice. I figure the best way to do this is to compare the Latitude & Longitude of the address to what is stored in the database.

I am using Google Map API to find the Latitude & Longitude of the address entered by the user. Following Google's recommendation, I am storing (caching) these values as a Float (10,6) value.

The issue that I am experience though seems to rounding the Float value when I try to compare the values of an address entered by a user and what I have stored in the database.

Here is the code that I am using to compare these values:

$url = "http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=".urlencode($address);
$lat_long = get_object_vars(json_decode(file_get_contents($url)));
// pick out what we need (lat,lng)
$lat = $lat_long['results'][0]->geometry->location->lat;
$lng = $lat_long['results'][0]->geometry->location->lng;
// Check to see if Property is already in database
$getlatlng = sprintf("SELECT * FROM `Property` WHERE CAST(lat AS DECIMAL(10,6)) = CAST(%s AS DECIMAL(10,6)) AND CAST(lng AS DECIMAL(10,6)) = CAST(%s AS DECIMAL(10,6))",
$PDO_DB->quote($lat),
$PDO_DB->quote($lng));
$resultlatlng = $PDO_DB->query($getlatlng);
$rowlatlng = $resultlatlng->fetch(PDO::FETCH_ASSOC);


An example of this would be address:
601 N Lincoln Rd, Escanaba, MI 49829


The values stored in the database are:

lat = 45.752487
lng = -87.082733


The values returned by Google Map API are:

lat = 45.7524878
lng = -87.0827338


My guess has to do with the values being returned from is being rounded to:

lat = 45.752488
lng = -87.082734


And this results in the what I have in my database and what Google is returning is not equal. The value stored in the database is stored off the same code as above, just left off the section that is inserting the values into the database.

Thanks for reading and any help that can be provided.

Answer

Thanks @Aaron for pointing out the PHP command BCCOMP. I used it to solve the issue.

Here is the code now:

$url = "http://maps.googleapis.com/maps/api/geocode/json?sensor=false&address=".urlencode($address);

$lat_long = get_object_vars(json_decode(file_get_contents($url)));
// pick out what we need (lat,lng)
$lat = $lat_long['results'][0]->geometry->location->lat;
$lng = $lat_long['results'][0]->geometry->location->lng;

$propertyexist = FALSE; 

    // Check to see if Property is already in database  
    $getlatlng = sprintf("SELECT * FROM `Property`");
    $resultlatlng = $PDO_DB->query($getlatlng);
    $rowlatlng = $resultlatlng->fetchALL(PDO::FETCH_ASSOC);

    foreach ($rowlatlng AS $rowlt) {
        $checklat = bccomp($rowlt['lat'], $lat, 6);
        $checklng = bccomp($rowlt['lng'], $lng , 6);

        if (($checklat === 0) && ($checklng === 0)) {
            $propertyexist = TRUE;
            break;
        }
    }