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

Howdy,
ISSUE 1: See issue 2 below. I have a distance calculator on my site which wo
rks 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 inclu
de the distance from origin. Here's my dilemma, I have the script working gr
eat 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(DegT
oRads(_Lat1)) * Math.Cos(DegToRads(_Lat2)) * Math.Cos(Math.Abs((DegToRads(_L
ong2)) - (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((@.ToLa
t * @.PI / 180) as int)) * Cos(Abs(CAST((@.ToLong * @.PI / 180) as int)) - (CAS
T((@.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 w
ay 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 ret
urns 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 LozziDavid,
My math skills are not as good as yours, however, my SQL skills are strong.
I played with your logic some, to attempt to help you out, but, when you lo
ok at it, you'll probably find my math error right away. You got 15 miles,
and I'm getting 18 ...
this is probably a rounding error somewhere that you'll be able to find.
I started from your VB code, instead of trying to use the SQL code. I looke
d at the SQL code and recognized issues, so I started over from the VB Code.
When you find my rounding error, I'd appreciate a response
to ctruett3 at gmail.
hope this was helpful. As to the second portion of the post, try creating a
function (Like below) instead of a stored procedure, this will allow you to
use it in-line like:
/*
Select Top 1
dbo.fnuFindDistance(41.63, -87.73, 41.7, -88.07) Distance
, name
From
master.dbo.sysobjects
*/
--spuFindDistance 41.63,-87.73,41.7,-88.07
--Your answer = 15.81
Create Procedure
dbo.spuFindDistance
(
@.FromLat float
, @.FromLong float
, @.ToLat float
, @.ToLong float
)
As
Declare @.Miles float
Select @.Miles = Sin(dbo.fnuDegToRads(@.FromLat))
* Sin(dbo.fnuDegToRads(@.ToLat))
+ Cos(dbo.fnuDegToRads(@.FromLat))
* Cos(dbo.fnuDegToRads(@.ToLat))
* Cos(Abs(dbo.fnuDegToRads(@.ToLong) - dbo.fnuDegToRads(@.FromLong)))
Select @.Miles = Atan(Sqrt(1 - Power(@.Miles, 2)) / @.Miles)
Select @.Miles = (60.0 * ((@.Miles / PI()) * 180) * 1.1507794480235425)
Select @.Miles [Distance]
Go
Create Function
dbo.fnuDegToRads
(
@.Deg float
)
Returns float
As
Begin
Declare @.RetVal float
Select @.RetVal = Cast(@.Deg * Pi() / 180 as float)
Return @.RetVal
End
Go
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...|||
Tim Heap
Software & Database Manager
POSTAR Ltd
www.postar.co.uk
tim@.postar.co.uk
*** Sent via Developersdex http://www.examnotes.net ***