distance formula

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

distance formula

Post 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
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

viewtopic.php?t=38949

When in doubt, search my posts. :)
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Here is a great class for doing this

viewtopic.php?t=34156&highlight=distance#178423
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

I'd look into "views" :)
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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:
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

hmm... there's always temporary tables.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post 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?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

OK, thank you.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post 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.
Post Reply