I've got a data set that comes with coordinates, however they seem to have gotten scrambled somewhere along the way of data-collection, data-entering and data-export. For some of the places the actual coordinates are known. Now the goal is to use this to figure out how they got scrambled, in order to be able to reverse this.

Core question:

Which transformation has to be applied to data_lat and data_lon (below) to obtain coordinates in a known format.

The following table lists the approximate real coordinates (as decimal coordinates) and the scrambled coordinates I found in the data set. The latter have an (or several) unknown format(s).

real_latiude,   real_longitde,  data_lat,   data_lon
13.30214,       -14.22023,      1318136,    141347
13.31396,       -14.21844,      1318268,    1412561
13.34097,       -14.09441,      1320262,    1405442
,               ,               1316060,    1408182
13.39157,       -13.88993,      568980,     1484519
13.43406,       -14.6541,       537144,     1485106
13.47477,       -14.08452,      599042,     1489640
13.48308,       -16.50089,      337657,     1491175
13.48558,       -15.96453,      395687,     1491387
13.48643,       -16.54477,      333733,     1491481
13.49866,       -14.82873,      518529,     1491920
13.59012,       -15.13228,      485738,     1502171
13.60742,       -15.13525,      485367,     1504185
13.61425,       -15.16174,      482322,     1504335
13.64409,       -15.10624,      488472,     1508243

1/3 of the solution:

For only the first three entries, I was able to figure out that the data was entered in UTC format and then all-special characters where stripped. I.e. it corresponds to the real coordinate, i.e.:

1318136, 141347 => 13°18'13.6"N 14°13'47"W => 13.303, -14.230 (this is close enough to the real_* coordinates)

Possible starting points

We have reasons to believe that the data was manually entered in Excel. So one possible thing could be that Excel interpreted the entered figures as dates somehow and save it's own weird date/time format. I was however so far not able to figure out how that could've happened.


I will set a bounty to this question (as many as I might have by then), as soon as I'm allowed to set a bounty.

  • Take a look at the article from Microsoft How To Convert Decimal Degrees to Degrees Minutes Seconds or this article from Extend Excel or this video from Youtube – MDHald Sep 3 '15 at 13:57
  • 1
    My problem is not about converting the decimal format to degrees minutes seconds... It's about getting these coordinates into any sensible coordinate-format where they correspond to the real_*-coordinates – sheß Sep 3 '15 at 13:59
  • 1
    There's no coherent question here. It seems like you've figured out the problem, perhaps you just need a way to implement it to these data to check your interpration, and that using these data lon/lat pairs translate to the decimal ones? – mdsumner Sep 3 '15 at 14:32
  • Maybe I'm not phrasing it clear enough. Bit I did not figure out anything aside from how to fix the first three entries – sheß Sep 3 '15 at 14:35
  • I think I have clarified my question. Also, @bugmenot123 seemed to have understood what I was asking. Is there anything else still unclear about the question? – sheß Sep 3 '15 at 18:19

No idea what happened with the first three lines but the other lines are coordinates in UTM zone 28N. data_lat is X, data_lon is Y.

I noticed that simply plotting each pair as X and Y points looked very similar, so I gave it a try on http://projfinder.com with success.

  • Great, that worked in deed and I was able to convert all my coordinates. In two days I will set the bounty to give you 100 of my reps – sheß Sep 3 '15 at 15:59
  • 5
    Oh, no need. This was a fun distraction. – bugmenot123 Sep 4 '15 at 10:03
  • Maybe the people that collected the data played with the settings of the GPS unit while collecting. – AndreJ Sep 7 '15 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.