13,093,097 members (65,339 online)
Rate this:
See more:
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
As
Begin
Return (Select @StartLatitude + Sqrt(@Distance * @Distance / 4766.8999155991))
End```

longitude function:
```ALTER FUNCTION [dbo].[LongitudePlusDistance]
(
@StartLongitude float,
@StartLatitude float,
@Distance float
)
RETURNS Float
AS
begin

RETURN (select @startLongitude + sqrt(@Distance * @Distance/(4784.39411916406*Cos(2*@StartLatitude/114.591559026165)*Cos(2*@StartLatitude/114.591559026165))))
END```

calculatedistance:
```ALTER Function [dbo].[CalculateDistance]
(@Longitude1 Decimal(8,5),
@Latitude1   Decimal(8,5),
@Longitude2  Decimal(8,5),
@Latitude2   Decimal(8,5))
Returns Float
As
Begin
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) )

End
```

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 17:56pm

Rate this:

## Solution 1

For starters, did you mean to write
`WHERE dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude) As Distance <= @Distance`
Or
`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 number...it 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!

Top Experts
Last 24hrsThis month
 OriginalGriff 299 Graeme_Grant 198 Richard Deeming 150 RickZeeland 130 ProgramFOX 130
 OriginalGriff 3,911 Graeme_Grant 2,152 ProgramFOX 2,017 ppolymorphe 1,666 Jochen Arndt 1,645