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

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

 

 

PHP Error Handling and Debugging – Part 1

This article describes the process of testing PHP code.  Using the tips that I will explain can help to decrease the code/test cycle time.

The first thing that you must know in order to plan your code/test process is the environment in which your code will run.

If you have full control of the system, less configuration is required. In this case you can rely on the default settings, and simply need to know where the logs are kept by default. On a typical LAMP (apache) system you can find the log files in /var/log/httpd. Check the documentation for the operating system that you use as some operating systems use a different directory (i.e. one version of Ubuntu uses /var/log/apache2). By default error messages from php will be kept in this directory.

If you are developing on a server where you don’t have access to the default logs, you can configure where your log messages are sent by putting a php.ini file containing the directive

error_log: path_to_log

in the root of the domain.

With this information in mind, we can begin to find code errors.

There are two error types to look for:

  1. parse
  2. runtime

A parse error is the first thing to look for when testing new or modified code. This can be something like a missing semicolon or other grammatical mistake. If a parse error occurs, it will be sent to PHP’s error_log. A simple way to find this kind of error is to load the file directly in a browser (i.e. an AJAX script that would not normally run in a browser could be tested this way for parse errors). With a default PHP installation the parse error will shown on the screen.

Most errors that are encountered are runtime errors. There are two kinds of runtime errors:

  1. exception
  2. functional

The first kind of runtime error happens when a statement or function call that is grammatically correct encounters a unexpected circumstance such as an invalid parameter (i.e. fopen(file_that_doesnt_exist,’r’)). This kind of error can only be seen during an actual run of the code with valid inputs. Opening the file in the browser directly usually will not find it as the inputs will not be those that would typically be encountered. For example opening an AJAX script that relys on the _POST variable for its input will typically not run many of the branches because of the missing _POST variables. To find this error, run the script as it would typically be run and check the error log for errors.

A functional runtime error is when the code runs, doesn’t generate an error, but doesn’t produce the expected outputs. To find this error use one or more of the following techniques:

  • echo/printf
  • error_log
  • try/catch

The simplest way to find errors is by adding echo statements to the code. This method can be somewhat tedious and slower to use than others, but a few well placed echo statements that use print_r to show the value of key return data structures can sometimes quickly illuminate the source of the malfunctioning code. The problem with this method is that because it outputs directly to stdout (the web browser) it is only available if the script can be run in the web browser directly with typical inputs. Many times this is not possible (i.e. for AJAX or cron code).

A more general way of debugging is to use the error_log function instead of echo. With the error_log function you can direct the messages to a file of your choosing with

error_log($message,3,"path/filename")

or to the configured error_log mentioned earlier via

error_log($message)

A bonus when using the error_log() function is that you also get a timestamp for each error logged.

If a runtime error is expected, a try/catch statement should be placed to ignore it or otherwise handle it in a way that doesn’t cause the script to stop abruptly.  This way the script will continue to run and an error will be logged.  This is better because you will know at what section of code the error occurred.  If the blocking error had gone uncaught (in the case of AJAX responder script errors), the calling application might have received a malformed response (parse error).  A try/catch statement is only helpful when a blocking exception occurs, and will not help to debug functional runtime errors.  The structure of this type of code testing is as follows:

try {
 //your new code
 } catch(Exception $E) {
 error_log(E.getMessage());
 }

In this article we have discussed simple code/test cycle techniques for PHP.  Tune in next time for part 2 where we will review using a debugger such as XDebug.

Javascript Compression — Tools and Process

This article is a review of the tools and processes that I have tested and gives plusses and minuses of each.

Software Uncompressed Compressed Percent Comment
Closure Compiler 39K 16K 59% with ADVANCED_OPTIMIZATIONS
YUI Compressor 39K 22K 44%
perl-Javascript-Minifier 39K 25K 36%

 

perl-Javascript-Minifier
Since CPAN library’s Javascript-Minifier and CSS-Minifier are immediately available linux tools they are a good starting point. The Javascript-Minifier is simple to use. Here is a script that you can try to see how it works:

#!/usr/bin/perl
 use JavaScript::Minifier qw(minify);
 my($iFile)=$ARGV[0];
 my($oFile)=$iFile;
 $oFile=~ s/[.]js//;
 $oFile="${oFile}_perl_min.js";
 open(INFILE, "$iFile") or die;
 open(OUTFILE, ">$oFile") or die;
 minify(input => *INFILE, outfile => *OUTFILE);
 close(INFILE);
 close(OUTFILE);

