Solved

Help in writing user defined Function

Posted on 2004-08-20
5
493 Views
Last Modified: 2008-02-01
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
Comment
Question by:pgkooijman
  • 3
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
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

by:pgkooijman
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 142

Expert Comment

by:Guy Hengel [angelIII / a3]
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 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 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

by:pgkooijman
ID: 11851869
Works great!
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now