Blog Posts

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.


  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:

ini_set(‘allow_url_fopen’, true);
$CookiePath = ‘/var/www/GeoCode/Cookie/GeoCookie.txt’;
$userAgent = “”;
$ListURLRoot = “”;
$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);
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 = “” + youraddress + “&sensor=false”;
                type: “GET”,
                url: GeoURL,
                dataType: “json”
            }).success(function(GeoData)  {
                      var GeoCode = { lat: GeoData.results[0], 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’]}”,
    echo json_encode($rows);


 Resources for futher research



Targeting your customers – The Loyalty Project

Targeting your customers

Traditionally marketing has been done using a shotgun method in which mass media such as print, radio, or TV advertising was used to inform a population of possible potential customers of the offerings you had available and communicate value.  Those methods communicated the same message to everyone.  Here’s what we have, when we have it, where we have it, and the price. Everybody got notified of the same deal.  However everybody does not appreciate the same deal in the same ways so traditional methods didn’t do so great at connecting on an individual level.  Loyalty Targeted Advertising System

Enter the days of web marketing and peer to peer communication.  Now it is possible to communicate directly with a given customer with a customized offer that he or she will appreciate.  To do this you’ll want to identify the type of customer you are dealing with.  We’ve listed six different types of customers that you’ll meet on a daily basis.  While this list is in no way complete look at it and see if you can identify some of your customers.

Loyal Repeat Customers:

These customers are the core of your business being repeat buyers with a strong interest and buy in into your brand.  Loyal customer typically following Pareto’s Principle numbering no more then twenty percent of the total customers and producing eighty percent of the sales for an organization.  Loyal customers typically expect you to consistently maintain your methods and level of service and product offerings and to acknowledge them on an individual basis as an integral part of the business.

Discount Customers:

These customers are looking for a deal.  It doesn’t matter if you’ve got it or the business down the street has it.  They will go with the lowest price.  While it is possible to grow sales and even make money off this type of customer, it is important to remember that they are loyal to one thing:  The perceived discount that they are getting from the regular price.  You may be able to get this type of customer to brag about the deal they found at your place, but unless your brand is discount, you won’t be able to build much brand loyalty with them.

Social Customers:

Social customer patronize businesses that make them feel connected.  Oftentimes they make their choices based off of the recommendation of their friends.  If their friends recommend you then that’s great, but you’ll need to personally connect with this customer to convert them to a loyal customer or you’ll lose them when their friends recommend someone else.

Impulse Customers:

At times these customers don’t exhibit strong brand loyalties.  They like to shop impulsively and purchase products and services that meet their perceived immediate wants or needs.  Getting information to this customer in a timely manner is crucial if you want their business and you can build a brand with them if you can forecast their needs accurately and communicate.

The Curmudgeon

This customer can’t be pleased.  He or she behaves if doing business with you is just a big favor they are performing for you because you really don’t offer enough value to be worth their time.  There is a genuine difference between these customers and loyal customers with a legitimate complaint.  The loyal customers satisfaction will increase when they state there is a problem and you correct it.  On the other hand the curmudgeon will simply find something else to complain about.  It is very difficult to build a brand with this type of customer as they are never happy and cannot give positive referrals.  Occasionally this type of customer can be converted into a loyal customer if the business takes enough time to develop a relationship that transcends the personal issues the curmudgeon has.

The con artist complainer

This customer knows how to exploit your business to gain an advantage and will often engage your business in complaint sessions that play on your desire to satisfy them through “good customer service”.  It becomes easier to differentiate this type of customer from a loyal customer if you have well defined customer service practices and can focus in on the details of what they are saying the problem and desired solution should be.  He or she will generally not be happy with anything that you offer until you propose a solution that is a win for them and a loss for your business. Furthermore this type of customer often targets employees at different levels in the business hierarchy or employees in different departments for repeat complaint sessions to maximize the benefit your misdirected customer service attempts will afford them.  Failure to identify this type of customer can result in damage to the brand, demoralization of your employees, and financial loss.

The Loyalty Project

We won’t take up a lot of your time detailing how the tools we give you for targeting your customers with the Loyalty Project, but if you saw some of your customers in the list above and would like to target them specifically we’d love to hear from you.  We are currently beta testing of our project and are looking for partners that would like to grow their business through intelligent marketing.  We’d love to have a conversation with you about this topic.

Use the form below to contact us about the Loyalty Project

* indicates required field

Powered by Fast Secure Contact Form

The most popular web browser?

Lately there’s been a lot of talk about Google Chrome surpassing Microsoft’s Internet Explorer usage and becoming the most popular web browser. Because the web has become so important to our daily lives as we work, play, and conduct business this change of events naturally poses some questions. What does this mean to us as business owners and end users? Join me for a brief review of the data in the charts below and you’ll find that this change isn’t what it seems.

As you can see in the chart below Google Chrome has enjoyed a lot of growth world wide and has indeed become the most popular browser.

Web browser usage from 7/08 - 6/12
Browser Usage

Currently Google Chrome is slightly more popular then Internet Explorer in world wide usage. However this set of facts can be misconstrued if not viewed in context with regional website usage. The following chart shows browser popularity over the last four years.

Bar Chart Depicting Google Chrome as the most popular web browser
Google Chrome is currently the most popular web browser in the world


The following chart depicts the popularity of the same web browsers in the United States. As you can see Microsoft’s Internet Explorer is clearly the most popular browser by a wide margin.

Internet Explorer is the most popular browser in the United States 6/2012
Internet Explorer is the most popular browser in the US in June 2012

Furthermore by looking at the usage over the last month in the following graph you can see that Internet Explorer is growing while Firefox and Chrome are declining.

US browser popularity trends 6/2012. Internet Explorer is increasing in popularity.
Internet Explorer is increasing in popularity 6/2012

In conclusion: There’s really not much of a change. While regional shifts in browser usage has changed making Google Chrome the most popular web browser. The internet remains fragmented by users utilizing various different types of browsers. Internet Explorer is by far still the most popular browser in the United States where it continues to grow in popularity From a business owners perspective continued support for all browser types in your website and web applications remains vitally important. From an end users perspective browsers come and go and should be subjected to the acid 3 test to determine they are suitable for your use.

MS Biodiesel Site Development

Rolling out the MS Biodiesel Web Site!

I’ve been talking with Bob Glenn about a bio diesel venture for several months now.  He has been involved in two prior bio diesel ventures in the past, one which was very successful and one which ended in a tragic manner as Bob discovered he had what should have been terminal cancer.  Bob is an experienced business man and machinist and a tough and resilient cancer survivor with a vision for the future.  He believes that we can create energy sources from waste products that are being thrown in landfills.  Together we are launching to promote biodiesel production in the State of Mississippi and the Southeastern United States.

Join us as we create an online hub of resources for the promotion and production of Bio Diesel from waste stream materials.  We’ll be posting video content, links, and other resources on the site that showcase and explain methods and technologies available to small and midsize bio diesel producers.  We look forward to seeing you there.

– Joel

The Lean Startup–An Overview

There is more to creating a successful startup business than a great product, a brilliant team, amazing technology, and good timing, or so says Eric Ries, Chief Technology Officer and co-founder of IMVU and now author of The Lean Startup: How Today’s Entrepreneurs Use Continuous Innovation to Create Radically Successful Businesses. The fact is most startups fail, and not for the lack of determination, hard work, or perseverance.  No, they fail because they do not know the process of converting their ideas, their product and marketing insights, into a sustainable business. The goal of The Lean Startup is to improve the success rate of startup businesses attempting to bring new, innovative, products or services to the world.

At heart the Lean Startup Method is based upon five principles.

  1. Entrepreneurs are everywhere.  Ries defines a startup as “a human institution designed to create new products or services under conditions of extreme uncertainty.”  Anyone endeavoring to do this in whatever context is an entrepreneur.
  2. Entrepreneurship is management, albeit one done in the context of extreme uncertainty.  The boring stuff matters.
  3. Validated Learning.  First and foremost the objective of the startup is to build a sustainable business.  To begin with the startup has but “leap of faith assumptions” as to the desirability of its product or services and its potential customers.  These assumptions must be rigorously and scientifically tested.
  4. Build, Measure, Learn.  This is the fundamental activity of a startup.  Build, not a perfect, but a “minimum viable product.”  Great scepticism is often the response to this dictum.  It’s design is to get feedback  as soon as possible, so that we do not waste scarce resources, human and monetary, on a product no one wants.  Measure: scrutinize the feedback data from such customers or potential customers as we currently have, seeking to verify, falsify, or at minimum, clarify, our “leap of faith assumptions”.   Learning from the response to our minimum viable product,  we make small incremental changes in our product and/or business model and offer it once again to the public.  Build, measure, learn is done in cycles.  The faster our cycle time, the sooner we will learn that our fundamental assumptions concerning our product, business model, or potential customers are sound or that they are flawed: the sooner we will know that we are justified in staying on our present course or need to make a major change in our business, to “pivot or persevere.”
  5. Innovation accounting. To improve the success rate of startups, entrepreneurs must be held accountable.  But because a startup is different from an established business, the criterion for evaluation is different. Increases in sales, the total number of customers, the number of clicks on our website do not give enough information to warrant the conclusion that we are on the path to a sustainable business.  Such “vanity metrics”  are to be abandoned in favor of  what Ries calls “actionable metrics.”  The devil is in the details: “actionable metrics” come out of careful evaluations of feedback from the build, measure, learn cycles.

Lean startup principles have been applied to many diverse industries with amazing results.  They are worth the time you may spent examining them in detail.

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


or to the configured error_log mentioned earlier via


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) {

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%


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:

 use JavaScript::Minifier qw(minify);
 $oFile=~ s/[.]js//;
 open(INFILE, "$iFile") or die;
 open(OUTFILE, ">$oFile") or die;
 minify(input => *INFILE, outfile => *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.

An Introduction to the Privacy Policy

Our research analyst Larry has published a audio cast giving an introduction to the privacy policy.  He explains briefly why it is needed and what it covers.

If you have any questions or would like more information you can reach Larry using the form below.

Comments or questions are welcome.

* indicates required field


TWiki for a dynamic Company Operations Manual

This article is an overview of the TWiki system with an emphasis on usage as an intranet and Company Operations Manual. A small business can benefit by having such a system in several ways :

  • share knowledge and overlap responsibilities
  • document experience and improve processes
  • identify and facilitate process automation

A Company Operations Manual seeks to codify the processes that the company’s operation depends upon. It must

  • be simple to use/extend
  • be easy to navigate
  • have properties that facilitate improvement and revision
  • allow compartmentalization

TWiki fits each of these needs abundantly. It is by nature a system designed for ease of input. Concepts such as WikiWords and WebNotify allow for quick navigation by facilitating linkages among the various processes and automatically prompting personnel when processes of interest are updated. There are plugins that can keep statistics on how frequently topics are used. This allows identification of processes that would be good candidates for automation (high usage) and those that could be improved or deleted (low usage). TWiki also has built-in support for access control via group membership. This coupled with good design can simply the process of restricting auditors, contractors, and guests to areas of their focus, expertise, or clearance.

In small business there are fewer hands, and the processes and policys must be correspondingly light-weight. A dynamic, intranet-based Company Operations Manual is one way to achieve this necessary business requirement.

Copyright Math?

For those of you who’ve ever delved into the realm of copyright law and it’s absurdities in it’s present form please enjoy the following presentation from Rob Reid the founder of the company that created the music website Rhapsody.  If you’ll listen to his presentation you’ll see that the numbers used by traditional media companies to coerce judges, legislators, and the uninformed public into believing that technology is creating disruption in the market place that causes economic losses are largely fictional and greatly misrepresents the facts.

Copyright laws are based on the premise that information resources are rivalrous resources that can only be owned and utilized by a single owner.  However, information is not a rivalrous resource and increases in value as it is shared and utilized, therefor the very act of sharing it magnifies its potential to create wealth.  Traditional media companies fear the technological disruptions that frees information from their constraints and as the information becomes free the dying companies oftentimes resort to patent and copyright trolling methods in a futile effort to stop the shift in the channel of distribution of information.  Great speech Rob Reid and thanks exposing these misrepresentations for what they are in such a humorous way.