Building a Store Locator with WordPress and Pods

Truth be told I was always a bit intimidated by proximity searches. That is to say the “Find X within Y miles” implementations we see everywhere. There was always a bit of magic behind how that worked and I’ve had a long time itch to figure out exactly how it worked.

Luckily enough, a client need came through which included such a feature. What better time to teach yourself something? Other programmers at my company had implemented proximity searches in the past, but I wasn’t involved on the projects and didn’t want to simply peruse their code and see what I could pull from it. I sat down with one of the guys as I began working on the feature to discuss past implementations. There were two ways to go about it:

1. Piggyback on Google Maps
2. Do things the hard way

It took me about zero seconds to determine that I wanted to do things the hard way. It’s not that I don’t enjoy working with Google Maps. Quite the contrary in fact. I just don’t want to depend on it too much in the day to day, especially for something as generic as a proximity search. Additionally, it just adds another layer of complexity with remote calls, API limits, and the other issues associated with using any other third party service. Hard way it is.

Pods setup

The first thing we’ll do is create a ‘stores’ Pod. If you’re not familiar with creating Pods, take a few minutes to read the beginner series, starting with Pods Basics: Installation and Setup. I’m going to use the following columns:

• name (txt)
• slug (slug)
• address (txt)
• city (txt)
• state (PICK state)
• zipcode (txt)

We’re also going to simply make this Pod a Top Level Menu. If you’re looking to make things even nicer, go ahead and include Pods UI where applicable.

I’ve made every column required data, as an address isn’t very useful without complete information. I segmented the addresses as such simply because the proximity search I’ll be implementing is based on ZIP code, and having that data in a separate column will help with server load. Lastly, I chose to use a txt column for the zipcode instead of a Number because many ZIP codes begin with one (or more) zeros, which would cause a bit of trouble.

As a last step, we’ll go ahead and add a few test store listings using various ZIP codes you’re familiar with. The more variance the better, as you’ll be able to see whether the proximity aspect of the search is working properly or not.

Once you’ve added a few samples, it’s time to integrate.

WordPress setup

For the sake of brevity, this location based search will sit on its own WordPress Page, using a template we’ll put together. We’ll continue working with the WordPress default theme, and add a new template; store-locator.php which includes the following:

<?php

/* Template Name: Store Locator */

get_header(); ?>

  <div id="content" class="narrowcolumn" role="main">

    <?php the_post(); ?>
    <div class="post" id="post-<?php the_ID(); ?>">
    <h2><?php the_title(); ?></h2>
      <div class="entry">

        <?php the_content(); ?>

        <form action="" method="post">

          <p>
            <label for="mbn_zipcode"><small>Your ZIP</small></label>
            <input type="text" name="mbn_zipcode" id="mbn_zipcode" value="<?php echo $_POST['mbn_zipcode']; ?>" size="5" tabindex="1" />
          </p>

          <p>
            <label for="mbn_distance"><small>Within (miles)</small></label>
            <select name="mbn_distance" id="mbn_distance" tabindex="2">
              <option value="5"<?php if( empty( $_POST['mbn_distance'] ) || $_POST['mbn_distance'] == "5" ) : ?> selected="selected"<?php endif ?>>5</option>
              <option value="10"<?php if( $_POST['mbn_distance'] == "10" ) : ?> selected="selected"<?php endif ?>>10</option>
              <option value="25"<?php if( $_POST['mbn_distance'] == "25" ) : ?> selected="selected"<?php endif ?>>25</option>
              <option value="50"<?php if( $_POST['mbn_distance'] == "50" ) : ?> selected="selected"<?php endif ?>>50</option>
              <option value="100"<?php if( $_POST['mbn_distance'] == "100" ) : ?> selected="selected"<?php endif ?>>100</option>
            </select>
          </p>

          <p><input name="submit" type="submit" id="submit" tabindex="3" value="Submit" /></p>

        </form>

      </div>
    </div>

  </div>

<?php get_sidebar(); ?>

<?php get_footer(); ?>

Nothing exciting happening here, we’re simply setting the stage with the basic form we’ll use to search our store directory by location.

Now that we’ve got our Page using our template, we’ll implement the location search functionality via Pods.

Setting up the location search

