I have some db records that I am trying to extract. I've hit a stumbling block with the lat/long fields. This might sound computer-y so far but it's going to end in a maths question, I promise. I am not a mathematician, not mathematically inclined, so please don't laugh too hard at me

The only info I have is (some guesswork from a previous explorer of this issue) that the lat/long is converted to a decimal by:


Looking at my first record, I have:
Longitude 108.45.0 W -> apply magic formula -> expect to find 19575
Actual extracted data: 19575 (hurrah, it matches!)

Across the W Longs it seems happy and consistent... but then I hit some records with E Longs...
Longitude 27.28.0 E -> apply magic formula -> expect to find 4944
Actual extracted data: 27824 (hmm, that's not good)

But wait... there's more. That vast majority of records have their second set to 0. I've found one record so far that shows seconds.
Latitude 44.38.20 N -> apply magic formula -> expect to find 8054
Actual extracted data: 8035

This last one, however, I think I can explain. (m * 3) + (h * 3 * 60) would be 8034. The difference between expected and found is 1 -> seconds are 1 * 20 = 20 seconds. If the difference had been 2, the second would have been 2 * 20 = 40. This approach has been used elsewhere in the database. Unfortunately. I've not found any records with 40 seconds to confirm this but heyho, it seems to work. Because the vast majority of records have 0 seconds, it doesn't crop up as a problem very often.

My questions

Does anybody recognise what the hell is going on with this conversion? Perhaps it's a standard way to wedge DMS into 15 bits that everyone else in the world already knows about

Even if I confirm how the DMS is being turned into a decimal (well, integer it appears) how do I turn the extracted data back into DMS?

Any suggestions or pointers are welcome, this is really starting to bug me...

Sample Data to show what I'm working (or "failing") with

Longitude 108 45 0 W -> db says 19575
Longitude 105 20 0 W -> db says 18960
Longitude 79 28 0 W -> db says 14304

Longitude 126 54 0 E -> db says 9926 (expected 22842)
Longitude 125 54 0 E -> db says 10106 (expected 22662)
Longitude 27 28 0 E -> db says 27824 (expected 27824)

Latitude 39 2 0 N -> db says 7026
Latitude 44 38 20 N -> db says 8035 (expected 8054)
Latitude 46 13 0 N -> db says 8319

Latitude 12 11 20 S -> db says 30574 (expected 2213)
Latitude 11 40 20 S -> db says 30667 (expected 2120)

https://www.fcc.gov/media/radio/dms-decimal

I had a look at that. Whatever this programmer was doing, that doesn't appear to be it, unless there's some 16bit half-float voodoo going on (which might well be the case).

It's a custom database, written about 25 years ago, in C, for DOS. I'm impressed by how much info he managed to cram into a mere(!) 107 bytes per record.

Might be time to investigate some floating points!

For those still following the saga, the calc for converting the db entry to a decimal degree is:

data = 19575
data * 20 / 3600 = dd
dd = 108.75

To turn this into Degree Minute Second ("DSM") notation, it's bit more finicky:

dd = 108.75

(i) Round down the number to get the whole degrees: Whole Degrees = 108, remainder = 0.75
(ii) Multiply the remainder by 60, then round down this new answer: Whole Minutes = 45, remainder = 0
(iii) Multiply this new remainder by 60, then round down: Whole seconds = 0

This gives us 108 45 0 W (the W is from a flag set in the original data)

So, in summary:
data = 19575
decimal degrees = 108.75
degree second minute = 108 45 0 W (which agrees with the information shown in the program that owns the data file)

There are still two major issues presenting.
(i) There appear to be some occasional rounding issues with some coordinates which mean some records are showing up to 1 minute difference from the program.
(ii) This all goes completely wrong when the direction is changed. The values for E or S just don't work. Clearly something needs to be changed but no idea what because I suxor at math

In your first equation, multiplying by 20 and then dividing by 3600 is just dividing by 180.  This makes sense, because if you are breaking your minutes into thirds (0, 20 or 40 minutes) then there are 180 segments in each degree.  Your rounding errors are occurring because 1/3 (and by extension both 60 and 180) can't be perfectly represented in a finite number of decimal digits (or binary, which is what the computer is using).  Doing so multiple times with rounding results in errors piling up.  So don't use floating point math, but instead use integer math.

 If you have a modulus function available, you can take your original data and do an integer division by 180 to get your degrees, and a modulus of 180 to get the remainder.  From the remainder, an integer division by 3 will give you your minutes, and a modulus of 3 will give you 1/3 of a minute, which you then multiply by 20 to get your seconds.

 If you can't directly do integer modulus, you can find the modulus in each equation by taking the rounded result of the division, multiplying it by the divisor, and then subtracting that from the original value to get the remainder.

Now for the screwy part.  S lattitude is "sort of" using a "complement" function (in math, using a complementary number system allows negative numbers to be represented without a negative sign, and allows negative numbers to be added to positive numbers.  In binary, negative numbers are typically represented in what is known as "twos complement.").  In this case, if you take the numbers generated by the system for your S latitudes, you'll notice that you generate  latitudes outside the normal 90 degree latitude - this is what you expect in a complementary system.  For a normal "complementary" system, if you add your negative and positive numbers together, you'd get a number that is exactly one "unit" outside of your normal range.  In this case, you "sort of" get this, but not quite.

Adding up your starting (southern) latitudes and your generated latitudes in degrees, minutes, and seconds, you get 181 degrees, 62 minutes, and 40 seconds.  At the same time, adding the database values and your generated values together add up to be 32787.  The largest latitude value should be 90 degrees, or database value 16200.  If your DB entry is greater than 16200, you can subtract it from 32787 and use the resulting number to calculate your latitude, which will be S.  Alternatively , if you have a S latitude, you can generate an interim database value, which you subtract from 32787 to get your final db entry value.  I haven't looked at longitude yet, but your values are a bit screwy.

Update:  OK, Longitude actually works the same way, which is good/bad.  If you look at the expected and db values for your East longitude numbers (excluding E 27d 28m, for which both expected and db are the same, I expect a typo), adding them together still runs to 32787.  This is good, because it indicates that you can calculate an East longitude from the db value and vice versa as in latitude above.  It is bad, however, because your East and West values overlap (your range would go up to 32400 for 180 degrees), meaning that you can't determine whether it is east or west just by looking at the db value, which is the whole point of complementary numbers.  You'll need to use the flag from the database to determine whether to modify your db value to correct for East longitude or not.

Mike

First of all, while I maintain a fairly strict rule about not kissing random strangers, I've put your name on the waiting list for an exemption. Thank you for your reply, it's resolved a few issues for me.

I would like to clarify something, though:


The issue I have with this, is that I only know that (sorta-almost-correct-ish) number because I have manually calculated it from the appropriate screen in the original program using Excel and the Mark II Typing Finger - which I did purely to check what numbers I should expect to find in the database. It's not practical to do this for the 20,000+ records involved

In practice I won't have 2194 and 30574, I will just have 30574 (and a flag to indicate if it's N or S). I need to work out how 30574 turns into 12.18889 (which will give me the number I need to store in a new db as a decimal degree, and have a number to plug into your very helpful explanation above for DMS).

