Trouble with variables and function

Question

Trouble with variables and function

stepsimon

Grasshopper

Points: 20
More actions
January 30, 2009 at 2:58 pm

#127705

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” .
NumberFull NameCompanyAdd1Add2CityStateZIP+4LatitudeLongitudeDistance
1TRAVIS R BILLINGTON MDSUPER DUPER5 AUDREY PL STE 40415AFAIRFIELDNJ07004-340140.876599-74.290742NULL
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”.
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

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

Well this is easy enough to fix, but it sure does sound like homework. Could you please tell us what the business purpose of this query is? I would be especially interested to learn why you are using Lat/Lon ratios that are incorrect for the state of NJ.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

stepsimon Grasshopper Points: 20 More actions · Answer 2

Thanks for your reply.

It is a lot like home work.:w00t:

That's what got me into this trouble in the first place.

I just finished a SQL Server course at Kaplan University where something very close to this was on the final. Plenty close enough that I could see this is what I needed for work.

I work for a bulk mail company, processing data. axisdirect.org, I designed the web page. It's not much, but there it is. No, the .org was not my idea. One of our newer clients wants us to be able to pare down the mailing lists they send us, by selecting a radius around the store they are doing the mailing for.

I don't no why the list provider is not doing this for them. But, I'm definitely trying to make the most of every opportunity I get to generate income for the company.

The mailing software we use provides me with the latitudes and longitudes. It will figure the distance from the center of a given zip code, but not from a specific set of coordinates. Why I have the wrong coordinates for NJ, I don't know. I'm glad you noticed it before my boss:)

I'm attaching a .jpg of my Kaplan homepage showing the Advanced SQL is in the past.

Hope you can help me.

Thanks

Stephen

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

OK, fair enough.

First, the ORDER BY is giving an error because you cannot put it there. In fact, it cannot be applied (directly) to the output of a script or stored proecedure. An ORDER BY can only by applied to a query (SELECT statement).

Secondly, you do not want to use a loop here. Not only is it grossly slow, it is also what is preventing you from applying your ORDER BY clause. What you want to do is to use a single query for everything.

Thirdly, you are using @RLat twice in your "dbo.DistanceFunc()" call and @RLon not at all.

Try this script instead:

DECLARE @DLat varchar(20)

DECLARE @DLon varchar(20)

SET @DLat = '39.5267'

SET @DLon = '-119.804'

SELECT FullName AS [Full Name]

, Add1

, Add2

, City

, Zip

, dbo.DistanceFunc(@DLAT,Latitude, @DLon, Longitude) AS Distance

From Distance

Order By Distance

Much simpler, Yes? Faster too. That's the beauty of Set-based programming.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

stepsimon Grasshopper Points: 20 More actions · Answer 4

That completely did the trick.

A clear case of not being able to see the forest for the trees.

I really appreciate your help.

Thanks

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Glad I could help.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004696 More actions · Answer 6

Heh... Barry graduated from the school of "Right Now" and teaches from the good book of "Boy Howdy!" 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Heh. Darn tootin'.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

I'm glad you bumbed this Jeff, because I had meant to get back to the OP with a better function to calculate, then forgot :(, ...

Stephen:

Here is a Much better function to calculate distances from Lat/Lon parameters:

CREATE Function [dbo].[CalculateDistance](

@Latitude1 Float,

@Latitude2 Float,

@Longitude1 Float,

@Longitude2 Float

) Returns Float

/*

Faster way to calculate distance in miles using Latitude & Longitude. This

is accurate in miles to about 4.5 decimal places.

NOTE: 57.295779513082323 = SELECT 180.0 / PI()

*/ As

Begin

/* tests:

select dbo.calculateDistance(31.0, -93.0, 31.1, -93.0) --should be 6.9169 miles

select dbo.calculateDistance(31.0, -93.0, 31.0, -93.1) --should be 5.9290 miles

select dbo.calculateDistance(20.0, -93.0, 20.0, -93.1) --should be 6.4998 miles

select dbo.calculateDistance(40.0, -93.0, 40.0, -93.1) --should be 5.2987 miles

*/

Return 3963.0*acos(

sin(@Latitude1/57.295779513082323)

* sin(@Latitude2/57.295779513082323)

+ cos(@Latitude1/57.295779513082323)

* cos(@Latitude2/57.295779513082323)

* cos(@Longitude2/57.295779513082323 - @Longitude1/57.295779513082323))

End

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

For Stephen or anybody else who may be interested, here is a previous thread where Jeff, andrewd.smith, myself and others discuss the finer points of Latitude/Longitude calculations and searchs with SQL: http://www.sqlservercentral.com/Forums/Topic609716-338-1.aspx.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004696 More actions · Answer 10

RBarryYoung (1/31/2009)
For Stephen or anybody else who may be interested, here is a previous thread where Jeff, andrewd.smith, myself and others discuss the finer points of Latitude/Longitude calculations and searchs with SQL: http://www.sqlservercentral.com/Forums/Topic609716-338-1.aspx.

Aw... DUDE! Not THAT post... :blush:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004696 More actions · Answer 11

Jeff Moden (1/31/2009)
RBarryYoung (1/31/2009)
For Stephen or anybody else who may be interested, here is a previous thread where Jeff, andrewd.smith, myself and others discuss the finer points of Latitude/Longitude calculations and searchs with SQL: http://www.sqlservercentral.com/Forums/Topic609716-338-1.aspx.
Aw... DUDE! Not THAT post... :blush:

Nothing like a link to a senior moment... 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Sorry Jeff. 🙂 There is some good stuff there...

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004696 More actions · Answer 13

RBarryYoung (1/31/2009)
Sorry Jeff. 🙂 There is some good stuff there...

Heh... NP... just remember when it happens, the Force flows in more than 1 direction. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

"Impressive. Most impressive."

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]