Our first step will be to add a conditional that checks to see if the form has been submitted. In this case, I’ll simply add a hidden input to track the current action:

<input type="hidden" name="mbn_action" value="search" />

With this action in place, we can include our conditional:

<?php if( !empty( $_POST['mbn_action'] ) ) : ?>
  <p>List our results</p>
<?php endif ?>

Here’s where everything happens. Once we find ourselves in the conditional, we’ll need to do a bit of work:

1. Sanitize our data
2. Retrieve search results
3. Display data

Our conditional will expand to be:

<?php
  if( !empty( $_POST['mbn_action'] ) )
  {
    if( !is_numeric( $_POST['mbn_zipcode'] ) )
    {
      $the_zip = '0';
    }
    else
    {
      $the_zip = sanitize_text_field( $_POST['mbn_zipcode'] );
    }

    $the_distance = intval( $_POST['mbn_distance'] );

    $stores = mbn_get_stores_by_location( $the_zip, $the_distance );
    $stores_total = count( $stores );
  ?>

    <?php if( $stores_total > 0 ) : ?>
      <h3>Search Results</h3>
      <ol>
        <?php foreach( $stores as $store ) : ?>
          <?php $store_address = $store['address'] . ' ' . $store['city'] . ', ' . $store['state'][0]['name'] . ' ' . $store['zipcode'] ; ?>
          <li><dl>
            <dt>Name</dt><dd><?php echo $store['name']; ?></dd>
            <dt>Address</dt><dd><?php echo $store_address; ?></dd>
            <dt>Distance</dt><dd><?php echo $store['distance']; ?> miles</dd>
          </dl></li>
        <?php endforeach ?>
      </ol>
    <?php endif ?>

<?php } ?>

You’ll notice that there’s no reference to Pods anywhere in that conditional, but there is a new function to take a look at; mbn_get_stores_by_location().

Implementing the proximity aspect

This new function, mbn_get_stores_by_location(), will take care of all the work including:

1. Determine the latitude and longitude of the source ZIP code
2. Use triginomitry to determine which additional ZIP codes fall within the chosen radius
3. Retrieve all Pods entries matching the pool of ZIP codes
4. Return an array of results, ordered by distance

The quickest and easiest way to implement the function will be adding it to functions.php in your theme. Before we implement the function, however, you’ll need to add a new table to your database. This table is going to include a listing of all US-based ZIP codes and acts as an integral piece for this function. The table is used to determine the latitude and longitude of applicable ZIP codes, and is used with each search submission. There are a number of places to obtain a ZIP code database, the one I’ve used in the past is made available for free from PopularData.com. It’s made available as a CSV file weighing in at about 700k. There is a donation button available on the download page, and I’d suggest showing your appreciation to the provider should you use the database for any commercial work.

You’ll need to set up a new table in your WordPress database and be sure to name it something unique. You’ll need to remember both the table name and column names as you implement the location search. Once you’ve added the table and verified its integrity, we can go ahead and implement mbn_get_stores_by_location() in functions.php:

<?php

