[thelist] Zip Code Distance From

Matt Warden mwarden at gmail.com
Wed Mar 28 12:45:44 CDT 2007


On 3/28/07, Michael Pack <michaelpack at wvdhhr.org> wrote:
> I've successfully used this with SQL Server 2000 and ASP. It was very
> easy to implement.
>
> http://www.Planet-Source-Code.com/vb/scripts/ShowCode.asp?txtCodeId=7584&lngWId=4
>

This actually uses a very good formula. Most estimate lat/long -> mile
conversions using a constant ratio, whereas the ratio is not constant
(1 lat = ~70 miles at the equator, decreasing as you move from the
equator).

Specifically, here is the SQL it is using:

SELECT name, phone, addr1, city, state, zip
FROM dealer
WHERE zip in (
      SELECT ZIP_CODE FROM zip "& _
      WHERE @radius > 3959 * ACOS(SIN(@lat/ 57.3) * SIN(LAT/ 57.3) +
      COS(@lat/ 57.3) * COS(LAT/ 57.3) * COS((LNG/ 57.3) - (@lng/ 57.3)))
)
ORDER BY name

-- 
Matt Warden
Cleveland, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.



More information about the thelist mailing list