Page 1 of 2

distance formula

Posted: Sat Feb 18, 2006 4:51 am
by s.dot
Does anyone have (or know where to find) a script that can find all zip codes within a given distance (radius) of a certain zip code?

I have all the data in my database of canadian and us zip codes, latitude and longitudes, but the script i'm currently using is WAY off, often by miles (which is way inaccurate).

The script I'm currently using returns all zip codes in an array, which is perfect for what I need to do with the results.

[edit] the script i'm currently using is from planet-source-code.com

Posted: Sat Feb 18, 2006 8:49 am
by feyd
viewtopic.php?t=38949

When in doubt, search my posts. :)

Posted: Sat Feb 18, 2006 9:01 am
by hawleyjr
Here is a great class for doing this

viewtopic.php?t=34156&highlight=distance#178423

Posted: Sat Feb 18, 2006 5:22 pm
by s.dot
oddly enough the class hawleyjr posted is the class I'm using
unless I'm doing something wrong on my end the calculations are drastically off (sometimes up to 100 miles when searching within a 500 mil radius)

I find the post feyd suggests rather interesting. I think I shall follow that so I can use all those cool math functions and feel smart.

Posted: Sat Feb 18, 2006 5:40 pm
by s.dot
hmm, working on a 200,000+ record database of lats/longs it would be best to store search results in cookies? instead of querying the database on every load? perhaps providing an "update search" button..

i imagine searching all those records would take a good bit of time.

Posted: Sat Feb 18, 2006 7:14 pm
by feyd
I'd look into "views" :)

Posted: Sat Feb 18, 2006 8:29 pm
by s.dot
Actually I combined US+Canada records and I now have 863,862 records :oops:

A single search for a zip code averages .7 seconds.

I looked into "views" for MySQL, but it's showing version 5.x references. Are views available in 4.0.26? I'd like to read about views since I don't know what they are, but I'm afraid I'm unfamiliar with mysql.com manual. Looks like the PHP manual when i first started reading it. :lol:

Posted: Sat Feb 18, 2006 8:41 pm
by feyd
hmm... there's always temporary tables.

Posted: Sat Feb 18, 2006 8:50 pm
by s.dot
Well, I'm only using the database for 1 query. They won't be able to "search within the search results" or use the data in any other way other than to just look at it. So any records returned will be the final data. So using a temporary table wouldn't make sense, right? I mean... I don't believe anything could be faster than pulling the data from the original table?

Posted: Sat Feb 18, 2006 9:07 pm
by feyd
If you store a search id with the records in the temporary table, no further distance calculations need be run until that id runs out.

Posted: Sat Feb 18, 2006 9:17 pm
by s.dot
Oh, I get it.

Write a script that goes through each ZIP code and gathers all zip codes within X miles of zip code Y and store them in a separate table.

This way, when they perform the search, it simply selects from this table instead of scanning the entire 800,000+ record table.

Although if I do that, this new table would have 800,000*6 records (since there will be 6 different distances to search by).

Is selecting a record from a 4.8 million record table a lot quicker then searching for records in an 800,000 record table?

Posted: Sat Feb 18, 2006 9:37 pm
by feyd
the separate table's data is cleared periodicaly. The search remains in the results table for say one hour beyond the last use of them, then they are deleted.

Posted: Sat Feb 18, 2006 9:49 pm
by RobertGonzalez
Hotscripts.com has these scripts. If not, I'll look for mine. I have one that calculates distance based on great circle radius using the curvature of the earth's surface. Not entirely precise, but accurate enough. I have run it on mysql 3.X and it can scan all 50K records and perform a few crazy math functions and return a result set in about .9 seconds.

Give me a little bit to find my code. It's dinner time with the family so I'll post back in a couple of hours.

Posted: Sun Feb 19, 2006 3:56 am
by s.dot
OK, thank you.

Posted: Sun Feb 19, 2006 4:07 am
by Benjamin
scrotaye wrote:oddly enough the class hawleyjr posted is the class I'm using
unless I'm doing something wrong on my end the calculations are drastically off (sometimes up to 100 miles when searching within a 500 mil radius)

I find the post feyd suggests rather interesting. I think I shall follow that so I can use all those cool math functions and feel smart.
Probably some invalid entries in the database, ie latitude and longitude are switched.