CREATE FUNCTION [dbo].[fnHaversineBox]
(
	@Lat DECIMAL(9, 6),
	@Lon DECIMAL(9, 6),
	@Distance DECIMAL(8, 3)
)
RETURNS @Box TABLE
	(
		minLat DECIMAL(9, 6),
		minLon DECIMAL(9, 6),
		maxLat DECIMAL(9, 6),
		maxLon DECIMAL(9, 6)
	)
AS
BEGIN
	DECLARE	@minLat DECIMAL(9, 6),
		@minLon DECIMAL(9, 6),
		@maxLat DECIMAL(9, 6),
		@maxLon DECIMAL(9, 6)

	SELECT	@minLat = ASIN(SIN(@Lat / 57.2957795130823) * COS(@Distance / 6371.0E) - COS(@Lat / 57.2957795130823) * SIN(@Distance / 6371.0E)),
		@maxLat = ASIN(SIN(@Lat / 57.2957795130823) * COS(@Distance / 6371.0E) + COS(@Lat / 57.2957795130823) * SIN(@Distance / 6371.0E)),
		@minLon = ATN2(COS(@Distance / 6371.0E) - SIN(@Lat / 57.2957795130823) * SIN(@minLat), - SIN(@Distance / 6371.0E) * COS(@Lat / 57.2957795130823)),
		@maxLon = ATN2(COS(@Distance / 6371.0E) - SIN(@Lat / 57.2957795130823) * SIN(@maxLat), + SIN(@Distance / 6371.0E) * COS(@Lat / 57.2957795130823))

	INSERT	@Box
	VALUES	(
			57.2957795130823 * @minLat,
			@Lon - 57.2957795130823 * @minLon + 90.0E,
			57.2957795130823 * @maxLat,
			@Lon - 57.2957795130823 * @maxLon + 90.0E
		)
	RETURN
END

declare @Lat float
declare @Lon float
declare @Kilometers float

set @Kilometers = 300E
set @Lat 	= 45E
set @Lon 	= 20E

select
	DistanceToMaxLon =
		dbo.F_GREAT_CIRCLE_DISTANCE( 
		@Lat,@Lon,@Lat,maxLon),
	DistanceToMinLon =
		dbo.F_GREAT_CIRCLE_DISTANCE( 
		@Lat,@Lon,@Lat,minLon)
from
	[dbo].[fnHaversineBox]( @Lat, @Lon, @Kilometers)

select
	DistanceToMaxLat =
		dbo.F_GREAT_CIRCLE_DISTANCE( 
		@Lat,@Lon,maxLat,@Lon),
	DistanceToMinLat =
		dbo.F_GREAT_CIRCLE_DISTANCE( 
		@Lat,@Lon,minLat,@Lon)
from
	[dbo].[fnHaversineBox]( @Lat, @Lon, @Kilometers)

DistanceToMaxLon                                      DistanceToMinLon                                      
----------------------------------------------------- ----------------------------------------------------- 
314.52503224103395                                    286.30174055507513

(1 row(s) affected)

DistanceToMaxLat                                      DistanceToMinLat                                      
----------------------------------------------------- ----------------------------------------------------- 
300.00302252760667                                    299.99868592546659

(1 row(s) affected)

declare @Lat 		float
declare @Lon 		float
declare @Kilometers	float
declare @KmDg		float
declare @LonCorrection	float
declare @MaxLon		float
declare @MinLon		float
declare @MaxLat		float
declare @MinLat		float

set @Kilometers = 200E
set @Lat 	= 45.00E
set @Lon 	= 20E

declare @degrees	float
set @degrees = @Kilometers/(dbo.F_GREAT_CIRCLE_DISTANCE(0E,0E,0E,90E)/90.000000000E)

set @LonCorrection = Cos(@Lat*(pi()/180.0E))

select
	@MaxLon	= @Lon+(@degrees/@LonCorrection) ,
	@MinLon	= @Lon-(@degrees/@LonCorrection) ,
	@MaxLat	= @Lat+@degrees ,
	@MinLat	= @Lat-@degrees

-- Test Values
select
	DistanceToMaxLon =
		dbo.F_GREAT_CIRCLE_DISTANCE( 
		@Lat,@Lon,@Lat,@MaxLon),
	DistanceToMinLon =
		dbo.F_GREAT_CIRCLE_DISTANCE( 
		@Lat,@Lon,@Lat,@MinLon)

select
	DistanceToMaxLat =
		dbo.F_GREAT_CIRCLE_DISTANCE( 
		@Lat,@Lon,@MaxLat,@Lon),
	DistanceToMinLat =
		dbo.F_GREAT_CIRCLE_DISTANCE( 
		@Lat,@Lon,@MinLat,@Lon)

DistanceToMaxLon                                      DistanceToMinLon                                      
----------------------------------------------------- ----------------------------------------------------- 
199.99178701586769                                    199.99178701586743

(1 row(s) affected)

DistanceToMaxLat                                      DistanceToMinLat                                      
----------------------------------------------------- ----------------------------------------------------- 
199.99999999999986                                    199.99999999999986

(1 row(s) affected)

