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 < 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.

-- Rogers Cadenhead

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.


 

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.


 

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.


 

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


 

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.


 

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


 

Great knowledge, do anyone mind merely reference back to it
SR&ED tax Consultant


 

That appears to be excellent however i am still not too sure that I like it. At any rate will look far more into it and decide personally!
rasensamen kaufen


 

nike air max 90 essential nz ,nike air foamposite one 2018
wholesale nike mercurial all red http://www.leadwirral.com/wholesale-nike-mercurial-all-red-shipping_en


 

nike air force 1 elite all star uk ,nike magista obra reinigen que
nike air max sequent 2 black gold on sale http://www.packmonitor.com/nike-air-max-sequent-2-black-gold-on-sale-football_it


 

lebron ambassador 10 all black ,adidas crazy light boost 2015 grey instagram
nike kobe 9 mid gris para barato http://www.carvingsaegen.com/nike-kobe-9-mid-gris-para-barato-running_es


 

nike flyknit lunar 2 womens all black ,nike kobe 9 elite high cheap nz
jordan stretch fit hat zip code http://www.carvingsaw.com/jordan-stretch-fit-hat-zip-code-hat_no


 

I think this is an informative post and it is very useful and knowledgeable. therefore. I would like to thank you for the efforts you have made in writing this article.
interesting articles


 

This is really a nice and informative. containing all information and also has a great impact on the new technology. Thanks for sharing it.
Herbal Incense


 

This is really a nice and informative. containing all information and also has a great impact on the new technology. Thanks for sharing it.
Herbal Incense


 

The information you have posted is very useful. The sites you have referred was good. Thanks for sharing..
voyance amour telephone


 

nike dunk sb low wolf grey ,nike kd 6 texas quizlet
nike air max plus tn ultra women shoes blackish green http://www.bnbpilipinas.com/nike-air-max-plus-tn-ultra-women-shoes-blackish-green-shoes_en


 

adidas eqt support kith korea ,adidas zx 700 burgundy usa
how to knit a baby hat beginners kit http://www.carvingsaw.com/how-to-knit-a-baby-hat-beginners-kit-hat_no


 

It is a great website.. The Design looks very good.. Keep working like that!.
download counter strike 1.6


 

These are some great tools that i definitely use for SEO work. This is a great list to use in the future..
best grind and brew coffee maker


 

nike mercurial superfly njr ,adidas ultra boost white laceless jazz
a buon mercato tutti rosso air jordan 5 (v) http://www.beanumber.com/a-buon-mercato-tutti-rosso-air-jordan-5-%28v%29-cheap_it


 

These are some great tools that i definitely use for SEO work. This is a great list to use in the future..
mold in coffee maker


 

adidas yeezy boost 350 pirate black release date ,kd v womens all black
air 6 jordan http://www.bnbpilipinas.com/air-6-jordan-shoes_fr


 

nike hyperdunk 2017 flyknit ep ,adidas iniki runner boost core black pink
grey black adidas crazy byw australia shoes http://www.clubofit.com/grey-black-adidas-crazy-byw-australia-shoes-urine_de


 

This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.
onlinecasinos-24


 

The website is looking bit flashy and it catches the visitors eyes. Design is pretty simple and a good user friendly interface.
007onlinecasino.club


 

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.
cityonlinecasino


 

Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking.
700


 

nike air pegasus sneakers usa ,nike shox clearance mens size 13 review
san francisco giants 55 tim lincecum 2012 fashion womens by majestic athletic jersey http://www.chaddhuffman.com/san-francisco-giants-55-tim-lincecum-2012-fashion-womens-by-majestic- athletic-jersey-jersey_au


 

I am definitely enjoying your website. You definitely have some great insight and great stories.
theonlinecasinos


 

lebron ambassador 10 all black ,adidas crazy light boost 2015 grey instagram
nike air presto red womens sneakers for australia http://www.nyrupplast.com/nike-air-presto-red-womens-sneakers-for-australia-nouveau_de


 

nike dunk hi nylon premium ,adidas f50 messi edition
red oakland athletics hat http://www.bettsgallery.com/red-oakland-athletics-hat-hat_de


 

nike magistax proximo tf amazon in ,nike hypervenom phelon junior astro turf trainers blue jean
youth new york yankees 24 gary sanchez blue 2017 all star american league stitched mlb jersey http://www.carvingsaegen.com/youth-new-york-yankees-24-gary-sanchez-blue-2017-all-star-american- league-stitched-mlb-jersey-jersey_ca


 

authentic air jordan 12 retro ovo ,air huarache mens all black
adidas predator tango 18.1 all pink sale http://www.leadwirral.com/adidas-predator-tango-18.1-all-pink-sale-shipping_en


 

jordan slippers philippines price list gif ,nike magista opus eastbay quote
nike lebron 13 r?d and svart http://www.bnbpilipinas.com/nike-lebron-13-r%C3%B8d-and-svart-shoes_no


 

jual adidas ace 15.1 boost ,under armour curry 3 review format
monsters knit hat 2016 http://www.chaddhuffman.com/monsters-knit-hat-2016-hat_no


 

air max 2016 red 2017 ,adidas eqt running support boston marathon live
adidas neo derby red sneakers for australia http://www.savewaterla.com/adidas-neo-derby-red-sneakers-for-australia-siren_de


 

This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.
Dating


 

This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.
Dating


 

HOW TO GET verizon fios pay my bill?


 

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.
Best Courses after 12th Arts


 

This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.
casino-automatengames


 

I am definitely enjoying your website. You definitely have some great insight and great stories.
onlineslots24


 

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.
onlinecasinosupport


 

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.
onlinecasinosupport


 

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.
17casinos.de


 

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.
casinojackpotslots


 

I think this is an informative post and it is very useful and knowledgeable. therefore. I would like to thank you for the efforts you have made in writing this article.
eurocasinojackpot


 

I think this is an informative post and it is very useful and knowledgeable. therefore. I would like to thank you for the efforts you have made in writing this article.
geldspielautomatenonline


 

This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.
jackpotconnection


 

Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking.
jackpotseven


 

Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking.
livecasinoonline


 

Thanks for sharing the post.. parents are worlds best person in each lives of individual..they need or must succeed to sustain needs of the family.
jackpotmaker.com


 

Awesome and interesting article. Great things you've always shared with us. Thanks. Just continue composing this kind of post.
casino-automatenspiele.org


 

Air Canada Customer Service Number


 

This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.
onlinecasino24.club


 

This is my first time i visit here and I found so many interesting stuff in your blog especially it's discussion. thank you.
onlinelottospielen.net


 

This is my first time i visit here and I found so many interesting stuff in your blog especially it's discussion. thank you.
onlinelottospielen.net


 

This is a great inspiring article.I am pretty much pleased with your good work.You put really very helpful information. Keep it up. Keep blogging. Looking to reading your next post.
lotto2000


 

The website is looking bit flashy and it catches the visitors eyes. Design is pretty simple and a good user friendly interface.
sportwetten-guide.de


 

I am definitely enjoying your website. You definitely have some great insight and great stories.
mobilecasinos24


 

Excellent article. Very interesting to read. I really love to read such a nice article. Thanks! keep rocking.
sportwetten-online.cc


 

Pretty good post. I just stumbled upon your blog and wanted to say that I have really enjoyed reading your blog posts. Any way I'll be subscribing to your feed and I hope you post again soon. Big thanks for the useful info.
netonlinecasino


 

I admit. I have not been on this web page in a long time... however it was another joy to see It is such an important topic and ignored by so many. even professionals. professionals. I thank you to help making people more aware of possible issues.
novoline-online


 

Add a Comment

These HTML tags are permitted: p, b, i, a, and blockquote. A comment may not include more than three links. Participants in this discussion should note the site's moderation policy.