# Calculated distance to a POI

This topic has probably been discussed before.

If it has I apologize, if it has not, I hope someone finds it useful.

I've been on a quest...

The quest was on how to enhance the contents of POI csv files so the POI's descriptions would, in addition to name, address, phone etc, it would also have things like coordinates and distance between my house and each location.

I managed to modify easily enough the csv files of my POIs lists to add phone numbers and coordinates.

And that is when it hit me:

Since I was doing this in Excel, it was easy enough to have a formula that took in consideration the coordinates of where I live and the coordinates of the POI, applied some trigonometry to it and gave me in miles the distance between those two points "as the crow flies". Not as the car drives.

It is NOT a perfect calculation, but it is close enough for me to get a rough idea how far something is.

This is how I did it:

I added two columns: one for my house's longitude and one for its latitude.

I added another column to hold the formula.

Searching online, I found this formula:

3958 = Earth's radius in miles
C1 = My House's latitude
D1 = My house's latitude
G1 = POI's latitude
F1 = POI's longitude

The radius number is not perfect since the earth is not a perfect sphere, but it is close enough for these calculations. If you would like the results to be in Kilometers, use 6371 instead.

Once I had those distances, I added one last column with a formula that concatenated the contents of the POI description with the contents of the calculated distance.

It did the trick.

### .

 I'm not sure about the newer Garmin models, but when I owned a 750 the POI search results would automatically be sorted by distance. So why do you need a fixed distance calculation in the description field?

### Cross check

 I am assuming that your 1490 operates somewhat like my 765. A good way to cross check your calculations would be to load this POI file and then look at the entries in the file by doing the following: Where To > Extras > Custom POIs > "Home to POI distance file" At this point you will have up to 50 of your POIs listed and will see the "as the crow flies" distance to each individual POI - starting with the closest one to your home. Selecting one of those listed POIs will take you to a screen that will give you the driving distance to the selected POI. If you press the "more" button your Nuvi will show your "description" which you can use to verify that your "as the crow flies" distance is accurate.

### Calculated distance to a POI

 My 1490 does indeed sort by distance. And shows the distance of course. The real reason is that, before loading the POI file, I needed to do a scrub of only points within 100 miles of home.

### .

 Frank Lopes wrote: The real reason is that, before loading the POI file, I needed to do a scrub of only points within 100 miles of home. Ah. Then let me introduce you to a free program called Extra POI Editor. It was created by one of our members. Among the hundreds of amazing things it can do is sort POIs by distance!! http://turboccc.wikispaces.com/Extra_POI_Editor

### Thanks

 Frank Lopes wrote: This topic has probably been discussed before. If it has I apologize, if it has not, I hope someone finds it useful. I've been on a quest... The quest was on how to enhance the contents of POI csv files so the POI's descriptions would, in addition to name, address, phone etc, it would also have things like coordinates and distance between my house and each location. I managed to modify easily enough the csv files of my POIs lists to add phone numbers and coordinates. And that is when it hit me: Since I was doing this in Excel, it was easy enough to have a formula that took in consideration the coordinates of where I live and the coordinates of the POI, applied some trigonometry to it and gave me in miles the distance between those two points "as the crow flies". Not as the car drives. It is NOT a perfect calculation, but it is close enough for me to get a rough idea how far something is. This is how I did it: I added two columns: one for my house's longitude and one for its latitude. I added another column to hold the formula. Searching online, I found this formula: =3958*ACOS(COS(RADIANS(90-D1))*COS(RADIANS(90-G1)) +SIN(RADIANS(90-D1)) *SIN(RADIANS(90-G1)) *COS(RADIANS(C1-F1))) 3958 = Earth's radius in miles C1 = My House's latitude D1 = My house's latitude G1 = POI's latitude F1 = POI's longitude The radius number is not perfect since the earth is not a perfect sphere, but it is close enough for these calculations. If you would like the results to be in Kilometers, use 6371 instead. Once I had those distances, I added one last column with a formula that concatenated the contents of the POI description with the contents of the calculated distance. It did the trick. good tip

 Trigo... what? Your putting way too much brain work into my fun and relaxing hobby! Let us know how your calculations worked.
--
Harley BOOM GTS, Zumo 665, (2) Nuvi 765Ts, 1450LMT, 1350LM & others | 2019 Harley Ultra Limited Shrine - Peace Officer Dark Blue

