Convert VB.NET to TSQL PROC & Reference a Proc from another Proc

Convert VB.NET to TSQL PROC & Reference a Proc from another Proc

Post by David Lozz » Fri, 27 May 2005 23:06:48


his is a multi-part message in MIME format.


Howdy,

ISSUE 1: See issue 2 below. I have a distance calculator on my site which works great. However, the users need to sort by distance, which make sense. I'm not sure how to do it other than like this. With the returning query include the distance from origin. Here's my dilemma, I have the script working great in VB which provides the distance, but that is not sortable, but when I port it over to TSQL I get differing results. Here is the code in VB:

x = (Math.Sin(DegToRads(_Lat1)) * Math.Sin(DegToRads(_Lat2)) + Math.Cos(DegToRads(_Lat1)) * Math.Cos(DegToRads(_Lat2)) * Math.Cos(Math.Abs((DegToRads(_Long2)) - (DegToRads(_Long1)))))

x = Math.Atan((Math.Sqrt(1 - x ^ 2)) / x)


x = 60.0 * ((x / Math.PI) * 180) * 1.1507794480235425

return x



Function DegToRads(ByVal Deg)

DegToRads = CDbl(Deg * Math.PI / 180)

End Function



As you can see, nice and simple. Here is how I ported it over to TSQL



CREATE PROCEDURE [dbo].[cp_FindDistance]
@FromLat as decimal(38,18),
@FromLong as decimal(38,18),
@ToLat as decimal(38,18),
@ToLong as decimal(38,18)

AS

DECLARE @X as decimal(38,20)
DECLARE @PI as decimal(38,20)

SET @PI = 3.14159265358979323846

SET @X = (Sin(CAST((@FromLat * @PI / 180) as int)) * Sin(CAST((@ToLat * @PI / 180) as int)) + Cos(CAST((@FromLat * @PI / 180) as int)) * Cos(CAST((@ToLat * @PI / 180) as int)) * Cos(Abs(CAST((@ToLong * @PI / 180) as int)) - (CAST((@FromLong * @PI / 180) as int))))

SET @X = Atan((Sqrt(1 - SQUARE(@X))) / @X)

SET @X = (1.852 * 60.0 * ((@X / @PI) * 180))

SET @X = @X / 1.609344

SELECT @X as Miles



The VB is returning accurate miles while the TSQL is returning some number way out of reach, for example when entering cp_FindDistance 41.63,-87.73,41.7,-88.07, the PROC returns -4516.23854688618468000000 while the VB script returns 15.81.

ISSUE 2: Once I get this proc working, how do I get it into the proc that is returning the recordset of locations? i.e. select *, cp_GetDistance(fromlat,fromlong,places.lat,places.long) as distance from places.



Thanks a ton!!!

David Lozzi


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2900.2627" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY>
<DIV><FONT face=Arial size=2>Howdy,</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial><FONT size=2><STRONG>ISSUE 1:</STRONG> See issue 2 below.
I have a distance calculator on my site which works great. However, the users
need to sort by distance, which make sense. I'm not sure how to do it other than
like this. With the returning query include the distance from origin. Here's my
dilemma, I have the script working great in VB which provides the distance, but
that is not sortable, but when I port it over to TSQL I get differing results.
Here is the code in VB:</FONT></FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV>
<P><FONT face="Courier New" size=2>x = (Math.Sin(DegToRads(_Lat1)) *
Math.Sin(DegToRads(_Lat2)) + Math.Cos(DegToRads(_Lat1)) *
Math.Cos(DegToRads(_Lat2)) * Math.Cos(Math.Abs((DegToRads(_Long2)) -
(DegToRads(
 
 
 

Convert VB.NET to TSQL PROC & Reference a Proc from another Proc

Post by Steve Kas » Fri, 27 May 2005 23:51:55

avid,

I have no idea why you are casting to int, and why you are using
high-precision decimal types instead of floats, but between those things
and all the extra parentheses, I think either you've got typo or you
have run up against some rounding/truncation issues with the
high-precision decimals. I'm sure you won't have longitudes on the
order of 1,000,000,000,000,000,000, and you won't need 20 decimals of
precision, especially if you are rounding things to ints.

In any case, here is a user-defined function that does what you want,
using the T-SQL functions Radians() and Acos() to simplify things. I
don't know what your units are, but this should be closer to what you
want. You can execute a select query with this function, once you get
it to return the answer you want:

select this, that,
dbo.uf_Distance(fromlat,fromlong,places.lat,places.long) from ... order
by dbo.uf_Distance(fromlat,fromlong,places.lat,places.long)

This gives the answer 18.19 for your example, not 15.81, but I think it
is the correct T-SQL for what you show in VB.

Steve Kass
Drew University

create function uf_Distance (
@FromLat float, @FromLong float, @ToLat float, @ToLong float
) returns float as begin

declare @X float
SET @X =
Sin(Radians(@FromLat))
* Sin(Radians(@ToLat))
+ Cos(Radians(@FromLat))
* Cos(Radians(@ToLat))
* Cos(Radians(@ToLong)-Radians(@FromLong))

SET @X = Acos(@X)
RETURN 1.852 * 60.0 * Degrees(@X) / 1.609344

end

go
select dbo.uf_Distance (41.63,-87.73,41.7,-88.07)
go


David Lozzi wrote: