April 15, 2011 at 3:16 pm
alastair-804470 (4/15/2011)
Well, yes... but in that case you've probably got other problems. Like no support 😉
And no budget...
April 15, 2011 at 3:36 pm
You work in the healthcare industry and you've got no budget? You surprise me 😉
April 21, 2011 at 9:39 am
Be careful; on at least SQL Server 2005 SP3 with a very similar formula, I've seen a very interesting issue where the internal SQL Server rounding results in the ACOS receiving a value very slightly higher than 1, and exploding (with an error or a NULL output of ACOS, depending on ANSI_WARNINGS and ARITHABORT).
Tracking this down was interesting, as the difference is extremely subtle; SQL Server would not show the difference between a working center point and a failing center point in DECIMAL or FLOAT data types. Only a conversion to a (VAR)BINARY type actually showed that the two values were, in fact, different.
To make a long story short, we were operating in a batch environment on a limited set of tens of thousands of points, so the simplest (if not very elegant) answer was at the end of each batch update, set ANSI_WARNINGS and ARITHABORT off, run each point through a trial with the precise math we were using as the center point of a radius, and any points that return a NULL from the ACOS math get moved by 1/100000th of a degree latitude (about 11 cm, per http://www.csgnetwork.com/degreelenllavcalc.html)and retried; there's often a handful that need to be moved by 2/100000th or 3/100000ths of a degree latitude as they turn up NULL on the retest. In all but surveying and specialist applications, even moving a point by a few meters isn't going to be significant compared to the error already present in the calculation (think: elevation + inaccuracies in measuring/recording).
Essentially: whatever lat/long math you're trying to do in SQL, please do test every single point you're going to use as a center to see if it's going to return NULL results for any of them. Postal code tables usually have less than a few million entries in them (for the US, usually less than 50,000 5 digit zip codes are listed), so a full test only takes a few seconds.
ETA: SQL 2005 SP4 build 5432 has the same issue. Worse, the x86 and x64 editions have the same issue, but at different numbers, so you have to check and fix on x86, then x64, and if you're moving zip codes, repeat back and forth until both report 0 problems.
September 26, 2014 at 2:58 am
Possibly similar I adapted some code to create my version:
(ACOS(COS(RADIANS(90-SD.StuLat)) *COS(RADIANS(90-BS.SchLat)) +SIN(RADIANS(90-SD.StuLat)) *SIN(RADIANS(90-BS.SchLat)) *COS(RADIANS(SD.StuLong-BS.SchLong))) *3958.756) AS DISTANCE_M
Your version seems more robust and I look forward to playing with it.
I'm new to SQL so haven't gotten my head round variables and spatial data types so this in the Select statement worked.
I used it to work out how far kids travelled to school. And by multiplying it by 1.35 it seemed to give a more accurate distance to account for the fact people don't travel in straight lines.
Many thanks for sharing.
September 26, 2014 at 4:53 am
Awesome, thanks.
It might be good to put the radius of the earth as a constant at the top of the function (or initialised variable in SQL), so readers of the function can understand how it works, and change it to kilometers, or whatever. The number of degrees per radian (57.29...) would also make a good candidate for a constant since it is repeated several times.
September 26, 2014 at 4:54 am
Well for the real world it would be 1.60934 kilometers per mile
September 26, 2014 at 7:28 am
It would be interesting to make a table of measure distances and calculated distances for different calculations to see if this is a meaningful problem for your users. It is unlikely any one will need more than a 99% accuracy for many uses of the calculation.
Here is a web calculator:
September 26, 2014 at 11:04 am
For cadastral surveying (i.e. property / town / state / country boundaries) with corrected GPS readings, we regularly get less than 1cm error per kilometer, which is 0.001% error. Of course, for this you (or your equipment) has to know how far you are from the center of the earth where you are, for which you may have to take the irregularity of the geoid into account.
Even without doing that, the difference between the equatorial radius and polar radius of the earth is only 0.34%, less than the error you impose by not using the exact ratio of kilometers per mile.
It's costs nothing extra to use the exact figure, since it's available, of 1.60934 km / mile. Why use a knotted piece of string to measure when you can use a measuring tape?
Nice calculation website though. Thanks !!
February 2, 2016 at 7:46 am
I'm having a problem with this function.
I find that in some situations, I get an error: "An invalid floating point operation occurred."
This is true for instance, in *some* (not all) cases where both the coordinates are the same.
e.g: this results in an error:
DECLARE @lat1 float
DECLARE @lat2 float
DECLARE @lon1 float
DECLARE @lon2 float
SET @lat1 = 51.164429
SET @lat2 = 51.164429
SET @lon1 = -1.765275
SET @lon2 = -1.765275
PRINT 6378137 -- average radius of the earth in metres
*ACOS(SIN(@Lat1/57.295779513082323)
* SIN(@Lat2/57.295779513082323)
+ COS(@Lat1/57.295779513082323)
* COS(@Lat2/57.295779513082323)
* COS((@Lon2-@Lon1)/57.295779513082323))
It seems to be because ACOS(1) is essentially called in this scenario. I'm not sure if it's enough to just put in a short-circuit like this:
IF ((@lat1 = @lat2) AND (@lon2 = @lon2))
BEGIN
SET @result = 0
...
Or if the error could occur in other circumstances also? (my maths is far from great!)
Thanks,
Nick
February 2, 2016 at 8:37 am
I had the same problem years ago. You have to do an interim check to the value you send to ACOS; see function below ...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
* Given two points in decimal degrees, this
* function will return the distance in kilometers
* between the two points using the spherical law of
* cosines ...
*
* Reference: http://en.wikipedia.org/wiki/Great-circle_distance
*
*/
CREATE FUNCTION [dbo].[geodistance]
(
@lat1 FLOAT,
@lon1 FLOAT,
@lat2 FLOAT,
@lon2 FLOAT
)
RETURNS FLOAT
AS
BEGIN
DECLARE @d FLOAT
DECLARE @n FLOAT
DECLARE @k_earth_mean_radius_km FLOAT
DECLARE @p1_lat FLOAT
DECLARE @p1_lon FLOAT
DECLARE @p2_lat FLOAT
DECLARE @p2_lon FLOAT
SET @k_earth_mean_radius_km = 6371.0
/* Convert first point from degrees to radians ... */
SET @p1_lat = RADIANS( @lat1 )
SET @p1_lon = RADIANS( @lon1 )
/* Convert second point from degrees to radians ... */
SET @p2_lat = RADIANS( @lat2 )
SET @p2_lon = RADIANS( @lon2 )
SET @n = SIN( @p1_lat ) * SIN( @p2_lat ) + COS( @p1_lat ) * COS( @p2_lat ) * COS( @p2_lon - @p1_lon )
/*
* Range check interim result as ACOS only accepts values from -1 through 1.
* Values outside this range return NULL and report a domain error; this is
* reported as "An invalid floating point operation occurred." ...
*/
IF @n > 1
SET @n = 1
ELSE
IF ( @n < -1 )
SET @n = -1
SET @d = ACOS( @n ) * @k_earth_mean_radius_km
RETURN @d
END
February 3, 2016 at 2:20 am
Thanks - I've tested this on a database of 1.8 million postcodes and it seems the short-circuit approach is enough to stop this problem happening on my data:
IF ((@lat1 = @lat2) AND (@lon1 = @lon2)) RETURN 0
However I will also do your ACOS value check just for belt and braces 🙂
BEGIN
IF ((@lat1 = @lat2) AND (@lon1 = @lon2)) RETURN 0 -- short circuit for case where the same coords are supplied
DECLARE @n float
SET @n = SIN(@Lat1/57.295779513082323)
* SIN(@Lat2/57.295779513082323)
+ COS(@Lat1/57.295779513082323)
* COS(@Lat2/57.295779513082323)
* COS((@Lon2-@Lon1)/57.295779513082323)
IF @n > 1 SET @n = 1 ELSE IF ( @n < -1 ) SET @n = -1
RETURN ACOS(@n) * 6378137 -- average radius of the earth in metres
END
February 3, 2016 at 8:02 am
Sounds good, however, you could comment out the following line and everything will still work:
IF ((@lat1 = @lat2) AND (@lon1 = @lon2)) RETURN 0
Also, see the wiki article regarding earth radius at:
https://en.wikipedia.org/wiki/Great-circle_distance
A good choice for the radius is the mean earth radius, ... 6371.0 km (for the WGS84 ellipsoid); in the limit of small flattening, this choice minimizes the mean square relative error in the estimates for distance.
Not sure about your application needs, but the value above might help reduce errors in distance calculations.
October 20, 2020 at 2:45 pm
Hello my fella DBA
I just accept a position utilize spatial data types in Sql Server. Can someone email me a tutorial on where I can learn as much as possible on the topic.
October 20, 2020 at 2:53 pm
Hi Robert,
Depending on how you plan to use the lat-long calculator for miles, it works great for "as the crow flies" calculations. Unfortunately, not so great for distance calculations for auto or truck mileages. You probably will need to utilize a 3rd party mapping application that can account for the various road infrastructures.
Just a heads up!
Viewing 14 posts - 46 through 58 (of 58 total)
You must be logged in to reply to this topic. Login to reply