Solved

# Help in writing user defined Function

Posted on 2004-08-20
Medium Priority
565 Views
I am trying to convert an ASP function into a user defined function in SQL Server 2000. This is the ASP code:

Function DistCalc2(lat1,lon1,lat2,lon2)
A = lat1/57.29577951
B = lon1/57.29577951
C = lat2/57.29577951
D = lon2/57.29577951

IF A = C AND B = D THEN
DistCalc2 = 0
ELSE
IF (SIN(A)*SIN(C) + COS(A)*COS(C)*COS(B-D)) > 1 THEN
DistCalc2 = 3963.1*Arccos(1)
ELSE
DistCalc2 = 3963.1*Arccos(SIN(A)*SIN(C) + COS(A)*COS(C)*COS(B-D))
End IF
End If
DistCalc2 = 1.6094 * DistCalc2
End Function

Using some transact sql resources I created the following function which keeps on giving syntax errors:

CREATE FUNCTION dbo.geo_distance (@lon1 decimal, @lat1 decimal, @lon2 decimal, @lat2 decimal)
RETURNS decimal
AS

IF @lat1 = @lat2 AND @lon1 = @lon2
BEGIN
DECLARE @dist decimal
SET @dist = 0
RETURN @dist
END
ELSE
IF (SIN(@lat1/57.29577951)*SIN(@lat2/57.29577951) + COS(@lat1/57.29577951)*COS(@lat2/57.29577951)*COS((@lon1/57.29577951)-(@lon2/57.29577951))) > 1
BEGIN
SET @dist = (3963.1*ACOS(1)) * 1.6094
RETURN @dist
END
ELSE
BEGIN
SET @dist = (3963.1 * ACOS(SIN(@lat1/57.29577951)*SIN(@lat2/57.29577951) + COS(@lat1/57.29577951)*COS(@lat2/57.29577951)*COS((@lon1/57.29577951)-(@lon2/57.29577951)))) * 1.6094
RETURN @dist
END
0
Question by:pgkooijman
• 3
• 2

LVL 143

Expert Comment

ID: 11850679
You have to respect all the syntax rules, and here we go:
CREATE FUNCTION dbo.geo_distance (@lon1 decimal, @lat1 decimal, @lon2 decimal, @lat2 decimal)
RETURNS decimal
AS
BEGIN
DECLARE @dist decimal
IF (@lat1 = @lat2 AND @lon1 = @lon2)
BEGIN
SET @dist = 0
END
ELSE
IF (SIN(@lat1/57.29577951)*SIN(@lat2/57.29577951) + COS(@lat1/57.29577951)*COS(@lat2/57.29577951)*COS((@lon1/57.29577951)-(@lon2/57.29577951))) > 1
BEGIN
SET @dist = (3963.1*ACOS(1)) * 1.6094
END
ELSE
BEGIN
SET @dist = (3963.1 * ACOS(SIN(@lat1/57.29577951)*SIN(@lat2/57.29577951) + COS(@lat1/57.29577951)*COS(@lat2/57.29577951)*COS((@lon1/57.29577951)-(@lon2/57.29577951)))) * 1.6094
END

RETURN @dist
END

go
select dbo.geo_distance ( 1,2,3,4)
go
drop function dbo.geo_distance

CHeers
0

Author Comment

ID: 11850828
I am getting different results than my ASP function. Could it be that I have to set the decimal specifications? The input will be in format decimal(10,7) should I set this?
0

LVL 143

Expert Comment

ID: 11850872
Yes, you can do that.  However, the problem might not be only that, but it could be other (T/SQL) problems
For example, check if this get's better results (i assume you only get slightly different results):

SET @dist = (cast(3963.1 as decimal)*ACOS(1)) * cast(1.6094 as decimal)
etc

CHeers
0

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 2000 total points
ID: 11850886
Actually, the first implementation returned this:
select dbo.geo_distance ( 1,2,3,4)  -> 391

The version below returns (which seems more accurate):
select dbo.geo_distance ( 1,2,3,4) -> 390.9918505

Full function:
CREATE FUNCTION dbo.geo_distance (@lon1 decimal, @lat1 decimal, @lon2 decimal, @lat2 decimal)
RETURNS decimal ( 15,7 )
AS
BEGIN
DECLARE @dist decimal(15,7)
DECLARE @m1 decimal(15,7)
DECLARE @m2 decimal(15,7)
SET @m1 = cast(3963.1 as decimal)
SET @m2 = cast(1.6094 as decimal)

IF (@lat1 = @lat2 AND @lon1 = @lon2)
BEGIN
SET @dist = 0
END
ELSE
IF (SIN(@lat1/57.29577951)*SIN(@lat2/57.29577951) + COS(@lat1/57.29577951)*COS(@lat2/57.29577951)*COS((@lon1/57.29577951)-(@lon2/57.29577951))) > 1
BEGIN
SET @dist = (@m1 *ACOS(1)) * @m2
END
ELSE
BEGIN
SET @dist = (@m1 * ACOS(SIN(@lat1/57.29577951)*SIN(@lat2/57.29577951) + COS(@lat1/57.29577951)*COS(@lat2/57.29577951)*COS((@lon1/57.29577951)-(@lon2/57.29577951)))) * @m2
END

RETURN @dist
END

CHeers
0

Author Comment

ID: 11851869
Works great!
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.