?
Solved

Help in writing user defined Function

Posted on 2004-08-20
5
Medium Priority
?
543 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 143

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 143

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 143

Accepted Solution

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

by:pgkooijman
ID: 11851869
Works great!
0

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Suggested Courses

765 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