Web Development

Ask something
Question Locked

Stuck in SQL

0 helpful
3 replies

by stepsimon

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

3 replies

by tony hopkinson

inside the loop you need to store it

Create a temporary table that will hold the output you want.

Insert into #MyTempTable SElect ...
inside your loop

When the loop exits, select from the temp table with the Order By

You'll at least be able to see what's going on then.

By the way this is a very expensive way of doing what you want (wahtever that is???)
As soon as you are using while loops or cursors you don't want to be doing them too often.

I can't actually figure out from the above what you want to do though.

It looks to me like you could simply do

Select ..., dbo.DistanceFunc(@DLAt,@DLon,Latitude,Longitude) as Distance
From Distance

To get < @Distance and Order

Select * From (Put query above in here) alldist
Where Distance < @Distance
Order By Distance

Might as well get them all, as it's going to compute distance for every one anyway

Another way to do it, would be to compute a reasonable boundary for the target coordinate.

Anything within 10 must fit in circle of that radius. If you 'draw' a square round that, it gives you a bounding set of lat and longs, and you can chop out all those that definitely can't be within @Distance units

With a where lat >= NorthLat and Lat <=Southlat ... long <= EastLong etc.

Be careful if you could go over a 0, a few if staments should get you the range of lats and longs that could satisfy the requirements

Five calcs and the only ones that < @distance will filter out will be those in the 'corners'

HtHs

by dr dij

the LEAST SQUARES algorithm you are are using is WRONG!

It is a very gross approximation that, with the mulitplier factor will ONLY WORK AT ONE LATITUDE. You will get 50 miles for the same diffence in lat/lon pairs at high latitudes vs 500 miles near the equator.

This is because the earth is a sphere, not a flat plane. The longitude lines are much closer in Alaska than at the equator, or in Texas or Florida.

USE SPHERICAL TRIG FUNCTION!

It is only about 3 lines long.

If you google for "spherical" +distance you will find several sites with formulas.

Melissadata.com, which sells data for zip codes that contains lat, long for the centroid of that zip code, has a .PDF with the formula and a programming implementation in 3 lines:

http://www.melissadata.com/manuals/mp4man.pdf

(page 255, search for spherical)

This way, with a database of zip codes you can calculate distance between zip codes. Of course if you have lat, lon to a street level even more accurate tho most won't need that, e.g. to find nearest dealer is most likely accurate based on zip code only.

Several websites, including melissa data and an indonesian site have online where you can verify your results in miles by typing in two city names anywhere in the world.

Also trap the prog so you don't send identical pairs (e.g. same zip code producing same lat/lon) so you don't get divide by zero or trig functions approaching zero. Simply set the return distance to zero and skip the algorithm if this happens.

This discussion is closed to new replies.