Sorting by Zip code

[pocobueno1388]

I have a table that stores the users zip code and I'm trying to display users by distance (nearest to furthest). I'm hoping there is a simple way to where I don't have to get a database with lat/long values...but when I think about it I doubt there is a formula that can measure the distance between two zip codes.

 

I would appreciate if someone could point me in the right direction. If I do need to get a database with lat/long values, it would be great if you could refer me to a good one.

 

Thanks!

 

 

 

 

[pocobueno1388]

Ok, so I figured out that I would need a database full of zip codes along with their latitude/longitude. I now have that,  but I still need to figure out how to setup the query to sort users by distance.

 

Here is the relevant table information tables:

 

TABLE users
zip

TABLE zipcodes
zipcode
latitude
longitude

 

So if a user is logged in, I want it to display the users nearest to THEIR zipcode. How would I setup this query??

 

Thanks!

 

 

[bhogg]

Depending on how dynamic the sorting and calculations will be, you might want to look into something like Sphinx to index the data and give you fast distance calculations/sorting, assuming you're able to install and run programs on your web server:

 

http://sphinxsearch.com/

 

You'll also need the lat/lon in radians.

[pocobueno1388]

Thanks bhogg, I'll probably get into that later. For now I'm just worried about the query.

[bhogg]

I stumbled across this presentation which should point you in the right direction:

 

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

 

It even covers geo distance searching with Sphinx when/if you want to make the switch