Closest location look up scripts

  • carlosrapa
  • Newbie
  • Newbie
  • carlosrapa
  • Posts: 11
  • Loc: Fairview Heights, IL. (St. Louis Area)

Post 3+ Months Ago

We have a company that has many distributors through out the nation... I would like to write a small program that has a couple of fields...

It is commonly found on websites like Radioshack.com or circuitcity.com....


I want the user to be able to type in an address or city/state and indicate the max amount of miles they are willing to travel... and be given a list of the closest distributors/stores to them...

How do these popular websites do it??? I am wondering if any of you Web-Gods out there know the answer... and can help point me in the right direction to create a similar effect for our company site...

Just curious,
Thanks a Bunch,
Carlos
  • Anonymous
  • Bot
  • No Avatar
  • Posts: ?
  • Loc: Ozzuland
  • Status: Online

Post 3+ Months Ago

  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

You know, I was looking for something similar to this on my website...

There are many reptile shows & expos throughout the US during the course of the year. I'm currently looking to do a database of all the upcoming shows, so it'd be handy for my site's members to be able to type in their zip code and "view all shows within 100 miles" or whatever.
  • ATNO/TW
  • Super Moderator
  • Super Moderator
  • User avatar
  • Posts: 23456
  • Loc: Woodbridge VA

Post 3+ Months Ago

You might Google for: zip code database

There seem to be some reasonably priced subscription services that should meet your needs.
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

Hey thanks ATNO/TW, I managed to find one that looks like a pretty good price, only 30 bucks for the one including latitude & longitude...

http://www.zipcodedownload.com/products.aspx?ID=DataUSA

As for working out the distance, I've found a few scripts & equations to help me figure that one out, so I don't need to spend another 40 bucks on the "Distance Assistant" right now, but I might go for the "distance & radius assistant" bundle sometime in the future.
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

Okies, here we go...

If it's not essential that your zip code list remains bleeding edge, then you can get a free list of zip codes right here..

http://www.census.gov/geo/www/gazetteer/places.html

That's the US Census Bureau, and their most recent free list is from Y2K.

I've just downloaded the .txt file, so as soon as I get it converted over to a format I can easily whack in a MySQL database, and get a PHP script or two together, I'll pop it up here.
  • UNFLUX
  • Genius
  • Genius
  • User avatar
  • Posts: 6376
  • Loc: twitter.com/unflux

Post 3+ Months Ago

moved per request...
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

// Thanks for moving this to the coding forum Unflux.

Ok, so after some downloading, messing around in Excel, and coding for a lil while, I got a solution going...

You can see the script up and running here.

There is one function in the script that is not mine (and I labelled it as such), and as it worked, I didn't bother to recode it or rework it at all).

This is simply as a demonstration of how it can be done. There is no error checking, so it will fall over if you enter anything other than a valid pair of zip codes :)

Here's the actual code to do it... (BWM, please install [php], heh)

save as distance.php (or edit the form to reflect new filename)

Code: [ Select ]
<?php

$dbhost = 'localhost';
$dbname = 'database_name';
$dbuname = 'account_name';
$dbupass = 'account_password';

$connection = mysql_connect("$dbhost","$dbuname","$dbupass")
    or die ("Couldn't connect to server.");
    
$db = mysql_select_db("$dbname", $connection)
    or die("Couldn't select database.");

// I did not write this function - I found it on the web somewhere.
function distance($lat1, $lon1, $lat2, $lon2, $unit) {

 $theta = $lon1 - $lon2;
 $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
 $dist = acos($dist);
 $dist = rad2deg($dist);
 $miles = $dist * 60 * 1.1515;
 $unit = strtolower($unit);

 if ($unit == "k") {
  return ($miles * 1.609344);
 } else if ($unit == "n") {
  return ($miles * 0.8684);
 } else {
  return $miles;
 }
}

function page_header() {
 return '<html>

<head>
<title>Distance Calculator</title>
<style>
<!--
input    { font-family: Verdana; font-size: 8pt; font-weight: bold }
td      { font-family: Verdana; font-size: 8pt; font-weight: bold }
-->
</style>
</head>

<body>
<center><table border="0" width="350" cellspacing="0" cellpadding="0">
<tr><td align="center"><u>Axe\'s Distance Calculator</u><br><br></td></tr>
<tr><td>';
}

function page_footer() {
 return '</td></tr></table></center></body></html>';
}

