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