In my tests, it didn’t break my code, but did generate errors because of incorrectnesses in my code. I used the google chrome jslint plugin to find the errors. jslint only works on pure javascript, but strings are not parsed. Thus you can use php to initialize variables by putting the php code inside of quotes, and still check it with jslint.

 

YUI Compressor
The YUI Compressor is Yahoo’s library, and works better than perl-Javascript-Minifier. Here is an example command for using YUI Compressor:

java -jar yuicompressor-2.4.7.jar --type js -o filename_yui_min.js filename.js

A nice feature of the yuicompressor is that it can accept javascript strings from the command line. This makes it simple to script. It’s goal is to not break code, and in my tests this was observed to be true.

 

Closure Compiler
The google closure compiler is the most advanced of the ones that I tested. It has a simple mode that doesn’t break code and an option for ADVANCED_OPTIMIZATIONS that produces very compressed code. Here is an example command for using the closure compiler in simple mode:

java -jar compiler.jar --js filename.js --js_output_file filename_closure_min.js --externs externs.js

And similarly for advanced mode:

java -jar compiler.jar --compilation_level ADVANCED_OPTIMIZATIONS --js filename.js --js_output_file filename_closure_min.js --externs exterms.js

Similar to perl-Javascript-Minifier, closure compiler only works on pure javascript files. Because of the effectiveness of the optimizations that it does, it can break code. To effectively use it, you need to design your javascript with minification in mind. Typically you want to use your javascript as a library (i.e. as handers for events such as mouse clicks) to do this, you need to add a small amount of code that preserves the function names that will be available to external scripts. Similarly if you want to use external libraries in your library, you need to add extern declarations that will preserve the external symbols. There are less modifications required if you use the simple mode than for the advanced mode. I wanted to use advanced mode for some script that contains jQuery calls (including jQuery Mobile), but wasn’t able to find a way to preserve the jQuery and $ symbols. I tried using –externs with the externs file available as an addon from google svn, but this didn’t solve the problem. Therefore I recommend using simple mode for files containing jQuery and advanced mode for files that do not.

 

In summary of the tools reviewed google closure compiler is the most effective, perl-Javascript-Minifier is the least likely to break code, and yuicompressor is a compromise between these extremes. Additionally each of these tools can be run locally on your machine.

What modern business applications should offer users.

Modern business applications should offer users the following features…

While developing solutions for various projects that I’ve undertaken I’ve come up with a few ideas on the types of features that I’d like my applications to offer users.  While this list is far from complete I think it would be good to post it for comment.  Bear in mind that when I’m discussing modern business applications I’m discussing thin client web based applications using cloud architecture.

Modern business application features:

  • Remote connectivity to data and functionality.
  • Encapsulation of business logic.
  • Continuous contextual decision support.
  • User specific views of the data and logical processes
  • Security
  • Flexibility
  • Data Importing / Exporting capabilities

Remote connectivity to data and functionality: Traditional business applications ran on workstations and desktop computers.  Data was stored on the computer itself or on a server and was not accessible unless the user was sitting in front of the desktop computer itself or another computer connected to the server.  This in effect chained the user to his or her desktop device.  Modern business applications must utilize web technology and provide device independent services that allow the user to connect to the data and logic of the business through any device that they can be authenticated through that has internet connectivity.

Encapsulation of business logic: Traditional business applications such as spreadsheets, word-processors, and email allowed for the transportation and transformation of data, but the use of business logic was largely left to the discretion of the user.  Modern business applications can be designed to encapsulate the steps that must be taken with information and resources to automate processes and insure execution of critical procedures required by users.  By profiling the procedures and steps required for critical processes in the business, developers can create software that encapsulates them and insures that the order of operations is followed, all steps required are taken, all resources required are employed, and follow through is complete.  Outcomes and ownership of inputs from each step can also be tracked to determine each users contribution to the success of the process.  Success ratios for processes can be increased as the mundane oversight of each detail is moved from managers to system applications with the general oversight of larger categories of processes taken on by tech empowered managers.

Continuous contextual decision support: Because of the connectivity offered by todays diverse array of computing devices, from smart phones, laptops, tablet PCs, to thin client enabled desktop PCs.  Users now have the ability to receive data and logic at any point of the business process.  Modern business applications must provide contextual decision support by offering relevant information to the user at the right time with suggestions for its proper use and allow for user feedback into the system.

User specific views of the data and logical processes: Modern business applications must offer users unique views of the data and logical processes that fits their context to the business.  For example a vendor that supplies a business would need access to statistics indicating demand for the particular commodity they offer.  Their view of the data and logic would be an on demand view or automated communication with forecasted consumption data generated by internal business processes that the vendor never sees.  User specific views of the data and logic offer modular controls to the business and protects its core competencies from probing by competition.

Security: Traditional business applications were prone to interruption by natural disasters and other disruptive events such as fires, floods, tornadoes etc.  Modern business applications can offer security to its users by storing data and logic in a cloud environment where information is distributed off site amongst computers in multiple data centers.  This offers a degree of security to the user and provides for a significant reduction in recovery time when the business is impacted by such an event.

Flexibility: Modern business applications created with web based technologies are more flexible than traditional business applications because of the ease in which updates can be rolled out to the user.  In traditional environments users had to undergo an application update procedure before receiving an updated version of a business application that contained the latest data and logic.  Web applications are updated remotely with no effort required by the end user making their innovation, iteration, and revision much more seamless and transparent to end users.

Data Importing / Exporting capabilities: Modern business applications should easily convert data with traditional applications such as spreadsheets and allow the user the comfort of converting data for use outside of the application.  Data transfer methods such as XML are a good standard for data export and can allow for data interchange between disparaging systems and applications.

Comments and discussion are welcome.

Missing documentation for using Flexigrid

Recently one of the projects I’ve been working involving database tables with hundreds and thousands of records required a grid to display data.  After doing a little searching I found a jquery project called Flexigrid.  In this article I’ll talk a little about my experiences with Flexigrid and some things I found that weren’t readily available on the internet.

What is Flexigrid and what does it do?

Flexigrid is a lightweight, highly customizable, limited functionality, grid for displaying table data in a spreadsheet styled table.  It offers dynamic ajax loading of xml or json data from a database and displays records in sortable, searchable, and rearrangeable columns that are organized by navigateable pages containing as many records as your user desires to see on his or her screen at one given moment.  It is remarkably easy to begin using because it offers basic functionality examples in php, javascript, and sql.  You can download if from http://flexigrid.info or http://code.google.com/p/flexigrid/ and view discussion on it here http://groups.google.com/group/flexigrid/?pli=1

Issues with Flexigrid:

  • Flexigrid does not work with the latest version of jquery and I had to use version 1.5.2 to get it to work, but thats not really a problem when you consider that jqueryui works just fine with that version.
  • Very limited documentation.
  • No current editing or updating functionality.
  • No mouse over tool tip style information for context sensitive help messages.  Requires customization to add such functionality.
  • Renders differently in different browsers.

My biggest issue was determining how to dynamically load a url.  The documentation offered did not show the correnct method of first updating the url property of the object before calling the reload method as shown here:

