5

Suppose I have an Excel spreadsheet with the latitude and longitude of the centroid of every county in the U.S. I want to know the shortest distance from each county to any part of Oklahoma (presumably, somewhere along the border). I then want this distance in this Excel spreadsheet as a new column.

Is this possible to do and how? I know next to nothing about GIS

underdark
  • 84,148
  • 21
  • 231
  • 413
J G
  • 51
  • 4
  • 3
    What tools do you have at hand? There are n ways to skin this cat. Let us know what tools you have, skills (GIS, Programming, etc), and we'll give you a good start. – OptimizePrime May 24 '12 at 19:55
  • @OptimizePrime Thank you for your quick response! Would that be easier to hash out over a chat forum here? – J G May 24 '12 at 20:06
  • Have you checked http://gis.stackexchange.com/questions/2/how-do-i-find-the-distance-between-two-coordinates – underdark May 24 '12 at 20:41
  • I understand how to do the distance between 2 coordinates. The problem -- as I wrote below -- is that I don't know how to do the distance between a county whose coordinates I know and the boundary of Oklahoma, whose coordinates I do not know. Perhaps it is possible to get those coordinates. – J G May 24 '12 at 20:51

1 Answers1

1

If you know the coordinates of the point in OK, the haversine formula should be pretty easy to implement in Excel. This will give you the approximate as-the-crow-flies distance between the points.

dimitriy
  • 343
  • 2
  • 12
  • My problem is that I do not know how to get the coordinates for the state of Oklahoma. Do you know how to do this? – J G May 24 '12 at 20:49
  • If you only have a couple points, you can get the coordinates by hand from Google Earth. For example, OK City is 35.467588 and -97.516426. If you have lots of addresses, you can geocode them as a batch. There are some free options if you don't have too many (like https://webgis.usc.edu/Services/Geocode/BatchProcess/Default.aspx). – dimitriy May 24 '12 at 21:00
  • Could any of this be done through GIS? – J G May 24 '12 at 21:02
  • 2
    This answer seems to miss the thrust of the question, which inquires about a way to compute distances between centroids (points) and a polygon (representing the state of Oklahoma). I'm afraid that will not be resolved only by knowing the Haversine formula. – whuber May 24 '12 at 21:06
  • You can definitely use other software, including a full GIS package, to do this, but your original post said you were not familiar with this. I think you need to clarify how you specify which point on the OK boundary you want. In Google Earth, you can just use the ruler tool to the point you want. In a full-feature GIS tool, you can pick the closest point or do something more complicated. – dimitriy May 24 '12 at 21:12
  • Isn't "shortest distance" a sufficiently clear criterion, Dimitriy? – whuber May 24 '12 at 21:15
  • @whuber Thank you so much for your response! Do you know how to do this computation of distance from a centroid to a polygon? Is this possible to do? – J G May 24 '12 at 21:18
  • Not to me. I take the "shortest distance from each county to any part of Oklahoma (presumably, somewhere along the border)" to be as the crow-flies distance to a point somewhere on the border. How you pick that point is not clear to me. I understand shortest as describing the route to that point. – dimitriy May 24 '12 at 21:18
  • 1
    @DimitriyV.Masterov I want the shortest distance between the centroid and any point along the OK boundary. – J G May 24 '12 at 21:18
  • There's an infinite number of points on OK boundary. How do you specify which one you have in mind? Do you mean the point on the boundary nearest (in some sense) to your centroid? – dimitriy May 24 '12 at 21:25
  • J G, the calculation is possible to do (even in Excel, although that would be an impressive feat), but it requires a few steps, some GIS software, and some judgment. The GIS will compute distances between points and polygons, so you have to (a) obtain a representation of OK as a polygon (in latitude and longitude coordinates), (b) request the distance calculations in the GIS (by performing a "spatial join" or writing a loop), and (c) export the output to Excel. Every step is easy, but (b) and (c) require you to find, install, and learn the basics of an appropriate GIS app. – whuber May 24 '12 at 21:26
  • @Dimitriy The OP is not asking to find a nearest point on the OK boundary! All they want is the distance to OK from each centroid. It appears to me this is quite clearly spelled out in the question ("I want to know the shortest distance from each county to any part of Oklahoma"). Are we perhaps reading different versions of the question? – whuber May 24 '12 at 21:28
  • @whuber How would I get representation of OK as a polygon? (Is that equivalent to asking how would I get the coordinates of OK?) What type of GIS program would I need? Is GIS the best way to do this? – J G May 24 '12 at 21:37
  • (1) It's available in any "vector" format representation of the state, which you can find in myriad places. (2) You need a "vector" based GIS ("raster" GISes can do the job, too, and in a more flexible manner, but learning to use them will be more difficult). (3) Absolutely GIS is the best way! Any program that can solve your problem easily already deserves to be called a GIS. – whuber May 24 '12 at 21:41
  • @whuber It sounds like the next step is to find someone who I can work with 1-on-1 to do all of these things. Would you be interested or know anybody who might be? – J G May 24 '12 at 21:53
  • @whuber I always take your advice seriously and have learned a lot from you in the past. Can you tell me the distance from the centroid of Oklahoma Country to OK and what that means geometrically? – dimitriy May 24 '12 at 21:59
  • That distance is an easy one, Dimitriy: it is zero. Geometrically it means that the point lies inside the polygon. // If you have access to ArcGIS, just ask it for a spatial join of an OK polygon layer (see the link I gave @J G) to any layer of points: if you ask, the resulting table will have a column of the shortest distances to OK. Unfortunately, ArcGIS--after all these years--still uses Euclidean calculations for these distances! One has to work around that by reprojecting both layers suitably: I would choose an equidistant projection centered in OK. – whuber May 24 '12 at 22:04
  • @DimitriyV.Masterov Distance between Oklahoma City and OK is zero because it is within Oklahoma. How about a county such as Dade County, Florida given the centroid for Dade? – J G May 24 '12 at 22:04
  • J G, I have a center for Dade county for which ArcGIS 10.0 reports a distance of 16.141698 degrees to OK. That's next to useless because this (industry leading) software hasn't computed spherical distances correctly. (Remember I said a while back that "judgment" was part of the work flow? :-) However, this solution (which needed just a few mouse clicks to create for all US county centroids) shows what a GIS is supposed to be capable of. I'm hoping that experts in other GIS software will shortly come forward and say "hey, my software does the same thing but it gets the answer right!" – whuber May 24 '12 at 22:08
  • @whuber How would that distance in degrees be converted to miles? Also, is it possible for GIS to tell me which county in OK has the point on its boundary that yields that distance? Also, did you catch my earlier note about looking for someone to work 1-on-1 with? Thank you for all of your help so far!! – J G May 24 '12 at 22:16
  • @Sylvester Sneekly I don't know how to contact people privately. – J G May 25 '12 at 13:27
  • @whuber Exactly which software should I try to access to do this calculation? Thank you for all of your help. – J G May 29 '12 at 17:06