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

Share

Share

Rate