Need help locating my problem

Question

Post reply

Need help locating my problem

dtupitza

Valued Member

Points: 52
More actions
July 28, 2009 at 7:48 am

#95883

Alright, I'm trying to do a lookup of the closest place in the database depending on the zipcode you enter. Here I'm manually typing in 15205 but on a bigger scale I'm going to be using a variable there.
I'm getting the lat and lng of the zipcode entered from the zipCodeLookup. Using a simple formula i'm comparing the lat and lng in another table (EatonDirectory) to find the closest location from the spot I'm currently at.
This is what I have so far. From what I see everything should work. Hopefully someone can point me in a better direction because I get the following errors
msg 156, level 15, state 1, Line 2
Incorrect syntax near the keyword 'SELECT'.
msg 102, level 15, state 1, Line 2
Incorrect syntax near ')'.
msg 102, level 15, state 1, Line 5
Incorrect syntax near ')'.
msg 102, level 15, state 1, Line 7
Incorrect syntax near ')'.
SELECT *, (3959 * acos( cos(radians
(SELECT lng FROM zipCodeLookup WHERE zipcode = '15205'))
* cos(radians(lat))
* cos(radians(lng) - radians
(SELECT lat FROM zipCodeLookup WHERE zipcode = '15205'))
+ sin (radians
(SELECT lng FROM zipCodeLookup WHERE zipcode = '15205'))
* sin (radians (lat))))
AS distance FROM EatonDirectory having distance < 100
ORDER BY distance limit 1;[/code]
Any help would be much appreciated.

Viewing 6 posts - 1 through 5 (of 5 total)

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

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 1

I don't think you can use the select statement in the radians function. Try using a variable and see if it works.

DECLARE @lng float

Set @lng = (SELECT lng FROM zipCodeLookup WHERE zipcode = '15205')

SELECT *, (3959 * acos( cos(radians

(@lng))

* cos(radians(lat))

* cos(radians(lng) - radians

(@lng))

+ sin (radians

(@lng))

* sin (radians (lat))))

AS distance

FROM EatonDirectory

HAVING distance < 100

matt6288 SSCertifiable Points: 5058 More actions · Answer 2

SELECT *, (3959 * acos( cos(radians

((SELECT lng FROM zipCodeLookup WHERE zipcode = '15205')))

* cos(radians(lat))

* cos(radians(lng) - radians

((SELECT lat FROM zipCodeLookup WHERE zipcode = '15205')))

+ sin (radians

((SELECT lng FROM zipCodeLookup WHERE zipcode = '15205')))

* sin (radians (lat))))

AS distance FROM EatonDirectory having distance < 100

ORDER BY distance limit 1;

I got the radians function to work correctly by adding a second set of () around the select statement. Try that and see if it works for you.

dtupitza Valued Member Points: 52 More actions · Answer 3

Thanks a lot, they both worked perfectly.

Unfortunitely I'm now finding my math was bad.

Don't suppose anyone is good at math around here that can help me out 🙂

Lowell SSC Guru Points: 323506 More actions · Answer 4

i have this saved in my snippets,it's supposed to find all zip codes within a given distance, assuming you have a table with zipcode, lattitude,longitude in it:

hope this helps:

/*

Returns zip codes within specified range.

*/

CREATE Procedure sp_ZipCode_Range

(

@ZipCode Numeric(5, 0) = Null,

@Miles Float

)

As

set nocount on

Declare @Latitude Float(10)

Declare @Longitude Float(10)

-- Lookup longitude, latitude for zip codes

Select @Latitude = Latitude, @Longitude = Longitude From State Where Zip = @ZipCode

Select

Zip, Zip_Name,

-- Zip_Name,

Avg(3958.75 * ACos(Sin(@Latitude/57.2958) *

Sin(Latitude/57.2958) +

Cos(@Latitude/57.2958) *

Cos(Latitude/57.2958) *

Cos(Longitude/57.2958 - @Longitude/57.2958))) As Miles

From

State

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

dtupitza Valued Member Points: 52 More actions · Answer 5

Thanks a lot, it works perfect.

I needed to create a stored procedure with the code so this was even a +

Thanks everyone for all the help.