declare @Lat 		float
declare @Lon 		float
declare @Kilometers	float
declare @degrees	float
declare @LonCorrection	float
declare @MaxLon		float
declare @MinLon		float
declare @MaxLat		float
declare @MinLat		float

set @Kilometers = 500E
set @Lat 	= 50E
set @Lon 	= -40E

set @degrees = @Kilometers/(dbo.F_GREAT_CIRCLE_DISTANCE(0E,0E,0E,90E)/90.000000000E)

set @LonCorrection = Cos((abs(@Lat)+.085)*(pi()/180.0E))

select
	@MaxLon	= @Lon+(@degrees/@LonCorrection),
	@MinLon	= @Lon-(@degrees/@LonCorrection) ,
	@MaxLat	= @Lat + @degrees+.00000001 ,
	@MinLat	= @Lat - @degrees-.00000001

-- Display Search limit results
select
	Lon	= convert(numeric(10,6),round(@Lon,6)) ,
	MaxLon	= convert(numeric(10,6),round(@MaxLon,6)) ,
	MinLon	= convert(numeric(10,6),round(@MinLon,6)) ,
	Lat	= convert(numeric(10,6),round(@Lat,6)) ,
	MaxLat	= convert(numeric(10,6),round(@MaxLat,6)) ,
	MinLat	= convert(numeric(10,6),round(@MinLat,6))	

-- Test Values
select
	DistanceToMaxLon =
		convert(numeric(12,6),round(
		dbo.F_GREAT_CIRCLE_DISTANCE(@Lat,@Lon,@Lat,@MaxLon),6)) ,
	DistanceToMinLon =
		convert(numeric(12,6),round(
		dbo.F_GREAT_CIRCLE_DISTANCE(@Lat,@Lon,@Lat,@MinLon),6)) ,
	DistanceToMaxLat =
		convert(numeric(12,6),round(
		dbo.F_GREAT_CIRCLE_DISTANCE( 
		@Lat,@Lon,@MaxLat,@Lon),6)) ,
	DistanceToMinLat =
		convert(numeric(12,6),round(
		dbo.F_GREAT_CIRCLE_DISTANCE( 
		@Lat,@Lon,@MinLat,@Lon),6))

Lon          MaxLon       MinLon       Lat          MaxLat       MinLat       
------------ ------------ ------------ ------------ ------------ ------------ 
-40.000000   -32.992122   -47.007878   50.000000    54.496608    45.503392

(1 row(s) affected)

DistanceToMaxLon DistanceToMinLon DistanceToMaxLat DistanceToMinLat 
---------------- ---------------- ---------------- ---------------- 
500.702809       500.702809       500.000001       500.000001

(1 row(s) affected)

quote:

---

Originally posted by Michael Valentine Jones

Well, what I really want is a simple, accurate method to compute the min/max longitude to get the limits for the query. For performance, I think it is far more important to limit the number of points for which you do a computation, than to use a less CPU intensive calculation.

Instead of "How far away is this point?", I would like to find, "What are the longitudes that are exactly X number of distance units east or west of a given latitude/longitude using the great circle distance?"

I found the following formula in Wikipedia that may be what I need. Unfortunately, they failed to post a TSQL implementation, so I guess I have to do that myself.

http://en.wikipedia.org/wiki/Longitude
"As opposed to a degree of latitude, which always corresponds almost exactly to sixty nautical miles or about 111 km (69 statute miles, each of 5280 feet), a degree of longitude corresponds to a distance that varies from 0 to 111 km: it is 111 km times the cosine of the latitude, when the distance is laid out on a circle of constant latitude; if the shortest distance, on a great circle were used, the distance would be even a little less. More precisely, one degree of longitude = (111.320 + 0.373sin²ö)cosö km, where ö is latitude)."








CODO ERGO SUM

---

quote:

---

Originally posted by Michael Valentine Jones

This function computes the great circle distance in Kilometers using the Haversine formula distance calculation.

If you want it in miles, change the average radius of Earth to miles in the function.

create function dbo.F_GREAT_CIRCLE_DISTANCE
	(
	@Latitude1  float,
	@Longitude1 float,
	@Latitude2  float,
	@Longitude2 float
	)
returns float
as
/*
fUNCTION: F_GREAT_CIRCLE_DISTANCE

	Computes the Great Circle distance in kilometers
	between two points on the Earth using the
	Haversine formula distance calculation.

Input Parameters:
	@Longitude1 - Longitude in degrees of point 1
	@Latitude1  - Latitude  in degrees of point 1
	@Longitude2 - Longitude in degrees of point 2
	@Latitude2  - Latitude  in degrees of point 2

*/
begin
declare @radius float

declare @lon1  float
declare @lon2  float
declare @lat1  float
declare @lat2  float

declare @a float
declare @distance float

-- Sets average radius of Earth in Kilometers
set @radius = 6371.0E

-- Convert degrees to radians
set @lon1 = radians( @Longitude1 )
set @lon2 = radians( @Longitude2 )
set @lat1 = radians( @Latitude1 )
set @lat2 = radians( @Latitude2 )

set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) + 
	(cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )

set @distance =
	@radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))

return @distance

end

Edit: corrected spelling


CODO ERGO SUM

---