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