$(“#flex1”).flexOptions({‘url’: GetGridURL()});

$(“#flex1”).flexReload();

The flexReload() function accepts a url as an argument but it does not use it so the only way to specify a different url is to call flexOptions first and update it there.

Also I wanted to process the grid’s data and render the row  text color accordingly.  I found some references in the discussion forum and was able to piece together this solution.  If you add the onSuccess : YourFunction parameter to the flexGrid options you can cause it to call a YourFunction each time the grid is successfully loaded.  I added this function to onSuccess :

function StyleRows(){
$("tr").each(function() {
var st = $(this).find("td").eq(0).text();
if (st != "0" && st != "1"){ $(this).css("color", "black");}
else if (st == "1")  { $(this).css("color", "green");}
else if (st == "0")  { $(this).css("color", "red");}
 }); }

This information was not readily available.

I hope by posting these things here that I’ve saved you some time.

– Joel

How to: A quick easy jquerymobile shell application

How to write a basic shell application with jquerymobile

Here’s a quick easy tutorial from my explorations in web development on how to write a shell application with jquerymobile. This was written with beginners in mind as the documentation on the jquerymobile site, while complete, is not written for someone who is not used to handling this type of thing.

  • Determine where jquerymobile will be hosted. You can download it and host it on your site if you wish or simply copy and paste the following lines into the header of your page:

<link rel=”stylesheet” href=”http://code.jquery.com/mobile/1.0a3/jquery.mobile-1.0a3.min.css” />
<script src=”http://code.jquery.com/jquery-1.5.min.js”></script>
<script src=”http://code.jquery.com/mobile/1.0a3/jquery.mobile-1.0a3.min.js”></script>

  • Create an index or default page in the directory you’ve designated for your application and include the following code This page will be the shell that you will load all of your content into using the built-in ajax methods included in jquerymobile:
    <!DOCTYPE html>
    <html>
     <head>
     <title>Mobile Site</title>

    <link rel=”stylesheet” href=”http://yourdomain.com/mobileapp/jquery.mobile-1.0a3/jquery.mobile-1.0a3.min.css” />

    <script type=”text/javascript” src=”http://yourdomain.com/mobileapp/jquery.mobile-1.0a3/jquery-1.5.min.js”></script>

    <script type=”text/javascript” src=”http://yourdomain.com/mobileapp/jquery.mobile-1.0a3/jquery.mobile-1.0a3.min.js”></script>

    </head>
    <body>
    <div data-role="page" data-theme="a">
     <div data-role="header">
     <h2>Welcome To Your Site</h2>
     </div>
     <div data-role="content" >
     <p>Your page content here.</p>
     </div>
     <div data-role="footer" data-theme="a" data-position="fixed">
     <div data-role="controlgroup" data-type="horizontal">
     <a href="contentA.php" data-role="button">Content A</a>
     <a href="contentB.php" data-role="button">Content B</a>
     </div>
     </div>
    </div>
    </body>
    </html>

    You’ve probably noticed that the page is divided into three divs that are contained in the page div: header, content, and footer. The header will contain our menu title for the page and we’ve set the content div to load contactA.php by default. The footer is a little more complicated because it contains our navigation menu and we’ve set it to stick to the bottom of the page as the user scrolls. The navigation menu is contained in a div with the data-role set to control-group and links with the data-role set to button.

  • Now we are going to define the scripts that will output the html content to go in our shell when the page loads or a user selects an option from the navigation menu. If you’re not great with server side scripting you can use html files with the following code snippets in them. Just change the links to point to the appropriate files and extensions. We will have our scripts contentA.php and contentB.php output the following html. I’ll leave the customization of the message in the page to you.
    <div data-role="page" data-theme="a">
    <div data-role="header">
    <h2>Content A Menu</h2>
    </div>
    <div data-role="content">

    <p>You can put any html here you’d like.</p>

    </div>
    <div data-role="footer" data-theme="a" data-position="fixed">
    <div data-role="controlgroup" data-type="horizontal">
    <a href="contentA.php" data-role="button" data-icon="home">Content A</a>
    <a href="contentB.php" data-role="button">Content B</a>
    </div>
    </div>
    </div>
    

    As you can see this file just contains the html structure that is contained within the div with the data-role set to page in the index file we’ve created. We reload our header, content, and footer from this file. The navigation menu in the footer of the page can be updated to show the changes we’ve made if we so desire.

  • Upload these files to your server or hosting account and navigate to http://yourdomain.com/mobileapp to see the results.

Pretty easy huh? All the work from this example is done on the client side. You can easily change up the html to customize this example. If you’d like to see an example of this type of shell in action go to http://m.catontech.com. It’s written using the same methods used in this tutorial. Please post any questions you might have in the comments section.

– Joel Caton

A simple ajax page update example using jquery

An ajax example using jquery

Ajax Loading Image

Download the latest version of jquery from jquery.com and load it into your server. Create an html file or php script and output the html detailed below:
<!DOCTYPE html PUBLIC “-//W3C//DTD HTML 4.01 Transitional//EN”
“http://www.w3.org/TR/html4/loose.dtd”>
<html>
<head>

<script type=”text/javascript”>
function AjaxUpdate()
{
$(document).ready(function(){$(“#MyDiv”).load(“http://yourdomain.com/yourscript.php”)});
/* setTimeout(“RssFeeder()”,90000); */
}
</script>
<title>Quick Ajax with jquery Demo</title>
</head>
<body>
<script type=”text/javascript” src=”http://yourdomain.com/javascript/jquery-1.3.2.js”></script>
<div id=”MyDiv”>
<img src=”http://yourdomain.com/images/loading.gif” alt=”Loading content…” style=”display:block; margin-left:auto; margin-right:auto; vertical-align:middle;”/>
</div>
<!– Use this method for user event loading. –>
<button onclick=”AjaxUpdate();”>Update Page</button>

<script type=”text/javascript”>
// Use this for delayed loading
eval (‘setTimeout(“AjaxUpdate()”,30000)’);
</script>
</body>
</html>

Now if you’ll create a php script to send the information to page. The data you send to the page will go into the div id:Mydiv.

In this example when the user clicks the Update button the div contents will refresh. I’ve also included an example on the page that will update the div within 30 seconds of the page load. You can write javascript back to the page with the php script so you could cause it to load again if you wanted to.

Go to http://ajaxload.info to get loading graphics for your ajax powered web applications. It’s free.

Hope this helps.

– Joel