Solved

Help in writing user defined Function

Posted on 2004-08-20
5
500 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

932 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

14 Experts available now in Live!

Get 1:1 Help Now