Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

 SQL Server Forums Profile | Active Topics | Members | Search | Forum FAQ Register Now and get your question answered!
 All Forums  General SQL Server Forums  Script Library  Great Circle Distance Function - Haversine Formula Reply to Topic  Printer Friendly
Author  Topic
Page: of 3

Van
Constraint Violating Yak Guru

462 Posts

 Posted - 11/06/2007 :  17:39:18 Oh yea, well what if "cat" really spelled dog... Edited by - Van on 11/06/2007 17:40:06

Kristen
Test

United Kingdom
22859 Posts

 Posted - 11/06/2007 :  17:44:50 I didn't know that schrodinger's cat could spell "dog" ...

Van
Constraint Violating Yak Guru

462 Posts

 Posted - 11/06/2007 :  17:49:20 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

Kristen
Test

United Kingdom
22859 Posts

 Posted - 11/07/2007 :  01:21:22 "Commodore PET"OMG ... You must feel as old as I am!

Kristen
Test

United Kingdom
22859 Posts

 Posted - 11/07/2007 :  02:10:13 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

Kristen
Test

United Kingdom
22859 Posts

 Posted - 11/07/2007 :  02:29:38 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

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 11/07/2007 :  03:31:42 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"

Kristen
Test

United Kingdom
22859 Posts

 Posted - 11/07/2007 :  03:52:05 Wikipedia has radius 6356.78 km at the poles and 6378.14 km at the equator.

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

 Posted - 11/07/2007 :  08:46:55 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

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

 Posted - 11/07/2007 :  08:48:49 and don't forget the reshifting of the earths weight due to global warming and ice melting. _______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

United Kingdom
22859 Posts

 Posted - 11/07/2007 :  08:57:52 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 ...

spirit1
Cybernetic Yak Master

Slovenia
11752 Posts

 Posted - 11/07/2007 :  09:07:02 what's your estimated asteroid size?_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenpSSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

United Kingdom
22859 Posts

 Posted - 11/07/2007 :  09:36:07 "what's your estimated asteroid size?"Average .. from an asteroid's perspective ...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

 Posted - 11/07/2007 :  09:36:31 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

Van
Constraint Violating Yak Guru

462 Posts

 Posted - 11/07/2007 :  10:02:06 Hmmm. I remember playing Asteroids on my Commodore 64.

Zoroaster
Aged Yak Warrior

USA
702 Posts

 Posted - 11/07/2007 :  14:13:00 Commodore 64, lucky .. I had a vic 20.Future guru in the making.

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

 Posted - 11/07/2007 :  17:09:32 quote:Originally posted by Michael Valentine JonesYou 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 SUMnot to mention destroy the server where all of this is running. do you have a DR server on Mars?elsasoft.org

Kristen
Test

United Kingdom
22859 Posts

 Posted - 11/08/2007 :  00:19:50 "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 ..."

Ubbe
Starting Member

Sweden
14 Posts

 Posted - 01/07/2008 :  07:12:20 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-

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 01/07/2008 :  08:55:34 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"
Page: of 3  Topic
 Reply to Topic  Printer Friendly Jump To: Select Forum General SQL Server Forums       New to SQL Server Programming       New to SQL Server Administration       Script Library       Data Corruption Issues       Database Design and Application Architecture SQL Server 2012 Forums       Transact-SQL (2012)       SQL Server Administration (2012)       SSIS and Import/Export (2012)       Analysis Server and Reporting Services (2012)       Replication (2012)       Availability Groups and DR (2012)       Other SQL Server 2012 Topics SQL Server 2008 Forums       Transact-SQL (2008)       SQL Server Administration (2008)       SSIS and Import/Export (2008)       High Availability (2008)       Replication (2008)       Analysis Server and Reporting Services (2008)       Other SQL Server 2008 Topics SQL Server 2005 Forums       Transact-SQL (2005)       SQL Server Administration (2005)       .NET Inside SQL Server (2005)       SSIS and Import/Export (2005)       Service Broker (2005)       Replication (2005)       High Availability (2005)       Analysis Server and Reporting Services (2005)       Express Edition and Compact Edition (2005)       Other SQL Server Topics (2005) SQL Server 2000 Forums       SQL Server Development (2000)       SQL Server Administration (2000)       Import/Export (DTS) and Replication (2000)       Transact-SQL (2000)       Analysis Services (2000)       MSDE (2000) Development Tools       ASP.NET       Reporting Services Development       Other Development Tools Site Related Forums       Site Related Discussions       Article Discussion       Poll Discussion       The Yak Corral Other Forums       SQL Server 6.5 \ SQL Server 7.0       Other Topics       MS Access       ClearTrace Support Forum Old Forums       CLOSED - General SQL Server       CLOSED - SQL Server 2005/Yukon  -------------------- Home Active Topics Frequently Asked Questions Member Information Search Page
 SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC