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 the points
Diego tells us that he has a way of sending the 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 make them 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, the "export coordinates" tool is used, I create a "fence" that covers all the points and I 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 units format from “master units” which 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 a click on the screen, the system has created the txt file and has created a number for each of the points, from 1 to 36.
2. Open the table from Excel
To open this file from Excel, go to “file/open” and choose the type of file “text file, .prn .csv .txt” Then, in the panel that appears, select that the text is separated by commas. Finally the file has been opened with three columns, in the first 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 the 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. Remember 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 is only done in Excel dividing column L by column P. We also do the calculation of the breast, which will be to divide the Delta Y by the hypotenuse (M by 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: if the cosine is positive, write E, if the cosine is negative, write W. The formula looks like this: =SI(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 sine; that is, if the sine is positive, write N, if it is negative, write S and the formula would be like this: =SI(R2<0,”W”,”E”)… is in column U
Now let's remember that the previously calculated angle 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 90 degrees away, so what we do is create the condition that if the cosine is negative, subtract 90 and in the NE and SE quadrants we take 90 minus the angle ... in column V
Column V shows the angle, but in decimal format. To convert the decimals to degrees, minutes and seconds, what we do is truncate it with zero decimal places, as it is in column W. To Calculate the minutes, we subtract the complete degrees minus the truncated degrees and multiply them by 60. Then we truncate them with zero decimal places as it appears in column Y. In case of them seconds, the minutes are subtracted minus the truncated minutes, and multiplied by 60. Finally, the seconds are truncated to two decimal places ... be careful, if the UTM coordinates that were used did not have more than two decimal places, the decimal value of the seconds will not be very exact so it would be better to leave them to a decimal.
5. Creating a table of directions and distances
For this I have used the concatenate formula, so I add the cell that has the number 1, then space hyphen space, and then the cell with the number 2; so I have the form of stations “1 – 2”
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 Days, The Excel file and the file txt.
So that Here is the file in Excel with which you can create the heading and distance table from sequential UTM coordinates. To add points, you can copy columns and insert them, it is better because this way you guarantee the formulas, seeking to respect the first and last row. You must also copy the data from the first point to 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.