CREATE PROCEDURE pr_Widgits_mid_SearchDistributors ( @inpt_DistanceFromLat numeric(12,0) = NULL OUTPUT, /* REQUIRED: The latitude of a user supplied address to do radial search from */ @inpt_DistanceFromLong numeric(12,0) = NULL OUTPUT, /* REQUIRED: The Longitude of a user supplied address to do radial search from */ @inpt_DistanceMaxMiles smallint = NULL, /* REQUIRED: On a radial search, the number of miles in the radius. */ @inpt_DistributorLastName varchar(30) = NULL, /* OPTIONAL: LIKE Mask for filtering by Provider Last Name */ @otpt_ResultRowCount int = NULL OUTPUT, /* OUTPUT: The number of rows returned by the search. */ @rslt_SysStatus int = NULL OUTPUT, /* The Outcome of the stored procedure. 1=success, 0=soft err, -1=hard err */ @rslt_SysMessage varchar(80) = NULL OUTPUT /* Message describing the reason for the outcome of the stored procedure. */ ) AS /*******************************************************************************************************************************************/ /* GEOQuery Radial Searching */ /* */ /* Best viewied with a monospace font: */ /* */ /*******************************************************************************************************************************************/ /* Change History: */ /* -------------- */ /* 10/01/2001 JIF Credit to go out to my friend Jason for his help with the initial radial distance functions */ /* 01/01/2002 KMP JIF Left the stud and I fixed all other problems like decimal calculation errors and added error handling */ /* 04/23/2002 KMP Converted code to sanitized format for public upload */ /* */ /*******************************************************************************************************************************************/ /* */ /* Assumptions: You have already calculated the Point of Origin (longitude,latitude) and you know how many mile that you want to search */ /* from this point of origin. */ /* */ /* 1> Generate Geocoding (latitude and longitude) data is easy with a couple of COM */ /* tools from the ActiveX controls available from Check Point http://www.ckpoint.com/ ($199) */ /* */ /* 2> ********** Then use the formulas gleamed from the following link to calculate distances to */ /* find Addresses within a certain radius very quickly for displaying as a list */ /* result on a web page. http://www.meridianworlddata.com/Distance-Calculation.asp */ /* note that the formula employeed below is an approximation */ /* */ /* 3> If you need more percise results for like nautical navigation see the following web page for JavaScript code that */ /* shows examples that you could easily re-code. http://www.wcrl.ars.usda.gov/cec/java/lat-long.htm */ /* */ /* 4> Then using the check point tool we can generate an overlay map with all the */ /* Distributors's listed as pinpoints on the map. Furthermore we can make the map */ /* interactive with click ability that would immediatly take you to the */ /* distributors address in a list below. */ /* */ /* 5> If you are using MapQuest */ /* */ /* 6> Alternativly some more information */ /* http://www.planet-source-code.com/xq/ASP/txtCodeId.10987/lngWId.1/qx/vb/scripts/ShowCode.htm */ /* This code/program will convert geographic location (ie latitude/longitude) from DMS format */ /* to decimal format and vice versa. Works pretty nicely. Works on earth's geography but */ /* can be extended to anything that is DMS based. Any bugs or recommendations can be */ /* sent to me at www. WarpEngine .com. */ /* */ /* 6> Also see http://www.4guysfromrolla.com/webtech/040100-1.shtml for a nice version that will allow you to */ /* create queries of the following form */ /* SELECT * */ /* FROM Locations */ /* WHERE Latitude <= [HighLatitude] */ /* AND Latitude >= [LowLatitude] */ /* AND Longitude >= [LowLongitude] */ /* AND Longitude <= [HighLongitude] */ /* This one is an excellent method and would be very fast and a perferred method */ /* But here you must do the calculations outside of SQL Sp's and in VB or VBS/ASP */ /* (Be warned though this example is accurate enough for most e-commerce web sites but not for navigation */ */ /* */ /*******************************************************************************************************************************************/ /****************************************/ /* Database Settings */ /****************************************/ SET NOCOUNT ON /****************************************/ /* Variable Declarations */ /****************************************/ Declare @inpt_DistanceFromLat1 numeric(12,5), @inpt_DistanceFromLong1 numeric(12,5) SELECT @otpt_ResultRowCount = 0 SELECT @inpt_DistanceFromLat1 = (@inpt_DistanceFromLat/10000) SELECT @inpt_DistanceFromLong1 = (@inpt_DistanceFromLong/10000) DECLARE @err_nbr int, /* The Error Number of the error (if any) that has occured on the last operation*/ @row_count int /* Number of Rows affected by last SQL operation. */ /****************************************/ /* Data Validation */ /****************************************/ IF (@inpt_DistanceFromLat IS NULL) BEGIN SELECT @rslt_SysMessage = 'Invalid Input: Latititude is required.' GOTO SOFT_ERROR END IF (@inpt_DistanceFromLong IS NULL) BEGIN SELECT @rslt_SysMessage = 'Invalid Input: Longitude is required.' GOTO SOFT_ERROR END /****************************************/ /* Data Scrub */ /****************************************/ IF @inpt_DistributorLastName is Null SELECT @inpt_DistributorLastName = '%' ELSE SELECT @inpt_DistributorLastName = @inpt_DistributorLastName + '%' /****************************************/ /* Perform Distributor Search */ /****************************************/ BEGIN SELECT distrib.DistributorID, LastName = distrib.LastName, FirstName = distrib.FirstName, Address1 = distrib.Address1, Address2 = distrib.Address2, City = distrib.City, State = distrib.State, Zip = distrib.ZipCode, County = distrib.County, BusinessPhone = distrib.BusinessPhone, DistanceMiles = SQRT(SQUARE(69.1 * (distrib.lat - @inpt_DistanceFromLat1)) + SQUARE(69.1 * (distrib.Long - @inpt_DistanceFromLong1) * COS(@inpt_DistanceFromLat1/57.3))), FROM Distributor distrib WHERE distrib.LastName Like @inpt_DistributorLastName AND SQRT(SQUARE(69.1 * (distrib.lat - @inpt_DistanceFromLat1)) + SQUARE(69.1 * (distrib.Long - @inpt_DistanceFromLong1) * COS(@inpt_DistanceFromLat1/57.3))) <= @inpt_DistanceMaxMiles ORDER BY DistanceMiles, distrib.LastName, distrib.FirstName, distrib.Address1 END SELECT @err_nbr = @@error, @row_count = @@rowcount SELECT @otpt_ResultRowCount = @row_count IF (@err_nbr <> 0) BEGIN SELECT @rslt_SysMessage = 'Error reading table.' GOTO HARD_ERROR END IF (@row_count = 0) BEGIN SELECT @rslt_SysMessage = 'No records found for the search parameters supplied ' GOTO SOFT_ERROR END /****************************************/ /* Error Handling Code */ /****************************************/ SUCCESS: SELECT @rslt_SysStatus = 1 RETURN @otpt_ResultRowCount SOFT_ERROR: SELECT @rslt_SysStatus = 0 RETURN 0 HARD_ERROR: SELECT @rslt_SysStatus = -1 RETURN -1 GO