Creating a Closest Store Locator in PHP

Over the past year, one of my side projects has been the development of shopping directory sites for categories such as wargames, sports cards, videogames and farmers markets, the last of which I launched over the weekend. The sites are running on LAMP (Linux, Apache, MySQL and PHP) using my own code and the Smarty template language, which keeps me from cluttering up my web pages with PHP.

As I prepared the newest site, I decided to implement a feature that takes a user-submitted address and finds the closest stores. This functionality was the original impetus for the project -- I thought it would be cool if Wargames.Com had a store locator that could find the closest wargame store when I'm out of town.

To accomplish this, I needed to split latitude and longitude into their own fields in the MySQL database and use the following SQL query to find the closest stores to a user-submitted latitude and longitude:

SELECT *, (3959 * acos(cos(radians({$user_latitude})) * cos(radians(latitude)) * cos(radians(longitude) - radians({$user_longitude})) + sin( radians({$user_latitude})) * sin(radians(latitude)))) AS distance FROM stores HAVING distance < 250 ORDER BY distance LIMIT 0, 10

This query, which I found in a PHP/MySQL tutorial by a Google Maps engineer, employs the Haversine formula to compute distances between two pairs of coordinates on a sphere. The fields latitude and longitude are from the MySQL database. The PHP variables $user_latitude and $user_longitude contain the coordinates of the user address.

An address can be specified many different ways, but most people don't know the latitude and longitude of their location. Fortunately, Google Maps offers a web service that can take an address in a wide variety of formats and attempt to determine its latitude and longitude. The web service, which is a simple URL request, returns the information in either XML or JSON format. It requires a Google MAPS API key:

http://maps.google.com/maps/geo?q=ADDRESS&output=json&oe=utf8&sensor=false&key=YOUR+API+KEY

Plug the address and your API key into the request, changing "json" to "xml" if you want XML data. Here's example output for Disney World in JSON and XML.

I chose JSON over XML because it's easier to work with in PHP. PHP 5 has built-in support for JSON, but my sites are on a server running PHP 4, so I installed the Services_JSON library. After a brutal hour of trial and error that made me question programming as a lifestyle choice, I figured out that the following four lines of PHP code will pull a latitude and longitude out of Google's JSON address data:

$json = new Services_JSON(); $json_data = $json->decode($this->get_web_page($url)); $addr_latitude = $json_data->Placemark[0]->Point->coordinates[1]; $addr_longitude = $json_data->Placemark[0]->Point->coordinates[0];

The get_web_page() function returns the contents of a web page as a string.

I've added the closest-store search to all four sites, which you can try on the home page of Sportscard-Stores.Com. The next project will be to create mobile versions of the shopping sites so users can hunt stores with their phones.

Programming · Php · Ecommerce · 2010/02/01 · 6 COMMENTS · Link

Comments

That's interesting. Too bad some of your data is... incorrect. My address in Orange Park lists a few which seem reasonable, but these two stand out:

Vallejo Wednesday CFM, 154.60 miles
(800) 949-3276
Vallejo, California 94520

Tucumcari Farmers Market, 164.11 miles
(505) 461-3346
Tucumcari, New Mexico

I'm also sure that you're missing one in Fleming Island (which just started, so I can't say that I'm surprised), and I thought there was at least one other in Jacksonville besides Neptune Beach.

#1 · Bryan Price · 2010/02/01

I'll look into those -- looks like bad GPS data.

The farmer's market database is being built over time. Since we gather the data ourselves, the sites are small when they start out.

#2 · Rogers Cadenhead · 2010/02/01

I was reading that same code the other day in researching potential similar project. That query would be hard to optimize as it is, if you have a good sized database. I saw another listing somewhere that added some pre-calced fields that you could index.

#3 · Steve Michel · 2010/02/01

Is it possible to create your mysql code also in phpmyadmin or have I to pay attention about something else?

#4 · Sophie · 2010/02/15

You can execute a query like that in PhpMyadmin, but if you want something you can offer users, I think you need to use a web application language such as PHP.

#5 · Rogers Cadenhead · 2010/02/15

Hi Rogers.
I studied Smarty in one of my PHP classes but wasn't very impressed about it. Others, though, rave about it. To me, it seems to add an additional, unnecessary layer. Abstraction where abstraction isn't necessary. Why do you like it? I want to understand its attraction, so I ask.
Cheers.
Rudy

#6 · rudy · 2011/08/26