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

 

 

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

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.

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.

Interesting facts about media consumption

I’ve been doing some research lately about media consumption by age group and I found a couple graphical representations of the data on this subject.  We’ve been working on a project that utilizes the data shown, but it’s top secret right now so I’ve got to keep it hush hush 😉

The first chart is from adage.com and lists the types of media consumption by generation.  It’s a tough read but the information you’ll get is well worth the effort.

Media Consumption - 2011
Created by: MBA Online

The second chart is much more easy to read :)  It shows the change in internet consumption over the last several years.  As you can see mobile web usage is doubling yearly now while desktop usage is on the decline.  People will be doing what they do online all the time in the near future and internet media consumption will be a continuous thing that never stops.

 

Source: StatCounter Global Stats – Mobile vs. Desktop Market Share

Good luck guessing what we are up to.  I can’t wait to explain it in a blog post.  For now… We’ll see you online.

 

Process Automation: Customer Relationship Management Software – CRM

One of the modern tools of business automation is customer relationship management software commonly known as CRM software. CRM software provides process automation and a centralized data storage point for all things pertaining to suspects, prospects, and customers.

Services offered by the CRM help businesses to organize, synchronize, and manage business processes with the goal of finding, attracting, and winning new clients and then nurturing and retaining those relationships by providing a system that facilitates communication between the client and various departments such as sales, marketing, and support.  The software often serves as the digital hub of a company wide marketing strategy that places a high value on customers relationships and ties financial data to every step involved with acquiring and servicing a customer.

At Catontech we’ve been working with the open source solution Vtiger CRM and evaluating it for future usage and integration with web presence packages allowing leads generated by online marketing efforts to be stored in the system for future marketing efforts.  You can view the video below to get an idea of how the system looks and feels.


What we like about Vtiger is that it is open source and runs on linux, appache, mysql, and php and can be implemented at a very low cost with a low learning curve and integrates nicely with WordPress.  Vtiger CRM is relatively easy to learn and you can get up to speed in about 1/2 an hour.

We have began testing the use of Vtiger Customer Relationship Management Software on three different projects and we will be reporting on the outcome of those ventures in the near future.

– Joel

Protect our Internet Freedom


Those of you that keep up with me know about my book a month program that I use to keep pace with business and technology trends.  Last months book was The Laws of Disruption: Harnessing the New Forces that Govern Life and Business in the Digital Age by Larry Downes.  In it Larry discusses how technologies change society and how law makers oftentimes clash with technology in an effort to appear to be legislating issues that are identified by elements of the society.  Most of the time lawmakers just throw a monkey wrench into markets that are created by innovation.

The following video portrays the latest government intrusion into a market built on technology that it does not understand.  Please watch this video and take action.  We must stand up and protect our internet freedom or we will lose it.  Increased governmental regulation of the internet could change the internet environment and make web applications as we know them a thing of the past.  I didn’t have any part in creating this video but I agree with it 100%.

PROTECT IP Act Breaks The Internet from Fight for the Future on Vimeo.

Tell Congress not to censor the internet NOW! – http://www.fightforthefuture.org/pipa

PROTECT-IP is a bill that has been introduced in the Senate and the House and is moving quickly through Congress. It gives the government and corporations the ability to censor the net, in the name of protecting “creativity”. The law would let the government or corporations censor entire sites– they just have to convince a judge that the site is “dedicated to copyright infringement.”

The government has already wrongly shut down sites without any recourse to the site owner. Under this bill, sharing a video with anything copyrighted in it, or what sites like Youtube and Twitter do, would be considered illegal behavior according to this bill.

According to the Congressional Budget Office, this bill would cost us $47 million tax dollars a year — that’s for a fix that won’t work, disrupts the internet, stifles innovation, shuts out diverse voices, and censors the internet. This bill is bad for creativity and does not protect your rights.

Protect our internet freedom.  Express your concern here!!!

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

