PHP/MySQL zip code proximity query

Databases
1

So, I’m trying to get a zip code proximity query that will be the most efficient way to select a number of users within a certain zip code (w/ PHP/MySQL). Here’s my setup:

  • Each user in the users table has a zipcode assigned to him/her.
  • I have a zipcodes table which contains columns zip code, latitude and longitude
  • The search/browse lists users that fit a certain criteria.
  • The form used passes variables like whether the users listed should be male or female, age range, etc., and two variables pertaining to zip code location: miles from the zip code and the zip code itself.
  • The function the form passes those values to returns a result set of users which fit the criteria.

So far, I start with this query:

SELECT user_id, fname FROM users, zipcodes WHERE

Then I add on whatever conditions the user chose on a case by case basis:

AND country_id = $cid

AND name LIKE '%$name%'

Etc. Now, for the zip code, here’s the pseudo-query I constructed:

 AND (degrees(acos(
	   (sin(  zipcodes.latitude(user's zip)     / 57.29577951)
	  * sin(  zipcodes.latitude($requestedzip) / 57.29577951)
	  + cos(  zipcodes.latitude(user's zip)     / 57.29577951)
	  * cos(  zipcodes.latitude($requestedzip) / 57.29577951)
	  * cos( (zipcodes.longitude(userszip) - zipcodes.longitude($requestedzip) ) / 57.29577951))
	     ))) < $milesfromzip

As you can see, I want to only select users whose zipcode lat and long are within $milesfromzip distance of the $requestedzip. My question is, how do I resolve the zip codes? I would have to access the zip code database basically for each user (ideally, having resolved the $requestedzip’s lat and long). Is this the most efficient way to do this? Any help or suggestions is strongly appreciated. Thanks very much!

-Sam

2

So here’s what I have it whittled down to (in a case where I alerady have the longitude and latitude of the target zip code (47.604718, -122.335230) and miles from zip (50)):

SELECT user_id
FROM users, zip_codes
WHERE users.zip = zip_codes.zip
AND degrees(acos(
sin( radians(zip_codes.latitude) )
* sin( radians(47.604718))
+ cos( radians(zip_codes.latitude))
* cos( radians(47.604718))
* cos( radians(zip_codes.longitude - -122.335230) )
) ) < 50

I really thought this would do it, but it returns every user, not those within a 50 mile radius of the target zip code. Any ideas as to why this doesn’t work? Or perhaps a more efficient query? Thanks in advance.

3

Got it! I forgot a part of the formula: * 69.09. Final code:

SELECT user_id
FROM users, zip_codes
WHERE users.zip = zip_codes.zip
AND degrees(acos(
sin( radians(zip_codes.latitude) )
* sin( radians(47.604718))
+ cos( radians(zip_codes.latitude))
* cos( radians(47.604718))
* cos( radians(zip_codes.longitude - -122.335230) )
) ) * 69.09 < 50

And then maybe a subselect query to get the long and lat of $target_zip? Again, suggestions on efficiency much appreciated. Thanks!

4

Thanks for the details. I know this is mathematically accurate, but how would it perform searching a table of a million rows? We decided to implement an approximation using scaled integer lat and long values and the between clause. We also hard-coded miles/degree ignoring the curvature of the Earth.

5

Just wondering if anyone knows where I can get the Canadian zip codes in database form? And which I doubt is available without having to remortgage - the UK postal codes. Looking for longitude & latitude more than addresses

Iain

6

Could you give information on how to convert to scaled integer, as well as how you coded miles/degree? That would be very helpful. Thanks!

7

I personally would pull out the latitudes and longitudes and perform the calculation in PHP.

The formula that is being used calculates with the curve of the Earth factored in. (to do this it needs to be in Radians, using the arc cosine function).

8

I’m new to PHP and this forum but I have the “same” application. That is, finding records in a database representing sites w/in a specified distance of the user.
I can get the database items’ latitudes and longitude (A) from their zip code and a table of zip code L & L’s , or (B) from Microsoft’s Terra Server, or (C) I believe that Google now has an API that will provide it given the address information.
Is the above info in this thread still good or is there a preferred way of doing it?
Would having 50 tables (one for each state) which contained only the records which HAD to be w/in (say) 50 miles of that state’s border (e.g. contigous states) make any sense? (Only use table for state that the user was located in.)
TIA

11

I have been tryin to get Ceptor’s query to work but I just cant seem to get it to.
I have a table called phpbb_users and phpbb_zipcodes.

Here is the code I am trying to implement. I also have taken out the variables that will be passed from the form and replaced tehm with static for testing.


$zip_query = mysql_query ("SELECT latitude, longitude FROM phpbb_zipcodes WHERE zip = 92648");
while($coords = mysql_fetch_array($zip_query))
{
$lat = $coords["latitude"];
$long = $coords["longitude"];
}
$results = mysql_query ("SELECT username FROM phpbb_users, phpbb_zipcodes WHERE phpbb_zipcodes.zip = phpbb_users.user_zip AND degrees
	(acos(sin( radians(phpbb_zipcodes.latitude) )
	* sin( radians('" . $long . "';))
	+ cos( radians(phpbb_zipcodes.latitude))
	* cos( radians('" . $long . "';))
	* cos( radians(phpbb_zipcodes.longitude - '" . $lat . "';) ) )) * 69.09 < 5");
while($advisors = mysql_fetch_array($results))
{
$ads = $advisors["phpbb_users.username"];
}
echo $ads;
echo $lat;
echo $long;

The page this is on is here: http://www.equitythinktank.com/search_results.php

The line 17 error is my last WHILE statement.

Any help is most appriciated!!

Thank you,
Rob

12

I have the basics of the distance calculation working.
However, later, I upgraded MySQL and it won’t work. I’ll get back to you/all when MySQL is solved.
(Have you tried the formula in a SQL in something like SQLYog or another MySQL front-end?)
Bob

13

Which one is line 17? The formatting that the forum applies makes it hard to guess.

This is the code I created to do this function, it is implemented with some XMLHttpRequest manipulation:


<?php
$dbHost = "localhost";
$dbUser = "*********";
$dbPass = "*********";
$dbName = "**********";

mysql_connect($dbHost, $dbUser, $dbPass);
mysql_select_db($dbName);

$zip1 = $_GET['zip1'];
$zip2 = $_GET['zip2'];
//$selectZip1 = "SELECT latitude, longitude FROM zipcodes WHERE zip = $zip1";
//$selectZip2 = "SELECT latitude, longitude FROM zipcodes WHERE zip = $zip2";

$selectCoords = "SELECT latitude, longitude FROM zipcodes WHERE zip = $zip1 OR zip = $zip2";


$coordsRS = mysql_query($selectCoords);
$i = 1;
while($aCoords = mysql_fetch_array($coordsRS))
{
	$lat[$i] = $aCoords['latitude'];
	$long[$i] = $aCoords['longitude'];
	
$i++;
}
	


//$zip2RS = mysql_fetch_array($zip2RS);
//$lat2 = $zip2RS['latitude'];
//$long2 = $zip2RS['longitude'];

$distance = 3963.0 * (acos(sin($lat['1']/57.2958) * sin($lat['2']/57.2958) + cos($lat['1']/57.2958) * cos($lat['2']/57.2958) * cos($long['2']/57.2958 - $long['1']/57.2958)));
$distance = round($distance,2);
$return_value = '<?xml version="1.0" standalone="yes"?><distance><author>Stephan Segraves</author><locations><location1><zip>'.$zip1.'</zip><latitude>'.$lat[1].'</latitude><longitude>'.$long[1].'</longitude></location1><location2><zip>'.$zip2.'</zip><latitude>'.$lat[2].'</latitude><longitude>'.$long[2].'</longitude></location2></locations><miles>'.$distance.'</miles></distance>';
header('Content-Type:text/xml');
echo $return_value;

?>
14

Awesome Benjamin, that helps alot.

I actually have been replacing the variables with constants and dropping the query into phpmyAdmin and I get no results. I think it is something in my PHP code.

Thanks to you too Hartman! The only thing is that I am querying for results within a radius of a certain ZIP code, essentially a store locator.

I tried using Ceptor’s formula, but grabbing the variables “ZIP” and what will become the radius “5” “20” “50” “100” etc… and using them.

If you notice the first query, I am looking up the latitude and longitude of the ZIP. then taking those coordinates and i need to find all people that are whatever radius away.

When looking at the query, the “92648” is the ZIP that will be a variable (but for explainations sake I have made a constant) and the last part of teh second query (where it says < 5) I want the results within 5 miles of 92648.

I hope this helps. Thank you again for your help so far!!!

Rob

15

I’ll post it tomorrow. :slight_smile:

select id, city,state,3963 *
acos(
cos(radians(90-latitude ))
*cos(radians(90-34.200001))
+
sin(radians(90-latitude ))

  • sin(radians(90-34.200001))
    *cos(radians(longitude- -118.370003))
    )as dist

from zipcodes having dist < 100000 order by dist ;

The 3963 is for statute miles.
The 34.200001 and -118.370003 are the lat/long for the Burbank, CA airport
Note the double minus (-) as we are subtracting a “negative” longitude.
I checked it against a website with airmiles between airports and it was within 9 miles out of 2460.
The above was against my db of zipcodes (72,000) with id, city, state, latitude, longitude. You could also limit it more by distance and also by “LIMIT 20” or whatever.

Bob

16

Thanks again…now how do I get rid of my error I am experiencing in my WHILE statement??

17

This worked great…thank you guys for all of your help!!

18

select Members.Name, Members.Zip, zipcodes.id, zipcodes.city,zipcodes.state,3963 *
acos(
cos(radians(90-latitude ))
*cos(radians(90-34.200001))
+
sin(radians(90-latitude ))

  • sin(radians(90-34.200001))
    *cos(radians(longitude- -118.370003))
    )as dist
    from zipcodes, Members having dist < 10000 AND Members.Zip = zipcodes.zip

Why is this code not working? I know it is an error with my added code for pulling data from another table. Thanks in advance.

19

What are your table structures?
How will the sql find the latitude and longitude?
Have it work with one table with the latitudes and longitudes first and then add you joins.

20 
CREATE TABLE zip_codes
(zip varchar (16) unsigned primary key,
city varchar (30),
state varchar (30),
latitude decimal(10,6),
longitude decimal(10,6),
timezone tinyint(2),
dst tinyint(1),
country char (2)
);

Excuse my syntax it probably is not correct. But it should give you the idea.

22

This solution to the ZIP proximity question was posted some time ago, and the code works for me, but I am a little confused about the units. What are the units for distance? It seems unlikely that you would search for something 100000 miles away.

I am coming up with distances of like 6671.778364 for locations that are a few miles apart.

Thanks.

23

I don’t know what you mean by “the code works for me” when combined with your other statement.
A. The 3963 multiplier in the formula is what to use if you are interested in statute miles (as opposed to a different number for kilometers).
B. I ‘hard coded’ the 34.200001 and -118.370003 , the lat/long for the Burbank, CA airport, as I had it handy and knew what the mileages should be for surrounding areas. These numbers should be substituted with the latitude and longitude for your particular ‘from’ or source location.
C. I was testing various distances and just stuck in the 10000000 number to include anything in my database. I could have eliminated the ‘having’ clause for that test but I was lazy. the units for distance are statute miles.

Get back to me by PM if this doesn’t answer your question.
:slight_smile: