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

Discussion in 'ASP .Net' started by David Lozzi, May 26, 2005.

1. ### David LozziGuest

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.Atan((Math.Sqrt(1 - x ^ 2)) / x)

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

return x

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

David Lozzi, May 26, 2005

2. ### Steve KassGuest

Re: Convert VB.NET to TSQL PROC & Reference a Proc from anotherProc

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 =

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:

>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.Atan((Math.Sqrt(1 - x ^ 2)) / x)
>
>
>x = 60.0 * ((x / Math.PI) * 180) * 1.1507794480235425
>
>return x
>
>
>
>
>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
>
>
>
>

Steve Kass, May 26, 2005

3. ### Guest

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

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) -
AS Distance,
Store
from StoreTable
ORDER BY Distance ASC

HTH,
Jim

David Lozzi (remove) wrote:
> 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.Atan((Math.Sqrt(1 - x ^ 2)) / x)
>
>
> x = 60.0 * ((x / Math.PI) * 180) * 1.1507794480235425
>
> return x
>
>
>
>
> 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

, May 26, 2005
4. ### David LozziGuest

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

perfect

thank you!

<> wrote in message
news:...
> 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) -
> AS Distance,
> Store
> from StoreTable
> ORDER BY Distance ASC
>
> HTH,
> Jim
>
> David Lozzi (remove) wrote:
>> 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.Atan((Math.Sqrt(1 - x ^ 2)) / x)
>>
>>
>> x = 60.0 * ((x / Math.PI) * 180) * 1.1507794480235425
>>
>> return x
>>
>>
>>
>>
>> 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

>

David Lozzi, Jun 1, 2005