# Create a box of bearings and distances from UTM coordinates

This post is in response to Diego, from Paraguay who makes us the next question:

A pleasure to greet him ... some time ago for a search that I had arrived without wanting to his web and I found it interesantisima, as much for the contendio as for the pleasant mode of communicating his science. I would like to ask him, if he knows some script, or some frame Excel to help me do the following: I have digitized in CAD a polygon with its vertices points well identified, and with their respective coordinates UTM. I can perfectly export them to txt to read them in excel.My question: knowing the UTM data of the points 1 ... N, it is possible to obtain the data of the stations, directions and distances? that is, from the data that I provide , point 1 has X ... of Y ..., and knowing that point 2 has X ... Y ...; CAN YOU TELL ME THE DISTANCE THAT SEPARATES THEM AND THE ANGLE OF THE SAME? In order to be able to prepare the correspondent form automatically? Thanks ... best regards from Paraguay!

Well, what Diego wants is from UTM coordinates Generate the box of courses and distances ... and as Easter ended, I have eaten fish cake, turjas in honey and I have taken some good pictures of sawdust carpets ... here is the answer. First let's recognize that the best way to do this is with the right tool (It can be with the Macro Vba of Microstation, with Softdesk or AutoCAD Civil 3D) but for learning purposes, and understanding that in life you have to use what you have, let's see how to do it with Excel.

### 1 Generate points

Diego tells us that he has a way of sending points to a txt file, to open it with Excel, so in my case I will do it through Microstation. Because I want to send the data to Excel, I will place points on each vertex. To be visible, I have changed the line thickness, and it is very important to understand that Microstation will send them to the txt file in the order they are created, so it is necessary to create them consecutively.

To send them to Excel, use the "export coordinates" tool, create a "fence" that covers all the points and configure the data:

• I assign the name of the txt file as test444.txt
• I indicated that the order that interests me is XYZ
• The format of units from "master units" that is in meters
• I indicate that I only want two decimals
• Then the comma separator and the numbering from 1

By clicking on the "fence" button and clicking on the screen, the system created the txt file and created a number for each of the points, from the 1 to the 36.

### 2. Open the table from Excel

To open this file from Excel, it becomes "file / open" and choose the type of files "text file, .prn .csv .txt" Then in the panel that appears, it is selected that the text is separated by commas. Finally the file has been opened with three columns, in the first there are the point numbers, in the second the X coordinate and in the third the y coordinate.

### 3. Calculate Distance

Let's go back to the basic rules of trigonometry first. Remember that we are looking for a distance and an angle.

A = differential in the Y coordinates (subtracting x2 - x1), in the column Mb = differential of the X coordinates (subtracting y2 - y1), in column Lc = hypotenuse that will be the square root of b square plus To square, in column P and this Will be the value of the distance.

### 4. Course calculation

Now, for the course we need to do several calculations; But all leave from the angle between a station and the consecutive one. Calculation of the angle. Recall that the cosine Of the angle is equivalent to dividing b between c, or the delta x between the distance calculated as hypotenuse.

So it's only done in Excel by dividing column L between column P. We also do the calculation of the breast, which will divide the Delta Y between the hypotenuse (M between P). Now for Calculate the angle, We only apply inverse cosine to the column that contains it and as Excel uses radians, multiply the value by 180 and divide it between PI; The formula would look like this: = ACOS (column R) * 180 / PI ().

Now to calculate the East / West orientation We assign a condition: that if the cosine is positive, write E, if the cosine is negative, write W. The formula looks like this: = YES (R2 <0, »W», »E»)… is in column T To calculate the North / South orientation, we assign a condition similar to the previous one, but with the breast; that is, if the sine is positive, write N, if it is negative, write S and the formula would be like this: = YES (R2 <0, »W», »E»)… is in column U

Now remember that the angle previously calculated is from the horizontal, in the east and what we need is with respect to the north or south. So in the case of the NW and SW quadrants we take away 90 degrees, so what we do is create the condition that if the cosine is negative, we subtract 90 and in the NE and SE quadrants we take subtract 90 minus the angle ... in column V

Column V shows the angle, but in decimal format. To convert decimals to degrees, minutes and seconds what we do is truncate it with zero decimals, as it is in the column W. Calculate the minutes, we subtract the complete degrees minus the truncated degrees and multiply them by 60. Then we truncate them with zero decimals as it appears in column Y. In case of them seconds, subtract the minutes minus the truncated minutes, and multiply by 60. Finally the seconds are truncated to two decimals ... eye, if the UTM coordinates that were used did not have more than two decimals, the value of decimals of the seconds will not be very exact, so it would be better to leave them in a decimal.

### 5. Creating a table of directions and distances

The seasons.

For this I used the concatenated formula, so I add the cell that has the number 1, then space hyphen space, and then the cell with the number 2; this way I have left in the form of «1 - 2» stations

