Zip Code Script with Longitude and Latitude

[phence]

I have converted an Access Database Of Zip Codes, City, County, State Name, Longitude, Latitude, Time Zone, Area Code and so on... to a MySQL database.

Now the tough part, applying it to my site.

One part of the site I am working on is a classifieds listing application.

---

Problem :

(1). One Search Option is: a user types in a zip code and, an X amount mile radius to find all listings in that specific area.

As of now, I am working with two tables that apply to this case, One Table for the Listings data,
and a
Second Table which holds the Zip Codes, longitude, latitude and so on...

How should i go about coding the PHP to run the zip code/radius listing search?

What are your recommendations?

All Input is appreciated!

[Weedpacket]

I know PostgreSQL has field types for geometric objects...

Well, you can get the lat/lon of a given ZIP - so basically you need to get MySQL to calculate the distance between two points given their coordinates, then SELECT stuff WHERE that <= searchreadius.

Now if latitude and longitude are stored in degrees, the calculation would be (and although I'm writing this in PHP it's because I don't know enough of MySQL's maths functions to write it properly):

$lat1 = $lat1*3.1415/180;
$lon1 = $lon1*3.1415/180;
$lat2 = $lat2*3.1415/180;
$lon2 = $lon1*3.1415/180;
$delta_lat = $lat2 - $lat1;
$delta_lon = $lon2 - $lon1;
$sinlat=sin($delta_lat/2.0);
$sinlon=sin($delta_lon/2.0);
$temp = $sinlat*$sinlat + cos($lat1) * cos($lat2) * $sinlon*$sinlon;
$earthRadius = 2*6370; // in km.
$distance = $earthRadius * atan2(sqrt($temp),sqrt(1-$temp));

where $lat1/$lon1 would be the lat/lon of the point at the centre of the search, while $lat2/$long2 are the fields being searched on.

The alternative to writing that in MySQL would be to extract the whole zip/lat/lon table into PHP and use the above code to search for matching Zips there.

[Weedpacket]

Okay, how about this?

The distance between two zip codes tends not to change, right?

Precompute the distances as per the above code and build a table:

zipdistances:
 zip1 | zip2 | distance
------+------+----------

For each pair of zip codes zip1, zip2.

"SELECT zip2 FROM zipdistances where zip1=$zip AND distance<=$radius".

Nicer?

[phence]

okay, makes sence... i'll give it a try... may take me a week or so to test it but thank-you very much!!!

[MrAlaska]

Setting up tables might otimize some applications, but there is another way to approach it also.

First you make a query to find the latitude north and south of the distance you are trying to isolate, and the longitude east and west, then pass those variables into your next sql statement. You could skip this query do it all in one query, but it would be making redundant calculations, so the extra query pays for itself.

When you make your sql statement to return the results, you include in your WHERE clause the lat and lon must be within the range of the variables you passed in from the first sql query. The excess calculations are then limited to only those needed to trim out the 'corners'.

I hope I explained that right. It does work and it is very fast, but you need to use MySql to compute the haversine or may find yourself somewhat bogged down.

This is the SQL statement I use to get the distance between two zip codes. This query is for only selecting between two zipcodes only. The code I used to do the radius calculations is on another page, this happened to be on the page I was working on when I decided to take a break l However, the haversine formulae works splendidly, and this query is the one I built all my distance functions from. (or in some cases trimmed from)

It accepts a lat and lon variable passed in from PHP ($l1 and $o1) and compares it to zip codes selected from the database for the member whos ID matches the variable passed in as $locate:

$sql="select
(7912 atan2(sqrt(pow((sin(((radians($l1))-(radians(z.lat)))/2)),2)+ cos((radians(z.lat))) cos((radians($l1))) pow((sin(((radians($o1))-(radians(z.lon)))/2)),2)), sqrt(1-(pow((sin(((radians($l1))-(radians(z.lat)))/2)),2)+ cos((radians(z.lat))) cos((radians($l1))) * pow((sin(((radians($o1))-(radians(z.lon)))/2)),2)))))
as distance
from zipcode_all z, member m
where $locate=m.member_id && m.zipcode=z.zipcode";

[MrAlaska]

Originally posted by Weedpacket
Okay, how about this?
The distance between two zip codes tends not to change, right?

Actually, the distance changes quite OFTEN, believe it or not. The post office is always moving zip codes around. The challenge is finding somebody who has the resources to keep up with the changes. The post office does not concern themselves with lat and lon for each zip code, they work in zones.

So, your logic works, but not for the reason you thought.

BTW: The method of storing distance ranges in seperate tables is the one used by Zipdy, who offers the tables as downloads or at least they did when I first tackled zipcodes (a couple months ago)

http://www.cryptnet.net/fsp/zipdy/

[Weedpacket]

Originally posted by MrAlaska
Actually, the distance changes quite OFTEN, believe it or not. The post office is always moving zip codes around.

And probably keeps losing track of mail as a result - not very useful if they keep shifting, are they?

Oh, I am glad I live in a place where post codes are optional - most people here probably don't realise their full address has a post code.

