Russell, thanks for the tip. That worked fine.
Roberto, I took a look at earthdistance and did some experimenting.... Thanks for pointing this out. It actually helped me catch an error in my tables where I swapped lat and long.
The USPS gives little information about how much area zipcodes can represent. The question here is if I need to use earthdistance (which uses a lot of float multiplication and division as well as 5 sin and cos and one acos operation for each measurement) or whether some bad assumptions about our flat planet would suffice and let me use point_distance which should require no trig and probably takes two mults and one square root per measurement.
Googling for a table of State sizes in square miles, and dividing that by the count(*) of zips in that state revealed that except for Alaska, the average size of a zip code is 100 sq. miles with a max of 492 sq miles and a std. deviation of 113 sq miles.
Alaska has an avg zip code size of 2120 sq. miles.
Also, if you examine the max and min latitudes of the zipcodes and you haphazardly guess that Washington or Maine have the highest in the continental US, then it turns out that the US is bounded by about 24 degrees in Florida and about 50 degrees somewhere in Washington.
In Washington, geodistance shows that one degree of latitude is about 44 miles. In Florida, it's 60 miles, and at the top of Alaska, it's only 26 miles.
So in the lower 48, the average zip code area is 10 miles on a side and one degree of latitude covers 44 miles at the smallest. Zip codes within the first std. deviation (is it right that that would be 68% of all zip codes?) are root(100 + 113) on a side (assuming square zip code shapes) and zip codes in the first three std. deviations (99%?) are root (100 + 113 + 113 + 113) = 21 miles.
The world IS flat! As far as this application is concerned, it's safe to ignore geo_distance and just use point_distance, and, in the optimization above, a number like .5 is probably conservative.
And Alaska (a state I really love and I encourage you to visit) Well as far as this app goes, if you're not within 22 miles of the center of a zip code, well you probably still are interested in the services in that zip code.
So I'll have to be concerned about that, uh, someday. First thought at that solution, is that if the first query returns no rows, then check to see if you're in the Alaskan zone and rerun it without the the delta_x optimization.