So, for example (and to stop me having to scroll up to the top of the screen every few moments )

db says 30574 (and the S flag is set)

db says 9926 (and the E flag is set)

What are the steps (described for idiots, as that is clearly what I am ) that I am taking from these points to reach their respective decimal degrees?

Based on the numbers you gave from the database, your N latitudes essentially count up from 0d:0m:0s at 0, then every 20 seconds is one, so 0d:0m:20s would be 1, 0d:1m:0s would be 3, and 1d0m0s would be 180.

By the same token, your S latitudes count DOWN from 32787 in the same manner, so the following steps should do it.

Step 1:  Set A to be your database entry,  and SFlag to be your flag showing whether that entry is a South latitude

Step 2:  If SFlag = 1 then let A = (32787 - A)     ->this converts your db index for South into a "normal" db entry, where 0 = 0d0m0s.  If it is North, then you don't have to convert anything.

Step 3:  Latitude = A/180                                    ->this will be your decimal latitude.  You will then have to reference the flag to determine whether it is North or South

For Longitude, it works the same way.  According to the numbers you posted, you are also counting down for E longitudes, again starting at 0d:0m:0s with a db value of 32787.  So you would use the same steps as above, except that you check the flag to indicate E longitude instead of S.  If you don't want a separate designation showing N/S and E/W, you COULD simply add step 4 to make your latitude/longitude negative if the appropriate flag was set in the database.  Heck, if you want to do that, you can do it in step 2 by using (A - 32787)  instead of (32787 - A), doing your conversion and negation in one step.  This would also allow you to avoid having 2 flags (S and E) for each coordinate.

