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 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
The seasons.
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.
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.
Question: Because the deltas computes them from the bottom up and not the other way around?
Excellent template, I have generated a macro directly in Microstation that does exactly the same to me and exports it to a txt file.
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
Check your mail, in the spam.
The trip link could go there.
regards
Excellent, it's very useful !! But I just made the contribution and did not download the template ..
regards
Hi, good afternoon, will you have an article or examples of the scale factor calculation? Ie the application and importance, greetings from Mexico
Excellent, with this guide I was able to create an excel sheet that calculates directions, thanks for sharing this valuable information.
Greetings from Nicaragua
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.
Greetings .. I hope your answer if possible ..
Area of a polygon using the coordinates of its vertices: http://play.kendincos.com/155382/Wvznrfjptptvzvzrv-area-de-un-poligono-usando-las-coordenadas-de-sus-verti.html
Friends, reading your questions about topographic calculations, generation of "tables" of coordinates and labeling of directions and distances in Autocad, I recommend using the CIVILCAD tool, developed in Mexico, this module converts autocad into a tool for topographic calculations, profiles , sections, contour lines, etc., etc.
Regarding your questions from UTM to flat coordinates, all GPS processing 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 of coordinates, all they need is at least a couple or more coordinates in UTM and in “flat” coordinates (i.e. the coordinates they want to use with the total station) and the programs will calculate the scale factors and information needed they need, you just have to read the corresponding manuals of their equipment and plan the data collection well. Greetings from Mexico City. my email is Gilberto1@sitg.com.mx.
Adjustments to the table would have to be made, but it could well be. If you send us an example we can try.
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 already have this table. Thank you!!
Teacher, and for the reverse process?
From directions to geographic coordinates?
Thank you very much
You are right, it happens that the bandwidth of geofumadas.com, which is where they are hosted files and images of this blog was exceeded. I'll have to go up the width, try later.
How about, try to download the excel file but mark me that it is not available, I could help with this.
regards
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.
I have UTM coordinates as I do to reduce them to topographic cooperations.
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.
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
I think you're confusing the procedure, if you do not have utm coordinates, you do not have to look for them. You must interpret what your sketch of the uprising says, there is a starting 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.
The file is not for that, it is to calculate the box of bearings based on points with coordinates
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
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
Hello 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 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
http://geofumadas.com/construir-curvas-de-nivel-usando-autocad/
Then to add the elevations it configures a point form in which when entering this you can give the elevation.
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 traffic and level to UTM in excel, I have a defined axis and my starting point 0 + 000 I lift it with GPS to be able to georeference it ... how do I calculate the POI points of my axis and the sections to UTM
I'm from Honduras
Jcpescotosb@hotmail.com
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
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:
1. You press the “MENU” button
2. Select “Map Units”
3. Select "Coordinate system" and select UTM
4. You select “Datum” and here you indicate WGS84
They are small, they are brand promark 2, in the city of mexico zone 14, I would appreciate any information that you can provide.
… Ahem, ahem… 🙂
And what kind of gps, make and model do they use?
What country and area are you in?
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.
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
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 would like 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" it seems to me that 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
if you mean a plot measured in an area where the area changes ... it won't work for you.
If you mean having those coordinates in another utm zone, there should not be any change because you're supposed to be talking about the same latitude, so the coordinates are equivalent
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
I already sent it to you by mail
greetings.
Mmm, send me the script that you mention in the mail that you have, to review it.
I'll see what's over there
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 AutoCAD, having drawn the georeferenced polygon, with its known UTM vertices, what I need is a marker for the segments, so that clicking on them gives them their corresponding "label", for example: 176.35
OK, I've already uploaded an 2 version of the excel file. The reason was that in the NE and SE quadrants, 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.
Mmm, you're right, I think I know how to solve it
I'll check it when I get back to my house.
Esteemed teacher, first of all thank you for your quick response, I really arrive on time!
I tell you that it helped me a lot, I was testing it, although I would like to express a doubt/suggestion/concern: it calculates the distance very well, now the angle gives me the complement, for example instead of being 88d13'13”, it gives me 1d21'47”!!! It has to be a small adjustment and here we are! Thank you so much again for your help!!!