[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 561
  • Last Modified:

Help in writing user defined Function

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
pgkooijman
Asked:
pgkooijman
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
pgkooijmanAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
pgkooijmanAuthor Commented:
Works great!
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now