The distances. These come from the hypotenuse column.

The course. This only requires bringing the calculated value in the columns previously calculated, and for the cell to add the degree symbol, minute or second is created in the properties of the cell as shown in the image. Additionally I have added a column of observations, because in topographic surveys it is usually required. From here you can download the file in format dwg, in format Dgn, The Excel file and the file txt.

So that Here is the file in Excel with which you can create the course of bearings and distances from sequential UTM coordinates. To add points, you can copy columns and insert them, it is better that way you guarantee the formulas, seeking to respect the first and last row. You must also copy the data of the first point at the end of the first, so that the last station is calculated well.

Here you can download the template to create a box of bearings and distances from UTM coordinates.

It requires a symbolic contribution for the download, which you can do with PayPal or credit card.

It is symbolic if one considers the utility it provides and the ease with which it can be acquired.

Learn how to make this and other templates in the Excel-CAD-GIS cheat course.

## 41 Replies to "Create a course of bearings and distances from UTM coordinates"

1. Jean castro says:

There is a tool called Calculo_Topográfico.exe, which automates the process of converting utm coordinates to a spreadsheet, including angles, directions and distances, as well as other useful tools such as finding the probable error of the topographic survey, either by angle or by distance, it reduces a polygon to the area searched by modifying a side, or an angle, ideal for dismemberments, and above all it creates the drawing in autocad, allowing you to copy and paste the path (s) of the polygons with the that you are working; It also exports the data obtained to the formats of the total stations, in order to upload (or download) the information.

2. Danger says:

Question: Because the deltas computes them from the bottom up and not the other way around?

3. Roberto G. says:

Excellent template, I have generated a macro directly in Microstation that does exactly the same to me and exports it to a txt file.

4. I really have been very satisfied and at the same time very sorry for not being able to download the application, since I can not make the symbolic contribution, because I am not currently producing? in my profession as a surveyor and sincerely I am interested in those information to expand and strengthen my profession, if you can do me a favor to send it to me I will thank you very much
Samana Dominican Republic

5. Check your mail, in the spam.
The trip link could go there.

regards

6. Alberto Velázquez says:

Excellent, it's very useful !! But I just made the contribution and did not download the template ..
regards

7. Josue Olvera says:

Hello good afternoon will you have an article or examples of the calculation of the scale factor? that is the application and importance, greetings from Mexico

8. Leopold says:

Excellent, with this guide I was able to create an excel sheet that calculates directions, thanks for sharing this valuable information.

Greetings from Nicaragua

9. Juan Reyes OPorta says:

Hello, how are you? I hope that well, I want to see if you can help me as you calculate oh distances between two points and their coordinates with coordinates Oh rather with latitudes and longitudes because I can do it with the utm x coordinates and with that I have no problems but I can not do it with This other type of coordinates they show on Google Earth maps.

Friends, reading your questions about topographic calculations, generation of “tables” of coordinates and labeling of bearings and distances in Autocad, I recommend using the CIVILCAD tool, developed in Mexico, this module makes autocad a tool of topographic calculations, profiles , sections, contours, etc., etc.
Regarding your flat UTM coordinate questions, all GPS process programs (GNSS solutions for promarks, Ashtech solutions for Promark 2, Spectra Precision Survey Office for GPS Epoch 10 and 50, etc.) have transformation routines between systems coordinates, all they need is at least one or more coordinates in UTM and in "flat" coordinates (that is, the coordinates they wish to use with the total station) and the programs will calculate the scale factors and the necessary information they need, just read the corresponding manuals of their equipment and plan well the data collection. Greetings from Mexico City. My email is Gilberto1@sitg.com.mx.

11. It would be necessary to make adjustments to the table, but it could well. If you send us an example we can try.

12. MARYFER says:

Excellent contribution. I already have my table ready. I would like to know if you could help me to get the deflection angles with the data that is already available from this same table. Thank you!!

13. JS says:

Teacher, and for the reverse process?
From directions to geographic coordinates?
Thank you very much

14. You're right, it happens that the bandwidth of geofumadas.com, which is where files and images from this blog are hosted, was exceeded. I will have to raise the width, try again later.

15. GUILLERMO says:

How about, try to download the excel file but mark me that it is not available, I could help with this.

regards

16. You should be more specific to what you mean by topographical coordinates.

Did you just keep saying geographic as well as degrees, minutes, seconds? For that you can use This tool.

If you did not refer to that, see if you give us an example to help you.

17. JUANK says:

I have UTM coordinates as I do to reduce them to topographic cooperations.

18. The most practical thing is for you to calculate the area in AutoCAD, what you are looking for is not only to calculate the area but to verify the closure, maybe there is a template there that does what you want but I do not know it.

19. Mabel says:

