PDA

View Full Version : Macro for finding all zips within a specific zip



rodman
02-27-2013, 10:06 PM
I am looking for a code that would search all zip codes within a certain mile of a specific zip code. For example, let's say I have the zip code 98101 in A1, the code would look for all zips in 30 mile radius of zip code in A1 and put them in A2, A3, A4, etc. Can anyone help?

Eric Frost
02-28-2013, 07:52 AM
Rodman,

I'd be glad to help you.

I think it's going to require a little more than just some code, as there's no way to "Query" the built in demographic layers.

You first need a Pushpin layer with zip codes. You could do this one of two days:

1. Map any demographic variable in MapPoint by zip code. Draw a huge rectangle or radius covering all of the U.S. and AK/HI, right click on the polygon and Export to Excel. Then import the worksheet as pushpins.
2. Obtain a zip code database from some third party, just search for it ( zip code database (http://www.bing.com/search?q=zip+code+database) ). I think you can get a quality list for $10-$25? The database should have lat lon which makes it easy to import.

The downside of #1 is that MapPoint only has about 33k zip code areas, but many zip codes are just points and won't be included in what you would get Exporting from MapPoint.
A quality list obtained from #2 is going to have about 42k to 44k zip codes I think, so it will be a lot more complete.

Whether #2 would be helpful for just doing #1 is sufficient depends on your application, but I would almost always think a more complete zip code list is worthwhile?

After you get the zip codes, the macro/code is fairly straightforward. What kind of interface do you want, is it list driven or the user puts in one zip code at a time? You would just use the QueryShape or QueryPolygon method on the zip code pushpin set and interate over the resulting recordset (I think I write this code or variations of it a couple times per week for clients, lol!)

Let us know when you get to this point and I'll help more.

HTH!
Eric

rodman
02-28-2013, 11:36 PM
Rodman,

I'd be glad to help you.

I think it's going to require a little more than just some code, as there's no way to "Query" the built in demographic layers.

You first need a Pushpin layer with zip codes. You could do this one of two days:

1. Map any demographic variable in MapPoint by zip code. Draw a huge rectangle or radius covering all of the U.S. and AK/HI, right click on the polygon and Export to Excel. Then import the worksheet as pushpins.
2. Obtain a zip code database from some third party, just search for it ( zip code database (http://www.bing.com/search?q=zip+code+database) ). I think you can get a quality list for $10-$25? The database should have lat lon which makes it easy to import.

The downside of #1 is that MapPoint only has about 33k zip code areas, but many zip codes are just points and won't be included in what you would get Exporting from MapPoint.
A quality list obtained from #2 is going to have about 42k to 44k zip codes I think, so it will be a lot more complete.

Whether #2 would be helpful for just doing #1 is sufficient depends on your application, but I would almost always think a more complete zip code list is worthwhile?

After you get the zip codes, the macro/code is fairly straightforward. What kind of interface do you want, is it list driven or the user puts in one zip code at a time? You would just use the QueryShape or QueryPolygon method on the zip code pushpin set and interate over the resulting recordset (I think I write this code or variations of it a couple times per week for clients, lol!)

Let us know when you get to this point and I'll help more.

HTH!
Eric

I see! That seems too much of work. I was just hoping one code would do it. Right now I am using this code and it is kinda doing what I need. The only problem is I have to let the code go through 600 zips each time I change "szZip1" and then sort it by miles (Nrow, 3). Once it has been sorted by miles, I can tell what the first 30 miles , 40 miles, 50 miles zips and so on are . Obviously here I already have all 600 zips in column A but can this code somehow be modified to do the same thing if I didn't have all zips in column A and I only had one zip in cell (1,2)?


Private Sub CommandButton1_Click()

Set oApp = CreateObject("MapPoint.Application.NA.17")
oApp.Visible = True
Set objMap = oApp.NewMap
Set objRoute = objMap.ActiveRoute
szZip1 = Worksheets("Sheet1").Cells(1, 2)
Nrow = 3
Do
szZip2 = Worksheets("Sheet1").Cells(Nrow, 1)
'Add route stops and calculate the route
objRoute.Waypoints.Add objMap.FindResults(szZip1).Item(1)
objRoute.Waypoints.Add objMap.FindResults(szZip2).Item(1)
objRoute.Calculate
Worksheets("Sheet1").Cells(Nrow, 3) = objRoute.Distance

For i = 1 To objRoute.Directions.Count
Worksheets("Sheet2").Cells(Nrow, i) = objRoute.Directions.Item(i).Instruction
Next i

objRoute.Clear
Nrow = Nrow + 1
Loop While Worksheets("Sheet1").Cells(Nrow, 1) <> ""
objMap.Saved = True





End Sub

Eric Frost
03-01-2013, 07:47 AM
This seems like a very inefficient way to go about things, but on the plus side it would probably give you the most accurate driving distances if you need that.

rodman
03-01-2013, 09:18 AM
This seems like a very inefficient way to go about things, but on the plus side it would probably give you the most accurate driving distances if you need that.
That's correct. The mileage accuracy is important to me.

Eric Frost
03-01-2013, 09:30 AM
*Thinking*..

Are you only concerned with those 600 zips? Could you just import those zips as a Pushpin Layer rather than doing #1 or #2 above?

Then you would just use QueryShape/Polygon on the pushpin layer with those 600 zips.

Eric

dudel
09-20-2013, 07:03 AM
You may be able to do this with simpler code, but here's my take on a similar problem. I needed to find the number of pins in a county. I don't have accurate county information in my source data, so I mapped the addresses, and pulled county off of the map.



private void button1_Click(object sender, EventArgs e)
{
// Tested with the following select:
//SELECT NId, NLatitude, NLongitude FROM idbName INNER JOIN idbAccount ON NId = AId WHERE NLatitude <> 0 AND NLongitude <> 0 AND [NZipCode] = '30536'
//SELECT NId, NLatitude, NLongitude FROM idbName INNER JOIN idbAccount ON NId = AId WHERE NLatitude <> 0 AND NLongitude <> 0 AND Nstate='GA' and Year(AOpenDate)=2010 AND NTYPE=0 AND AMemberGroup in (801,802,803)
string[] Counties = new string[200]; // 159 counties in GA
int[] Count = new int[200];
string t = "";
int x = 1;
int pins = 0;
object key = 2;
MapPoint.Pushpin objPP = null;
MapPoint.Location objLoc = null;
MapPoint.FindResults objFR = null;
MapPoint.Recordset objRS = null;
//Find number of pushpins in the My Pushpins set
button1.Text = "County Pop";
foreach (MapPoint.DataSet mds in MPC.ActiveMap.DataSets)
{
if (mds.Name == "My pushpins")
{
objRS = mds.QueryAllRecords(); // fill the Recordset with all records in the "My Pushpins" dataset
do
{
objPP = objRS.Pushpin; //get the pushpin to see if it's a branch
if (objPP.Symbol != clsGM.BranchMarker)
{
pins++;
if ((int)pins % 100 == 0)
{
button1.Text = pins.ToString();
Application.DoEvents();
}
objLoc = objPP.Location; // and the Location object
objLoc.GoTo(); // Set the pin on the map
// Find what's at that location on the map
MPC.ActiveMap.Altitude = 1; // set the altitude low to reduce the number of objects returned (just need county)
objFR = MPC.ActiveMap.ObjectsFromPoint(MPC.ActiveMap.Locat ionToX(objLoc), MPC.ActiveMap.LocationToY(objLoc));
// Item 1 can't be retrieved
//for (x = 2; x < objFR.Count; x++)
try
{
foreach (object o in objFR)
{
MapPoint.Location locR = o as MapPoint.Location;
if (locR == null) continue;
//MapPoint.GeoShowDataBy locType = locR.Type;
if (locR.Type == MapPoint.GeoShowDataBy.geoShowByRegion2)
{
AddByCounty(locR.Name, ref Counties, ref Count);
//t += "x: " + x.ToString() + " Name: " + objLoc.Name + "\r\n";
break; // there may be MANY objects associated with the location, so quit when you find the county
}
}
}
catch (Exception) // ignore
{ }
}
objRS.MoveNext(); // move to next pushpin in the recordset
} while (!objRS.EOF); // till done
for (x = 0; x < Counties.Length; x++)
{
if (Counties[x] != null)
{
t += Counties[x] + " " + Count[x].ToString() + "\r\n";
}
else
break;
}
t += "Processed " + pins.ToString() + " pins.";
MessageBox.Show(t); // show what we found.
}
}
}

You can uncomment the line: //t += "x: " + x.ToString() + " Name: " + objLoc.Name + "\r\n";
to see all the data that's available from the map (and to find the zip string).


I also neede to pull the number of pins that were within various circles centered on branch locations. That was done through this code. Combining the two will let you find the zips in the radius from your start point.



private void cmdMembersInTimes_Click(object sender, EventArgs e)
{
int PCnt = 0; // count of pushpins
if (clsGM.curselShape == null)
{
MessageBox.Show("Please select a zone or drive time.");
return;
}
else
{
PCnt = PinsInShape(clsGM.curselShape);
MessageBox.Show("There are " + PCnt.ToString() + " member pins in the selected area (" + clsGM.curselShape.Name + ")");
}
}

private int PinsInShape(MapPoint.Shape shp)
{
int PCnt = 0; // count of pushpins (figure out how to not count the branch!)
MapPoint.Recordset mrs = null;
if (shp.Type == MapPoint.GeoShapeType.geoAutoShape || shp.Type == MapPoint.GeoShapeType.geoFreeform) // got a distance zone or drive time
{
foreach (MapPoint.DataSet mds in MPC.ActiveMap.DataSets)
{
if (mds.Name == "My pushpins")
{
if (mds.RecordCount > 0)
{
mrs = mds.QueryShape(shp);
do
{
if (mrs.Pushpin.Symbol == clsGM.MemberPin) PCnt++; // only count members
mrs.MoveNext();
}
while (!mrs.EOF);
}
shp.Text += " (" + PCnt.ToString() + " pins)";
return PCnt;
}
}
}
return 0;
}


Hope that helps,

Don

Eric Frost
09-21-2013, 08:27 AM
Wow, this is awesome. Thanks for sharing the code! :punk: :punk:

dudel
09-21-2013, 08:38 AM
Wow, this is awesome. Thanks for sharing the code! :punk: :punk:

No problem. Hope it helps someone out.Don