12,399,799 members (48,332 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
 ppolymorphe 375 OriginalGriff 265 Vincent Maverick Durano 265 0x01AA 195 Karthik Bangalore 155
 OriginalGriff 6,723 ppolymorphe 2,835 Karthik Bangalore 2,707 Richard MacCutchan 2,057 F-ES Sitecore 2,052