Importing GPS Coordinates from Excel into ArcMap

okay so in this love we're taking GPS units that we took out in the field with our GPS receivers and the goal of this lab is to learn how GPS measurements can vary over time and we're going to compute the relationship between longitude and latitude distances on the surface of the earth and in order to do this we're gonna have a bunch of formulas that we're gonna refer to in the lab manual or in the lab level assignment on the website and then at the end of this we're turning a map that shows the different locations of our points and then we're going to explain the formulas and have a bunch of formulas here so you can always refer to these formulas in order to figure out where where I'm getting all the formulas from so basically we're gonna have our Excel spreadsheets going to open up Excel and what we're going to want to do is make a bunch of columns one for measurements one for launched two degrees one for launch two minutes launch two seconds live two degrees latitude seconds and this is what I've collected out in the field I collected 25 points so I'm going to put here one two if you select both of them and then drag from the corner you can go ahead and fill it out to 25 and then after I'm going to go in here and I'm going to type in my degrees minutes and seconds that I calculated that I've collected and recorded by hand in the field so degrees minutes seconds and I'ma put in my latitudes and as you can see my collect my points I've collected we're very similar with very little change in the seconds some of you might have similar results and that would be because you had really good visibility the sky a little interference and so forth so the first thing I want to do is convert this into decimal degrees so I'm going to go here put longitude decimal degrees and the latitude decimal degrees and I'm going to use my formula which is equal to degrees I don't have anything to convert degrees because so ready and it's already in degree format so I'm just going to hit an equal sign and click it and hit plus and then longitude minutes there's you know there's 60 minutes in per degree so we're going to divide that by 60 and then we're gonna add that to seconds which there is 3,600 seconds per degree whenever we use that formula we're gonna get our degrees here one thing to be careful about is that I forgot to put here negative numbers because truly all my thing has said West which is negative so I'm going to make this whole thing negative so I end up with the correct numbers there we go negative 97 that's correct so that's my decimal degrees there and then my latitude I'm going to the same thing with degrees plus the minutes divided by 60 plus the latitude seconds divided by 3600 again if I click on this and click on this little corner here and double click will auto fill and fill it up there so now for us to find out what our position was is that we're going to take the average the average is and that's going to let us determine where we will work from our samples we took multiple measurements so we're going to click on equals and we're going to type average and you can see here the average formula returns the average arithmetic mean so we're going to choose that one and then we'll click the first one and then just keep holding down the mouse button and drag to select the whole code now that's going to give us the average again we can use our little mouse thing where we go to the corner to do the smart fill move over to one side and now we have to average here too before our error we're going to say standard deviation of one so standard deviation and here when I say equals to the standard deviation stdev and we're going to select our range again and we have our standard deviation so our standard deviation was really small but that's in degrees we're gonna have to convert that into meters one thing that we also want to put on there is the elevation that we were reading off of our of our GPS units so I'll put mine in there and I'm going to go ahead and take all the heights and paste them in there and then again I'm just going to take these averages and standard deviations and that's how are you at that with our Heights what was cool is that our standard deviation in height is already in meters so no conversions necessary there so now going back to our formulas we can see here in our excel formula so far we've used this convert decimal degree salata to degrees we've done that with this formula then we have here a formula to do the averages and standard deviation we've done that now in order to calculate and convert the standard deviations of decimal degrees and to meters we're going to have to compute a few variables and you can see here meters to compute meters a decimal degrees latitude we have a pretty standard formula here but whenever you want to start converting a calculating degrees of longitude is a little bit more complicated and that's where we're going to calculate the N and this n value and then we're going to use that M and M value to determine the relationship between longitude and latitude distances on the surface of earth so you can see here that we can find our change in longitude by taking this change in longitude here and multiplying it by the N value and then multiplied by cosine so far with the M and n so basically we're going to need four more spots we're going to need em in a de and a D in so go ahead and add those columns here m and de which is going to equal to our distance east and west and then DN which is an equal to our change in latitude and then for a change in height which I'm just called th here or vertical air that's fine we already know that that's here so that's gonna be our vertical yeah there okay so let's calculate in actually I'm also going to go ahead and put in my standard variables of a and yet I already know these numbers what they are so a Zen to be six thirty seven eight eight three plus r81 37.0 and es which is our first mystery squared it's going to be I'm just gonna yes okay so 0.006 six nine four three seven nine nine nine zero one four so basically let's go here and start calculating calculating M so M is going to equal to a so we just say equal sign here's a times open put the c1 minus es here's a yes click on that close parentheses divided by open parentheses open parentheses 1 minus es so go ahead and click on yes again times the sign and the number for this I we do latitude in radians so latitude in radians which we're going to do that by typing in the Radian command radians and then click on the lab the degrees of latitude which was that's our average then we're going to square that whole thing by doing the caret which is shift 6 push to then we're in a closed parentheses and then we're going to raise that to the cubic root which is also going to be squared for it and we're gonna do that by reason so that three and a half three halves close parentheses closed paren see and one more time one more time close Part C so there we get our in value now we want to calculate our n value so a so we're equals to we're going to click on a divide that by what do we have here from our worksheet I'm pulling this all from the from the worksheet here so in a divided by the square root okay so the square root which we're going to say here I think we can do SQ RT now as the formula the square root of one minus the es so click on yes times the sign again in radians with type 2 Radian command and then go ahead and click on the average for launch - this time Oh No if we're also doing light at sweets again so it sorry click on latitudes again and then close the parentheses close the parentheses and then take the whole thing shift 6 to make it squared and then close by to see one more time so it gets good so now we know our M and our n value which again are in values the radius of curvature and the prime vertical and the image radius of curvature and the prime meridian once we know those two things were actually starting to model the the geoid of the earth ok so then now we're going to be able to take our final equations here so so far now we've calculated n we've calculated M now we're moving on to calculate the change in east and west which is de and a change in latitude which is DN so you can see we can just take the change and multiply by n multiplied by cosine so de it is going to equal to the change unlaunchable deviation in radians so that's one thing that with Google type in radians and here times the in value so it is one times the cosine of latitude and radians so radians again the radians function just converts degrees into the radians value for an angle so ever and go here and we're going to click on this one and close the parentheses enter so you can see now we have 1.3 3 meters of error in our in our east-to-west about the value now we're going to do it for north to south this one's a little bit easier we just go and type in radians choose our angle which is this one and then that's going to be the standard deviation of latitude this time and then we're going to simply multiply that 1 by n and that's our error in latitude so our vertical error is equal to our standard deviation of our Heights that was easy one our horizontal air is a little bit more tricky here we're going to use the Pythagorean theorem and you can see here horizontal error is the square root of each of these two things squared and added to each other so we're just going to take this here and where's it equal to s Cube T square root of this number de squared plus DN squared and then we have our error our horizontal error of 1.6 meters so that we use Excel to calculate the geodetic relationship so you can see here that we have 1.2 meters vertical air 1.6 meters of horizontal errors now I'm asking you to go and make me a map at this of your points where they work you can do this by looking at this x and y here launch 2d do latitude DD copy this and paste it into its own sheet this is going to make your life a lot easier when you bring it into arcmap if you get this number of reference things happening whenever you paste click on this little bottom error and say paste only the values that's gonna like just to paste the values of your points here you can tick off any kind of parenthesis you have happening that's gonna make things a lot easier and then save your workbook so I'm gonna save this on my desktop I'm so much yes save great there's not enough free disk space free dis spacing and try again yet this is going to obviously hit my limit on my H Drive and so I end up having to contact I TS to maybe try to give me some more space Brandi if you're listening you should think about doing the same what I'm gonna double doing is dead then is I'm going to end up saving it on my I'm like best on my C Drive under the C workspace so I'm just gonna go to C and workspace and this was a fold that was created by arcmap so say here new folder and small GPS GPS safe good and then I'm going to open up I'm going to close this and open up part now so go ahead and open up our mouth so arcmap opened up now go ahead and increase that and what I'm going to do here is I'm going to add in my GPS Excel spreadsheet since I put on that second sheet I'm gonna click on sheet number two not sheet number one she number ones in that be where I calculated everything so I'm gonna soak up sheet number two but go here and I look at this I say open I can see I have my longitude and latitude all right there so now I can just simply go to right clicking my sheet and say display XY data it's and give me the options here longitude goes in X village it goes into Z the Y field for coordinate system I want to select the coordinate system that I was using when I collected my points I use to wgs84 so geographic coordinate system world wgs84 okay and okay and this is going to put my points on the sheet so you can see my points have been mapped onto the sheets now what I'm going to do is I'm going to add data and I'm going to add a basement up I'll go here and I'll choose imagery and you'll see imagery is added onto my base map I can go here and make my dots something brighter maybe a green or I can even make it a red I think a red might come out really nice and you can see my points from my GPS lab are there some points are actually on top of each other and I can zoom out and make a nice map and you can see I was in a very open area if I flip this over I can make a layout and go ahead and make a layout showing your points and of course with the appropriate map elements and then of course you need to talk about why you have a certain points you have think about what affects a GPS receiver whenever you're on the field open air thinking about open air and different kinds of electromagnetic waves that could affect your affect your readings

Channel: Moulay Anwar Sounny-Slitine

Views: 115,724

Rating: 4.7754388 out of 5

Keywords: ArcMap (Software), GIS, Excel, GPS

Id: OU8BVAxDDdg

Length: 19min 34sec (1174 seconds)

Published: Wed Jun 05 2013