SQL Query to sort by distance from a zip code
File this into the cateogry of useless information that might come in handy some day. What is needed is a table with the latitude and longitude of every zip code. This information can be bought from numerous companies (google is your friend), but rumor has it that the census bureau, and/or the US Postal Service has this information for free, albeit a bit dated.
So once you have those lat/lon coordinates, and a users zip code, you can sort your results by distance from the users zip code. This is typically put into a stored procedure which is passed the “@z” variable that is the users zip. This was done on a MS SQL Server so I am not sure if any is specific to their procedural language.
The zip_posit table has the lat/lon for each zip code and the “dist” variable is the computed distance of each zip code from the users entered zip code. The “yourTable” table contains the detail records that you want to show ordered by their distance from the entered zip code. In my case it was a list of addresses for businesses and we were showing the closest businesses to the zip code entered by the user.
select @lat1 = lat FROM zip_posit WHERE zip5=@z
select @lon1 = lon FROM zip_posit WHERE zip5=@z
SELECT * FROM yourTable As z
INNER JOIN
(SELECT
SQRT((69.1 * (lat - ' + @lat1 + ')) *
(69.1 * (lat - ' + @lat1 + ')) + (69.1 *
(lon - ' + @lon1 + ') * COS(' + @lat1 + '/ 57.3)) *
(69.1 * (lon - ' + @lon1 + ') * COS(' + @lat1 + ' / 57.3))) As dist, zip5
FROM zip_Posit) As locat
ON z.zip=locat.zip5
ORDER BY dist
I had to strip out some specific information but I think it will still work.