Building a Store Locator with WordPress and Pods

Posted: February 22, 2010 Comments(34)

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.

Get my newsletter

Receive periodic updates right in the mail!

  • This field is for validation purposes and should be left unchanged.

Comments

  1. Great tutorial and interesting implementation. I think I might be able to learn from it to add certain functionality to our Pods implementation.

    Pods can still be a little daunting to non programmers since the User Guide is very lacking and your tutorials go a long way in helping fill this gap. Please consider creating a post on the Pods API. I have been trying to figure out how to update a column in a particular Pod item without using Public Forms and I believe the API is the way to do it. However, without proper documentation, I have no idea how to start.

    If you are looking for an example to showcase in a Pods API tutorial, you could show us how to create a simple analytics tool for pod pages (views, registered user vs. visitors etc). This is what I am looking to do with the API 🙂

  2. Very interesting information. What else can Pods do? I’m interested in the pluton but can’t really find a list of things like this that it can do. Could it be used to create a RSVP for instance?

  3. It can be used to do just about anything you can dream of. Seriously, you ask, I will most likely say yes. Yes it can do RSVP 🙂

  4. Great post, thanks. Could this be used with regular post types rather than pods? I’m looking for a proximity search solution in wordpress and haven’t found anything really except this. thanks again

  5. If you have a large number of stores I would not use the trig function in the database query, but first get a bounding box and then use only the coordinates of that box. That way, the database can use an index and doesn’t need an (expensive) calculation for the distance. You can then only calculate the distance for the returned records, as the others will always be further away.

  6. Thank you for a great article.

    I just implemented this on my new website, but I am having a problem.

    Let’s say I have 5 stores around zipcode 90020. The search result should show nearest store first, but it does not. I looked into your code and tried to debug. I found out that all five stores have 0 for $nearby_zip->distance.

    Do you know how to fix this?

  7. Jon, this looks fantastic. I was curious if it were difficult to add another variable? Such as: Keyword + Radius?

    An example would be: Search for an Electrician w/in 10 mile radius of your zip code?

  8. If everything is returning a distance of 0 then there’s nothing else to do, really. This example takes into account distance by mile only, your results all must be within 1 mile, right?

  9. @KMK
    This will only calculate the distance of the user’s zip to the distance of the store’s zip – not the actual address of the store. So if you have 5 stores all within the same zip, the returned results won’t be sorted by distance since they are all the same distance when you base it only on the zip code.

    The reason you are getting 0 for the returned results is that you are searching a zip code that has 5 stores in that zip code. Its essentially saying the distance from 90020 to 90020 is 0 miles.

  10. have you tried this on a member directory yet? How do you connect the Pods entries (address/zip) with the existing WP user data?

  11. thank you for the replay. the thing is i modified it to work with post types instead of pods. i’ve never used pods before. but since i posted my question until now that i saw your replay i managed to create pagination by sending the form output via URL. not the best method but the only one i could find. anyhow, this is a great tutorial. i actually took it a little further and almost done creating my first plugin. Thank again.

  12. Hi Jonathan,
    i just done developing a beta version for a radius search plugin. you can check it out at http://www.wpplaceslocator.com. if you dont mind i added a “thanks” link in the “about” page back to your tutorial since this is where i got the main idea from.
    Thanks again

  13. Great post Jonathan,

    Thanks.

    A question you don’t need to answer. Just something to think about, as other searches do have this functionality.

    How would you go about implementing a dropdown lookup, so when typing in the zip or suburb name, there is autocomplete for the zipcode/suburb entry?

    cheers
    Ed

  14. Hey Dalton,

    Would you be able to provide more information about how you got this working with ACF? Using the code above in combination with your GitHub demo, I have not been able to produce a working demo and its kinda doing my head in.

    Would appreciate any help you can provide.

    Zach

Leave a Reply

Your email address will not be published. Required fields are marked *