help importing coordinates

[francescarusso]

Hi All,

I am struggling with something related to coordinates;

I have been given some coordinates for a cable: they are in excel but I had to modify a bit it as it was with blanks rows and things like that.

the original format of each cell is :

latitude: N32 28.5701

longitude : E034 53.1880

so in order to import all the coordinates into GIS (ARCGIS 10.2.2 desktop), I modified a bit the cells to remove the letters "N", "E" and the spaces between the numbers.

It does not recognize the zero as first number so I managed to leave it but when I import it in GIS the "0" disappears anyway.

Now I think (but I might be wrong) that it is not a big deal it deletes the zero as to be honest all my coordinates give me lines linked each other so it should be allright.

The main problem is that who gave me the coordinates told me to assume they are in WGS84. Well they do not appear on the right place of the world (They should be in Mediterranean sea) but they appear in Africa. (using ARCGIS Basemap)

I do not know whether this is a problem on how GIS reads the excel with the "0" or it is a projection problem.

These coordinates are for a quite big area so anyway I do not know which projection to give to them as they fall in different part of the Mediterranean sea.

Any suggestion will be much appreciated.

Thanks a lot,

F.

[JayantaPoddar]

Could you attach a sample Excel file so that we can review it?

Also use GCS_WGS_1984 (Geographic Coordinate System) as the coordinate system while adding the XY points to ArcMap.

[ChristopherMcClain]

Your original Data is in DD MM.ss format meaning the 1st group of numbers represent Degrees the second groups present Minutes with seconds shown as a decimal. you should convert the numbers to Decimal Degrees DD.mmss this will eliminate and spaces and prevent and translation errors.  Also make sure you indicate Longitude as the X Coordinate and Latitude as the Y coordinate.  Lastly check you data to make sure none of the Longitudes are W and Latitudes are S as these would require a leading '-' sign to indicate direction.

[francescarusso]

Hi hanks a lot. It sounds it is the problem. Do you know any formula I an use with excel to convert them?

Thanks

F

[francescarusso]

Hi it is really helpful but I do not know how to make the conversion and on internet there are too many different things.

I am trying to use this formula to get the DD:

Deg+min/60+sec/3600

which makes some sense to me but:

- before applying the formula I had to divide the cell into Deg, min and sec as the original value was in a singol cell; the points are plotted in the right area but they are just spreaded everywhere so it means there is something wrong.(they should be coordinates for points which will make a line...and now I am getting just random points in the area)

- how can I know check you data to make sure none of the Longitudes are W and Latitudes are S ?

thanks

F

[NeilAyres]

Your data is indeed in HDD MM.ss (degrees, minutes and decimal minutes), so it is a text string and should be imported as such. So I would mess with the format.

Just need to calculate the decimal degrees from this. This could be done in the spreadsheet using a bit of string manipulation or try to use :

Convert Coordinate Notation—Help | ArcGIS Desktop 

[MelitaKennedy]

Francesca, 

You need to confirm whether the data is DD MM.ssss or the more likely DD MM.mmmm as Neil mentions above. That simplifies the calculation: DD + MM.mmmm/60.0.

Melita

[francescarusso]

Hi Melita,

yes indeed it was the case of DD+MM.mmm/60. In fact now every thing seems in place. Thank you very much all for your help.

This forum is always very useful and all of you are always very kind.

Cheers,

Francesca

[francescarusso]

Neil,

thanks a lot for your help.

Cheers,

F