CODEWITHSUNDEEP
phpmysqlzipdistance
Doug Molineux
Doug Molineux

Reputation: 12431

MySQL PHP zip code comparison specifically distance

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!

Upvotes: 0

Views: 826

Answers (1)

bcosca
bcosca

Reputation: 17555

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.

Upvotes: 2

Related Questions