zip code proximity search

[kristinac13]

I am trying to create a search page for users to search for our affiliates based on their zip code. I want to retrieve all affiliates within a 50 mile radius. I have looked all over the web and must admit I have no idea where to even start. I've seen everything but an idea of how to do it in VB6. Has anyone written a search like this or could anyone give me an idea where to start?

 

[Sheco]

seems like you'd need a database

 

[kristinac13]

Yes I have a database in SQL that will hold my affiliates and zip codes, and I've found zip code databases available online. It's the actual procedure or function to calculate which ones to retrieve that I can't seem to figure out.

 

[Sheco]

Does your zip code database include a longitude and latitude values that you could use to calculate distance?

 

[Sheco]

Check out the following link...

http://www.freevbcode.com/ShowCode.asp?ID=5532

it was the first google hit with search term: vb calculate distance longitude latitude

It is in VBScript but that should be easy enough to translate.

 

[gmmastros]

>> I have a database in SQL

Do you mean Microsoft SQL Server? If so, here is a User Defined Function that will calculate distances for you based on Latitude/Longitudes.

CREATE Function dbo.CalculateDistance(@Longitude1 Float, @Latitude1 Float, @Longitude2 Float, @Latitude2 Float)
Returns Float
AS
Begin
 Declare @DeltaX Float
 Declare @DeltaY Float
 Declare @DeltaXMeters Float
 Declare @DeltaYMeters Float
 Declare @MetersPerDegreeLong Float
 Declare @CenterY Float
 Declare @gEARTH_CIRCUM_METRES Float
 
 Set @gEARTH_CIRCUM_METRES = 6378007 * 2 * PI()

 Set @DeltaX = Abs(@Longitude2 - @Longitude1)
 Set @DeltaY = Abs(@Latitude2 - @Latitude1)
 Set @CenterY = (@Latitude1 + @Latitude2) / 2
 Set @MetersPerDegreeLong = (Cos(@CenterY * (PI()/ 180)) * @gEARTH_CIRCUM_METRES) / 360   --dbo.MetresPerDegreeLong(@CenterY)
 Set @DeltaXMeters = @DeltaX * @MetersPerDegreeLong
 Set @DeltaYMeters = @DeltaY * 111113.519

 Return Sqrt(@DeltaXMeters * @DeltaXMeters + @DeltaYMeters * @DeltaYMeters) / 1609.344
 
End

>> search for our affiliates based on their zip code

How many affiliates do you have? I don't need an exact number, but is it 10, 100, 1000, 20000. It makes a difference in how you write the query.

If it's a relatively small number of affiliates, you could do something like....

select Top 1 Affiliate,
       dbo.CalculateDistance(Affiliate.Longitude, Affiliate.Latitude, @Longitude, @Latitude)
From   AffiliateTable
Order BY dbo.CalculateDistance(Affiliate.Longitude, Affiliate.Latitude, @Longitude, @Latitude)

If this code is too slow for you, let me know. There are ways to optimize it (like limiting the records to search on, for example).

Hope this helps.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

This assumes you have the latitude and longitude for your affiliates. I assume you know where your affiliates are located (by street address). If you need to get their Latitude/Longitude, you could use this method.

[URL unfurl="true"]http://rpc.geocoder.us/service/rest?address=1600[/URL] Pennsylvania Avenue NW, Washington, DC

