1 Feb 2010 rcaden   » (Journeyer)

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.

PHP elephant iconAs 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

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:


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.

Syndicated 2010-02-01 20:37:30 from Workbench

Latest blog entries     Older blog entries

New Advogato Features

New HTML Parser: The long-awaited libxml2 based HTML parser code is live. It needs further work but already handles most markup better than the original parser.

Keep up with the latest Advogato features by reading the Advogato status blog.

If you're a C programmer with some spare time, take a look at the mod_virgule project page and help us with one of the tasks on the ToDo list!