Click here to Skip to main content
11,802,389 members (29,646 online)
Rate this: bad
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 17: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 at 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 at 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 at 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 at 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 at 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 at 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
0 OriginalGriff 475
1 KrunalRohit 366
2 F-ES Sitecore 295
3 Afzaal Ahmad Zeeshan 230
4 CHill60 220
0 OriginalGriff 2,950
1 Maciej Los 1,910
2 KrunalRohit 1,862
3 CPallini 1,645
4 Richard MacCutchan 1,157

Advertise | Privacy | Mobile
Web03 | 2.8.151002.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