The page is very good. I would like to know how to calculate (in Excel) the area resulting from the polygon generated from loading the sides and bearings obtained in the field. I am a draftsman and I use the Autocad program, combining it with Excel tables.
I deeply appreciate your interest in responding to my query.
Best regards from Asuncion of Paraguay
Mabel

20. I think you are confusing the procedure, if you don't have utm coordinates, you don't have to look for them. You must interpret what your survey sketch says, there is an initial point, if it does not say coordinate place it anywhere, then see if there is a course and distance to go to the next point.

21. The file is not for that, it is to calculate the box of bearings based on points with coordinates

22. Jcpescotosb says:

Maestro Alvarez, this file calculates UTM coordinates from a cross section raised with traffic and level is wonderful but I still can not understand it

23. Jcpescotosb says:

Look a friend from Peru sent me a template with the query but I can not understand that UTM placed at the beginning between each PI, as the file attachment

24. Hi Jcp, I understand that you have a central line, with stations and each station there are perpendicular to the right and to the left. I think that the most appropriate thing is that you do not try to get the utm coordinate, but make circles from the center, placing the point at the intersection of the circle and the line.

This would make you have something similar to this exercise

Then to add the elevations it configures a point form in which when entering this you can give the elevation.

25. Jcpescotosb says:

Dear Mr. Alvarez I am using civil 3d 2009, I enter UTM points in the .cvs format but now I have to convert the cross sections raised with transit and level to UTM in excel, I have a defined axis and my starting point 0 + 000 raise it with GPS to be able to georeference ... how I calculate the points of the PI of my axis and the sections to UTM

I'm from Honduras

Jcpescotosb@hotmail.com

26. the the says:

In fact, the coordinates I already have in utm, but I want to reduce them to the topographical plane in order to work them in the field

27. Look, I have not used that Promark but I see it looks a lot like the Mobile Mapper ever from Thales.

In this I did so:

2 Select «Map Units»
3 Select «Coordinate system» and select UTM
4 You select «Datum» and here you indicate WGS84

28. the the says:

They are small, they are brand promark 2, in the city of mexico zone 14, I would appreciate any information that you can provide.

29. ... eg, example ...

And what kind of gps, make and model do they use?
What country and area are you in?

30. the the says:

Dear alvarez, first of all a greeting, I noticed that your knowledge in topography and software are advanced, maybe you can help me, the company where I work, GPS, and I am interested in how I can adjust the UTM coordinates, coordinates TOPOGRAPHY OR PLANAS, for field management, I have searched for a lot of information and I would appreciate your response.

31. Paul, your appreciation right, I have already done the correction of writing, is column L between column P

The other problem you raise, I think you failed to complete the comment to be able to analyze it

32. PABLO SAKAMOTO says:

I followed the instructions step by step. Theoretically I understood, but in practice I have not been able to achieve good results.
Mae seems like I'm doing something wrong. also in the configuration of the units of Autocad len the part of the angles and direction I am not very clear.
I want to suggest that you review the part of the angles I think there is an error, where it says "dividing column N by column P" I think it is column L by column P.
Another error of writing also appears in the calculation of the North / South orientations, the formula says: would be: = SI (R2

33. If you mean a plot measured in an area where the area changes ... it will not suit you.

If you mean having those coordinates in another utm zone, there shouldn't be any change because you are supposed to be talking about the same latitude, so the coordinates are equivalent

34. Juan says:

Hello,

I liked this customization in excell, I have a question

How does it apply when there is a change of spindle?

Thank you

Juan

35. Diego says:

I already sent it to you by mail
greetings.

36. Mmm, send me the script that you mention in the mail that you have, to review it.

I'll see what's over there

37. Diego says:

impeccable!!! Now go to 100%, I've used it and it's true! Thank you very much, I have been useful.

A question about it, since we are in automation processes. Using the AutoCAD, having the georeferenced polygon traced, with its known UTM vertices, what I need is a marker of the segments, so that when I click on them I will give them their corresponding “label”, for example: 176.35

38. ready, I have already uploaded an 2 version of the excel file. The reason was that in the quadrants NE and SE the angle to 90 had to be subtracted.

Give it a try and let me know.

You have to be clear, that the more decimals are used in the UTM coordinates, it will change the decimals of the seconds.

39. Mmm, you're right, I think I know how to solve it

I'll check it when I get back to my house.

40. Diego says:

Esteemed teacher, first of all thank you for your quick response, I really arrive on time!
I commented that it helped me a lot, I was testing it, although I would like to express a doubt / suggestion / concern: I calculate the distance very well, now the angle gives me the complement, for example instead of being 88d13â € ™ 13Â », I Da 1d21â € ™ 47Â »!!! It has to be a small adjustment and we are done! Thank you again for your help!

This site uses Akismet to reduce spam. Learn how your comment data is processed.