I'm currently working on an internship at my university creating home range maps with squirrel data that's been collected in one of my classes. I've been having some issues with some of the data; when I import the data from Excel into ArcMap and project the points to WGS 1984, sometimes it will show up either in Europe or South America instead of on my campus. I've had multiple GIS teachers here try and help me out with this issue and nobody has been able to tell me why this is happening. I've even used the "define projection" tool to try and get the layers on the same projection and even that hasn't helped.
If anyone has any ideas on why this is happening, I could really use some advice.
Thanks in advanced!
From looking at the data, the 181 points need to have longitude as negative values. Another thing I notice is that these sets of points are from distinct areas - are they supposed to be? 43.45, -70.38 typical in the 134 file, 43.27, -70.23 typical in the 181 file.
I think at least some of the 181 data is in DD.MMSSs aka packed degrees minutes seconds. If I convert the top row, I get values are closer to the 134 data.
Or it could be degrees-minutes, DD.MMmmm.
43.27566 = 43 27 56.6 or 43 27.566
which converts to:
43.465722 or 43.459433
(edited: to include DD.MMmmm option and example.)
This is almost certainly NOT the most efficient method. Hopefully, someone else will take pity on me and post a better solution!
The Convert Coordinate Notation function will take packed DMS, but wants it as DDMMSS.sss where this data might be DD.MMSSs. So.
1. Create 2 new fields in the csv file. I used lat_2 (column E) and lon_2 (F).
2. In the new latitude column, 2nd row, type =E2*10000.
3. Copy E2 and paste to the rest of the cells in the columns.
4. In the new longitude column, 2nd row, type =F2*(-10000).
5. Repeat step 3 but using F2.
6. In ArcMap, open the ArcToolbox/geoprocessing window. Search or browse to Data Management toolbox, Projections and Transformations toolset, Convert Coordinate Notation tool.
7. In the tool, use the following settings (change output shapefile name, fields, etc.)
The most important ones are that you want DMS_2 and DD_2 for the input and output formats, respectively. That's it! This will create a new shapefile (or you could give a geodatabase/feature class location instead).
Brian you would have to check the 181.csv it appears to be inconsistent with 131 as there is lot of variation in both latitude and longitude values.
131 is in close proximity with University of New England at Biddeford.
You may have to check the field data collection method.
Can I ask what coordinate system the points from excel are in? Ie/ are they Lat/Long values or UTM coordinates? It is very important to know what system the coordinates were recorded as. If they are Lat/Long, try to make sure that all points are in the same format ie/ decimal degree. Also make sure that (assuming you are in the US) the longitude value is negative and the latitude positive. After you've made sure of these and at least some of the points don't line up then you don't have the correct information for how they were entered into excel. If some points line up where they should be and some don't, that is an error where different points were recorded differently (ie/ decimal degree compared to decimal minute) but entered as if they were recorded the same.
The first thing I would say is to know what a typical coordinate is for your study area (from other data or google earth). This is a quick way to see if your data makes sense in excel.