Domain error occured

Question

Post reply

Domain error occured

Taco Fleur

SSC-Addicted

Points: 457
More actions
June 25, 2007 at 9:33 pm

#174199

Hello all,
I've been using the following code for a while now and only recently came across an error with it. The error is "A domain error occurred".
I have pasted the code and values that generate the error below. If anyone can shed some light on this that would be much appreciated.
The version of SQL I am using is MS SQL 2005
DECLARE
@fromLatitude AS REAL
, @fromLongitude AS REAL
, @toLatitude AS REAL
, @toLongitude AS REAL
SET
@fromLatitude = -31.111695
SET
@fromLongitude = 117.792134
SET
@toLatitude = -31.111695
SET
@toLongitude = 117.792134

DECLARE
@distance REAL
SET
@distance = ( ROUND( ( ACOS( ( SIN( @fromLatitude / 57.2958 ) * SIN( @toLatitude / 57.2958 ) ) +
( COS ( @fromLatitude / 57.2958 ) * COS( @toLatitude / 57.2958 ) * COS( @toLongitude/57.2958 - @fromLongitude / 57.2958 ) ) ) ) * 6378.135, 3 ) )
SELECT
@distance;

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 1

SELECT ACOS(1.0000000000000002) will raise a domain error.

Taco Fleur SSC-Addicted Points: 457 More actions · Answer 2

Hi,

thanks for your prompt response. I don't see the solution to the problem though? Would you happen to know it and be so kind to list it?

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 3

Koji Matsumura

SSCrazy

Points: 2241

June 25, 2007 at 9:51 pm

#715092

What result do you expect from your query?

Taco Fleur SSC-Addicted Points: 457 More actions · Answer 4

Hi,

I expect it to calculate the distance between the two coordinates, which it does.

I could code something that will prevent the code form executing when both coordinates are the same, but that does not help with me understanding what the underlying issue is.

Following example does not give an error;

DECLARE

@fromLatitude AS REAL

,

@fromLongitude AS REAL

,

@toLatitude AS REAL

,

@toLongitude AS REAL

SET

@fromLatitude = -27.339993

SET

@fromLongitude = 152.936964

SET

@toLatitude = -27.339993

SET

@toLongitude = 152.936964

DECLARE

@distance REAL

SET

@distance = ( ROUND( ( ACOS( ( SIN( @fromLatitude / 57.2958 ) * SIN( @toLatitude / 57.2958 ) ) +

(

COS ( @fromLatitude / 57.2958 ) * COS( @toLatitude / 57.2958 ) * COS( @toLongitude/57.2958 - @fromLongitude / 57.2958 ) ) ) ) * 6378.135, 3 ) )

SELECT

@distance;

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 5

It is precision/rounding problem.

You can try

SET @distance =

ROUND((ACOS(ROUND((SIN(@fromLatitude / 57.2958) * SIN(@toLatitude / 57.2958))

+ (COS(@fromLatitude / 57.2958) * COS(@toLatitude / 57.2958) * COS(@toLongitude/57.2958 - @fromLongitude / 57.2958)), 3))) * 6378.135, 3)

Taco Fleur SSC-Addicted Points: 457 More actions · Answer 6

You're a champ, it doesn't error anymore.

I gained a few kilometres but thats ok, maybe this calculation is more accurate?

Anyways, thanks a million for that.

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 7

I think your previous calculation was more accurate.

You can try increasing the digits of the inner ROUND function.

Taco Fleur SSC-Addicted Points: 457 More actions · Answer 8

Hi again, can you provide a sample of what you mean? Thanks in advance.

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 9

With

SET @distance =

ROUND((ACOS(ROUND((SIN(@fromLatitude / 57.2958) * SIN(@toLatitude / 57.2958))

+ (COS(@fromLatitude / 57.2958) * COS(@toLatitude / 57.2958) * COS(@toLongitude/57.2958 - @fromLongitude / 57.2958)), [15]))) * 6378.135, 3)

you get 0, but with

SET @distance =

ROUND((ACOS(ROUND((SIN(@fromLatitude / 57.2958) * SIN(@toLatitude / 57.2958))

+ (COS(@fromLatitude / 57.2958) * COS(@toLatitude / 57.2958) * COS(@toLongitude/57.2958 - @fromLongitude / 57.2958)), [16]))) * 6378.135, 3)

you get a domain error.

Taco Fleur SSC-Addicted Points: 457 More actions · Answer 10

Thanks, but I was referring to: You can try increasing the digits of the inner ROUND function.

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 11

By saying inner ROUND......., I was refering to [3], [15], [16] of the following:

SET @distance = ROUND((ACOS(ROUND(....., 3))) * 6378.135, 3)

SET @distance = ROUND((ACOS(ROUND(....., 15))) * 6378.135, 3)

SET @distance = ROUND((ACOS(ROUND(....., 16))) * 6378.135, 3)