views:

188

answers:

1

Hi everyone!

I'm trying to figure out what would be the most efficient (with respect to load time) to compare the distance between one zip code (which the user provides) and a whole bunch of other zip codes (there's approximately 200 zip codes right now, but its subject to increase over time). I don't need anything exact just in the ball park.

I downloaded a zip code csv file for all of the US, and I've got a function which produces the distance between two zip codes (in radians i believe.) I don't need to display the distance i just need to sort the 200 zip codes with the closest being the first of the results.

I uploaded the csv file to a mysql table. I was thinking I could cycle through all the 200 zip codes, and update a field for each one containing the distance. Then using ORDER BY, sort them from closest to furthest.

Does anyone know of a more efficient way to do this? This way would require updating the entire db of zip codes every time a search query is run. With only 200 zip codes its not a big deal now, but i imagine it will slow down the load time as the db builds. Thanks ahead for any advice, its much appreciated!

+2  A: 

In Javascript:

var R = 6371; // km
var dLat = (lat2-lat1).toRad();
var dLon = (lon2-lon1).toRad(); 
var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
        Math.cos(lat1.toRad()) * Math.cos(lat2.toRad()) * 
        Math.sin(dLon/2) * Math.sin(dLon/2); 
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
var d = R * c;

where d = distance between two points

This is the Haversine formula.

stillstanding