This example shows the address for the white house (because I didn't want to use my address ).

This will return XML that includes latitude and longitude.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[TysonLPrice]

I worked at a place that scheduled medical appointments and one of the requirements was to find specialist within fifty miles of the claimant as a government requirement. It's not an exact science. We used a product called ZipFind to get zip codes within a fifty mile radius and then MapPoint to provide directions. The problem arises that finding by zip code is as the "crow flies", ie not taking turns, hills, etc. into consideration. Also if it is a large zip code area the city might be miles away from the beginning of the zip code area.

For instance ZipFind would say a particular zone is within fifty miles but the actual distance from A to B might be 75 or more.

Not much help but it needs to be a consideration.

 

[kristinac13]

Thank you so much for the replies! I so appreciate the help. So it looks like I need latitude and longitude for my affiliates in order to do this correctly. That one link is great for finding them. We have over 1600 affiliates so my next issue would be finding and storing the latitude and longitude for each. Obviously I can't go thru each one one at a time. But if I have a table in my database (I did mean Microsoft SQL Server yes) that holds this info then the code here, either the VB or the SQL, should work yes? Is there a way other than figuring lat and long for each affiliate that I'm not catching? Again thank you all so much for replying.

 

[gmmastros]

It all depends on how accurate you must be. If you have the center point for each zipcode, you could 'assume' your affiliate is at that location. Unfortunately, this will add more innacuracies in to your algorithm because you are already assuming that the customer is located at the center point of the zipcode (which can't be true for all the inhabitants of a zip code ).

Think about it. Suppose you were trying to find the nearest wal-mart for your customers. If a walmart exists in the same zipcode as the customer, but there is another walmart closer (but in a different zipcode) you will get the wrong store. Tyson makes a good point about the 'crow fly' problem.

Only you can decide on the accuracy you need.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Zathras]

With over 1600 affiliates, the trick is to limit the number you have to test for the 50-mile radius. I am assuming you already have (or will have) the lat/long for the affiliates and the possible zip codes. This algorithm reduces the number of affiliates to test by an order of magnitude or more:

1. Imagine a grid overlay of squares with 50-mile sides.

2. Number the grid squares any way you want.

3. Create a function that returns the grid square number for a given lat/long.

4. Store the grid number in your table of affiliates along with the lat/long.

5. Create another function that returns a list of up to nine grid numbers for any given grid number. These would be the given grid number and the surrounding grids in a 3x3 matrix. (Be careful for boundary wrapping conditions. You don't want to list a Georgia store for a California location.)

6. When given a zip code, use the first function to obtain the grid number. Then use the second to get the nine grid numbers of interest.

7. Retrieve the affiliates with the nine grid numbers in the list and test them for 50 mile distance.

8. To allow for crow-flight vs. actual distance, use a multiplier. 1.2 is typical. so list affiliates within a 50/1.2 or 41.67 mile radius.

 

[kristinac13]

Okay judging by what I've been told, the guideline is any zip code within 50 miles of the zip code of the user. I am going to double check with my boss about the level of accuracy they need, but it sounds like all that matters is that the zip code of the affiliate is within 50 miles of the user's zip code. If that is the case, I need the latitude and longitude of the central point of the zip code itself correct? That would make the list smaller as many affiliates are in the same zip codes. Is there a way similar to that link for addresses you gave me to find the lat and long for zip code only?
Again thank you so much for helping me.

 

[gmmastros]

http://sourceforge.net/project/showfiles.php?group_id=111073&package_id=120039

This is a link to a download page. You can download a zip file (that contains a csv file) listing all the zip codes with lat/long coordinates.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kristinac13]

You're the heat George. Thank you so much I'll try that!

 

[kristinac13]

One more question and please forgive my being a pain. I see the code here is for calculating distances between two locations. Now that I have a latitude longitude table with my zip codes, can I request from SQL not the distance BETWEEN two but any that fall within 50 miles of a given location? I have found some code online but again it seems to focus on the distance between two points. I see Zathras is explaining something like what I'm trying, but I'm afraid I'm a little green...

 

[Zathras]

If you want to use pure SQL, you could create a zip-code table that has rows for each combination of zipcode and surrounding grid number. Then join that to the affiliate table on grid number. You still would need to write the function to calculate the distance, but the resulting query could look something like this:
[tt]
SELECT a.affiliate, a.city, a.state
FROM zipcodes z
INNER JOIN affiliates a on z.grid=a.grid
WHERE z.zipcode = @zipcode
AND fnDistance(a.lat,a.long,z.lat,z.long) < 50
[/tt]
It would take a bit of work to set up the zipcodes file, but that's a one-time job. The fnDistance function would be invoked for probably fewer than 150 affiliates.

You might want to try it first without the grid overlay to see if it is fast enough for you. In that case the SQL would look something like this (only one record in zipcodes per zip code):
[tt]
SELECT a.affiliate, a.city, a.state
FROM zipcodes z, affiliates a
WHERE z.zipcode = @zipcode
AND fnDistance(a.lat,a.long,z.lat,z.long) < 50
[/tt]
The fnDistance function would be invoked for all 1600 affiliates in this case.

 

[TysonLPrice]

Here's a link to zipfind. It's under $200.00. Might be cost effective if your making big bucks

http://www.zipfindlocator.com/ZipFindDeluxe.aspx

 

[gmmastros]

Let's try to be a little more specific.

What do we know? Do we know a ZipCode? We are looking for all affiliates within 50 miles? Do you want to do the work within SQL Server?

I think the approach to take would be to identify the zip codes that you are looking for. Then, join with your affiliates table on zip code. This is not so easy for a beginner, but I'll show you how this can be done.

Create Procedure GetAffiliatesWithin50Miles
  @ZipCode Integer
As
SET NOCOUNT ON

Declare @Latitude Decimal(10,6)
Declare @Longitude Decimal(10,6)

Select @Latitude = Latitude,
       @Longitude = Longitude
From   ZipCodeTable
Where  ZipCode = @ZipCode

Select ZipCode
Into   #Zips
From   (
       Select ZipCode,
              Latitude,
              Longitude
       From   ZipCodeTable
       Where  Latitude Between (@Latitude - 2) And (@Latitude + 2)
              And Longitude Between (@Longitude - 2) And (@Longitude + 2)
       ) A
Where  dbo.CalculateDistance(@Longitude, @Latitude, A.Longitude, A.Latitude) <= 50

Select Field1, Field2, Etc
From   Affiliates
       Inner Join #Zips On Affiliates.ZipCode = #Zips.ZipCode

This should be a good starting point for you.

The code, as written, does not order the data. I'll leave it up to you to figure that out. I suppose you will want to order on distance.

Note:
Looking at the query, you'll see that I am getting zipcodes that are +/- 2 degrees away from the original zip code's lat/lon. In the continental U.S. 1 degree lat/lon represents approximately 30 miles. If this algorithm needs to work for other locations (near the equator or near the north pole) you will need to modify this.

Hope this helps, and good luck.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Zathras]

I would go with gmmastros approach. It is more dynamic and does not require pre-calculating grid numbers. While theoretically slightly slower, the difference in processing time would not be noticed.

SQL Server provides more processing power than the database I was using at the time I developed the grid overlay algorithm.

Good one, George. I'll keep it around for future reference.

 

[kristinac13]

Thank you so much for all your help. I have a table with the zip codes including latitude and longitude, the function and procedure you suggested, and my vb that calls everything to the program. I'm sure the problem at this point is my vb code. The function and procedure seem fine in SQL, however I need to test them outside the program perhaps? I mean within SQL server...a query perhaps?
Again thank you so much!