Solved

# Convert Radius Search Into Function

Posted on 2009-04-17
154 Views
I need to convert the attached code into a function that will return me a table where each row is a zipcode that matches the search.  I'm then going to use that data to build a sql statement against my main table like this

where ZipCode in (select Zip from dbo.fnZipCodeRadiusSearch('33611','150'))

The code i'm attaching is not complete.  It doesn't contain any of the code that returns the table of zipcodes
``````ALTER PROCEDURE dbo.GetLocations

(

@ZipCode char(11),

@Miles int

)

AS

/* SET NOCOUNT ON */

Declare @HighLatitude float

Declare @LowLatitude float

Declare @HighLongitude float

Declare @LowLongitude float

Declare @StartLatitude float

Declare @StartLongitude float

Declare @LatitudeRange float

Declare @LongitudeRange float

SELECT @StartLatitude = Latitude, @StartLongitude = Longitude FROM ZipCodes WHERE POSTALCODE = @Zipcode

Set @LongitudeRange = @Miles / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60)

Set @LatitudeRange = @Miles / 69.045454545454545454545454545455

Set @LowLatitude = @StartLatitude - @LatitudeRange

Set @HighLatitude = @StartLatitude + @LatitudeRange

Set @LowLongitude = @StartLongitude - @LongitudeRange

Set @HighLongitude = @StartLongitude + @LongitudeRange

Insert into @Table

SELECT ZipCode FROM ZipCodes WHERE (Latitude <= @HighLatitude AND Latitude >= @LowLatitude)  AND (Longitude <= @HighLongitude AND Longitude >= @LowLongitude))

RETURN @Table
``````
0
Question by:davidcahan

LVL 26

Accepted Solution

See if this works for you.
``````SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

(

@ZipCode char(11),

@Miles int

)

RETURNS

@Table_Var TABLE (ZipCode Char(11))

AS

BEGIN

/* SET NOCOUNT ON */

Declare @HighLatitude float

Declare @LowLatitude float

Declare @HighLongitude float

Declare @LowLongitude float

Declare @StartLatitude float

Declare @StartLongitude float

Declare @LatitudeRange float

Declare @LongitudeRange float

SELECT @StartLatitude = Latitude, @StartLongitude = Longitude FROM ZipCodes WHERE POSTALCODE = @Zipcode

Set @LongitudeRange = @Miles / (((Cos(@StartLatitude * pi() / 180) * 6076.0) / 5280.0) * 60)

Set @LatitudeRange = @Miles / 69.045454545454545454545454545455

Set @LowLatitude = @StartLatitude - @LatitudeRange

Set @HighLatitude = @StartLatitude + @LatitudeRange

Set @LowLongitude = @StartLongitude - @LongitudeRange

Set @HighLongitude = @StartLongitude + @LongitudeRange

Insert into @Table_Var

SELECT ZipCode FROM ZipCodes WHERE (Latitude <= @HighLatitude AND Latitude >= @LowLatitude)  AND (Longitude <= @HighLongitude AND Longitude >= @LowLongitude))

RETURN

END

GO
``````
0

LVL 22

Expert Comment

Are you planning on putting the SQL you plan to build in a stored procedure?  Or, are you planning on using this function when you create dynamic SQL in your application?
If it is the former, why not include the code you provided in the procedure and create a temp table that you just join to in the SQL statement against your main table?
0

Author Comment

with a little modification it worked great.  thanks!!!!
0

LVL 26

Expert Comment

Glad you could get it to work.  For my curiosity, what did you have to modify?  Since I don't have your database I could not test it.
0

Author Comment

you had an extra parenthesis at the end.  The only other things I had to change were columns and table names to match my DB.
0

LVL 26

Expert Comment

Thanks for the feedback.
0

## Featured Post

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…