function diststuff($zipo, $zipd) {
 $origsql = 'select * from zipcodes where zip = "'.$zipo.'"';
 $origresult = mysql_query($origsql);
 $origdata = mysql_fetch_assoc($origresult);
 $destsql = 'select * from zipcodes where zip = "'.$zipd.'"';
 $destresult = mysql_query($destsql);
 $destdata = mysql_fetch_assoc($destresult);
 return '<table border="0" width="350" cellspacing="0" cellpadding="0">
 <tr>
  <td nowrap>Originating zip code :</td>
  <td align="right" nowrap>'.$origdata['zip'].', '.$origdata['state'].'</td>
 </tr>
 <tr>
  <td nowrap>Destination zip code :</td>
  <td align="right" nowrap>'.$destdata['zip'].', '.$destdata['state'].'</td>
 </tr>
 <tr>
  <td nowrap>Distance :</td>
  <td align="right" nowrap>'.number_format(distance($origdata['lat'], $origdata['long'], $destdata['lat'], $destdata['long'], "m"), 2).' Miles</td>
 </tr>
 <tr><td colspan="2"><hr></td></tr>
</table>';

}

function zipform() {
 return '<table border="0" width="350" cellspacing="0" cellpadding="0">
<form method="POST" action="distance.php">
 <tr>
  <td nowrap>Enter originating zip code :</td>
  <td align="right" nowrap><input type="text" name="origzip" size="20" value="'.$_POST['origzip'].'"></td>
 </tr>
 <tr>
  <td nowrap>Enter destination zip code :</td>
  <td align="right" nowrap><input type="text" name="destzip" size="20" value="'.$_POST['destzip'].'"></td>
 </tr>
 <tr>
  <td colspan="2" align="right" nowrap><input type="submit" value="Submit" name="B1">
   <input type="reset" value="Reset" name="B2"></td>
 </tr>
</form>
</table>';
}

echo page_header();
if ((!$origzip) || (!$destzip)) {
 echo zipform();
} else if (($origzip) && ($destzip)) {
 echo diststuff($_POST['origzip'], $_POST['destzip']);
 echo zipform();
}
echo page_footer();

?>
  1. <?php
  2. $dbhost = 'localhost';
  3. $dbname = 'database_name';
  4. $dbuname = 'account_name';
  5. $dbupass = 'account_password';
  6. $connection = mysql_connect("$dbhost","$dbuname","$dbupass")
  7.     or die ("Couldn't connect to server.");
  8.     
  9. $db = mysql_select_db("$dbname", $connection)
  10.     or die("Couldn't select database.");
  11. // I did not write this function - I found it on the web somewhere.
  12. function distance($lat1, $lon1, $lat2, $lon2, $unit) {
  13.  $theta = $lon1 - $lon2;
  14.  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
  15.  $dist = acos($dist);
  16.  $dist = rad2deg($dist);
  17.  $miles = $dist * 60 * 1.1515;
  18.  $unit = strtolower($unit);
  19.  if ($unit == "k") {
  20.   return ($miles * 1.609344);
  21.  } else if ($unit == "n") {
  22.   return ($miles * 0.8684);
  23.  } else {
  24.   return $miles;
  25.  }
  26. }
  27. function page_header() {
  28.  return '<html>
  29. <head>
  30. <title>Distance Calculator</title>
  31. <style>
  32. <!--
  33. input    { font-family: Verdana; font-size: 8pt; font-weight: bold }
  34. td      { font-family: Verdana; font-size: 8pt; font-weight: bold }
  35. -->
  36. </style>
  37. </head>
  38. <body>
  39. <center><table border="0" width="350" cellspacing="0" cellpadding="0">
  40. <tr><td align="center"><u>Axe\'s Distance Calculator</u><br><br></td></tr>
  41. <tr><td>';
  42. }
  43. function page_footer() {
  44.  return '</td></tr></table></center></body></html>';
  45. }
  46. function diststuff($zipo, $zipd) {
  47.  $origsql = 'select * from zipcodes where zip = "'.$zipo.'"';
  48.  $origresult = mysql_query($origsql);
  49.  $origdata = mysql_fetch_assoc($origresult);
  50.  $destsql = 'select * from zipcodes where zip = "'.$zipd.'"';
  51.  $destresult = mysql_query($destsql);
  52.  $destdata = mysql_fetch_assoc($destresult);
  53.  return '<table border="0" width="350" cellspacing="0" cellpadding="0">
  54.  <tr>
  55.   <td nowrap>Originating zip code :</td>
  56.   <td align="right" nowrap>'.$origdata['zip'].', '.$origdata['state'].'</td>
  57.  </tr>
  58.  <tr>
  59.   <td nowrap>Destination zip code :</td>
  60.   <td align="right" nowrap>'.$destdata['zip'].', '.$destdata['state'].'</td>
  61.  </tr>
  62.  <tr>
  63.   <td nowrap>Distance :</td>
  64.   <td align="right" nowrap>'.number_format(distance($origdata['lat'], $origdata['long'], $destdata['lat'], $destdata['long'], "m"), 2).' Miles</td>
  65.  </tr>
  66.  <tr><td colspan="2"><hr></td></tr>
  67. </table>';
  68. }
  69. function zipform() {
  70.  return '<table border="0" width="350" cellspacing="0" cellpadding="0">
  71. <form method="POST" action="distance.php">
  72.  <tr>
  73.   <td nowrap>Enter originating zip code :</td>
  74.   <td align="right" nowrap><input type="text" name="origzip" size="20" value="'.$_POST['origzip'].'"></td>
  75.  </tr>
  76.  <tr>
  77.   <td nowrap>Enter destination zip code :</td>
  78.   <td align="right" nowrap><input type="text" name="destzip" size="20" value="'.$_POST['destzip'].'"></td>
  79.  </tr>
  80.  <tr>
  81.   <td colspan="2" align="right" nowrap><input type="submit" value="Submit" name="B1">
  82.    <input type="reset" value="Reset" name="B2"></td>
  83.  </tr>
  84. </form>
  85. </table>';
  86. }
  87. echo page_header();
  88. if ((!$origzip) || (!$destzip)) {
  89.  echo zipform();
  90. } else if (($origzip) && ($destzip)) {
  91.  echo diststuff($_POST['origzip'], $_POST['destzip']);
  92.  echo zipform();
  93. }
  94. echo page_footer();
  95. ?>


