SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Great Circle Distance Function - Haversine Formula
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 3

Ubbe
Starting Member

Sweden
14 Posts

Posted - 01/07/2008 :  09:17:41  Show Profile  Reply with Quote
Hello Peso!

I i have a database with Latitude and Longitude and i use Haversine formula.
and i calculate tha distance.

ex.
STO (59°20'N 18°03'E) LON (51°30'N 00°10'W) = 1437 km.
and I wish to no the heading to LON from STO.

I try this with no luck:
Course between points (course, tc1 = at point 1, start point)

IF sin(lon2-lon1)<0
tc1=acos((sin(lat2)-sin(lat1)*cos(d))/(sin(d)*cos(lat1)))
ELSE
tc1=2*pi-acos((sin(lat2)-sin(lat1)*cos(d))/(sin(d)*cos(lat1)))
ENDIF


//ubbe




-oOo-
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 01/07/2008 :  09:49:37  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See point (9) here
http://www.ianrpubs.unl.edu/epublic/live/ec157/build/ec157.pdf



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Ubbe
Starting Member

Sweden
14 Posts

Posted - 01/07/2008 :  12:26:08  Show Profile  Reply with Quote
Yes! thank you Peso.

Very interesting.

regards
Ubbe B.


-oOo-
Go to Top of Page

williamrichard
Starting Member

USA
1 Posts

Posted - 08/30/2008 :  05:28:40  Show Profile  Reply with Quote
It’s very interesting formula is very use full to me but can someone help a beginner to calculate the course between Start and Finish when I try to use Have sine formula. Then apply some kind of Dijkstra algorithm upon that.('')
===============================================================
williamrichard

WideCircles
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30116 Posts

Posted - 10/09/2008 :  15:46:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/09/2008 :  17:33:10  Show Profile  Reply with Quote
The fnHaversineBox function only seems to be accurate for the min/max Longitude when the starting Latitude is 0.

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)

Results:

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

(1 row(s) affected)

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

(1 row(s) affected)




CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/09/2008 :  19:14:39  Show Profile  Reply with Quote
This script seems to work well within limits, but it gets a little short on the longitude distances at high latitudes and/or a large radius.


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)

Results:

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

(1 row(s) affected)

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

(1 row(s) affected)


CODO ERGO SUM
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/09/2008 :  20:24:01  Show Profile  Reply with Quote
This script should produce usable search limits for a radius around a latitude/longitude point where the latitude is between -80 to +80 and the search radius is <= 500 kilometers. I added correction factors that are designed to make the limits slightly outside the search radius, but with an error of less then a kilometer.

The limits should be generous enough for the vast majority of search applications. For latitudes between -70 to +70, the search radius can be extended to 700 kilometers, and for latitudes between -60 to +60, the search radius can be extended to 900 kilometers.



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))


Results:

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)


CODO ERGO SUM

Edited by - Michael Valentine Jones on 10/09/2008 20:25:05
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 10/10/2008 :  16:41:21  Show Profile  Reply with Quote
Function F_FIND_SEARCH_LIMITS will return values for a bounded search:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112369


CODO ERGO SUM
Go to Top of Page

dmbware
Starting Member

4 Posts

Posted - 12/02/2008 :  12:09:02  Show Profile  Reply with Quote
Michael have you found the solution to pulling all coordinates in a 1 mile radius from a given long and lat?

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

Go to Top of Page

meenu.monu
Starting Member

8 Posts

Posted - 05/28/2011 :  01:50:59  Show Profile  Reply with Quote
dear friend,can u help me to solve my issue.

in sql 2005

i have a table named radius and thre fields are long ,lat,radius

long=51,lat=22,radius=2 meter


and another table value is

long1 =45,lat1=25

i want to check long1 and lat1 is inside the radius of 2 meter from long and lat.

how can i do this ?i wnt the result like "radius in" and "radius out"
how can i found that?

longitude and latitude are actual location points

anybdy help me?



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

Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000