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

D

David Lozzi

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
 
S

Steve Kass

David,

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 said:
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
Click to expand...
 
J

jhcorey

Here is a paraphrase of some sql that we use. This could be a stored
procedure
with @my_lat and @my_long passed in as your search coordinates. This
code
assumes a table with XCOORD and YCOORD columns that hold the lat and
long for each entry.

SELECT (ROUND(3956 * (2 * ASIN( SQRT( (1-COS(2*(((RADIANS(@my_lat) -
RADIANS(YCOORD))/2))))/2
+ COS(RADIANS(YCOORD)) * COS(RADIANS(@my_lat)) *
(1-COS(2*(((RADIANS(@my_long) - RADIANS(XCOORD))/2))))/2))),2))
AS Distance,
Store
from StoreTable
ORDER BY Distance ASC

HTH,
Jim
 
D

David Lozzi

perfect

thank you!


Here is a paraphrase of some sql that we use. This could be a stored
procedure
with @my_lat and @my_long passed in as your search coordinates. This
code
assumes a table with XCOORD and YCOORD columns that hold the lat and
long for each entry.

SELECT (ROUND(3956 * (2 * ASIN( SQRT( (1-COS(2*(((RADIANS(@my_lat) -
RADIANS(YCOORD))/2))))/2
+ COS(RADIANS(YCOORD)) * COS(RADIANS(@my_lat)) *
(1-COS(2*(((RADIANS(@my_long) - RADIANS(XCOORD))/2))))/2))),2))
AS Distance,
Store
from StoreTable
ORDER BY Distance ASC

HTH,
Jim
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help: Javascript to Excel VBA? 3

Top