So, there's a bunch of useless prettying-up, heh. But I wanted it to produce fairly neat n' tidy output :)

Here's the SQL code to create the database table (Yeah, generated by phpMyadmin, heh)...

Code: [ Select ]
CREATE TABLE `zipcodes` (
 `state` char(2) NOT NULL default '',
 `zip` varchar(5) NOT NULL default '',
 `lat` varchar(15) NOT NULL default '',
 `long` varchar(15) NOT NULL default '',
 KEY `zip` (`zip`)
) TYPE=MyISAM;
  1. CREATE TABLE `zipcodes` (
  2.  `state` char(2) NOT NULL default '',
  3.  `zip` varchar(5) NOT NULL default '',
  4.  `lat` varchar(15) NOT NULL default '',
  5.  `long` varchar(15) NOT NULL default '',
  6.  KEY `zip` (`zip`)
  7. ) TYPE=MyISAM;


And here is all the zip code data in a CSV that you can import right into the database via phpMyadmin, or cmmand line if you know how.

The CSV was created from the data in this text file. That file also contains a bunch of other neat bits of info (population, houses, land & water areas in sq meters & sq miles (in case you wanna make your own CSV & tables and add those bits to this script).

That file, along with a bunch of others is available here..

That's basically it, just setup the database, modify the variables for database hostname, database name, database username & password, upload the script, and away you go. :)
  • carlosrapa
  • Newbie
  • Newbie
  • carlosrapa
  • Posts: 11
  • Loc: Fairview Heights, IL. (St. Louis Area)

Post 3+ Months Ago

thanks a bunch everyone... i appreciate the help... i think i am getting the just of it... but i may be back for assistance... soon..

Thanks,
Carlos
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

No problem Carlos,

Now, that script above just gives you the distance between A & B...

That won't allow you to search for all locations in a given radius (like "Find all stores within 50 miles of my house"), but it can be modified to work that way..

An obvious solution is to query your database for all store locations. Work out the distance between you and every one of them, then just display the ones that fall within X miles distance.

I'm sure there are more mathematical equations that can help in this process though (like working out the min & max latitude & longitude, and only querying those). You'd still have to query the main zip code database to find out those lats & longs, but if you've got 500 stores in the database, it can help speed up the loops if it knows not to run the loop for 95% of those stores.
  • carlosrapa
  • Newbie
  • Newbie
  • carlosrapa
  • Posts: 11
  • Loc: Fairview Heights, IL. (St. Louis Area)

Post 3+ Months Ago

Yep Axe.... thats pretty much exactly what i want to do... I am just going to take the min/max longitude/latitudes for the location... calculated by miles versue Lat./Log. Degrees... then compare them to the distributors...

Los
  • nfuids
  • Born
  • Born
  • nfuids
  • Posts: 2

Post 3+ Months Ago

Hi there,

I found all your suggestion very interesting, specially the one with the code.

Would be nice if you would share that math formula that would calculate the min/max lon/lat ..


I have a few 100k of team to search for, I'm sure it would help a LOT to use that.

Thank you very much in advance,
NfuidS
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

When I figure it out myself, I'll be sure to post it, lol.

Other projects, and Real-Life© has put this on the back-burner for a while, but it is still on my todo list :)
  • nfuids
  • Born
  • Born
  • nfuids
  • Posts: 2

