[thelist] Zip/Postal Code Radius
Howard Cheng
howcheng at ix.netcom.com
Thu Dec 19 14:19:01 CST 2002
The Great Circle method isn't that hard. Here's the formula (this is in C#
but it can be easily ported to any other language):
private int GetDistance(float lat1, float lon1, float lat2, float lon2)
{
double distance = Math.Acos((Math.Sin(lat1) * Math.Sin(lat2)) +
(Math.Cos(lat1) * Math.Cos(lat2) * Math.Cos(lon2 - lon1)));
int idistance = (int) Math.Round(distance * 3963);
return idistance;
}
Note that longitudes and latitudes must be in radians.
To use this to grab everything with a certain radius straight from the
database, you can do something like (this was in SQL Server 2K so you can't
use aliases in the WHERE clause):
SELECT a.name, a.city, a.state,
ROUND(
(ACOS(
(SIN(c.latitude) * SIN(a.latitude)) +
(COS(c.latitude) * COS(a.latitude) *
COS(a.longitude - c.longitude))
)
)
* 3963,1
) AS distance
FROM Airports a, Cities c
WHERE (
ACOS(
(SIN(c.latitude) * SIN(a.latitude)) +
(COS(c.latitude) * COS(a.latitude) * COS(a.longitude - c.longitude))
)
) * 3963 <= [RADIUS IN MILES]
AND c.zip='[ZIP CODE]'
ORDER BY distance
This also assumes that you've stored the longitude and latitudes in radians.
HTH.
At 02:54 PM 12/19/2002 -0500, Joshua Olson wrote:
>I find myself doing this quite a bit. Most zip code databases include the
>latitude and longitude of the Post Office. If you know the latitude and
>longitude of the two points, then finding the distance can be done a couple
>of ways. The hardest (mathematically) is to find the Great Circle distance.
>While it is a more complex equation that the other method (I'll cover that
>next) it provides the best results. The technique I use is MUCH simpler,
>though.
::::::::::::::::::::::::::::::::::
Howard Cheng
http://www.howcheng.com/
howcheng at ix dot netcom dot com
AIM: bennyphoebe
ICQ: 47319315
More information about the thelist
mailing list