Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 168
  • Last Modified:

Convert Radius Search Into Function

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

Open in new window

0
davidcahan
Asked:
davidcahan
  • 3
  • 2
1 Solution
 
Chris LuttrellSenior Database ArchitectCommented:
See if this works for you.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION fnZipCodeRadiusSearch 
(
	@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

Open in new window

0
 
8080_DiverCommented:
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
 
davidcahanAuthor Commented:
with a little modification it worked great.  thanks!!!!
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
Chris LuttrellSenior Database ArchitectCommented:
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
 
davidcahanAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
Thanks for the feedback.
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now