Zip Code Radius Results: Pull Customer Records from View 1

[JSHoltsIT]

I have a database of all US Zip codes. I've put together syntax to pull all zip codes from a radius of miles. I have two additional tasks to perform:

1. Calculate distance from each record returned to the original zip code
2. Use the result set from the query below to pull customer data from a view (v_customer_info_by_zipcode)

TIA

Jason

declare @distance int, @targetZip int
select @distance = 50
select @targetZip = 21223

SELECT DISTINCT t1.City, t1.State, t1.ZipCode, t1.Latitude, t1.Longitude
FROM ZipCodeDatabase t1
JOIN ZipCodeDatabase t2 ON (3963.0*acos(sin(t1.Latitude/(180/PI())) * sin(t2.Latitude/(180/PI())) + cos(t1.Latitude/(180/PI())) * cos(t2.Latitude/(180/PI())) * cos(t2.Longitude/(180/PI())-t1.Longitude/(180/PI())))<@distance)
WHERE t2.ZipCode=@targetZip
ORDER BY t1.ZipCode

 

[gmmastros]

I strongly encourage you to read this: thread183-1197121

I KNOW that you can speed this up by using the 'bounding box' technique, which will allow you to narrow down the results a lot faster. Performing a lot of trig calculations (sin and cos) will be slow.

That being said... You can modify your query to accomplish your goals. Something like this....

Select v_customer_info_by_zipcode.*, A.Distance
From   v_customer_info_by_zipcode
       Inner Join (
          SELECT DISTINCT t1.ZipCode,
                 3963.0*acos(sin(t1.Latitude/(180/PI())) * sin(t2.Latitude/(180/PI())) + cos(t1.Latitude/(180/PI())) * cos(t2.Latitude/(180/PI())) *  cos(t2.Longitude/(180/PI())-t1.Longitude/(180/PI()))) As Distance
          FROM ZipCodeDatabase t1
          JOIN ZipCodeDatabase t2 ON (3963.0*acos(sin(t1.Latitude/(180/PI())) * sin(t2.Latitude/(180/PI())) + cos(t1.Latitude/(180/PI())) * cos(t2.Latitude/(180/PI())) *  cos(t2.Longitude/(180/PI())-t1.Longitude/(180/PI())))<@distance)
          WHERE t2.ZipCode=@targetZip
          ) As A
          On v_customer_info_by_zipcode.ZipCode = A.ZipCode
ORDER BY A.Distance

But seriously... you should probably modify this to reduce the number of calculations, which will effectively speed up the query.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[JSHoltsIT]

Thanks! (it works)

I will study the link and look to improve upon my coding.

Jason

 

[codezilla94]

Here is a simple yet effective zip code radius search algorithm. The solution contains a Stored Procedure that accepts a zip code and mile radius and returns all zip codes within that radius. (Example: Give me all the zip codes within a 10-mile radius of 50325). The Solution comes with and a dataload script that will create a SQL Server database, the Stored Procedure containing the algorithm, and load over 42,000 zip codes.

http://www.spikesolutions.net/ViewSolution.aspx?ID=1e7d197c-7441-40f4-8e87-5ecc3ef5ab60