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*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.

.

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

Trigo... what?

Your putting way too much brain work into my fun and relaxing hobby! grin

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. smile

Where's the formula

Evert wrote:

Just spherical trigonometry. smile

I've always said you don't need to remember every formula, just where you can get the formula when you need it.

smile

--
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 smile

--
nuvi 2597LMT

Not me!

ewayne1941 wrote:

you fellas are making my head hurt with all that math smile

I'm the one the posted "Trigo ... what?" laugh out loud

--
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. smile

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!! grin

--
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.

degrees vs. radians

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. smile

And there is the fact that your list of variables does not have the mistake that Frank's has. razz

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. smile

And there is the fact that your list of variables does not have the mistake that Frank's has. razz

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. smile

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++ smile

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)))

Understood

Thanks for your comments.

..

You are welcome.

Turbo C++ = TurboCCC

Note: I programmed my tools in Visual Basic.

BTW, I used TurboCCC as my login because I assumed Turbo C++ would not work. smile

Turbo C++

turboccc wrote:

... Turbo C++ = TurboCCC

Note: I programmed my tools in Visual Basic.

I pretty much figured that was what your name stood for. I was joking about you using Turbo C or C++ for working on EPE (because of your name), knowing it would be very tedious to use them to build up the tools needed even for dropdown boxes etc. that are readily available in VB.

I still have my Turbo C and Turbo C++ disks and manuals but have not used them in years. I did some programming with VB 4 but have gotten too lazy even for that.

Come to think about it, I think I still have a Fortran IV compiler stashed away somewhere. smile

BTW: I have been using your RGN_Tool and GIR_Editor in addition to EPE. It has been a lot of fun using them. Thank you very much.