Finding the closest address with using the google geocoder, php, jquery, and mysql

googlemaps

Web development –  Finding simple geographical proximity using addresses, php, javascript, jquery, and mysql.

The following is an overview which details how to find the closest addresses by proximity given an initial point of origination address.

Steps:

  1. Create and poplulate a mysql table with addresses.
  2. Get the latitude and longitude points for each of the addresses using a CURL script and store them in the address table.
  3. Get the originating address from which you wish to determine proximity and originating address’s latitude and longitude points via the google geocoder.
  4. Query the mysql database using the latitude and longitude points with a haversine formula query.
  5. Return results to the end user.

 Step 1.  Populating a mysql table with addresses.

You’ll need to create the proper table with fields for city, state, address, zip code, latitude, and longitude.  You’ll then need to input each address which you would like to permanently geotag for future lookup.  Don’t forget to create a primary key and a couple of good search indexes.

Step 2.  Get the latitude and longitude points of each address and insert them into the address table.

Google places a limit of 2500 lookups per day per IP via its geocoder so this step will save you from going over that limit with your server.   Here’s an (slightly incomplete) example of how this could be done using PHP and CURL on your server:

<?php
ini_set(‘allow_url_fopen’, true);
set_time_limit(0);
session_start();
$CookiePath = ‘/var/www/GeoCode/Cookie/GeoCookie.txt’;
$userAgent = “YourSite.com”;
$ListURLRoot = “http://maps.googleapis.com/maps/api/geocode/json”;
$ListURLSuffix = ‘&sensor=false’;
$Curl_Obj = curl_init(); // Setup cURL
curl_setopt($Curl_Obj, CURLOPT_COOKIEJAR, $CookiePath);
curl_setopt($Curl_Obj, CURLOPT_USERAGENT, $userAgent);
curl_setopt($Curl_Obj, CURLOPT_HEADER, 0);
curl_setopt($Curl_Obj, CURLOPT_AUTOREFERER, TRUE);
curl_setopt($Curl_Obj, CURLOPT_FOLLOWLOCATION, 1);
curl_setopt($Curl_Obj, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($Curl_Obj, CURLOPT_TIMEOUT, 30);
curl_setopt($Curl_Obj, CURLOPT_POST, 0); // Disable Posting.
$dbconn = mysqli_connect(‘localhost’, ‘yourmysqluser’, ‘yourmyslqpassword’, ‘youraddresstable’);
$query = “SELECT ID, Address, City, State, Zip FROM youraddresstable”;
$results = mysqli_query($dbconn, $query);
while($rows[] = mysqli_fetch_assoc($results));
foreach($rows as $row)  {
    if(isset($row[‘Address’]) && strlen($row[‘Address’]) > 0)   {
        $stAddr = str_replace(‘ ‘,’+’, $row[‘Address’]);
        $City = str_replace(‘ ‘,’+’, $row[‘City’]);
        $address = “$stAddr,+$City,+{$row[‘State’]},+{$row[‘Zip’]}”;
        $address = str_replace(‘ ‘, ‘+’, $address);
        $ListURL = “{$ListURLRoot}?address=$address$ListURLSuffix”;
        curl_setopt ($Curl_Obj, CURLOPT_URL, $ListURL);
        $output = curl_exec ($Curl_Obj);
        GetLocation($row[‘ID’],$output);
        sleep(2);
    }
}
mysqli_free_result($results);
mysqli_close($dbconn);
function GetLocation($ID, $output)  {
    global $dbconn;
    $Loc = json_decode($output, true);
    if(isset($Loc))     {
        if(isset($Loc[‘status’]) && stristr($Loc[‘status’], ‘OK’))  {
            if(isset($Loc[‘results’][0][‘geometry’][‘location’]))  {
                $Lat = $Loc[‘results’][0][‘geometry’][‘location’][‘lat’];
                $Lng = $Loc[‘results’][0][‘geometry’][‘location’][‘lng’];
                $query = “UPDATE youraddresstable SET lat = ‘$Lat’, lng=’$Lng’ WHERE ID = ‘$ID’ LIMIT 1;”;
                mysqli_query($dbconn, $query);
            }
        }
    }
}
?>

Step 3.  Get the originating address from which you wish to determine proximity.

This is fairly straight forward.  You’ll probably want to use a form and do an ajax query using jquery to get the data from the geocoder.   The input form can be as simple as a single <input id=’youraddressinput’ name=’youraddressinput’>.

Here’s a partial example of how to obtain the latitude and longitude points of the address via jquery and javascript

var youraddress = $(“#youraddressinput”).val(); // Cleanup before passing
var GeoURL = “http://maps.googleapis.com/maps/api/geocode/json?address=” + youraddress + “&sensor=false”;
            $.ajax({
                type: “GET”,
                url: GeoURL,
                dataType: “json”
            }).success(function(GeoData)  {
                      var GeoCode = { lat: GeoData.results[0].geometry.location.lat, lng: GeoData.results[0].geometry.location.lng };
                      // Do stuff with the GeoCode
                      // This would be a good place to perform a second ajax request that sends the originating address geotag to the server.
            });

Step 4.  Query the database with the originating points latitude and longitude using a haversine formula in the mysql query.

Depending on how you pass the
if (isset($_POST[‘lat’]) && isset($_POST[‘lng’]))    {
    $lat = mysqli_real_escape_string($yourdbconnection, $_POST[‘lat’]);
    $lng = mysqli_real_escape_string($yourdbconnection, $_POST[‘lng’]);
$query = “SELECT *, ( 3959 * acos( cos( radians($lat) ) * cos( radians( lat ) ) * cos( radians( lng ) – radians($lng) ) + sin( radians($lat) ) * sin( radians( lat ) ) ) ) AS distance
FROM youraddresstable ORDER BY distance LIMIT 0 , 12;”;
}

 Step 5.  Return the data to the user.

There are many ways to do this, from simple table output to plotting the points on a map, it really depends on your overall objective.  For our purposes we will simply send back the addresses in json format.

$result = mysqli_query($yourdbconnection, $query);
if (mysqli_num_fields($result) > 0) {
    while($row = $result->fetch_assoc())    {
        $rows[] = array(‘id’=>”{$row[‘id’]}”,
            ‘address’=>$row[‘address’]
            );
    }
    echo json_encode($rows);
}

 

 Resources for futher research

http://stackoverflow.com/questions/574691/mysql-great-circle-distance-haversine-formula

https://developers.google.com/maps/documentation/geocoding/#GeocodingRequests

https://developers.google.com/maps/articles/phpsqlajax_v3?hl=en-EN

https://developers.google.com/maps/documentation/geocoding/#ComponentFiltering

 

 

Author: Joel Caton

Business Consultant and Web Developer from Meridian, MS

Leave a Reply

Your email address will not be published. Required fields are marked *