### Like a million bucks. They

 Like a million bucks. They were spot on.

### Way to go!

 Way to go!
--
Harley BOOM GTS, Zumo 665, (2) Nuvi 765Ts, 1450LMT, 1350LM & others | 2019 Harley Ultra Limited Shrine - Peace Officer Dark Blue

### Its easy

 bear007 wrote: Trigo... what? .... Just spherical trigonometry.

### Where's the formula

 Evert wrote: Just spherical trigonometry. I've always said you don't need to remember every formula, just where you can get the formula when you need it.
--
Harley BOOM GTS, Zumo 665, (2) Nuvi 765Ts, 1450LMT, 1350LM & others | 2019 Harley Ultra Limited Shrine - Peace Officer Dark Blue

### It is readily available :)

 bear007 wrote: Where's the formula The formula can be found in a number of places. It is in my 1974 copy of ARRL‘s Antenna book in the chapter titled “Finding Directions” (for calculating direction and distance to a radio station being communicated with). I think it probably is still in the latest editions as well. The formula is derived and presented in Bowditch’s American Practical Navigator. It can be found in Mixter’s “Primer of Navigation”, and other writings on Celestial Navigation. (Most sea navigation books will have it under Great Circle Sailing or some such.) And then there is the Internet, just Google “Great circle distance calculation”.

### Trigonometry

 you fellas are making my head hurt with all that math
--
nuvi 2597LMT

### Not me!

 ewayne1941 wrote: you fellas are making my head hurt with all that math I'm the one the posted "Trigo ... what?"
--
Harley BOOM GTS, Zumo 665, (2) Nuvi 765Ts, 1450LMT, 1350LM & others | 2019 Harley Ultra Limited Shrine - Peace Officer Dark Blue

### Nice job!!!!

 Thanks for taking all the brain work out of this. Great tip.
--
bob TomTom 730T App:9.465 Map: North_America_2GB v900.4602 Win XP Pro(SP3) TT Home 2.9.2.2837

### I Remember This Now

 I saw this in a navigation class I took when I was in the Navy. It's much easier with a spreadsheet than with a book of log tables or a calculator. We did use calculators after the first calculation.

### Log tables, Excel and assorted short subjects

 edwardw66 wrote: ... It's much easier with a spreadsheet than with a book of log tables or a calculator. ... It sure is, although the electronic calculator was a big improvement over using logarithm tables. However, it would be nice if Excel provided trigometric functions that directly uses angles in degrees instead of having to convert to radians first. It was a bit of help when they finally provided the radian function, but it is still a complication that should not be necessary IMO. And of course using a GPSr for position is a lot simpler than taking sextant sights and using sight reduction tables.

### nuvi2450

 The trip planner in my 2450, and probably all the newer modles is much like this. It showes the distance and time between each point and also gives you the option to put in the time you plan to spend at each point. You also can put in the time your trip is to begin. Edit: after reading this thread again, maybe it isn't much like this!!
--
Mary, Nuvi 2450, Garmin Viago, Honda Navigation, Nuvi 750 (gave to son)

### Use Near to get POI list from another location

 Frank Lopes wrote: The real reason is that, before loading the POI file, I needed to do a scrub of only points within 100 miles of home. Why? Garmin has millions of POIs in the CN maps and they are not blacked out from your home when you do a search. Custom POIs are in distance order from current location. If you change "Near" on the WhereTo? screen to be a destination, your Custom POIs will be displayed in distance from that location.
