2

I am very new to GIS.

I have a dataset in Excel containing XY coordinates of points. These coordinates are said to be in WSG84, but have different decimal places lengths. Longest of them have 9 decimal places. They have 3 digits before decimal points. So a 9 digit coordinate is XXX.YYY.ZZZ or a 6 digit one is XXX.YYY. I want them all to be in decimal coordinate system. Is there way that I can do this automatically in Excel?

When I try to convert the data to XY points, ArcMap disregards the decimals even though both the shape layer and the points layer are in WSG84 format. Thus, drawing the points to a totally unrelated space.

Example Coordinates from the dataset;

362.935.938 339.309.883
363.010.152 3.396.492
404.244.154 264.695.215
40.63   26.83
41.128.471  29.145.429
41.305.968  286.615.491
411.788.295 291.018.963
4.102.785   2.906.803
4.112.674   2.915.039
41.019  28.571
413.621.671 285.677.379
  • 2
    can you show some example points, please. – Andreas Müller Apr 6 '16 at 12:59
  • @AndreasMüller I've added some examples to the original post. – krypt Apr 6 '16 at 16:50
  • Wow, that looks quiet messy, i've no idea! But as you say Excel, sometimes Excel formats cells automatically to something completely different, often as a result of regional and language settings. – Andreas Müller Apr 6 '16 at 18:16
  • 1
    The values in ArcMap's table just show integer values, so the periods in Excel represent thousands and millions. It's more likely that each value should have a decimal delimiter inserted after 2 digits, thus leading to eastern European sightings. – mkennedy Apr 6 '16 at 18:51
  • 1
    It should, but I know that sometimes there are problems. Are the values in degrees or some projected coordinate system? That is, are the values: 37,206435 36,353921? – mkennedy Apr 6 '16 at 19:54
1

For WGS84 data, many of your values are out of range (see WGS84 Spatial Info Page):

WGS84 Bounds: -180.0000, -90.0000, 180.0000, 90.0000

Projected Bounds: -180.0000, -90.0000, 180.0000, 90.0000

I have a feeling that somewhere along the way, your excel file became corrupted or when the points were created (I'm assuming via script), many of the decimals ended in the wrong place. Especially if these are supposed to be somewhat near each other. Looking at the data, it appears that most of the first two digits are either the same or similar, (36, 40, 41 for X, 26, 28, 29 or 33 for Y). Taking this into consideration, you may want to see what happens when you plot those first two digits (by manipulating decimals, I was able to plot many of these in the Middle East/Russia areas of Europe/Asia. For example, 362.935.938 339.309.883, becomes 36.293, 33.930. And, 4.112.674 2.915.039 becomes 41.126, 29.150 (I left the seconds alone for now, to just try to figure out if these points are in the ballpark of where they are expected).

You know the data better than we do, so if these look like they are starting to fall into the areas that you expect them to, you will need to troubleshoot your data source--likely a problem with a script, calculations or file corruption.

  • That's precisely the problem. Yes, if I manipulate the decimals by hand and rewrite the coordinates, the points seem to fall on accurate positions. However I doubt it's a corrupted file. Data has many columns and none of them show signs of corruption. Data comes in a txt file, in the tab seperated tsv format. Excel can directly open tsv files and show all the columns and rows. Data is an occurance report of a certain group of species in Turkey.Thus, all points should fall in Turkey, which they are if I rewrite them in XX.YYY.ZZZZ format. – krypt Apr 7 '16 at 6:37
  • I think I solved the problem. Apparently, it was indeed a problem with decimal separator. In my country the decimal separator is "," and thousands separator is ".". So, it is set like this in the operating system. Usings Excel's import data function, I can choose the separators for the columns. When I choose "." as the decimal separator, coordinates have 2 real digits like they should. But, I'm out of town and don't have access to ArcMap so I can't test plotting the points at the moment. – krypt Apr 7 '16 at 7:06

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.