Click here to Skip to main content
Rate this: bad
good
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 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 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 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 406
1 Marcin Kozub 225
2 Sergey Alexandrovich Kryukov 205
3 Raul Iloc 170
4 Maciej Los 164
0 OriginalGriff 8,289
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,624
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 4 Jul 2013
Copyright © CodeProject, 1999-2014
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