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

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 msbiodiesel.us 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

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

 

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.

 

 

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.

 

The new year – A new resolve – Resolutions

Well the first month of the new year is closing and we have eleven months to make a difference and enact resolutions in our lives.  I had a mental list of ideas that I was going to write down on paper as resolutions at the turn of the year but it seems that time got away from me and I’ve almost missed the chance to enact them.  As it says on the our site “without a vision the people perish” and with out a dream to pursue life would be meaningless, so even though I’m a little late listing these the dream and vision of its fulfillment predicates that I must list these resolutions regardless of the time.

Here are my resolutions for 2012

  • Systemize the business processes needed to make catontech great and sustain performance in the long term.
  • Become a partner in all business that I interact with.
  • Really listen to the people that that I communicate with and search for the meaning behind the meaning of what they say and do.
  • Focus again on the disciplines I learned in the martial arts and get back in shape.
  • Communicate with all partners daily, weekly, monthly – utilize a personal touch by attending to the details of their lives and businesses.
  • Tell the catontech story everyday.
The dream lives and the vision will be realized.
Joel

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!!!