function mbn_get_stores_by_location( $zip, $radius )
{
  global $wpdb;
  $radius = intval( $radius );

  // we first need to get the source coordinates
  $sql = "SELECT `latitude`, `longitude` FROM `mbn_zip_codes` WHERE `zipcode` = '%s'";
  $coords = $wpdb->get_row( $wpdb->prepare( $sql, $zip ) );

  // now we'll get the other ZIP codes within the radius, ordered by distance
  $sql = "SELECT mbn_zip_codes.zipcode, ( 3959 * acos( cos( radians( $coords->latitude ) ) * cos( radians( mbn_zip_codes.latitude ) ) * cos( radians( mbn_zip_codes.longitude ) - radians( $coords->longitude ) ) + sin( radians( $coords->latitude ) ) * sin( radians( mbn_zip_codes.latitude ) ) ) )  AS distance FROM mbn_zip_codes HAVING distance <= $radius OR distance IS NULL ORDER BY distance";

  $nearby_zips = $wpdb->get_results( $sql );

  // we need to store the zips in order to build the Pods query
  $target_zips = array();
  foreach ($nearby_zips as $nearby_zip)
  {
    array_push($target_zips, $nearby_zip->zipcode);
  }

  // we're going to store the results as we go
  $store_results = array();

  if( count( $target_zips > 0 ) )
  {
    $final_target_zips = implode(',', $target_zips);

    if( strlen( $final_target_zips > 0 ) )
    {
      // let's snag the data
      $stores = new Pod('stores');
      $stores->findRecords('id ASC', 9, 't.zipcode IN (' . $final_target_zips . ')');

      $store_data = array();

      while ( $stores->fetchRecord() )
      {
        $store_data['id']       = $stores->get_field('id');
        $store_data['name']     = $stores->get_field('name');
        $store_data['slug']     = $stores->get_field('slug');
        $store_data['address']  = $stores->get_field('address');
        $store_data['city']     = $stores->get_field('city');
        $store_data['state']    = $stores->get_field('state');
        $store_data['zipcode']  = $stores->get_field('zipcode');

        foreach ($nearby_zips as $nearby_zip)
        {
          if( $store_data['zipcode'] == $nearby_zip->zipcode )
          {
            $store_data['distance'] = intval( $nearby_zip->distance );
          }
        }

        array_push( $store_results, $store_data );
        unset( $store_data );
      }

      usort( $store_results, "mbn_cmp" );
    }

  }
    return $store_results;
}

?>

Quite a bit is going on in this function. Once our globals are set and our data sanitized, we’re going to query the new table we set up to retrieve the coordinates of the submitted ZIP code. Once we have those coordinates, we’re going to perform a lengthy query on that same table to determine what other ZIP codes fall within the submitted radius:

SELECT mbn_zip_codes.zipcode, ( 3959 * acos( cos( radians( $coords->latitude ) ) * cos( radians( mbn_zip_codes.latitude ) ) * cos( radians( mbn_zip_codes.longitude ) - radians( $coords->longitude ) ) + sin( radians( $coords->latitude ) ) * sin( radians( mbn_zip_codes.latitude ) ) ) )  AS distance FROM mbn_zip_codes HAVING distance <= $radius OR distance IS NULL ORDER BY distance

You will need to be sure that table and column names are appropriately updated. There are a number of formulas which use trigonometry to solve proximity equations, but I’ve found the above to suit my needs well in this application.

Once we’ve got our nearby ZIP codes ordered by date, we query our Pod for any entries with ZIP codes that match our results. Instead of using get_field() to echo the display directly, we’ll instead use it to store the data in an array consisting of all returned results.

Once we’ve processed the results, our last step will be to use usort to ensure our results are actually sorted by distance:

usort( $store_results, "mbn_cmp" );

usort uses a custom function to determine the comparison result, and we’ll need to add that to functions.php as well:

function mbn_cmp($a, $b)
{
  $a = intval( $a['distance'] );
  $b = intval( $b['distance'] );
  if( $a < $b )
  {
    return -1;
  }
  elseif( $a > $b )
  {
    return 1;
  }
  else
  {
    return 0;
  }
}

Note that the comparison function uses a specific array key to generate a result. If you change the distance key, you’ll need to update this function as well.

That takes all the magic out of it. If we return to our submission form and search for a ZIP code that is known to produce results, we’ll see the functionality at work:

I realize the explanation is a bit lengthy, but I hope it helps to explain how a proximity search can work on a smaller scale.

Alternative implementations

As I researched location based proximity searches, I found a number of alternative solutions using PHP and MySQL. Some of which included stored procedures, and other advanced uses of MySQL. I’m nearly positive other implementations would require less computing power and run a bit faster, but I chose not to take that path because I wanted to keep the functionality as lightweight and portable as possible.

The queries used in the search function are performing quite a bit of math, and probably won’t scale extremely well. If you’re only looking to implement a location search on a smaller set of data (under 10,000 or so) I think using something like this will work for you as well.

Other uses

With Pods making a location based ZIP code search so easy, I’m hoping to implement it on more client projects since it won’t affect the budget very much. Some initial use cases that come to mind are:

• Member directory searches
• Service area searches
• Geographic zone searches
• Event listings

The next step I’d take with an implementation such as this would be to remove the requirement of entering your ZIP code manually altogether and instead use geolocation to automatically generate a starting point for the visitor.