Coordinate Conversion Formula

Posted: Tue Feb 09, 2010 9:01 am

Is there a formula that one could use, for example in an excel spreadsheet, to convert FROM our normal DDD MM.MMM coordinates TO Decimal coordinates.

As an example to convert FROM:
N 43° 23.361 W 087° 52.341
TO:
43.38935 -87.87235

I bought a used vehicle that has a built-in Stereo/GPS, which is a Pioneer AVIC-U310BT. Seems to be a nice unit (not a Garmin, but hey it's builti-in). You can upload coordinates/names/description to it from an excel file (saved as .CSV), but the darn file has to be in the 43.38935 -87.87235 format. Pioneer supplies some transfer software called "AVIC Feeds".

Seems to me if I new the formula for North and the formula for West I could transfer a .GPX file to excel add some columns to convert N and W coordinates and save it off to a .CSV to load into the AVIC Feeds software.

Posted: Tue Feb 09, 2010 9:06 am

If you look at any GPX file, the co-ords are listed in decimal degrees, just like you want. (view the GPX file in notepad)

Mapsource will convert if for you too.

Or, if you are a masochist, you can convert using the following formula.

Ndd mm.mmm Wdd mm.mmm

decimal degrees = dd + (mm.mmm / 60)

Posted: Tue Feb 09, 2010 9:07 am

Not sure if this will help but:

http://support.microsoft.com/kb/244951

Posted: Tue Feb 09, 2010 9:14 am

Tie ... thanks!

I noticed that the "raw" .GPX was in the correct format ... but couldn't see an easy way to get the fields I wanted (nice and clean) into excel.

I have been poking around with preferences in MapSource. Couldn't find a format that matched. But now I see there is a Lat/Lon that is decimal. However, they've left the N and W on ... I need to remove the Ns and turn the Ws in - (negative). But that's pretty easy to do in excel.

Thanks!

Posted: Tue Feb 09, 2010 9:36 am

Actually I feel so "complete" inside ... I figured out the excel formulas ... which is an amazing thing, considering how little I use excel.

In case your wondering:

Assuming A1 contains: N43 23.361 W87 52.341
North =((MID(A1,2,2))+(MID(A1,5,6)/60))*(IF(MID(A1,1,1)="N",1,-1))
West =((MID(A1,13,2))+(MID(A1,16,6)/60))*(IF(MID(A1,12,1)="E",1,-1))

Giving you:
43.38935
and
-87.87235

This assumes that A1 is exactly in the shown format ... spaces as indicated, no degree symbol and no leading 0 before the 87 ... which is the format that my MapSource is creating.

Posted: Tue Feb 09, 2010 9:54 am

I occasionally want / need to convert between systems.

I have a fake cache established on GC.Com, and enter one system, then look at the cache page for the others. Slightly ..... weird, but it works.

WGA Friend Joined: 2010-01-08 Posts: 275

This works too

http://boulter.com/gps/

Posted: Tue Feb 09, 2010 10:54 am

this is what I use

http://www.fizzymagic.net/Geocaching/FizzyCalc/

Posted: Tue Feb 09, 2010 11:00 am

Another option to get it into Excel would be to use the GSAK Export to Excel functionality. I've done this and used similar equations to convert the format. Obviously you need GSAK to do this.

Posted: Tue Feb 09, 2010 11:08 am

Lot's of ways to get this done... Like CodeJunkie, I usually just write something...
You'll need the latest version of GSAK for this one to work.
Copy and paste this into a new macro. Assuming the order you mentioned is correct, (and that you use GSAK) this should create a .CSV that you can use for your nav unit. The file (AVIC.CSV) will be in your GSAK directory.
On my laptop, it exports 1000 caches in about 4 seconds.

Code:

$CN=0
$OutputFile=$_Install + "\AVIC.CSV"
If FileExists($OutputFile)
FILEERASE File=$OutputFile
endif
$SQLdatabase=$_CurrentDataPath + "\sqlite.db3"
$TMPstr=sqlite("open","$SQLdatabase")
$_SQL="Select Latitude,Longitude,Code,Name from caches where $_Where"
$status = sqlite("sql",$_Sql, "sqlget=yes")
while not($_sqleol)
$Lat=val(sqlget("Latitude"))
$Lon=val(sqlget("Longitude"))
$Code=(sqlget("Code"))
$Name=(sqlget("Name"))
ShowStatus msg=Exporting cache $CN - $Code
$OutString="$Lat,$Lon,$Code,$Name"
$OutString=$OutString + $_NewLine
SET $Data = AppendFile($OutputFile,$OutString)
$CN=$CN+1
SQLNext
EndWhile
SQLGETCLOSE

Posted: Wed Feb 10, 2010 1:32 pm

Turns out there is a GSAK macro already made for the Pioneer units.

Could someone help me modify it?

The .GSK code is available at: http://gsak.net/board/index.php?s=d3c33c29efbc38c3fd16ea958b0eda27&showtopic=12927&st=0&#entry85903

The resulting <name> field is just the written name of the cache, like this:
That Amazing Cache

But I'd rather have the Waypoint Code followed by the written name, like this:
GC12DG3 That Amazing Cache

I thought I'd be able to figure it out, but I don't see any reference to the field containing the Waypoint in the code.

Posted: Wed Feb 10, 2010 1:48 pm

I'll take a look and either modify it or just let you know what to change.

Posted: Wed Feb 10, 2010 1:57 pm

Try this... Change line 115 to:

Code:

$CacheName = $d_code +"-"+ $d_Name

You can change the dash to a space if you like.

Posted: Wed Feb 10, 2010 2:31 pm

Thanks ... that worked great!