Hey,

I havelayed out my mysql database in the following format.

city, state, zip.

what I'm looking to do is... Find a way to show surrounding citys by zip code.

basically one person selects a zip code... and not only does it show results from that zip code... but from the surrounding zip codes as well.

any ideas?

    You'd need to acquire a zip code database that includes geographic data (latitude/longitude) for each zip code, and a script that could search the DB for zip codes within a specified distance of a given code. I'm sure some searching could find some commercial solutions. You might want to check sites such as sourceforge.org or hotscripts.com to see if there are any open-source solutions.

      Hey,

      I now have my sql server full of data for the state of PA.

      I have a form that submits to the php code. the values that are submitted are the zipe code and the distance of which they want to search.

      my goal here is send a query with the zipcode they entered to get the that zip codes lat. and lon. (thats done already). Then take that data.. put it through some kind of math formula and come out with max and min. lon. and lat for the distance they selected.

      my question is... what is the math formula?

      can you point me in the right direction?

        The maths formula is a little bit tricky. It really depends whether you want to assume the earth is a sphere or not.

        If so, it's not a really big problem, otherwise it gets really hard.

        Just find the great circle distance by projecting two vectors from the centre of the earth to the two points on the surface (again assuming that it's a sphere), then find the angle between them using a dot product and inverse cosine.

        Once you have the angle, that is the angle between the points, so just multiply by the diameter * pi and then you're done.

        Mark

          ummm. you lost me.

          I have this data in my mysql table

          zip   	 city   	 state   latitude   	 longitude  timezone  dst
17101 	Harrisburg 	PA 	40.261839 	-76.882790 	-5 	1

          now the users enter the following informaiton in a form field.

          Zipe code: 17110 <----the area they want to search.

          Miles: 25 <--- the amout of miles around the zip code they want to search.

          my script then looks to the data base for zip code 17110 and gets its latitude and longitude.

          i then need to do some kind of match forumla to figure out the difference in miles in both latitude and longitude. The rest will be kind of simple (the query to go back to the database and do a => and <= to find all citys within that range).

          What i need help with is my forumla.

          Thanks.

            i solved it..

            here is the code.

            require('..//new_site/pages/mysql_users.php');

$zip=$_POST['zip'];
$miles=$_POST['miles'];

$zip_string=("SELECT latitude,longitude,state FROM zipcodes where zip='$zip'");
$zip_query=mysql_query($zip_string);
if(!$zip_query)
	{//start of if statment (throw error)
	$error='<center>There was a mysql error in string 1<br />'.mysql_error().'</center>';
	}//end of if statment (throw error)

if(!isset($error))
	{//start of if statment (get results and start new sting)
	$zip_result=mysql_fetch_assoc($zip_query);



	//THIS VARIABLE SETS THE RADIUS IN MILES
	$iRadius = $miles;

	$istartlat=$zip_result['latitude'];
	$istartlong=$zip_result['longitude'];

	$LatRange = $iRadius / ((6076 / 5280) * 60);
	$LongRange = $iRadius / (((cos($iStartLat * 3.141592653589 / 180)) * 6076.) / 5280. * 60);

	$LowLatitude = $istartlat - $LatRange;
	$HighLatitude = $istartlat + $LatRange;
	$LowLongitude = $istartlong - $LongRange;
	$HighLongitude = $istartlong + $LongRange;

	$sql=("SELECT city
	FROM zipcodes
	WHERE Latitude <= '$HighLatitude$'
	AND Latitude >= '$LowLatitude'
	AND Longitude >= '$LowLongitude'
	AND Longitude <= '$HighLongitude'");


	$sql_query=mysql_query($sql, $users);
              3 months later

              In this snippet of the code from above:

              $LatRange = $iRadius / ((6076 / 5280) * 60); 
$LongRange = $iRadius / (((cos($iStartLat * 3.141592653589 / 180)) * 6076.) / 5280. * 60);

              Does any body know what the 6076, 5280, 60, or 180 represent? And why are there periods after 6076 and 5280 in $LongRange?

                2 months later

                Seems to me if you're specifying a search area based on four points, a max and min longitude and latitude, you're going to pull in results based on a square, not a circle, so you're not really getting a true radius, meaning you can get results that are further away than your maximum milage would suggest, no?

                  11 days later

                  How about a MySQL solution? I'd been struggling with this for the past week, on and off, and managed to get it to work this evening for a client project...

                  // Query to select all matching lat/long values within the given $radius
// $lat1 and $long1 are retrieved from an earlier database query in the script such as: 
//   SELECT latitude AS lat1, longitude AS long1 FROM $zip_table WHERE zipcode = $zip

$query = "SELECT *,".
"(((acos(sin(($lat1*$pi/180))*".
"sin((latitude*$pi/180))+cos(($lat1*$pi/180))*".
"cos((latitude*$pi/180))*".
"cos((($long1-longitude)*$pi/180))))*180/$pi)*60*1.423)".
" AS distance FROM $zip_table HAVING distance <= $radius ORDER BY distance ASC";

// Extract matching data past this point...

                  Hope this helps! It seems to work well so far.

                    Write a Reply...