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 | Next Page
Author Previous Topic Topic Next Topic
Page: of 3

Van
Constraint Violating Yak Guru

458 Posts

Posted - 11/06/2007 :  17:39:18  Show Profile  Reply with Quote
Oh yea, well what if "cat" really spelled dog...

Edited by - Van on 11/06/2007 17:40:06
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/06/2007 :  17:44:50  Show Profile  Reply with Quote
I didn't know that schrodinger's cat could spell "dog" ...
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 11/06/2007 :  17:49:20  Show Profile  Reply with Quote
Actually it can't. It's not really a real cat but a theory. A hypothetical cat. But I guess hypotetically a cat could spell dog if it really wanted to. Better yet would be to see a cat operating a computer...I guess that would make it a Commodore PET.

Edited by - Van on 11/06/2007 17:52:02
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/07/2007 :  01:21:22  Show Profile  Reply with Quote
"Commodore PET"

OMG ... You must feel as old as I am!
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/07/2007 :  02:10:13  Show Profile  Reply with Quote
I have tried replacing

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

with

set @distance =
	@radius * ( 2.0E * atn2(sqrt(@a), sqrt(1-@a)) )

that gives me a measurable variation in output values (looks lik 1% or 2%), but I still can't get the right values

Kristen

Edited by - Kristen on 11/07/2007 02:11:27
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/07/2007 :  02:29:38  Show Profile  Reply with Quote
Actually ignore that, I think it is accurate enough. Not sure whether ATN2 is more accurate than ASIN, I will report back on that point.

Kristen
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/07/2007 :  03:31:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
There is a formula that calculates the correct earth radius depending on latitude.

R' = a * (1 - e^2) / (1 - e^2 * sin^2(lat))^(3/2)

where a is the equatorial radius, b is the polar radius, and e is the eccentricity of the ellipsoid = (1 - b^2/a^2)^(1/2).

R' = a * (1 - (1 - b^2/a^2)) / (1 - (1 - b^2/a^2) * sin^2(lat))^(3/2)



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

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/07/2007 :  03:52:05  Show Profile  Reply with Quote
Wikipedia has radius 6356.78 km at the poles and 6378.14 km at the equator.
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 11/07/2007 :  08:46:55  Show Profile  Visit jezemine's Homepage  Reply with Quote
the eccentricity of the earth is gradually lessening because its rotation frequency is gradually decreasing, due to tidal forces from the earth-moon system. Eventually, in many billions of years, it will stop when the earth rotation and moon revolution are synchronized.

are you accounting for this?




elsasoft.org
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 11/07/2007 :  08:48:49  Show Profile  Visit spirit1's Homepage  Reply with Quote
and don't forget the reshifting of the earths weight due to global warming and ice melting.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/07/2007 :  08:57:52  Show Profile  Reply with Quote
I've added a predictive algorithm to take into account asteroid impact. Given that the probability of being hit by an asteroid is about the same as being killed by an aircraft accident I thought it was an important addition. I allowed for a 20,000 mile detour pro-rata the probability of it happening ...
Go to Top of Page

spirit1
Cybernetic Yak Master

Slovenia
11749 Posts

Posted - 11/07/2007 :  09:07:02  Show Profile  Visit spirit1's Homepage  Reply with Quote
what's your estimated asteroid size?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/07/2007 :  09:36:07  Show Profile  Reply with Quote
"what's your estimated asteroid size?"

Average .. from an asteroid's perspective ...
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/07/2007 :  09:36:31  Show Profile  Reply with Quote
You have to consider the angle, mass, velocity and point of impact for the asteroid. If it hits on one side, it speeds up the Earth’s rotation, but on the other side it slows it down. Either will change the eccentricity of the earth, and change the error of the calculation.



CODO ERGO SUM
Go to Top of Page

Van
Constraint Violating Yak Guru

458 Posts

Posted - 11/07/2007 :  10:02:06  Show Profile  Reply with Quote
Hmmm. I remember playing Asteroids on my Commodore 64.
Go to Top of Page

Zoroaster
Aged Yak Warrior

USA
702 Posts

Posted - 11/07/2007 :  14:13:00  Show Profile  Reply with Quote
Commodore 64, lucky .. I had a vic 20.



Future guru in the making.
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2884 Posts

Posted - 11/07/2007 :  17:09:32  Show Profile  Visit jezemine's Homepage  Reply with Quote
quote:
Originally posted by Michael Valentine Jones

You have to consider the angle, mass, velocity and point of impact for the asteroid. If it hits on one side, it speeds up the Earth’s rotation, but on the other side it slows it down. Either will change the eccentricity of the earth, and change the error of the calculation.



CODO ERGO SUM



not to mention destroy the server where all of this is running. do you have a DR server on Mars?


elsasoft.org
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 11/08/2007 :  00:19:50  Show Profile  Reply with Quote
"do you have a DR server on Mars?"

Ah ... that will be using the faster-than-light data replication my clients always seem to want. "No trouble Sir, just a little expense ..."
Go to Top of Page

Ubbe
Starting Member

Sweden
14 Posts

Posted - 01/07/2008 :  07:12:20  Show Profile  Reply with Quote
Hello!
I think the Haversine formula distance calculation is excellent.

But can someone help a beginner to calculate the course between Start and Finish when i try to use Haversine formula.

ex. Stockholm -> London distance is 775 nm.

and i wish to no the course (Initial Heading) to the destinaion.



//ubbe


-oOo-
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 01/07/2008 :  08:55:34  Show Profile  Visit SwePeso's Homepage  Reply with Quote
You will have to use a database with all possible routes for that.
And then apply some kind of Dijkstra algorithm upon that.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
Page: of 3 Previous Topic Topic Next Topic  
Previous Page | Next 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.25 seconds. Powered By: Snitz Forums 2000