Click here to Skip to main content
11,927,324 members (51,607 online)
Rate this:
Please Sign up or sign in to vote.
See more: SQL
I have a table

persons that has a column zipcode

that is related to a zipcodes table that has the zipcode as the id and a column for latitudes and longitudes.

I found a series of functions that calculate distance on a sphere (basically for zipcodes) using latitude and longitude. I'm rather new to sql and was wondering how to use these functions in a stored procedure.

latitude function:
ALTER Function [dbo].[LatitudePlusDistance](@StartLatitude Float, @Distance Float) Returns Float
        Return (Select @StartLatitude + Sqrt(@Distance * @Distance / 4766.8999155991))

longitude function:
ALTER FUNCTION [dbo].[LongitudePlusDistance]
		@StartLongitude float,
		@StartLatitude float,
		@Distance float
	RETURN (select @startLongitude + sqrt(@Distance * @Distance/(4784.39411916406*Cos(2*@StartLatitude/114.591559026165)*Cos(2*@StartLatitude/114.591559026165))))

ALTER Function [dbo].[CalculateDistance]
       (@Longitude1 Decimal(8,5),
       @Latitude1   Decimal(8,5),
       @Longitude2  Decimal(8,5),
       @Latitude2   Decimal(8,5))
   Returns Float
   Declare @Temp Float
   Set @Temp = sin(@Latitude1/57.2957795130823) * sin(@Latitude2/57.2957795130823) + cos(@Latitude1/57.2957795130823) * cos(@Latitude2/57.2957795130823) * cos(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823)
   if @Temp > 1
       Set @Temp = 1
   Else If @Temp < -1
       Set @Temp = -1
   Return (3958.75586574 * acos(@Temp) )

crappy query attempt:
Declare @Longitude Decimal(8,5)
    Declare @Latitude Decimal(8,5)
    Select  @Longitude = Longitude,
            @Latitude = Latitude
    From    ZipCodes
    Where   ZipCode = '20013'
Declare @Distance int
    Select  persons.personName, ZipCodes.City,  dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude) As Distance
    From    persons
            Inner Join ZipCodes
                On persons.zipcode = ZipCodes.ZipCode
    Order By dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude)
WHERE dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude) As Distance <= @Distance

I tried this just to filter the persons outside a parameterized search radius and it doesn't work. says there's an "incorrect syntax near the keyword "where"

that doesn't even include my desire to throw in a parameter for the zipcode so instead of:

where zipcode = '20013'

i'd like something like:

where zipcode = @zipcode

but it says i need to declare the zscalar variable @zipcode and no matter where i try to do that...i keep getting the same error
Posted 3-Jul-13 18:56pm

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

For starters, did you mean to write
WHERE dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude) As Distance <= @Distance
WHERE dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude) And Distance <= @Distance

If you want to use a parametrized query for @ZipCode, then you need to provide the parameter via a DECLARE just like any other variable - SQL meeting a variable it doesn't know won't prompt you for it's value!
memberxxxxxxxxxxxxxxxxx 4-Jul-13 3:38am
I was trying anything and everything i could to try to make it work. i tried the first one, not the second. it didn't work. my latest attempt was:

WHERE dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude) <= @Distance

and that didn't work.

i'm using vb web developer and trying all this out in the "new query" wizard. i'm expecting the query to ask me for a value for the distance, but it's not and simply returning an empty set. ie no rows, i know very little about sql so i don't want to get the terminology wrong.

i've left off the zipcodes part because that was another source of frustration. i've tried declaring it and replacing the '20013' with @zipcode, but i kept getting an error saying that i had to declare the scalar variable even though i declared it right under the declare for the lat and longitude
OriginalGriff 4-Jul-13 4:18am
SQL will not ask you for info. It will just complain when you don't provide it. Remember that SQL Server is not intended to interface with users directly - it expects to be the "back end" and interface with your application, which interfaces with the user.

What are you trying to achieve here? Because I can't help thinking you have gone down the wrong road! :laugh:
memberxxxxxxxxxxxxxxxxx 4-Jul-13 5:14am
lol it wouldn't surprise me. basically, i have a table of people, one of the columns has their zipcode. that zipcode is tied to a zipcodes table that has the zipcode, city, state, latitude and longitude. i'd like to add a filter that retrieves people within a radius of a specified zipcode, but i'd like to be able to put in the radius and the zipcode.

so i found those functions and tried incorporating them in my site, but i don't know much about sql and ran into the wall.

like i said, i'm using vb web developer. there's a query designer that sort of allows me to test queries and input whatever for variables, but when i try to run the query, nothing happens, just returns the columns, but no data. if i take out the @Distance and replace it with a works.

this is where i got the function:

i tried modifying the sample query for stores lol. not successfully though.
OriginalGriff 4-Jul-13 5:33am
So create a Function or Stored Procedure that takes two parameters: @Zipcode and @Distance: it then looks a lot like the code the site provided you, but it accepts the zip and distance as parameters, and returns the SELECT List. You can't do it directly in SQL as a query - because a query is self contained where an SP expects parameters. (It's also easier to use in your VB code than a big query each time)
memberxxxxxxxxxxxxxxxxx 6-Jul-13 15:15pm
thanks i appreciate the help. worked out just as you said. just needed to run it in a stored procedure
OriginalGriff 6-Jul-13 15:33pm
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web04 | 2.8.151126.1 | Last Updated 4 Jul 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100