Technical Article

Haversine Calculation in User Defined Function

,

Uses the haversine formula to calculate distance between 2 long / lat points.  This can be used with a zip code / lat and long table that are available from the census bureau or post office.

The @R can be tweaked dependent on what gives you the best result (its the radius of the earth, fluctuates based on your location, its in miles in my function, change it to meters for metric calculations).  @DtoR is the number used in converting degrees to radians.  This takes lat and long as degrees.

Info on haversine: http://www.census.gov/cgi-bin/geo/gisfaq?Q5.1 or just search on google.

This could be used as follows:

select * from people p inner join zipcodes z on z.zipcode = p.zipcode where dbo.udf_haversine(z.lat, z.long, @inputLat, @inputLong) < 50

(People within 50 miles of a specific lat / long point)

create function dbo.udf_Haversine(@lat1 float, @long1 float, @lat2 float, @long2 float) returns float begin
declare @dlon float, @dlat float, @rlat1 float, @rlat2 float, @rlong1 float, @rlong2 float, @a float, @c float, @R float, @d float, @DtoR float

select @DtoR = 0.017453293
select @R = 3937 --3976

select 
@rlat1 = @lat1 * @DtoR,
@rlong1 = @long1 * @DtoR,
@rlat2 = @lat2 * @DtoR,
@rlong2 = @long2 * @DtoR

select 
@dlon = @rlong1 - @rlong2,
@dlat = @rlat1 - @rlat2

select @a = power(sin(@dlat/2), 2) + cos(@rlat1) * cos(@rlat2) * power(sin(@dlon/2), 2)
select @c = 2 * atn2(sqrt(@a), sqrt(1-@a))
select @d = @R * @c

return @d 
end

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

A Normalization Primer

For most DBAs, normalization is an understood concept, a bread and butter bit of knowledge. However, it is not at all unusual to review a database design by a development group for an OLTP (OnLine Transaction Processing) environment and find that the schema chosen is anything but properly normalized. This article by Brian Kelley will give you the core knowledge to data model.

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

5 (3)

You rated this post out of 5. Change rating

2003-01-13

18,614 reads