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.

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  July 28, 2009 at 8:12 am

  #1031034

  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

  July 28, 2009 at 8:41 am

  #1031068

  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

  July 28, 2009 at 8:43 am

  #1031069

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

  More actions

  July 28, 2009 at 8:50 am

  #1031077

  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

  July 28, 2009 at 9:36 am

  #1031125

  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.