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 Lozzi

    David Lozzi Guest

    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
     
    David Lozzi, May 26, 2005
    #1
    1. Advertising

  2. David Lozzi

    Steve Kass Guest

    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 =
    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:

    >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
    >
    >
    >
    >
     
    Steve Kass, May 26, 2005
    #2
    1. Advertising

  3. David Lozzi

    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) -
    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

    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.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
     
    , May 26, 2005
    #3
  4. David Lozzi

    David Lozzi Guest

    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) -
    > 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
    >
    > 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.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

    >
     
    David Lozzi, Jun 1, 2005
    #4
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Eduardo Rosa

    TSQL

    Eduardo Rosa, Mar 17, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    1,294
    Karl Seguin
    Mar 17, 2005
  2. David Lozzi

    Array in TSQL?

    David Lozzi, Nov 1, 2005, in forum: ASP .Net
    Replies:
    16
    Views:
    23,206
    Rebecca York
    Nov 4, 2005
  3. David Lozzi

    SqlDataSource TSQL vs Procs

    David Lozzi, Mar 16, 2007, in forum: ASP .Net
    Replies:
    8
    Views:
    424
    David Lozzi
    Mar 22, 2007
  4. DR
    Replies:
    0
    Views:
    492
  5. don schilling

    EXEC in TSQL stored proc causes ERROR

    don schilling, Sep 29, 2003, in forum: ASP .Net Datagrid Control
    Replies:
    0
    Views:
    211
    don schilling
    Sep 29, 2003
Loading...

Share This Page