Re: your earlier post; embedding the distance calculation was my first idea, but I didn't know enough MySQL to figure the exact functionality. I did figure that it would be a very expensive function to calculate (and it looks it, too), and I kinda balked at the idea of doing it over and over for query after query. (In your SQL, I'd add a clause "AND distance<$x" to find "all zipcodes within $x").

[phence]

hey guys,

Thanks for all your input, i am going to be attacking this zip application this week. I'll probally have problems so expect a few question through the week!

Hopefully with your input, it will be a success!

Thank-you,

Sean

[MrAlaska]

Originally posted by Weedpacket
And probably keeps losing track of mail as a result - not very useful if they keep shifting, are they?

It seems to work for the post-office. When I first looked for lat-lon data I went to their site, of course. They had a lengthy explanation at the time explaining why they do not provide lat-lon info because of this and how keeping the zipcodes dynamic makes the mail so much more efficient. I can't remember the details now, or if they made sense or anything.

... and I kinda balked at the idea of doing it over and over for query after query. (In your SQL, I'd add a clause "AND distance<$x" to find "all zipcodes within $x").

The 'distance < $x' would be needed in the actual query. This example I posted to exemplify sql statement including the haversine. It actually is structured to only look for the distance between two lat-lon sets, one set being passed from PHP and the other set belonging for the member whos ID matches $locate.

There is a ton of calculations to make, of course. Which is why multiple tables, although de-normalizing the database, make more sense in applications such as those only looking within set distances like 5 miles, 10 miles, 15 miles, etc.

If you want to use dynamic distances entered by the user, you NEED to do it my way. If you want to return the actual distance from each result even from the tables, it then becomes an issue of normalization and an extra JOIN condition in your WHERE clause verses a few extra calculations. Trimming the n-s/e-w parameters in my first query brings the calculation down to a few hundred times instead of the 43000 calculations you would need to do otherwise.

[MrAlaska]

By the way, embedding the haversine formula in a SQL statement was NOT one I was able to pull off the top of my head lol It represents HOURS of hair pulling and cursing, even though I had already written a functional version in PHP. After I FINALLY got it working right is when the trouble started.

I was running several validation scripts to check the integrity of the data and was getting some odd results. Every once in a while, especially in the Florida area but also scattered in small pockets around the USA and a few individual outliers, the distance between next door neighbors was approximately equal to traveling around the radius of the Earth to get there.

It turned out to be because at first I was converting to radians with PHP and passing them to MySql. There is some sort of difference between PHP and MySql (can't remember the details) in how they handle long decimals that skews things from time to time. The calculations all need to be on one side or the other.

[rontgoff]

Weed what distance does your calculatro return feet km, miles?

[Weedpacket]

Originally posted by rontgoff
Weed what distance does your calculatro return feet km, miles?

Originally posted by Weedpacket

$earthRadius = 2*6370; // in km.

[/B]

[Weedpacket]

Originally posted by MrAlaska

It seems to work for the post-office. When I first looked for lat-lon data I went to their site, of course.

What I mean is, if the Post Office keeps changing zip codes, how are you supposed to know what zip code to use when you address an envelope - that they haven't gone and changed it again?

[bobthedog]

At last count.... my office had 3 post codes, only one of which is current (well listed as current on the Post Office web site anyhow).

Depending which companies you phone up and supply the address to depends which post code their system recognises (oh the wonders of having old postal address data)

🙂

[rontgoff]

Weed I used your formula which is much like those I found searching the internet. But It is off compared to zipfind.com. Any info on this would be helpful.

[PyroX]

I would be extremely interested helping you.

If you could get me a copy of your mysql tables, I would help you as much as possible.

My gain would be that data, I have been looking for a source 😉

[MrAlaska]

Originally posted by PyroX
My gain would be that data, I have been looking for a source 😉

If you have no data at all, you can get started with the data from here:
http://ftp.census.gov/geo/www/gazetteer/places.html

If it is a source of clean and current data you seek, good luck! I wouldn't mind having such a copy myself.

[PyroX]

A function I found that uses 1999 data from here:
http://ftp.census.gov/geo/www/gazetteer/places.html
( THANK YOU MrAlaska! )

function LatLonToMiles($lat1, $lon1, $lat2, $lon2){

// This is the formula for calculating distances
// from latitude and longitude.
$dist   = acos(sin(deg2rad($lat1))*sin(deg2rad($lat2)) + cos deg2rad($lat1))* cos(deg2rad($lat2))* cos(deg2rad($lon1 - $lon2)));

$dist   = rad2deg($dist);
$miles  = (float) $dist * 69;

// To get kilometers, multiply miles by 1.61
$km     = (float) $miles * 1.61;

// This is all displaying functionality
$display  = sprintf("%0.2f",$miles) . " miles" ;
$display .= " (" . sprintf("%0.2f",$km) . " kilometers)" ;

return $display ;

}

I checked this aainst a couple sites, seems to be great.

That was just too easy, now everyone under the sun can do this.

Now I am looking for us DOT data. I want to know how to calculate driving distances next.

[rontgoff]

I could email you a csv of the info.

[tgotchi]

For complete solution, please visit the developers section of this website.

http://www.zipcodeworld.com