Hello all,

I'm rather new here so very sorry if there are posts on similar types of searches. I'm working on a project for a delivery company what wants to be able to search for deliveries with a 500 mile radius of point A, that end up with in a 500 mile radius of point B.

I have a simple radius search working, a user can type in a zip code and get all the other zip codes with in a radius of up to 500 miles (the max required distance).

(The zip code table is listed below)*

I am using a php function to calculate locations with in the radius specified by the user (start and end point.)

Basically it's a function that returns all the zip codes with in the radius specified. And example of the search is here:

http://www.zensomedia.com/truckboard/

So what I end up with is 2 arrays of zip codes. However at a 500 mile radius sometimes there can be 16,000 zip codes. So I would have 16,000 start point zips and 16,000 end point zip.

In a second table I have all the loads that need to be delivered. For the sake of argument lets say it has

LoadID
StartZip
EndZip

What I was going to do is in php select all the loads where “StartZIP” matches my starting zip codes array. So I know all my start points match, then I would take my possible end points zip code array and see if there are any matches on that array.

I’m not sure if this is the best way to go about doing this, and how the performance will be. Can any one give me any suggestions on how to improve this?

Thank you,
Steven






************************************************** **
CREATE TABLE `zip_code` (
`id` int(11) unsigned NOT NULL auto_increment,
`zip_code` varchar(5) collate utf8_bin NOT NULL,
`city` varchar(50) collate utf8_bin default NULL,
`county` varchar(50) collate utf8_bin default NULL,
`state_name` varchar(50) collate utf8_bin default NULL,
`state_prefix` varchar(2) collate utf8_bin default NULL,
`area_code` varchar(3) collate utf8_bin default NULL,
`time_zone` varchar(50) collate utf8_bin default NULL,
`lat` float NOT NULL,
`lon` float NOT NULL,
PRIMARY KEY (`id`),
KEY `zip_code` (`zip_code`)
)
************************************************** **

What do you do with the zip codes that have more than one city, state, or area code in them? These are common, there are many thousand of them.

How do you handle the zip codes that are more than 500 miles from end-to-end? These are rare (only a few hundred), but these codes are the bane of any project like you are working on.

The simple solution is to create a table that stores pairs of zip codes, with a "wild hare" flag to indicate that special processing is required, and the computed distances between the centers of those zip codes. This trades off some disk in order to get decent query speed... You usually want answers while you are young enough to appreciate them!

-PatP

Well in all honesty I'm relying in the php function I found for the list of zips. The actual page is for truckers who are looking for loads to hall. There are other ways to look for loads (like state to state), this search just helps them get a good general idea of whats in a range of where they are.

One idea I had is to take my arrays [start_array] & [end_array], and drop them into a temp table if that was possible. That way I could use nested queries, I thought that maybe this would be faster then running through the arrays in php.

I'm just sure on how I should attack the problem for best performance.

Steve

I just stumbled over a different problem that made me think of this post.

How many miles (driving distance) is it from Midway, AR to Midway, AR ?

-PatP

heh

how many different cities called Toronto are there in the U S of A?

are any two of them in the same state?

Actually I think I'm going to do the search slightly different. You can tell what state a zip code is in by the first three digits. So for my search I can all ready produce all the zip codes that are with in X miles from a zip code. I'll take that list of 5 digit numbers and trim so I only have the first 3. I can either use an array or put those numbers in a temp table, use a SELECT DISTINCT statement that will return me all the 'state' zip codes. I can then do a lookup on those ten or so state codes that come back and pull all the loads to or from those states.

If basically goes out and finds all the states with in a 500 miles radius of the stating point, and gets the loads for those states. This way lets say a load is 515 miles away the trucker who is looking for a load to deliver wont miss that load.

Also if I write it this way I can tell the user "your loads extend into these states" and I can ask them if there are any states that they don't want to go into. If they are starting out in Michigan for example, they may not want to drive all the way up to New York.

I think this is a pretty solid way of preforming the search for what it's being used for.

Smack78