Post 3+ Months Ago

Ok :)

But maybe if you could tell me the basis, I could work it out.

let`s say I have many building around the US.

how would I ask my system to give me the list of building that are in a 50 miles radius?

I would supply the zip code of where I am.

then what ? I need to find all zip code that are in a 50 miles radius and then do a select * from table where zipcode IN('zip1', 'zip2', 'zip3') ???

the longitude & latitude would be used to find other zip code that are in a 50 miles radius with some sort of calculation ??

Thanks :-)
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

I have absolutely no idea right now, lol...

The most obvious solution, is just get the distance between customer & each individual store location.

As you do each one, if it's under 50 miles, add it to an array. If it's over 50 miles, ignore it.

Then output the contents of your array. Of course, if you have 10,000 stores, and you've got half a dozen people every minute looking up a storelocation, this can play hell on your server rather quickly.

Basically, my original solution was going to be similar to this, but on a more limited range.

You work out the X & Y points 50 miles (using your example distance) north, south, east & west of the customer's zip code.

Then, you tell it to list all stores that fall within that square. Now you've drastically decreased your search of stores from 10,000 to maybe 75. Then, you compare each one of those to get actual linear distance and ignore those over 50 miles, add those under 50 to the array, and print out the array.

But, I haven't looked how to figure out the X±50 and Y±50 co-ordinates yet, so yuo'll have to hit google for that one :)

See it's probably gonna be easier on the search to select a close grouped bunch of stores, then compare those distances, than it is to search every zip code to see what's within 50m, and then see which stores fall in those zip codes.

If you look for all stores within a 50 mile radius, you might end up getting 1 query come back once you've figured out the 100x100 mile square section that encompasses the 50mile radius from your zip. if you work out all the zip codes that are in that 100x100 mile square, and then see which stores exist in those zip codes, you're talking a lot more demand on the system.
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

Wait, never mind, ignore me, cuz the stores don't contain the lat & long, just a zip... so yes, you'd have to query the database for all zip codes within a 50m radius, then look for all stores in those zip codes.

Sorry, it's 3am, brain's fried, lol :)
  • Axe
  • Genius
  • Genius
  • User avatar
  • Posts: 5739
  • Loc: Sub-level 28

Post 3+ Months Ago

Okies... here we go.. it's 10 years since I left school, so I had to do a lil hunting around for calcultion formulas, but I think I got it right...

1 Mile basically works out to a 0.015 diference in latitude or longitude...

http://mathforum.org/dr.math/faq/formul ... angle.html

There's the URL I used for the calculation, and here's an image I was working to try and sort of demonstrate how I arrived at a 0.015:1 scale (Btw, all numbers ended up being rounded off to 2dp, except for latitude & longitude coordinates & differences).

Image

So, in this example, if you lived in zip code 94950, and you wanted all stores in a 50m radius, you'd first work out the difference...

50 * 0.015 = 0.75

So, if your zip code coords are 38.054433 x -122.777625

Your box would be...

Top left : 37.304433 x -122.027625
Top right : 38.804433 x -122.027625
Bottom left : 37.304433 x -123.527625
Bottom right : 38.804433 x -123.527625

So, you would search for all zip codes that had...

Latitude > 37.304433 AND
Latitude < 38.804433 AND
Longitude > -123.527625 AND
Longitude < -122.027625

Which would give all zip codes in that 100x100 mile square.

Then you look for all stores in those zip codes. Do each individual distance calculation on each store found. Anything found under 50m, add it to the array, anything over 50m, forget it and move onto the next one. Once all results are in, spew out the contents of the array.

Bear in mind, it's 4am now, and my head was already hurtin' before I started this, so I could be a lil bit off, hehe.

There's also a probably much more optimized way of doing this (actually getting the list of stores with fewer SQL queries), but this was the first logical method that struck me.

As I said, it was kinda left on the backburner for a while, but now I've actually gone n' done the calculations, I think I might go ahead and work on that part of my script a bit after this weekend's over (working all weekend, heh).

Have a play anyways, and let me know how you get along with it.
  • jjarrett
  • Born
  • Born
  • jjarrett
  • Posts: 1

Post 3+ Months Ago

Looking for this info myself is how I found this forum and thread via Google. In exchange for the script for distance between two zips, allow me to share two scripts I have found for getting a list of stores/dealers with in x miles:

http://internet-programming.org/sample.txt

and

http://forums.devshed.com/t40665/s.html

hth
John
john@logontexas.com

Post Information

  • Total Posts in this topic: 17 posts
  • Users browsing this forum: No registered users and 157 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.