--
Zumo 550 & Zumo 665 My alarm clock is sunshine on chrome.

 Evert wrote: However, it would be nice if Excel provided trigometric functions that directly uses angles in degrees instead of having to convert to radians first. There is only one certain outcome of discussions on topics like this: not everyone will be happy. GPS road warriors would probably prefer decimal degrees. Mathematicians and scientists would probably prefer radians. Sailors might prefer dd:mm:ss, or something else. (FWIW, I saw a statement somewhere claiming that Excel's trig functions were originally designed to use radians so they would be compatible with the trig functions in Lotus 1-2-3 way back when.)

### Excel 2007

 Excel 2007 has a function to convert radians to degrees. Have never used it though.
--
nüvi 3790T | Those who make peaceful revolution impossible, will make violent revolution inevitable ~ JFK

### Mox Nix

 VersatileGuy wrote: Evert wrote: However, it would be nice if Excel provided trigometric functions that directly uses angles in degrees instead of having to convert to radians first. There is only one certain outcome of discussions on topics like this: not everyone will be happy. GPS road warriors would probably prefer decimal degrees. Mathematicians and scientists would probably prefer radians. Sailors might prefer dd:mm:ss, or something else.... I am a Mechanical Engineer and am at ease with any of the ways of expressing angles, I just figure that it would be useful for Excel to have trig functions that directly use degees. Perhaps because a lot of my work involved using angles in degrees. VersatileGuy wrote: (...FWIW, I saw a statement somewhere claiming that Excel's trig functions were originally designed to use radians so they would be compatible with the trig functions in Lotus 1-2-3 way back when.) I know Lotus 1-2-3 Release 2.2 was usinq trig functions which use radians. (I still have the floppy disks and manuals.) I first started using Excel at Version 5 and they were using radians for trig function arguments but don't know if MS copied Lotus or just decided to do it that way. I also used VISICALC for a while in the early 80s but dont know if Lotus people carried forward the way from VISICALC when they bought it or not. I guess it does not matter who copied from who though. It just has been one of my pet peeves for many years.

### EPE formulae

 As mentioned, Extra_POI_Editor can sort all POI by distance and it gives the same number as in my GPS. If you select your current POI location and hit Tools-Sort by Location, all POI will be sorted by distance compared to the selected POI. Alternatively, select one POI and right click the mouse and select "Set as Current Location". It will do the same thing. Be sure to highlight the POI with a left click first. t1 = lon1 * 3.1415926 / 180 t2 = lat1 * 3.1415926 / 180 t3 = lon2 * 3.1415926 / 180 t4 = lat2 * 3.1415926 / 180 distance = acos(Sin(t2) * Sin(t4) + Cos(t2) * Cos(t4) * Cos(t3 - t1)) * K where K = 6371 (for distance in kilometers) or K = 3958 (for a distance in miles) Edit: yes, same as formula posted by Frank.

### same or equivalent

 turboccc wrote: Edit: yes, same as formula posted by Frank. Well, I would say they are equivalent in that they are of different form but one can be derived from the other and both give the same result. And there is the fact that your list of variables does not have the mistake that Frank's has.

### What was the mistake?

 What was the mistake? Please let me know so I can correct it. Evert wrote: turboccc wrote: Edit: yes, same as formula posted by Frank. Well, I would say they are equivalent in that they are of different form but one can be derived from the other and both give the same result. And there is the fact that your list of variables does not have the mistake that Frank's has.

### Correction

 Frank Lopes wrote: What was the mistake? Please let me know so I can correct it. Both C1 and D1 are listed as being latitude: C1 = My House's latitude D1 = My house's latitude I assume that C1 is longitude.

### I see the error

 Between me asking for the error and you answering, I found the problem. Here is a corrected formula: =3958 * (ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2)))) 3958 = Earth's radius in miles Lat1 = My House's latitude Lat2 = POI's latitude Long1 = My house's longitude Long2 = POI's longitude Thanks for the tip. ------------------------------------------- I saw some discussions earlier regarding degrees.decimal versus radians. In a pinch, multiply your degrees.decimal by 0.0174533 and you get radians. Where does that number come from? It is PI (3.1415926...) divided by 180 degrees since the length of the circumference (360 degrees) is 2*PI.

### I don't mean to be a pest but....

 Frank Lopes wrote: Between me asking for the error and you answering, I found the problem. Here is a corrected formula: =3958 * (ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2)))) 3958 = Earth's radius in miles Lat1 = My House's latitude Lat2 = POI's latitude Long1 = My house's longitude Long2 = POI's longitude Thanks for the tip. ..... Now it is ok as an equation but cannot be used directly as an Excel formula . Your original formula would work just by changing C1 in your orignial variable list to: C1 = My House's longitude Your original formula has the advantage that it could be used directly by copying and pasteing it into any cell on row 1 and then copying that cell to other cells in the same column. Note: You don't need to follow turboccc's way of listing the variables because he probably programs the equation in Turbo C or Turbo C++ However you could make your Excel formula simpler by using his form of the equation thus: =3958*ACOS(SIN(RADIANS(D1))*SIN(RADIANS(G1)) +COS(RADIANS(D1)) *COS(RADIANS(G1)) *COS(RADIANS(C1-F1)))