Click here to Skip to main content
12,078,795 members (47,924 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
    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 18:56pm

1 solution

Rate this: bad
 
good
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
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!
  Permalink  
Comments
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:
http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-zipcode-latitude-longitude-pr

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
Web01 | 2.8.160212.1 | Last Updated 4 Jul 2013
Copyright © CodeProject, 1999-2016
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