The basic weekly web awareness task list

Building web awareness of your business takes time and consistent effort.  Here is a list of tasks that I work on weekly to insure that people can find Catontech.com. Used consistently these methods will build awareness and cause your website to improve in search engine ranking.

  • Write a blog post about a topic relevant to your business.  I recommend using the WordPress blogging platform.
  • Use an rss reader to read other bloggers articles that are relevant to your business and leave comments with a link to your site.  Try to do a minimum of five comments per week.  I recommend using Google Reader to subscribe to other bloggers RSS feed.
  • Use twitter to comment about things you are doing that are relevant to your business.  I recommend using tweet deck and twaitter to organize and automate some of these tasks.
  • Post updates to Google+ and Facebook
  • Answer questions on Yahoo Answers or Linkedin Answers
Over time performing the tasks in this basic to do list will improve your websites ranking in search engines and drive traffic to your site.  It takes about six months to see a return on investment from this investment of time but it is well worth the effort.
What do you do to promote web awareness of your business online?
– Joel

 

How to know when: comments should be deleted

Tips for quickly determining if comments should be deleted.

Your blog or online publication is a serious investment and the centerpiece of a good inbound marketing program.  You work hard creating content and the payoff occurs when visitors leave their comments.  However there is an insidious element at work on the internet. Spammers are hard at work trying to find ways to trick search engines into thinking that their sites are linked to reputable sites such as yours across the internet. You’ll receive bogus comments on your blog from people trying to create links to sites that they represent.  Serious spammers devise scripts that look for blogs to post generic garbage type comments on that are filled with links to an advertising sites and you can end up getting several of these a day.  This can either give you a false sense of optimism about your readership if you are naive and allow this to take place or it can make your job as content provider tedious when weeding the garbage comments out.  There are several good plugins for handling comments such as Asikmet but they cost money when being used on a business site.  If your on a budget you won’t want to waste your time dealing with these type comments so here’s several quick indicators you can use to quickly and easily determine when comments should be deleted.

When comments should be deleted:

  1. The person leaving the comment didn’t leave their name.  Sometimes you’ll get legitimate commentors that don’t leave their names but if they don’t consider it a strike against them.
  2. The uri in their post (their website) links to a specific product page on a commercial site.
  3. The email address that they posted looks phony.  
  4. The comment isn’t coherent.  
  5. Their comment wasn’t about your article. 
  6. They posted multiple links to advertising sites.
Look for combinations of the things we’ve listed above when reading the comments you receive.  A comment with two or more issues such as the ones we’ve discussed here probably isn’t fit to appear on your site.  With a little practice you’ll become good at skimming comments for these issues and with very little time and effort you’ll know when comments should be deleted.
I’m sure as you continue working on your blog or online publication that you’ll notice more patterns that could be added to this list so feel free to leave comments here on the tips you find.
Legitimate comments are welcome here!!
Joel Caton
Meridian, MS

Catontech Web Presence Package

Introducing the Catontech Web Presence Package

Do you need a web presence for your organization?  Would you like to save money by doing it yourself but you don’t feel you have the time to study all the new technology and gain the required technical expertise?  There is a solution that fits your needs:  The Catontech Web Presence Package.  Let us provide you the tools to give your business an online presence in a cost effective way.  Here’s what the Catontech Web Presence Package offers:

Features:

  • Your own domain .com, .net. .us etc.  Example: yourbusiness.com
  • A cost effective upfront price that will pay for your service for a year!!
  • An easy to use content management system so you can update your site without the hassle of contracting programmers or developers.
  • Guides for do it yourself search engine submission at no cost to you.
  • Coaching on how to promote your site without spending money.
  • Access to free tools you can use to update and maintain your site.
  • Free design of your initial banner and icon.
  • Integration with social media sites such as Twitter and Facebook.
  • Catontech discount club membership.
  • Referral rewards.
– Joel
Meridian, MS