Mike

Ahhh, so 32787 can be treated a constant for these purposes, not a number that you need to somehow derive from the db value? That was the bit that confused me.

Thanks to your help and the miracle of the mod() function, I now have an Excel spreadsheet and a PHP page that are spitting out numbers much closer to what I expect and in many cases match exactly but... and there's always a but... there is some variation, in some cases up to an entire degree which is 70-ish miles difference

For example:

DB says: (W) 12979
Excel says: 72 6 20
PHP says: 71 5 40
Original program shows: 71 5 40

DB says: (E) 5587
Excel says: 151 6 40
PHP says: 151 6 40
Original program shows: 151 0 20

The person who originally entered the data would have been dealing with the DSM as it appeared on screen, so I have to work on the basis that this is *the* definitive lat/long that they wanted to be recorded.

Using Excel might be muddying the waters a bit, but it's quick and handy for checking numbers. I will actually be using PHP to extract the data and push it into a new database, but several miles of deviation really aren't acceptable

There is another option I might be able to explore, however. Off to tinker some more

What is it set up for?

0 to 360?

180 E to 180 W? Sorta sound like this with E/W held in another field.

You also have to factor in what range YOU  want the output in.

If it is a large database you might even encounter entry errors.

The reason your numbers are off slightly (for E and S I presume) is that I goofed.  The actual "zero point" for S and E is 32768, not 32787.  I must have mistyped it, or possibly made the calculation using one of the numbers that you generated in your first post, rather than the actual db value.

As for that example with excel disagreeing with PHP and the original program (12979 as your db value), I think you made a typo entering that.  12979 generates the value shown in Excel, but if you switch the 9s and 7s, you get 12797, which generates the program and PHP values.  I bet you copied the value for the post out of your spreadsheet, didn't you?

Mike

The original program basically uses a signed integer - 16 bits with 1 bit it acting as the flag. It displays as 180E-180W.

What I want to do is display in exactly the same way, while retaining the ability to search by distance (ie within 30 miles) and plug locations into googlemaps.

In terms of "databases" it's tiny. In terms of being faced with manually typing in all the information, it seemed bigger than the known universe.

My solution was a little different to the original plans. I tracked down a PDF created from a text file containing a limited data dump from the original program author It doesn't hold everything but it does have the record number and the DSM for lat/long. So, converted the PDF to XML and added a routine to the export thingy so that, when it hits the appropriate record, it completely ignores the DB value, pulls the DSM from the second XML, and inserts this as text into my new sparkly XML file that will later be used to populate a new DB. It also creates a digital decimal from the DSM and inserts that as a separate field.

The likelihood of anyone anywhere actually caring if the accuracy is out by a few miles is... essentially non-existent. This is purely to satisfy my own obsessive behaviour.

Hmm. Thankfully I've sidestepped the problem for the moment, but this is useful to note for the future.



Heh

We used ddd.mmssssssss... and generally 0-360 to avoid E-W problems. The seconds section is whole seconds and then decimal seconds all strung together.

It was compact and easy to write SW to deal with while not wasting excessive spacwe.

ddd from 0 to 359 whole numbers
mm is limited to 0-59 whole numbers.
ssssss...must be <60.

No sign bit is used.

This type of definition was in the notes at the front of the SW and then a reference back to those same notes down at the use location in the SW.

We did not duplicate the whole thing since that would produce two places the definition was presented that could (from changes) be incompatible.

It was a carry over of the drafting standard of never having duplicate dimensions in a drawing, or a chain of dimensions and the sum of that chain present.