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(

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:

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:

1. Do you prefer Proc Report or Proc Tabulate?Ans,If proc report,Would you Please explain?

2. Proc Transpose, Proc Tabulate, Proc Report?

3. Proc Assoc, Proc dmdb and Proc rulegen

4. Proc Tabulate or Proc Freq or Proc Report

5. [PATCH] Proc: move proc fs hooks from cpuset.c to proc/fs/base.c

6. [PATCH] Proc: move proc fs hooks from cpuset.c to proc/fs/base.c

7. FS: ORIGIN 2000 128 Proc.,64 proc,32 proc.

8. Do you prefer Proc Report or Proc Tabulate?Ans,If proc

9. PROC SQL Select Limit (was PROC SQL ERROR: Correlated reference

10. convert proc genmod into proc logistic

11. [PATCH]: proc: convert /proc/device file to use seq_file interface

12. [PATCH 4/7] procfs privacy: /proc/bus & /proc/net directory entries

13. tsql vs Stored proc and the tsql wins(?)

14. How to call a tcl script (proc) from a vb.net.

15. SSIS and VB.net - Can't see DTS stored Procs.

2 post • Page:**1** of **1**