Plotting Surrounding Zip Codes based on Longitude & Lati

rmfrase

Board Regular
Joined
Jul 6, 2006
Messages
123
Having the Zip Code Tabulation Module from the US Census Bureau, I have the following and much more. But based on the 3 items :
5-digit Zip Code, Longitude, Latitude.

I'm wanting to Select a Zip Code and have (let's say) several Zip Codes plotted (maybe 4 - 10) out from the center point of the map.
either with, or without a connecting straight line .

And possibly a Scalable Miliage indicator |---- 10 Miles ----| which would be based on the distance.

Is this possible?

Your thoughts?

-Robert
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

rmfrase

Board Regular
Joined
Jul 6, 2006
Messages
123
If it's not possible to plot the Surrounding Zip Codes, is it at least possible to identify (or List) which are the closest to the target Zip Code (based on Longitude and Latitude) ?
 

rmfrase

Board Regular
Joined
Jul 6, 2006
Messages
123
In case anyone is thinking that the nearest Zip Code is simply a digit or two away, here are my closest zipcodes.

76021
76022
76053
76092
76106
76111
76114
76117
76118
76131
76135
76137
76148
76180
76248


As you can tell, it isn't as simple as a digit or two. However, I am noticing a pattern of vacenity.

Latitude range is from 32.778(rounded) to 32.946
Longitude is from 97.138 to 97.451

But plotting on a map with the target zipcode in the middle has me stumpped..
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Fascinating quest. I might like to have that myself.

I'll leave the plotting aspect to you and others but offer this. The distance between zip codes would use the right triangle hypotenuse formula, i.e.
distance = sqrt( LongDelta^2 + LatDelta^2 ) translated to miles
So if a given map resolution calls for a 10 mile radius, plot those distances less than 10.

Is that useful?
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028

ADVERTISEMENT

Searching Google for 'distance latitude longitude' yields several hits including http://www.movable-type.co.uk/scripts/latlong.html

It gives you more than one way to calculate the distance, the simplest formula being =ACOS(SIN(LAT1)*SIN(LAT2)+COS(LAT1)*COS(LAT2)*COS(LON2-LON1))*R where R is the earth's radius (approx. 6371 KM).

To plot the information, "flatten" the earth's surface. Now, the longitude becomes x values and the latitudes become x values.

This is a very crude approximation since the longitudes all converge at the poles but by treating them as x values the chart treats them as parallel lines. However, it should be OK for small differences in latitude and longitude especially the further away from the poles one gets.
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Wouldn't the simpler hypotenuse formula still apply on the flattened map? If the curvature is assumed constant, then I see no reason that {the range of latitudes} can't be mapped linearly to one-fourth of the assumed circumference, and similarly with longitudes. Am I mistaken?

I expect that as a practical matter the actual variance of true circumferences (e.g., between polar and equatorial circumferences) may distort things, but by enough to matter? Trignometry doesn't handle that anyway.
 
L

Legacy 14611

Guest

ADVERTISEMENT

Wouldn't the simpler hypotenuse formula still apply on the flattened map? If the curvature is assumed constant, then I see no reason that {the range of latitudes} can't be mapped linearly to one-fourth of the assumed circumference, and similarly with longitudes. Am I mistaken?
Unfortunately you are mistaken.

It depends on how the map is "flattened", that is, what type of projection is used to depict a (somewhat) spherical earth onto a flat sheet.

If you use your hypotenuse formula on what appears a straight line on say a Mercator map, then this line would become a curve of possibly different length (and different calculation of length) if the Mercator were re-transformed to a polar stereographic or other of the commonly used map projections.
 

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,028
I know very little about the science of projecting 3D onto 2D but the approach I suggested (not my creation but from the page I linked to) would appear to be more amenable to different kinds of projections. If one wanted to use an algorithm that shows the lat/long points as though they converge as the latitude approaches +/- 90 degrees, one could use the following transformation: X=cos(lat)^2*sin(long); Y=sin(long)^2

Again, let me emphasize I don't know much about projections. I made up the above transformations based on trial and error. I imagine a search of Google would yield much more accepted methods.
Wouldn't the simpler hypotenuse formula still apply on the flattened map? If the curvature is assumed constant, then I see no reason that {the range of latitudes} can't be mapped linearly to one-fourth of the assumed circumference, and similarly with longitudes. Am I mistaken?

I expect that as a practical matter the actual variance of true circumferences (e.g., between polar and equatorial circumferences) may distort things, but by enough to matter? Trignometry doesn't handle that anyway.
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
This is not my area of expertise but like anyone I can appreciate the vagaries of "flattening." In mentally visualizing it I imagine a paper globe, and for even a one-by-one inch cutout, it still bulges when you set it down; to flatten it out, something has to give. Thus there are the different methods of mapmaking which look dramatically different - even the shape of a country like the U.S. looks much different under different mapping conventions. For just plotting zip codes in the close vicinity of a city - i.e. the one inch case - that bulge is pretty tiny, but exists.

A degree of latitude is approximately 69.1 statute miles (earth shape irregularity actually makes the latitude translation vary from 68.7 to 69.4 but I've set that aside.) But where my proposed method suffers is that longitudes vary according to latitude. Duh and dohhh! Of course, they are zero at the pole, up to circumference/360 at the equator (which is actually a little larger due to the earth's bulge, but 69.1 miles is ballpark).

Admittedly still setting aside that variation and these rounded approximations, it's not too rough a conversion at a given latitudinal point:
one degree = cos(latitude) * 69.1
This still doesn't end the problem because in measuring the distance there are two latitudes. So now I understand the need for the complicated calculation. For a poor man's approximation one might just use the average of the two, and merely looking at a 50 mile radius it would look just as good as the genuine article, but it still wouldn't be the genuine article :)

Just to finish out my original point, the approximation would be
SQRT( (69.1*(lat2-lat1))^2) + (X*(long2-long1))^2) )
This happens to match the "improved approximation" used at
http://www.meridianworlddata.com/Distance-Calculation.asp
except that they base X on lat1 rather than the average.

That article continues to give the precise answer which Tushar already stated,
"r * acos[sin(lat1) * sin(lat2) + cos(lat1) * cos(lat2) * cos(lon2 - lon1)]
Where r is the radius of the earth in whatever units you desire.
r=3437.74677 (nautical miles)
r=6378.7 (kilometers)
r=3963.0 (statute miles)"
All in radians, of course. And note the different circumference estimate, which is all over the place depending who you ask.

Lots of fun learning, and thanks tushar for reminding me...
bartgoog.gif

P.S. nice conversion link, Tushar.

P.P.S. This is working its way back to being an Excel post. When the O.P. or someone decides exactly what to do, that will help! Despite the fact that it seems we usually "do, then think!"
 
L

Legacy 14611

Guest
And note the different circumference estimate, which is all over the place depending who you ask.
Yeah.

Back around 200 BC the Greek Eratosthenes estimated the circumference of the earth by finding angles of the sun at midday at Alexandria and Aswan, sending a slave to step out the distance from one town to the other, and using elementary geometry.

Some have suggested that his estimate is within about 0.7% of the accepted present day figure (whatever that is), but who knows?

The Persians at that time also knew of him, but under a quite different name (forgotten what it was and Google is unlikely to be of much help). He was possibly also responsible for the present-day name of Iran.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,574
Members
414,390
Latest member
plimbu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top