Hi,
I’m trying to find the distance between two sets of coordinates, of latitude and longitude. I have a function, “DistanceFunc”.
I’m having trouble applying it.
I’ll be given values for @DLat and @DLon.
I need values for@RLat and @Rlon.
I can’t figure out just how to step the variables for the second set of coordinates.
Here is my table layout for the table “Distance”.
Number Full Name Company Add1 Add2 City State ZIP+4 Latitude Longitude Distance
1 TRAVIS R BILLINGTON MD SUPER DUPER 5 AUDREY PL STE 40415A FAIRFIELD NJ 07004-3401 40.876599 -74.290742 NULL
Here’s the code I have so far, parts of which work:
USE DirectSuccessDistance
DECLARE @DLat varchar(20)
DECLARE @DLon varchar(20)
DECLARE @RLat varchar(20)
DECLARE @RLon varchar(20)
DECLARE @Number int
DECLARE @MaxNumber int
DECLARE @Distance decimal (8,4)
SET @DLat = ‘39.5267’
SET @DLon = ‘-119.804’
SET @Distance = 10
SET @MaxNumber =
(SELECT MAX(Number) FROM Distance)
SET @Number = 1
WHILE @Number <= @MaxNumber
BEGIN
SET @RLat = (SELECT Latitude FROM Distance WHERE Number = @Number)
SET @RLon = (SELECT Longitude FROM Distance WHERE Number = @Number)
SELECT FullName AS [Full Name],
Add1,
Add2
City,
Zip,
dbo.DistanceFunc
(@DLAT,@RLat, @DLon, @RLat) AS Distance
FROM dbo.Distance
WHERE dbo.DistanceFunc
(@DLAT,@RLat, @DLon, @RLat)
<= @Distance
SET @Number = @Number + 1
END
ORDER BY Distance
;
If I insert values into the latitude and longitude, and take out everything designed to step through the list, I get either no output with "<= Distance", or every record with the same value in the distance field if I use ">= Distance”.
With all of the stepping stuff in there, the way it is shown above I come up with.
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword ‘ORDER’.
This is very close to what was on the final exam in a course in SQL I just took. But, now I?ve stumbled into this at the work place.
Any help would be greatly appreciated.
Thanks
Stephen
