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

Ubbe
Starting Member

Sweden
14 Posts

 Posted - 01/07/2008 :  09:17:41 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-

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 01/07/2008 :  09:49:37 See point (9) herehttp://www.ianrpubs.unl.edu/epublic/live/ec157/build/ec157.pdfE 12°55'05.25"N 56°04'39.16"

Ubbe
Starting Member

Sweden
14 Posts

 Posted - 01/07/2008 :  12:26:08 Yes! thank you Peso.Very interesting.regardsUbbe B.-oOo-

williamrichard
Starting Member

USA
1 Posts

 Posted - 08/30/2008 :  05:28:40 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.('')===============================================================williamrichardWideCircles

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

 Posted - 10/09/2008 :  15:46:36 ```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"

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

 Posted - 10/09/2008 :  17:33:10 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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

 Posted - 10/09/2008 :  19:14:39 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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

 Posted - 10/09/2008 :  20:24:01 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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

 Posted - 10/10/2008 :  16:41:21 Function F_FIND_SEARCH_LIMITS will return values for a bounded search:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=112369CODO ERGO SUM

dmbware
Starting Member

4 Posts

 Posted - 12/02/2008 :  12:09:02 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 JonesWell, 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

meenu.monu
Starting Member

8 Posts

 Posted - 05/28/2011 :  01:50:59 dear friend,can u help me to solve my issue.in sql 2005i have a table named radius and thre fields are long ,lat,radiuslong=51,lat=22,radius=2 meterand another table value islong1 =45,lat1=25i 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 pointsanybdy help me?quote:Originally posted by Michael Valentine JonesThis 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 spellingCODO ERGO SUM
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