http://www.developer.com/

Back to article

Create Your Own Store Locator with Google Maps, PHP, and MySQL


February 1, 2010

This occasional series introducing various facets of the powerful Google Maps API has examined quite a few different features that are likely to appear in an enterprise web-based mapping implementation, ranging from calculating user-defined route distances to using the Google Maps API Geocoder to convert mailing addresses to their respective coordinates. In this installment, I discuss yet another feature that has become standard, particularly on the web sites of large retail chains: the "Store Locator."

The Store Locator feature provides web site visitors with an easy way to determine the location of nearby stores, typically done by first asking the visitor to provide his or her zip code. The site then consults a database containing all of the chain locations, somehow determining which stores reside in or around the visitor's zip code. The locator feature is also often enhanced by providing the user with an option to view stores residing within a specified radius of the provided zip code, such as 10 miles, 20 miles, or 100 miles. In this tutorial, I'll show you one way to create this feature using the Google Maps API, JavaScript, PHP, and a MySQL database.

The Location Table

Let's start by creating the table, which will contain information about each of the retail chain's outlets. For the purposes of example, I'll chart several locations of my favorite store, Barnes & Noble. This table, called location, contains each outlet's name, mailing address, and latitudinal/longitudinal coordinates. The coordinates were presumably saved to the database after geocoding the outlet's address using the Google Maps API geocoder. As I explained this in a previous article, I'll leave this task as an exercise for you and instead just present the location table structure:

CREATE TABLE location (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  street1 VARCHAR(100) NOT NULL DEFAULT '',
  street2 VARCHAR(100) NOT NULL DEFAULT '',
  city VARCHAR(100) NOT NULL DEFAULT '',
  zip VARCHAR(10) NOT NULL DEFAULT '',
  state CHAR(2) NOT NULL DEFAULT '',
  latitude FLOAT(10,6) NOT NULL,
  longitude FLOAT(10,6) NOT NULL
);


With several stores in the database, I can plot their locations simultaneously on a map, as shown in Figure 1.


Figure 1. Plotting Ohio-based Barnes & Noble Locations:
I can plot all store locations in the database simultaneously on a map.

Geocoding a Zip Code

Next, we'll need to add a form field to the page that will prompt the user to enter his zip code. We'll also add a select field that asks the user to set the radius (in miles) that the locator should use to retrieve the stores. Figure 2 shows the revised screenshot.


Figure 2. Adding the Locator Form:
The form field prompts the user to enter his zip code and a radius.

I used jQuery's getJSON method, which can call a PHP script and receive a returned set of coordinates, to load the database markers to the map when the page is loaded. Here's the relevant part of the jQuery/JavaScript code, which performs this task:

$.getJSON('load-locations.php',
  function(data) {
    for(var x = 0; x < data.length; x+= 1) {
      addMarker(data[x].latitude, data[x].longitude);
    }  
  }
);
...
// Add marker to map
function addMarker(latitude, longitude) {
  var point = new GLatLng(latitude, longitude);
  var marker = new GMarker(point);
  map.addOverlay(marker);
}


If you want to learn more about how to connect jQuery and JSON, check out the tutorial "Gluing jQuery and PHP Together with JSON".

Locating Stores Within a Given Radius

To determine which locations reside within a given radius from a set of coordinates, we'll create a SELECT statement based on the Haversine formula. The Haversine formula is able to derive the distance between any two points on Earth given their coordinates. Therefore, we can use this formula to compare/calculate the distance between the stores in the location table and the zip code's coordinates, filtering out those that exceed the provided distance.

The Haversine formula will likely leave you straining to recall high school algebra and geometry lessons. However, understanding the formula isn't a prerequisite to taking advantage of it, so I'll forego the algebraic explanation and instead refer you to the previously linked-to Wikipedia page. It provides a deeper explanation of the details. Here's the formula when translated to SQL:

SELECT id, 
  ( 3959 * acos( cos( radians($zipLatitude) ) * cos( radians( latitude ) ) * 
  cos( radians( longitude ) - radians($zipLongitude) ) + sin( radians($zipLatitude) ) * 
  sin( radians( latitude ) ) ) ) AS distance 
  FROM location HAVING distance < $distance ORDER BY distance;


Incidentally, if you prefer to use kilometers instead of miles, substitute 3959 with 6371.

We'll use the SQL query in a moment, but first let's look at the JavaScript code that executes when the user submits the zip code and radius through the form. This code will retrieve the provided zip code and radius, clear the current markers from the map, and then use the aforementioned getJSON method to call radius.php, passing along the coordinates and radius. The radius.php script will return a JSON array consisting of the coordinates of those locations falling within the specified radius. These coordinates are plotted to the map using the addMarker() function defined earlier in this tutorial. Here's the code:

// Remap markers according to zip and radius
$('#locator').submit(function(e) { 
  if (geocoder) {
    e.preventDefault();
    zip = $('#zip').val();
    radius = $('#radius').val();
    geocoder.getLatLng(
      zip,
      function(point) {
        if (!point) {
          alert("The zip code was not found.");
        } else {
          map.clearOverlays();
          var lat = point.lat();
          var lng = point.lng();
          $.getJSON('radius.php',
            {radius: radius, latitude: lat, longitude: lng},
            function(data) {
              for(var x = 0; x < data.length; x+= 1) {
                addMarker(data[x].latitude, data[x].longitude);
              }  
            }
          );
        }
      }
    );
  }
});


The role of radius.php is simple: connect to the MySQL database and use the Haversine-adapted query to retrieve those locations falling within the defined radius:

<?php
 
  $db = new mysqli("localhost", "root", "jason", "storelocator");
   
  $zipLatitude  = $_GET['latitude'];
  $zipLongitude = $_GET['longitude'];
  $radius       = $_GET['radius'];
   
  $query = "SELECT id, latitude, longitude, 
    ( 3959 * acos( cos( radians($zipLatitude) ) * cos( radians( latitude ) ) * 
    cos( radians( longitude ) - radians($zipLongitude) ) + sin( radians($zipLatitude) ) * 
    sin( radians( latitude ) ) ) ) AS distance 
    FROM location HAVING distance < $radius ORDER BY distance";
 
  $result = $db->query($query);
 
  $coordinates = array();
   
  while ($row = $result->fetch_object()) {
    $coordinates[] = array("latitude" => $row->latitude, "longitude" => $row->longitude);   
  }
   
  $coordinates = json_encode($coordinates);
 
  echo $coordinates;
 
?>


When these scripts are in place, I can begin querying the database for locations residing within 10 miles of the zip code 43215, as demonstrated in Figure 3!


Figure 3. Querying for Locations Within a Defined Radius:
I can query the database for locations residing within 10 miles of the zip code.

You can play with a live demo of the store locator here.

Go Beyond Retail!

A Store Locator such as the one described in this article certainly has its place within the retail sector, but the potential applications are endless. If you wind up doing anything interesting with what you learned in this tutorial, I'd love to hear about it!

About the Author

Jason Gilmore is founder of EasyPHPWebsites.com, and author of the popular book, "Easy PHP Websites with the Zend Framework". Formerly Apress' open source editor, Jason fostered the development of more than 60 books, along the way helping to transform their open source line into one of the industry’s most respected publishing programs. Over the years he's authored several other books, including the best-selling Beginning PHP and MySQL: From Novice to Professional (currently in its third edition), Beginning PHP and PostgreSQL: From Novice to Professional, and Beginning PHP and Oracle: From Novice to Professional.

Jason is a cofounder and speaker chair of CodeMash, a nonprofit organization tasked with hosting an annual namesake developer’s conference, and was a member of the 2008 MySQL Conference speaker selection board.

Jason has published more than 100 tutorials and articles within prominent publications such as Developer.com.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date