?
Solved

Distance determination, ZIP code matching

Posted on 2009-02-18
8
Medium Priority
?
360 Views
Last Modified: 2012-05-06
This is a one-time data determination. After I generate this list, I will probably never need to generate it again. I need to find the closest (as the crow flies) US government weather station to each US ZIP code. I have the latitude and longitude for each weather station (2100 stations) and for each US ZIP code (41,000 ZIP codes). I would like to carry out this data generation in Microsoft Excel 2000. There is probably a simple VBA solution, but I am not skilled in VBA. Thanks in advance.
0
Comment
Question by:joco22
  • 3
  • 3
  • 2
8 Comments
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 23670392
Dunno about simple but it's a maths function as such so if you provide the data, (ideally in a spreadsheet) we ought to be able to design / process it.

Chris
0
 
LVL 45

Expert Comment

by:patrickab
ID: 23671281
joco22,

That's 2100 x 41000 calculations or in real terms 86,100,000 calculations. Unless of course you can break it down into much smaller areas - say the area bounded by for example 5 or 6 wearther stations at a time - but even then it's a lot of calculating. Apart from which if you don't know the relationship between zip code area centres and their latitude and longitude you can't even begin the calculations.

So the first task is to obtain the latitude and longitude of the centres all 41000 zip code areas. To do it by zip code boundaries would involve a huge data set and a truly vast number of calculations - so the latitude and longitude of the zip code areas centres is the crucial starting point.

If you can supply a file with a small sample of the latitude and longitude of the weather stations and say 100 centres of zip code areas then its pretty simple to calculate the distances to an accuracy of ± the radius of the zip code area. That will no doubt depend in the density of zip codes within a geographic region.

Patrick


0
 
LVL 45

Expert Comment

by:patrickab
ID: 23672330
joco22,

Attached is a file with USA zip codes and their latitudes and longitudes which I downloaded from the net. Just put 'USA zip codes latitude longitude'  into Google and you will find links like this:

http://www.boutell.com/zipcodes/

I didn't use that particular source but the principle is the same.

In the attached file the blue cells are for the weather station  latitude & longitude and the green cells are the calculated results. I have Conditionally Formatted column G so that it colours the minimum cell in red - the row number tells you where the result can be found. However if you are doing this for 2100 weather stations then you will need to remove the Conditional Formatting in column G.

If you want it the other way round, then I will leave you to copy and paste the data sets into the correct columns. Do please remember that INDEX is not a volatile function so you will need to ensure it has calculated.

Hope that helps

Patrick

ps. I strongly recommend that you do not use VBA for this. Excel native functions are many times faster than VBA and in this instance there really is no need to use VBA.

USA-Weather-station-zips.xls
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 45

Expert Comment

by:patrickab
ID: 23672422
joco22,

By the way I've just had a look at the file provided by:

http://www.boutell.com/zipcodes/

and it appears to be more comprehensive than the source I used. I'll leave you to download and make use of that data set instead of the one I used. When you've done that remember to update the ranges used in the formulas.

Patrick
0
 

Author Comment

by:joco22
ID: 23692203
Thanks for the many good suggestions. I have set up a spreadsheet which in fact works, but I have not been able to automate the process up to this point. I am sure that there is a programmatic way of carrying out this project efficiently. Any suggestions would be greatly appreciated.
Find-Closest-US-Weather-Station-.zip
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 23719610
The attached is a sample, the locations ideally need a validation check.

1. It runs trhough all the zip codes ... but skips the calcs where data is already populated in column H

2. I moved the weather station to a new sheet to simplify processing

3. It stops in blocks of 100 but this can be enlarged and or deleted to allow a full run.

Chris
Find-Closest-US-Weather-Station-.zip
0
 
LVL 59

Accepted Solution

by:
Chris Bottomley earned 2000 total points
ID: 23719901
And this is the result of running it against all the zip codes ... hopefully will support validation of the results.

Chris
Find-Closest-US-Weather-Station-.zip
0
 

Author Closing Comment

by:joco22
ID: 31548242
Thanks very much
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question