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
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.